Image database & R2 storage — build plan

Goal

Establish a single canonical image catalog so that every image we generate (or upload) ends up in one queryable place with full metadata: who/what generated it, with which prompt, at what cost, where the bytes live, and what use-case it's for. After this lands, no image bypasses the catalog — that's the discipline.

Decisions already made (do not relitigate)

• Object storage: Cloudflare R2 (same edge as sponicgardens.com, zero egress).
• Database: SponicControl Supabase Postgres, new public.images table.
• Primary model: Azure OpenAI gpt-image-2. Fallback: Google gemini-2.5-flash-image-preview. (Already wired into config/project.config.ts.)
• Public URL: serve via custom domain images.sponicgardens.com bound to the R2 bucket.
• Wrapper location: apps/control/src/lib/image-gen.ts. Single function generateImage() owns the full lifecycle.
• Backfill: the 6 hero auditions in apps/garden/branding/hp-01-hp-hero-overview-v[23]-* get migrated as the first rows.

Schema

SQL migration to add to apps/control/migrations/20260504_images.sql (use the next sequential date):

create table public.images (
  id           uuid primary key default gen_random_uuid(),
  slug         text unique,                       -- stable handle, e.g. 'hp-01-hero-v3-1'
  storage_key  text not null,                     -- R2 key, e.g. 'hero/2026-05/hp-01-hero-v3-1.png'
  public_url   text,                              -- https://images.sponicgardens.com/...

  -- generation
  provider     text not null,                     -- 'azure-openai'|'google'|'human-upload'
  model        text,                              -- 'gpt-image-2' | null
  prompt       text,                              -- exact prompt
  size         text,                              -- '1536x1024'
  quality      text,                              -- 'low'|'medium'|'high'
  format       text,                              -- 'png'|'jpg'|'webp'
  cost_usd     numeric(8,4),                      -- computed at gen time

  -- file
  bytes        integer,
  width        integer,
  height       integer,
  sha256       text unique,                       -- dedup

  -- domain / classification
  category     text,                              -- broad bucket: 'brand'|'product'|'docs'|'team'|'event'|'space'|'food'
  use_case     text,                              -- specific role: 'hero'|'audition'|'thumb'|'avatar'|'illustration'|'diagram'
  tags         text[] default '{}',               -- free-form: ['warsaw','spa','evening']
  alt_text     text,                              -- accessibility / SEO
  parent_id    uuid references public.images(id),

  -- audit
  created_at   timestamptz default now(),
  created_by   uuid references auth.users(id),
  metadata     jsonb default '{}'
);

create index on public.images (use_case, created_at desc);
create index on public.images using gin (tags);

-- Read access policy (intranet authenticated users)
alter table public.images enable row level security;
create policy images_read_authenticated on public.images
  for select using (auth.role() = 'authenticated');
-- Insert policy: only via service_role (the wrapper uses service-role key).

Cloudflare R2 setup

1. Create R2 bucket sponic-images in the wingsiebird account (9cd3a280…).
2. Bind custom domain images.sponicgardens.com to the bucket. DNS lives in the same Cloudflare account, so the CNAME is one click.
3. Set bucket CORS to allow GET from https://sponicgardens.com and https://in.sponicgardens.com.
4. Mint an R2 API token scoped to sponic-images with Read+Write. Store in BW item Cloudflare R2 — sponic-images in devops-sponic folder. Update config/project.config.ts with the bucket name + custom domain + BW item ID.
5. Smoke-test: PUT a tiny PNG via the S3-compatible API, GET it via https://images.sponicgardens.com/<key>, verify 200.

Wrapper API

One function in apps/control/src/lib/image-gen.ts. No image generation anywhere else in the codebase after this lands.

// apps/control/src/lib/image-gen.ts
import { PROJECT_CONFIG } from '../../../../config/project.config';

export interface GenerateImageOptions {
  category: 'brand' | 'product' | 'docs' | 'team' | 'event' | 'space' | 'food';
  useCase: 'hero' | 'audition' | 'thumb' | 'avatar' | 'illustration' | 'diagram';
  size?: '1024x1024' | '1024x1536' | '1536x1024';
  quality?: 'low' | 'medium' | 'high';
  tags?: string[];          // free-form, e.g. ['warsaw','spa','evening']
  altText?: string;         // accessibility / SEO
  parentId?: string;        // for variants / re-runs of an existing image
  slug?: string;            // optional human handle
}

export interface ImageRecord {
  id: string;
  slug: string | null;
  storage_key: string;
  public_url: string;
  provider: string;
  model: string;
  cost_usd: number;
  bytes: number;
  width: number;
  height: number;
}

export async function generateImage(
  prompt: string,
  opts: GenerateImageOptions
): Promise<ImageRecord> {
  // 1. Resolve config: primary first, fallback on Azure 5xx / quota errors.
  // 2. Call provider; receive PNG bytes (b64 → Buffer).
  // 3. Compute sha256. If already in public.images, return existing row (dedup).
  // 4. Compute cost from PROJECT_CONFIG.imageGeneration.pricing.
  // 5. Upload to R2 at `${useCase}/YYYY-MM/${slug || sha256.slice(0,12)}.png`.
  // 6. Insert public.images row via supabase service-role client.
  // 7. Return ImageRecord.
}

Backfill

One-shot script (or inline in the migration session) to import the existing branding folder:

• Walk apps/garden/branding/hp-01-hp-hero-overview-v[23]-*.{jpg,webp,png}.
• Pair each with apps/garden/branding/prompts/<id>.txt if present.
• For each, hash, upload PNG to R2 (skip if already there), insert row with use_case='audition', provider='azure-openai', model='gpt-image-2', quality='high', computed cost_usd.
• Leave the existing apps/garden/branding/ files in place for now — the audition page still references them. A later cleanup task can switch branding.html to read from R2 URLs and delete the in-repo copies.

Config updates

Add to config/project.config.ts under a new storage section:

storage: {
  r2: {
    bucket: 'sponic-images',
    publicHost: 'images.sponicgardens.com',
    accountIdEnv: 'CLOUDFLARE_ACCOUNT_ID',
    accessKeyIdEnv: 'R2_ACCESS_KEY_ID',
    secretAccessKeyEnv: 'R2_SECRET_ACCESS_KEY',
    bwItemId: null, // populate after BW item is created
  },
},

Also add the env var names to apps/control/.env.example with BW lookup hints.

Verification

The session is done when:

• Migration is applied to SponicControl Supabase, select count(*) from public.images returns at least 6 (the backfill).
• https://images.sponicgardens.com/<any-backfilled-key> returns HTTP 200 with the image bytes.
• One end-to-end generateImage('test prompt', { useCase: 'audition' }) call from a Node REPL or test script returns a valid ImageRecord and the row is visible in Supabase.
• infra/runbook.md has a "Cloudflare R2 — sponic-images" section with the recipe.
• systemarchitecture.md "Image generation" section is updated to reference the new flow.

Prompt — copy into a fresh Claude Code session

Doc owner: Rahul. Last updated 2026-05-03. After the build session lands, prune sections of this doc that are now redundant with systemarchitecture.md.