Provenance Belongs in the Image Table

A developer at a content studio implemented a PostgreSQL schema for AI-generated images that stores provenance directly in the image table, using a self-referencing foreign key to link edited variants to their originals. This design choice prioritizes fast access to current image metadata over audit-log history, reducing the need for joins on every render.

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. 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. flowchart TD original "Original row: prompt, model, seed, dimensions" editA "Edited child: edit prompt, edit strength, edit steps" editB "Edited child: edit prompt, edit guidance scale" brandedA "Branded output: branded url, branding options" brandedB "Branded output: branded url, branding options" original -- editA original -- editB editA -- brandedA editB -- brandedB 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. 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. | Field group | Question it answers | |---|---| prompt , negative prompt | What text guided the run | model , seed | Which settings identify it | width , height , aspect ratio , resolution | What shape came back | is edited , original image id | Whether it derives from another row | edit prompt , edit strength , edit guidance scale , edit steps | Which transform produced the child | branded url , branding options | Which publishable variant was created | metadata , tags | Extra details and classification | 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. 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. 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. 🎧 Listen to the audiobook — Spotify https://open.spotify.com/show/4ABVd5yDVfbX9HlV5JjT7D · Google Play https://play.google.com/store/audiobooks/details/How to Architect an Enterprise AI System And Why t?id=AQAAAECafz8 tM&hl=en · All platforms https://www.craftedbydaniel.com/audiobook 🎬 Watch the visual overviews on YouTube https://youtube.com/playlist?list=PLRteDbGJPYDb9XNjecvHplGlgW7tIv q6 📖 Read the full 13-part series https://www.craftedbydaniel.com/blog/series/how-to-architect-an-enterprise-ai-system-and-why-the-engineer-still-matters