Provenance Belongs in the Image Table

A generated image looks finished until review starts.

Someone approves the first version. Someone else crops it. A branded copy goes out. Another edit changes the prompt. A week later, the useful question is simple: which prompt, model, seed, size, parent image, and publishing settings produced the version on screen?

In a content studio, I put those answers in the PostgreSQL row that stores the image. Logs explain what happened during a run, then rotate away. Object storage keeps the bytes and forgets why they exist. The row is the only one of the three that survives edits, review, and publishing.

1. The row is the receipt

The table in apps/api/src/database/init-ai-images-table.js treats generated and edited images as one record type. An original image gets its own row. An edit gets another row, with original_image_id pointing back to the parent.

CREATE TABLE IF NOT EXISTS ai_generated_images (
  id SERIAL PRIMARY KEY,
  image_url TEXT NOT NULL,

  -- what produced it
  prompt TEXT NOT NULL,
  model VARCHAR(100) DEFAULT 'fal-ai/imagen4',
  model_version VARCHAR(100),
  seed BIGINT,
  width INTEGER,
  height INTEGER,

  -- how it derives from another row
  is_edited BOOLEAN DEFAULT FALSE,
  original_image_id INTEGER REFERENCES ai_generated_images(id),
  edit_prompt TEXT,
  edit_strength DECIMAL(3,2),

  -- what actually shipped
  branded_url TEXT,
  branding_options JSONB,

  metadata JSONB,
  tags TEXT[],
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

That self-reference is the design choice. It makes the image table append-only-ish: new variants are inserted as new rows instead of overwriting the earlier state. The cost is more rows and more discipline at write time. The benefit is editable history that product screens and debugging queries can follow.

2. Why not a separate audit table

An audit table is the obvious alternative. Write the image row, append an event per generation and edit somewhere else, reconstruct history when asked.

I did not do that, and the reason is where the cost lands. An audit table answers questions about history. The product asks questions about the current thing. Every screen that shows an image wants the prompt next to it. A gallery filters by model. A review queue sorts by whether a row is a branded variant. Each of those becomes a join against a log that grows faster than the images do.

The self-reference costs a recursive query when someone wants the whole branch, which is rare. The audit table costs a join on every render, which is constant. I moved the expensive case onto the rare one.

3. Columns carry facts the product asks about

A value gets its own column when the application filters, sorts, joins, or explains by it. metadata and branding_options stay JSONB because those shapes change more often than the core record, and tags is an array so classification can be indexed.

Seven indexes cover the access patterns: owner and session for scoping, creation time for the default sort, saved and public flags for the two filters the gallery exposes, and model for the question that only comes up during review. Six of those are ordinary B-tree indexes on scalars. The seventh is not:

CREATE INDEX idx_ai_images_tags ON ai_generated_images USING GIN(tags);

tags is an array, so a B-tree cannot help: a B-tree indexes a value, and the query asks whether a value sits inside a collection. A Generalized Inverted Index (GIN) inverts that. It stores one entry per distinct tag pointing at every row carrying it, which turns containment into a lookup instead of a scan.

Every index is a tax on writes. Seven of them on a table that grows a row per edit is a real cost, and I took it because these images are written once and then browsed, filtered, revisited and argued about for weeks.

4. Lineage becomes a query

Once parent links live in the same table as the model parameters, review can ask for a whole branch without reading logs. This recursive Common Table Expression (CTE) starts from one original row and returns every descendant with the fields needed to reproduce or debug the result:

WITH RECURSIVE image_lineage AS (
  -- anchor: the row you are asking about, at depth 0
  SELECT id, original_image_id, image_url, prompt, model, seed,
         edit_prompt, edit_strength, branded_url, created_at, 0 AS depth
  FROM ai_generated_images
  WHERE id = $1

  UNION ALL

  -- recursive arm: anything whose parent is already in the result
  SELECT child.id, child.original_image_id, child.image_url, child.prompt,
         child.model, child.seed, child.edit_prompt, child.edit_strength,
         child.branded_url, child.created_at, parent.depth + 1
  FROM ai_generated_images child
  JOIN image_lineage parent ON child.original_image_id = parent.id
)
SELECT * FROM image_lineage ORDER BY depth, created_at;

The two arms are doing different jobs. The anchor selects one row and calls it depth zero. The recursive arm joins the table back onto the results so far, so each pass picks up the children of everything found in the pass before it. Postgres repeats that until a pass returns nothing.

depth is the column that makes the output readable. Without it the result is an unordered pile of rows; with it, ordering by depth then time reproduces the order the edits actually happened in. One original branches into many children, and every node carries enough state to explain why it exists.

The recursion has no depth limit, which is fine while edits are made by people. A loop would hang it, and a row cannot become its own ancestor through the normal write path, so I have not added a guard. If edits ever get generated in a batch, that assumption is the first thing I would revisit.

The insert path in apps/api/src/database/azure-postgres.js follows the same contract: explicit columns for the core settings and edit parameters, JSON for the irregular details.

5. What the row cannot tell you

Two gaps, and both are visible in the schema above.

model stores a slug like fal-ai/imagen4, not a version. A provider can change weights behind that name without changing the string, and the row will keep claiming a reproducibility it no longer has. Same prompt, same seed, same model value, different image. The fix is a pinned version in its own column rather than buried in metadata, because reproducibility is a question the product asks directly.

The foreign key carries no ON DELETE clause, so Postgres refuses to remove a parent that still has children. Cleanup becomes a deliberate walk down the tree instead of one statement. That is the right default here, and it is worth stating plainly: anyone who reaches for ON DELETE CASCADE to make a purge easier is deleting the receipts.

Branding obeys the same rule. image_url records what the model returned, branded_url the version intended for use, branding_options the publishing configuration. A reviewer questioning the final visual can separate model output, edit choice, and branding treatment without opening a log.