🗃️ Data Model

Prompt Wizard replaces the archive’s WebSim collections with seven local SQLite tables. The archive declared an ideas collection, but the live archive had no reads or writes for it; the local port does not create it.

Database invariants

• engine:
  • SQLite through Bun
  • database: data/pwiz.db
  • journal mode: WAL
• every table:
  • id TEXT PRIMARY KEY
  • created_at TEXT NOT NULL
  • server-assigned values for both fields
• JSON fields:
  • stored as TEXT
  • parsed and serialized at API boundaries
  • never treated as queryable nested columns
• identity:
  • user-scoped tables are stamped from request identity
  • client-supplied user_id, id, and created_at are ignored where the server owns them

Tables

• wizards:
  • id
  • user_id
  • name
  • prompt
  • src
  • previous_urls
  • offset_x INTEGER
  • offset_y INTEGER
  • size INTEGER
  • is_custom INTEGER
  • created_at
• blueprints:
  • id
  • title
  • description
  • project_type
  • steps
  • author_id
  • author_username
  • is_public INTEGER
  • cover_image_url
  • metadata
  • tags
  • likes_count INTEGER DEFAULT 0
  • remixes_count INTEGER DEFAULT 0
  • created_at
  • updated_at
  • index: is_public
• prompt_history:
  • id
  • user_id
  • idea
  • project_type
  • complexity
  • style
  • focus
  • steps INTEGER
  • include_settings INTEGER
  • result
  • created_at
• saved_prompts:
  • id
  • user_id
  • title
  • description
  • project_type
  • steps
  • metadata
  • created_at
• likes:
  • id
  • user_id
  • blueprint_id
  • created_at
  • unique: user_id, blueprint_id
  • index: user_id
• admin_prompts:
  • id
  • key UNIQUE
  • text
  • description
  • updated_at
  • created_at
• llm_logs:
  • id
  • kind
  • template_key
  • system_prompt
  • user_message
  • response_json
  • created_at

Mutation rules

• POST /api/collections/:name:
  • creates row
  • stamps id and created_at
  • uses authenticated identity where applicable
• PATCH /api/collections/:name/:id:
  • updates allowed row fields
  • does not let callers rewrite ownership columns
• DELETE /api/collections/:name/:id:
  • removes the row by id
• POST /api/likes/toggle:
  • transactionally inserts or deletes one likes row
  • increments or decrements blueprints.likes_count in the same transaction
  • returns the authoritative liked state and count

Reconciled archive drift

• wizard size:
  • archive UI read and wrote size
  • archive schema omitted size
  • local schema includes size INTEGER
• wizard ordering:
  • archive logic sorted by created_at
  • archive wizard creation did not always stamp created_at
  • local server always stamps created_at
• prompt history project type:
  • archive wrote camelCase options such as projectType and includeSettings
  • archive readers expected snake_case fields such as project_type
  • local API stores snake_case only
• user ownership:
  • archive declared user_id but did not consistently write it on history and saved prompts
  • local server stamps it from authenticated identity
• ideas collection:
  • archive config declared it
  • verified app code had no live dependency on it
  • local data model omits it

See api for route shapes and verification for contract checks.