Design Template CRUD and Lifecycle

Purpose

A design template is the reusable canvas blueprint (a Fabric.js JSON tree, plus named-element and brand-color mappings on top of it) that an agency workspace authors once per format and that a client's Prima-driven design later gets instantiated from; the invariant that keeps a workspace's template library trustworthy is that every read path filters on DesignTemplate.workspace_id together with is_archived == False (template_service.py 87 to 95, 122 to 125), so a workspace never lists, fetches, updates, or duplicates a template it does not own or one that has been archived out of view. element_mappings and brand_mappings are carried on the row unchanged by this recipe; they exist so a downstream consumer can rewrite the canvas without this service ever inspecting their contents itself (module docstring, template_service.py 1 to 6).

When to use

Trigger phrases: "design template", "template CRUD", "Picasso template", "template gallery", "archive a template", "hard delete a template", "duplicate a template", "element_mappings", "brand_mappings", "editable_elements", "locked_elements". Read this before touching create_template, get_template, list_templates, update_template, delete_template, or duplicate_template in template_service.py, or the six template routes in design_router.py 46 to 224.

Not this recipe: actually instantiating a Design from a template and applying brand_mappings to its canvas objects (POST /api/designs/from-template, DesignService.create_from_template and _apply_brand_to_canvas, services/design/design_service.py 73 to 134 and 136 onward), the neighbouring recipe this one's element_mappings/brand_mappings columns exist to feed; Design CRUD and version history itself (design_router.py 231 onward, out of scope); DesignMedia upload handling; Prima's chat-driven canvas-operation DSL (prima_design_router.py, the DesignOperation/PrimaDesignChatRequest schemas, design_schemas.py 29 to 70, 363 to 392); get_templates_for_prima (template_service.py 264 to 311), the lightweight Prima-context listing that reads element_mappings.keys() but is a separate read path from this recipe's CRUD, cited for awareness only.

Stack assumptions

At 62ba4fc: fastapi==0.118.2, synchronous handlers on sqlalchemy.orm.Session (SQLAlchemy 2.0 sync style, and_/or_ imports in template_service.py 10), pydantic==2.12.0 (the schemas still use the v1-style @validator decorator rather than v2's @field_validator, design_schemas.py 89 to 96, legal under 2.12's compatibility shim but worth knowing before extending them), psycopg2-binary==2.9.10, alembic==1.16.5, Postgres (JSONB and ARRAY columns via sqlalchemy.dialects.postgresql, design_models.py 7 to 10). Foundations assumed: fastapi-project-layout for the app and mount_router; auth-dependency-pattern for get_current_user; workspace-scoping for the WorkspaceID dependency and its membership check; alembic-migrations for the one chained migration under Data model.

No environment variable gates this router specifically. DESIGN_ROUTER_AVAILABLE (app_fastapi.py 220 to 226) is only an import-success try/except around from api.routers import design_router, prima_design_router, true whenever the module imports cleanly, not a runtime feature flag a deploy can flip off; the frontend has no capability check either (PicassoGallery.tsx calls listTemplates/listDesigns unconditionally on mount). Fail behaviour: create_design_template wraps the service call in a bare except Exception and returns 400 with the exception's str() inlined into the response detail (design_router.py 61 to 74, the only route on this surface where an internal error message can reach a client); get_design_template, list_design_templates, update_design_template, delete_design_template, and duplicate_design_template have no try/except of their own, so an unhandled exception in any of them (a database error, for instance) falls through to the app's default error handling rather than a scoped response.

Data model

design_templates, one row per template, workspace-scoped (models/design_models.py 42 to 93; the one migration below):

design_templates (
  id                 uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  workspace_id       uuid NOT NULL REFERENCES workspaces(id) ON DELETE CASCADE,
  created_by         uuid REFERENCES users(id) ON DELETE SET NULL,
  name               varchar(255) NOT NULL,
  description        text,
  category           varchar(100),                 -- free text; Pydantic's allow-list does not reject unknown values (see Gotchas)
  width              integer NOT NULL DEFAULT 1080,
  height             integer NOT NULL DEFAULT 1080,
  canvas_data        jsonb NOT NULL DEFAULT '{}',   -- Fabric.js canvas JSON
  thumbnail_url      text,
  element_mappings   jsonb DEFAULT '{}',            -- element name -> object id in canvas_data, for Prima
  brand_mappings     jsonb DEFAULT '{}',             -- object name/id -> brand asset key ("primary", "headline_color", ...)
  editable_elements  jsonb DEFAULT '[]',             -- element names a client may edit
  locked_elements    jsonb DEFAULT '[]',             -- element names a client may not edit
  is_public          boolean NOT NULL DEFAULT true,  -- visible to all users of the workspace, not cross-workspace
  is_archived        boolean NOT NULL DEFAULT false, -- the soft-delete flag every read path filters on
  tags               text[] DEFAULT '{}',
  created_at         timestamptz DEFAULT CURRENT_TIMESTAMP,
  updated_at         timestamptz DEFAULT CURRENT_TIMESTAMP
);
-- indexes: idx_design_template_workspace(workspace_id), idx_design_template_category(category),
--          idx_design_template_public(is_public, is_archived), idx_design_template_created_by(created_by)

No CHECK constraint exists on this table at the database level (contrast the auto-topup recipe's ck_* constraints); every rule below is enforced in TemplateService/the Pydantic schemas only, not by Postgres. updated_at has a server_default but no onupdate/trigger at the DB layer; the ORM model's own onupdate=datetime.utcnow (design_models.py 83) is what actually refreshes it, so only writes that go through this SQLAlchemy model update it, a raw SQL UPDATE would not.

What is NOT stored: no template version history (a Design has version_number/previous_version_id/is_current_version; a DesignTemplate has none of those, an update_template call mutates the row in place and the prior canvas_data is gone); no deleted_at or archived_by timestamp, is_archived is a bare boolean with no record of when or by whom it was set; no per-template access-control list beyond is_public and workspace membership (see Security & tenancy).

API surface

Mounted at /api + router prefix /designs (app_fastapi.py 974: mount_router(app, design_router.router, prefix="/api", ...); design_router.py 39: APIRouter(prefix="/designs", ...)), so every path below is /api/designs/....

Route	Auth	Tenancy	Status codes	Notes
POST /designs/templates (design_router.py 46 to 74)	get_current_user	WorkspaceID (required)	201 on success; 400 on any exception from create_template, str(e) inlined into detail	Body: DesignTemplateCreate. canvas_data/element_mappings/brand_mappings default to {}, editable_elements/locked_elements to [] when omitted (template_service.py 56 to 62)
GET /designs/templates (77 to 108)	get_current_user	WorkspaceID (required)	200, body is a bare JSON array, no envelope	Query: category, is_public, search (name/description ILIKE), tags (comma-split, array-overlap), limit (1 to 100, default 50), offset (default 0, ge=0)
GET /designs/templates/{template_id} (111 to 136)	get_current_user	WorkspaceID (required)	200; 404 "Template not found"	Same 404 for "does not exist" and "exists but archived or in another workspace", the caller cannot distinguish them
PUT /designs/templates/{template_id} (139 to 166)	get_current_user	WorkspaceID (required)	200; 404 "Template not found or unauthorized"	Body: DesignTemplateUpdate, every field optional, only fields present in the JSON body are applied (model_dump(exclude_unset=True), template_service.py 173 to 175). The docstring claims "Only the template creator or workspace admin can update it" (design_router.py 147 to 150); the code enforces no such thing, see Security & tenancy
DELETE /designs/templates/{template_id} (169 to 195)	get_current_user	WorkspaceID (required)	204 on success (no body); 404 "Template not found or unauthorized"	Query: hard_delete (bool, default false). Default path sets is_archived = true; hard_delete=true calls db.delete(), and any Design.template_id pointing at it is set to NULL by the FK's ON DELETE SET NULL (design_models.py 131), the design itself is not touched
POST /designs/templates/{template_id}/duplicate (198 to 224)	get_current_user	WorkspaceID (required)	200 (not 201, despite creating a row); 404 "Template not found"	Query: new_name (optional; defaults to f"{source.name} (Copy)", template_service.py 243)

All six responses are the model's own to_dict() (a plain dict, design_models.py 95 to 117) returned directly; none of the six routes sets response_model. DesignTemplateResponse and DesignTemplateListResponse are imported into design_router.py (lines 19 to 20) but never referenced anywhere else in the file, so they validate nothing on this surface today, contrast the list endpoint which could use the lighter DesignTemplateListResponse but instead returns the full to_dict() including canvas_data for every row.

Application logic

create_template (template_service.py 31 to 70): builds a DesignTemplate directly from DesignTemplateCreate with workspace_id/created_by from the caller, canvas_data/element_mappings/brand_mappings falling back to {} and editable_elements/locked_elements to [] when the request omits them; one add/commit/refresh, no pre-check for a duplicate name within the workspace.

get_template (72 to 95): the single read primitive every other mutation calls first. Filters id == template_id AND is_archived == False, adds workspace_id == workspace_id only when a workspace_id argument is passed; .first(). The router always passes workspace_id (it is a required dependency), so in practice this recipe's get_template is always workspace- and archive-scoped.

list_templates (97 to 149): filters workspace_id == workspace_id AND is_archived == False, then optionally category ==, is_public ==, (name ILIKE %search% OR description ILIKE %search%), tags.overlap(tags) (Postgres array overlap); order_by(created_at.desc()); offset().limit(). The search value is interpolated into an f-string (template_service.py 134) but passed as a bound parameter to .ilike(), not concatenated into raw SQL, so this is not a SQL-injection vector despite the f-string.

update_template (151 to 183): calls get_template (workspace- and archive-scoped); on a hit, update_dict = update_data.model_dump(exclude_unset=True) and setattr for every present key, then stamps updated_at, commits, refreshes. Because get_template excludes archived rows and update_template has no other lookup path, an archived template can never be updated through this API, including flipping is_archived back to false, there is no unarchive route or code path at 62ba4fc; an archive (soft delete) is permanent from the API's point of view even though the column itself is just a boolean.

delete_template (185 to 215): calls get_template; on a hit, hard_delete=False (the default) sets is_archived = True and stamps updated_at; hard_delete=True calls self.db.delete(template). Both paths commit once and log which branch ran; there is no check for existing Design rows before a hard delete beyond the FK's own ON DELETE SET NULL.

duplicate_template (217 to 262): calls get_template for the source (workspace- and archive-scoped, so an archived source cannot be duplicated either); builds a new DesignTemplate copying every content field (canvas_data, element_mappings, brand_mappings, editable_elements, locked_elements, category, width, height, is_public, tags) with a fresh id, the calling user as created_by, and name = new_name or f"{source.name} (Copy)". Unlike the sibling forms TemplateService.duplicate_template pattern elsewhere in this codebase, this duplicate does not force is_public = False: a public template's duplicate stays public by default.

Invariants

• Every read this service performs is scoped to is_archived == False; nothing in TemplateService ever returns an archived row (get_template, list_templates, get_templates_for_prima, all filter it explicitly).
• Once is_archived is true on a row, no code path in this service can set it back to false or otherwise mutate that row: update_template, delete_template, and duplicate_template all route through get_template first, which excludes it. This is a genuine dead end at 62ba4fc, not a documented product decision (no comment or test asserts it is intended).
• workspace_id is the only tenancy filter get_template/list_templates apply; there is no row-level ownership check (created_by) anywhere in TemplateService, so any user who can resolve a WorkspaceID for a workspace (any active member, any role, via _get_workspace_id in api/dependencies.py) can read, update, archive, hard-delete, or duplicate any template in that workspace regardless of who created it.
• hard_delete is caller-controlled per request (DELETE .../templates/{id}?hard_delete=true) with no additional confirmation, role check, or audit row; it is one query-parameter flip away from the default soft-delete behaviour.

Frontend

The frontend module that renders this, by id:

media-audio-design/ui/picasso-design-gallery   the template/design gallery this CRUD surface backs; at 62ba4fc
                                                 it is alchemy_labs/frontend/src/pages/PicassoGallery.tsx, which
                                                 calls listTemplates()/listDesigns() on mount
                                                 (services/picassoService.ts 174, 254) and renders both in one
                                                 tabbed grid/list view with client-side search and category
                                                 filtering (PicassoGallery.tsx 30 to 74); picassoService.ts wraps
                                                 all six template routes (createTemplate 159, listTemplates 174,
                                                 getTemplate 182, updateTemplate 193, deleteTemplate 204,
                                                 duplicateTemplate 216) plus the sibling Design and brand-assets
                                                 calls this recipe does not own

Minimum admin/debug surface: none exists at 62ba4fc beyond the CRUD routes themselves, there is no dedicated admin listing, and unlike the auto-topup recipe there is no scheduler or background job touching this table to check on.

Security & tenancy

Auth dependency: get_current_user (api/dependencies.py 187 onward), the shared JWT-bearer/httpOnly-cookie/X-API-Key chain documented in auth-dependency-pattern; every one of the six routes requires it, there is no public or surface_exempt route on this surface. Tenancy: WorkspaceID (api/dependencies.py 680 to 779), which resolves and access-checks a workspace from request.state.workspace_id (pre-validated by WorkspaceMiddleware), the X-Workspace-ID header re-validated against _user_has_workspace_access, or a single-workspace fallback; it enforces membership only, not role: _user_has_workspace_access is a membership check, there is no owner/admin/member distinction anywhere on this path.

This means the update_design_template docstring's claim ("Only the template creator or workspace admin can update it", design_router.py 147 to 150) is not enforced by the code it documents: TemplateService.update_template/delete_template/duplicate_template never read created_by or any role, so any active member of the workspace can update, archive, hard-delete, or duplicate a template regardless of who created it. Whether that is the intended access model or a gap between the docstring and the implementation is not determinable from this code alone.

Fail direction: unauthenticated requests are refused by get_current_user (401) before any service code runs; a workspace the caller cannot access is refused by WorkspaceID (403) before TemplateService runs. Inside the service there is no fail-closed guard beyond the is_archived/workspace_id filters described above, an authenticated member of the right workspace has unrestricted CRUD over every template in it.

Credentials and PII at rest: none. canvas_data, element_mappings, and brand_mappings are design-authoring JSON (object ids, colour-role names, font names), not user PII, API keys, or password material; nothing stored in this table is sensitive on its own. Rate limits: none dedicated to this router at 62ba4fc (no rate-limit decorator or dependency on any of the six routes). Attacker-controlled inputs: template_id (UUID-typed by FastAPI's path converter, a malformed value is a 422 before the handler runs); search/category/tags query params (bound as query parameters, not string-concatenated into SQL, see Application logic); the full DesignTemplateCreate/DesignTemplateUpdate body, including arbitrary canvas_data JSON and element_mappings/brand_mappings string-to-string maps, none of which this service validates against the shape a Fabric.js canvas actually expects.

Tests

No test file exercises TemplateService or design_router.py's template routes at 62ba4fc. This was verified two ways, not assumed: grep -rln "DesignTemplateService" backend/tests/ returns nothing, and the coverage report checked into the repo at backend/htmlcov/index.html (generated 2026-09-12, the same day as this recipe's measured commit) shows api/routers/design_router.py at 0 of 156 statements covered (0%) and lists no services/design/template_service.py row at all, meaning it was not even imported during the last coverage run. models/design_models.py shows 93% (85 of 91 lines), covered incidentally by whatever elsewhere imports the model class, not by any test that exercises this recipe's behaviour.

The one file this repo's test suite does contain with a near-identical name, tests/v2/coverage/test_forms_g6_templateservice_crud_design_normalization_service_layer_db_backed_.py, is a decoy for this recipe: its own docstring says the file under test is backend/services/forms/template_service.py (FormTemplate/TemplateService for the Forms product), a completely different model and service from models/design_models.DesignTemplate/services/design/template_service.DesignTemplateService. Do not cite it as coverage for this recipe.

What a rebuild needs and this branch lacks entirely: hermetic or DB-backed tests for create_template/get_template/list_templates/update_template/delete_template/duplicate_template; a workspace-isolation test (two workspaces, assert cross-workspace 404); a test proving the archived-template dead end described under Application logic; a Playwright or integration test for the six routes through design_router.py itself, since the router adds its own 400-on-exception and 404 translation logic that the service layer alone does not cover.

Deploy & ops

Config keys: none specific to this router (see Stack assumptions; DESIGN_ROUTER_AVAILABLE is an import-success flag, not a configured toggle). Migration: migrations/versions/20260205_add_design_tables.py (revision = '20260205_design_tables', down_revision = '20260124_li_fat_id'), a single migration that creates design_templates, designs, and design_media together and their indexes; it has not been re-chained or amended since (no later migration under migrations/versions/ touches design_templates as of 62ba4fc, per a directory listing at this commit). Scheduler: none, this table is read and written only from request handlers, there is no background job. Rollback: the migration's own downgrade() drops all three tables and their constraints in dependency order (design_media first, then designs, then design_templates); rolling back the application code alone needs no data migration since nothing else in the schema depends on rows in this table beyond the ON DELETE SET NULL from designs.template_id.

Argus-style checks: none exist for this surface at 62ba4fc (no dedicated health-check query or endpoint), which follows directly from there being no tests or admin surface either; a minimal one worth adding would be SELECT count(*) FROM design_templates WHERE is_archived = true AND updated_at < now() - interval '30 days', a proxy for whether archived rows are silently accumulating with no path back.

Gotchas

• 2026-09-12 (measured at 62ba4fc, htmlcov/index.html, generated the same day): api/routers/design_router.py has 0% statement coverage (0 of 156) and services/design/template_service.py does not appear in the coverage report at all. This recipe's entire CRUD surface is unexercised by the test suite, not merely under-tested.
• 2026-09-12 (measured at 62ba4fc, template_service.py 72 to 183): archiving a template (DELETE .../templates/{id} with default hard_delete=false, or PUT .../templates/{id} with is_archived: true) is a one-way trip. get_template filters out is_archived == True rows, and both update_template and duplicate_template call get_template first, so there is no route or service method that can ever unarchive a template or otherwise touch it again short of a hard delete or a direct database write.
• 2026-09-12 (measured at 62ba4fc, design_router.py 147 to 150 vs template_service.py 151 to 183): the update endpoint's own docstring claims creator-or-admin-only authorization; the service performs no such check, any active member of the workspace, any role, can update, archive, hard-delete, or duplicate any template in it. Whether the docstring or the code is the bug cannot be determined from this codebase alone.
• 2026-09-12 (measured at 62ba4fc, design_router.py 19 to 20 and a full-file grep): DesignTemplateResponse and DesignTemplateListResponse are imported but never used as a response_model or otherwise referenced; every route returns the ORM model's raw to_dict() instead, so those two schemas currently document a contract the router does not enforce.

Reference implementation

Paths relative to the repo root; all at main 62ba4fc41009b454e1c033e5036c7e8577db2061 unless noted.

• alchemy_labs/backend/api/routers/design_router.py (655 lines total; template routes 39 to 224 are this recipe's scope, lines 227 onward are Design/export/brand-asset routes out of scope): create_design_template (46 to 74); list_design_templates (77 to 108); get_design_template (111 to 136); update_design_template (139 to 166); delete_design_template (169 to 195); duplicate_design_template (198 to 224).
• alchemy_labs/backend/services/design/template_service.py (311 lines, whole file in scope): DesignTemplateService.__init__ (28 to 29); create_template (31 to 70); get_template (72 to 95); list_templates (97 to 149); update_template (151 to 183); delete_template (185 to 215); duplicate_template (217 to 262); get_templates_for_prima (264 to 311, cited for context, not this recipe's CRUD surface).
• alchemy_labs/backend/models/design_models.py (258 lines): DesignTemplate (33 to 117, all columns, indexes, and to_dict); Design and DesignMedia (120 to 258, out of scope, cited because Design.template_id is the one live foreign key into this recipe's table).
• alchemy_labs/backend/schemas/design_schemas.py (392 lines): DesignTemplateBase and its validators (75 to 114); DesignTemplateCreate (117 to 119); DesignTemplateUpdate (122 to 137); DesignTemplateResponse (140 to 152, unused by the router, see Gotchas); DesignTemplateListResponse (155 to 169, unused by the router).
• alchemy_labs/backend/migrations/versions/20260205_add_design_tables.py: design_templates table and its four indexes (upgrade, first third of the file).
• alchemy_labs/backend/app_fastapi.py: the import-success guard (220 to 226) and the mount (972 to 977).
• alchemy_labs/backend/services/design/design_service.py: create_from_template (73 to 134) and _apply_brand_to_canvas (136 onward), the neighbouring recipe that actually reads brand_mappings, cited for context only.
• alchemy_labs/frontend/src/pages/PicassoGallery.tsx and alchemy_labs/frontend/src/services/picassoService.ts (template calls at lines 159, 174, 182, 193, 204, 216): the one real caller of this API surface, see Frontend.

No dedicated test file exists for either the router or the service; see Tests for what was checked to establish that, and for the decoy file that shares part of its name.

Acceptance checks

No hermetic or DB-backed test suite exists for this recipe at 62ba4fc (see Tests), so there is no pytest command to run as a verifier. What a verifier can confirm without a database:

1. grep -n "response_model" alchemy_labs/backend/api/routers/design_router.py | sed -n '1,20p' shows no response_model= on any of the six template routes (lines 46, 77, 111, 139, 169, 198), confirming the raw-to_dict() behaviour under API surface.
2. grep -n "is_archived" alchemy_labs/backend/services/design/template_service.py shows it appears in get_template (89), list_templates (124), get_templates_for_prima (284), and as the field delete_template sets on a soft delete (209), and nowhere is it ever set back to False, confirming the one-way-archive gotcha.
3. grep -n "created_by" alchemy_labs/backend/services/design/template_service.py shows it is only ever written (on create_template/duplicate_template), never read back as a filter condition, confirming the no-ownership-check finding under Security & tenancy.
4. python -c "import ast; ast.parse(open('alchemy_labs/backend/services/design/template_service.py').read())" (run from alchemy_labs/backend) parses without error, a baseline sanity check only, not a behavioural one, since no test in this repo currently imports and exercises the module.
5. grep -c "^def test_" alchemy_labs/backend/tests/v2/coverage/test_forms_g6_templateservice_crud_design_normalization_service_layer_db_backed_.py prints 34, all of them against services/forms/template_service.py's FormTemplate/TemplateService, none against this recipe's DesignTemplate/DesignTemplateService, confirming the decoy-file note under Tests.