Recipe app · PostgreSQL 16 · Normalized schema with audit trails
tag reference table. recipe_tag is now a pure junction table with recipe_id FK + tag_id FK. Tags are reusable, renameable, and queryable from both directions.ingredient_category reference table. The category string on ingredient is replaced with category_id FK. Rename once, applies to all ingredients. Canonical list of aisle categories for shopping grouping.system_role column on user_account (admin vs user). New admin_audit_log table tracks admin actions: account creation, updates, password resets.Variety score is computed, not stored — it's derived from cooking_log + recipe_ingredient + week_plan_slot.
Ingredients are a normalized reference table — enables merging, repetition tracking, and staple filtering.
Tags are a proper M:N: a tag reference table + recipe_tag junction. One recipe → many tags, one tag → many recipes. Rename once, applies everywhere.
Ingredient categories are a normalized 1:N reference table — one ingredient belongs to one category (e.g. "Produce", "Fish & Meat"). Rename a category once, applies to all ingredients. Powers the aisle-grouped shopping list (J5 variant V2).
Hero images store a URL/path reference to object storage (S3/R2).
Admin uses a system_role on user_account (not the household role). Admin actions are audit-logged in a dedicated table.
Pantry items link to the shared ingredient reference with best-before dates.
Before (v1.0): recipe_tag(recipe_id, tag varchar(50)) — tag name stored as raw string per row. 30 recipes tagged "chicken" = 30 copies of the string "chicken". Renaming requires updating every row. No canonical list of available tags. No typed categorization.
After (v1.1): tag(id, household_id, name, tag_type) + recipe_tag(recipe_id, tag_id) — pure M:N junction. Rename a tag in one UPDATE. List available tags with a simple SELECT. Filter by tag_type (protein, dietary, cuisine) for the J2 suggestion engine. The junction PK is composite (recipe_id, tag_id) — no surrogate key needed.
| Column | Type | Constraints | Purpose |
|---|---|---|---|
| id | uuid | PK, gen_random_uuid() | Surrogate PK |
| household_id | uuid | NOT NULL, FK → household ON DELETE CASCADE | Tags belong to a household |
| name | citext | NOT NULL | "Chicken", "Fish", "Vegetarian", "Pasta" |
| tag_type | varchar(20) | NOT NULL, CHECK(tag_type IN ('protein','dietary','cuisine','other')) | Classification. "protein" powers J2 consecutive-day filter. |
| created_at | timestamptz | NOT NULL, DEFAULT now() | Creation time |
| Column | Type | Constraints | Purpose |
|---|---|---|---|
| recipe_id | uuid | NOT NULL, FK → recipe ON DELETE CASCADE, part of composite PK | Which recipe |
| tag_id | uuid | NOT NULL, FK → tag ON DELETE CASCADE, part of composite PK | Which tag |
-- What protein tags are on adjacent planned days? SELECT wps.slot_date, t.name AS protein FROM week_plan_slot wps JOIN recipe_tag rt ON rt.recipe_id = wps.recipe_id JOIN tag t ON t.id = rt.tag_id WHERE wps.week_plan_id = $1 AND t.tag_type = 'protein' ORDER BY wps.slot_date;
Before: ingredient.category varchar(30) — raw string. 15 ingredients labelled "Produce" = 15 copies. Rename requires updating every row. No canonical list. No display ordering for the aisle-grouped shopping list.
After: ingredient_category(id, household_id, name, sort_order) + ingredient.category_id FK. Rename once, applies everywhere. sort_order controls the display order on the aisle-grouped shopping list (J5 V2). Category is nullable on ingredient — uncategorized ingredients fall into an "Other" group in the UI.
| Column | Type | Constraints | Purpose |
|---|---|---|---|
| id | uuid | PK, gen_random_uuid() | Surrogate PK |
| household_id | uuid | NOT NULL, FK → household ON DELETE CASCADE | Categories are per-household |
| name | citext | NOT NULL | "Produce", "Fish & Meat", "Dry Goods", "Dairy", "Sauces & Condiments" |
| sort_order | smallint | NOT NULL, DEFAULT 0 | Display order — matches supermarket aisle flow |
| created_at | timestamptz | NOT NULL, DEFAULT now() | Creation time |
system_role on user_account: platform-level. "admin" can manage all user accounts. "user" is a normal user. This is about platform administration.
household role on household_member: app-level. "planner" has full access to 18 screens. "member" sees C1 read-only + D1 collaborative. This is about what you can do within a household.
An admin can also be a planner in their own household. The roles are independent.
| Column | Type | Constraints | Purpose |
|---|---|---|---|
| id | uuid | PK, gen_random_uuid() | Surrogate PK |
| citext | NOT NULL, UNIQUE | Login identifier, case-insensitive | |
| display_name | varchar(100) | NOT NULL | Shown in UI (sidebar avatar initials) |
| password_hash | varchar(255) | NOT NULL | bcrypt/argon2 hash — never exposed via API |
| system_role | varchar(10) | NOT NULL, DEFAULT 'user', CHECK(system_role IN ('admin','user')) | NEW — platform role. Admin can manage all accounts. |
| is_active | boolean | NOT NULL, DEFAULT true | NEW — admin can deactivate accounts. Inactive users cannot log in. |
| created_at | timestamptz | NOT NULL, DEFAULT now() | Account creation time |
| updated_at | timestamptz | NOT NULL, DEFAULT now() | Last profile edit |
| Column | Type | Constraints | Purpose |
|---|---|---|---|
| id | uuid | PK, gen_random_uuid() | Surrogate PK |
| admin_id | uuid | NOT NULL, FK → user_account ON DELETE RESTRICT | Which admin performed the action |
| target_user_id | uuid | NOT NULL, FK → user_account ON DELETE RESTRICT | Which user was affected |
| action | varchar(30) | NOT NULL, CHECK(action IN ('create_account','update_account','reset_password','deactivate_account','reactivate_account','change_system_role')) | What happened |
| detail | jsonb | NULL | Changed fields snapshot: {"field":"email","old":"a@x.com","new":"b@x.com"} |
| ip_address | inet | NULL | Admin's IP for security audit |
| performed_at | timestamptz | NOT NULL, DEFAULT now() | When the action occurred |
| From table | Column | References | Cardinality | On delete |
|---|---|---|---|---|
| household | created_by | user_account.id | N:1 | RESTRICT |
| household_member | household_id | household.id | N:1 | CASCADE |
| household_member | user_id | user_account.id | N:1 | CASCADE |
| household_invite | household_id | household.id | N:1 | CASCADE |
| recipe | household_id | household.id | N:1 | CASCADE |
| ingredient | household_id | household.id | N:1 | CASCADE |
| ingredient_category | household_id | household.id | N:1 | CASCADE |
| ingredient | category_id | ingredient_category.id | N:1 (nullable) | SET NULL |
| tag | household_id | household.id | N:1 | CASCADE |
| recipe_ingredient | recipe_id | recipe.id | N:1 | CASCADE |
| recipe_ingredient | ingredient_id | ingredient.id | N:1 | RESTRICT |
| recipe_step | recipe_id | recipe.id | N:1 | CASCADE |
| recipe_tag | recipe_id | recipe.id | M:N junction | CASCADE |
| recipe_tag | tag_id | tag.id | M:N junction | CASCADE |
| week_plan | household_id | household.id | N:1 | CASCADE |
| week_plan_slot | week_plan_id | week_plan.id | N:1 | CASCADE |
| week_plan_slot | recipe_id | recipe.id | N:1 | RESTRICT |
| cooking_log | recipe_id | recipe.id | N:1 | RESTRICT |
| cooking_log | week_plan_slot_id | week_plan_slot.id | N:1 (nullable) | SET NULL |
| shopping_list | week_plan_id | week_plan.id | N:1 | RESTRICT |
| shopping_list_item | shopping_list_id | shopping_list.id | N:1 | CASCADE |
| shopping_list_item | ingredient_id | ingredient.id | N:1 (nullable) | SET NULL |
| pantry_item | household_id | household.id | N:1 | CASCADE |
| pantry_item | ingredient_id | ingredient.id | N:1 (nullable) | SET NULL |
| admin_audit_log | admin_id | user_account.id | N:1 | RESTRICT |
| admin_audit_log | target_user_id | user_account.id | N:1 | RESTRICT |
WITH recent_meals AS (
SELECT recipe_id, cooked_on
FROM cooking_log
WHERE household_id = $1
AND cooked_on >= CURRENT_DATE - INTERVAL '3 days'
)
SELECT DISTINCT i.id, i.name
FROM recent_meals rm
JOIN recipe_ingredient ri ON ri.recipe_id = rm.recipe_id
JOIN ingredient i ON i.id = ri.ingredient_id;
SELECT wps.slot_date, t.name AS protein FROM week_plan_slot wps JOIN recipe_tag rt ON rt.recipe_id = wps.recipe_id JOIN tag t ON t.id = rt.tag_id WHERE wps.week_plan_id = $1 AND t.tag_type = 'protein' ORDER BY wps.slot_date;
SELECT i.id, i.name,
SUM(ri.quantity) AS total_qty, ri.unit,
ARRAY_AGG(DISTINCT r.id) AS source_recipe_ids
FROM week_plan_slot wps
JOIN recipe r ON r.id = wps.recipe_id
JOIN recipe_ingredient ri ON ri.recipe_id = r.id
JOIN ingredient i ON i.id = ri.ingredient_id
WHERE wps.week_plan_id = $1
AND i.is_staple = false
GROUP BY i.id, i.name, ri.unit
ORDER BY i.name;
SELECT pi.id, COALESCE(i.name, pi.custom_name) AS name,
pi.best_before, pi.quantity, pi.unit
FROM pantry_item pi
LEFT JOIN ingredient i ON i.id = pi.ingredient_id
WHERE pi.household_id = $1
AND pi.best_before IS NOT NULL
AND pi.best_before <= CURRENT_DATE + INTERVAL '3 days'
ORDER BY pi.best_before;
SELECT aal.action, aal.detail, aal.performed_at,
admin.display_name AS admin_name, admin.email AS admin_email
FROM admin_audit_log aal
JOIN user_account admin ON admin.id = aal.admin_id
WHERE aal.target_user_id = $1
ORDER BY aal.performed_at DESC;
SELECT t.id, t.name, t.tag_type FROM recipe_tag rt JOIN tag t ON t.id = rt.tag_id WHERE rt.recipe_id = $1 ORDER BY t.tag_type, t.name;
SELECT r.id, r.name, r.effort, r.cook_time_min FROM recipe_tag rt JOIN recipe r ON r.id = rt.recipe_id WHERE rt.tag_id = $1 AND r.deleted_at IS NULL ORDER BY r.name;
CREATE EXTENSION IF NOT EXISTS "pgcrypto"; -- gen_random_uuid() CREATE EXTENSION IF NOT EXISTS "citext"; -- case-insensitive text CREATE OR REPLACE FUNCTION trigger_set_updated_at() RETURNS TRIGGER AS $$ BEGIN NEW.updated_at = NOW(); RETURN NEW; END; $$ LANGUAGE plpgsql;
1. user_account → 2. household → 3. household_member → 4. household_invite → 5. ingredient_category → 6. ingredient → 7. tag → 8. recipe → 9. recipe_ingredient → 10. recipe_step → 11. recipe_tag → 12. week_plan → 13. week_plan_slot → 14. cooking_log → 15. shopping_list → 16. shopping_list_item → 17. pantry_item → 18. admin_audit_log
-- Prevent accidental updates/deletes on audit log CREATE RULE no_update_audit AS ON UPDATE TO admin_audit_log DO INSTEAD NOTHING; CREATE RULE no_delete_audit AS ON DELETE TO admin_audit_log DO INSTEAD NOTHING;
| Journey | Reads | Writes | Critical path |
|---|---|---|---|
| J1 · Add recipe | ingredient, tag (autocomplete) | recipe, recipe_ingredient, recipe_step, recipe_tag, ingredient, tag | Recipe INSERT + child rows + tag associations in one transaction |
| J2 · Plan week | recipe, recipe_ingredient, recipe_tag, tag, cooking_log, ingredient | week_plan, week_plan_slot | Variety CTE joins tag (type=protein) for consecutive-day check |
| J3 · Cook tonight | week_plan_slot, recipe, recipe_ingredient, recipe_step | cooking_log | cooking_log INSERT (immutable event) |
| J4 · Adapt on fly | recipe, recipe_tag, tag, cooking_log | week_plan_slot (UPDATE recipe_id) | Slot UPDATE + variety recompute ≤ 3 taps |
| J5 · Shopping list | week_plan_slot, recipe_ingredient, ingredient, ingredient_category | shopping_list, shopping_list_item | Merge query (GROUP BY ingredient, SUM quantity) + aisle grouping via category |
| J6 · Household setup | — | user_account, household, household_member, household_invite, ingredient (staples), tag (seed data), ingredient_category (seed data) | Household creation + seed data in one transaction |
| Pantry | pantry_item, ingredient | pantry_item | Expiry notification query (daily) |
| Admin | user_account, admin_audit_log | user_account, admin_audit_log | Every admin action → audit log INSERT in same transaction |
Fixed in v1.1. v1.0 stored tags as raw strings, making recipe_tag structurally a 1:N (one recipe → many string rows) rather than a true M:N. The tag string had no identity — "chicken" on recipe A and "chicken" on recipe B were unrelated rows. This prevented tag renaming, tag listing, and structured filtering. v1.1 adds a tag reference table, making recipe_tag a proper M:N junction with FK integrity in both directions.
Fixed in v1.1. Same anti-pattern as the tag issue. 15 ingredients all storing the string "Produce" independently — no shared identity, no rename capability, no canonical list, no sort order. v1.1 extracts this to ingredient_category as a reference table with a 1:N FK from ingredient. The sort_order column enables the aisle-grouped shopping list (J5 D1 V2) to match supermarket layout. Category is nullable — uncategorized ingredients group into "Other."
Violates 1NF. But it's write-once display metadata, never joined against. A junction table would add ~60 rows/week for zero query benefit. Sunset plan: migrate to junction table if future requirements need "which list items came from recipe X?"
At ~50 recipes × 7 slots, the CTE runs in <100ms. Materialized view adds staleness risk. At 100× scale, we add materialized view with refresh-on-mutation triggers.
This is the right use case for schemaless data. Each action type has a different shape (password reset has a "reason", email change has "old"+"new", creation has full profile). The data is write-once, append-only, and queried for display only. Normalizing it into typed columns would require a different schema per action type for no benefit.
Having a separate table for admins would split identity across two tables. Login would need to check both. An admin who is also a household planner would need two accounts. Instead, system_role on user_account keeps one identity per person. The role check is a single column read.