# 04 — Data Model

This is the relational data model for Craftkit v0.1 + v0.2 (with embed). Tables are defined in packages/db/src/schema.ts using Drizzle.

# Identity & access

Copyusers
  id                uuid pk
  email             citext unique
  email_verified_at timestamp
  name              text
  avatar_url        text
  created_at        timestamp default now
  updated_at        timestamp default now

sessions             (managed by better-auth)
  id                text pk
  user_id           uuid fk users
  expires_at        timestamp
  ...

# Project ownership

A project is the unit of isolation. A user owns N projects. All API keys, templates, renders, and webhooks are scoped to a project.

Copyprojects
  id                uuid pk
  user_id           uuid fk users        # single-user in v0.1
  name              text
  slug              text                  # url slug per user
  brand_color       text                  # for embed branding
  brand_logo_url    text
  created_at        timestamp
  updated_at        timestamp
  deleted_at        timestamp nullable
  unique (user_id, slug)

# Templates & versions

Templates are immutable once published — every publish creates a new template_version row. The mutable working draft lives on templates.

Copytemplates
  id                uuid pk
  project_id        uuid fk projects
  name              text
  slug              text                  # unique per project
  current_version_id uuid fk template_versions nullable
  draft_content_json jsonb                # latest unpublished Tiptap doc
  draft_updated_at   timestamp
  created_at        timestamp
  deleted_at        timestamp nullable
  unique (project_id, slug)

template_versions
  id                uuid pk
  template_id       uuid fk templates
  version_number    integer
  content_json      jsonb                  # Tiptap doc (immutable snapshot)
  compiled_html     text                   # Handlebars template
  variables_manifest jsonb                  # extracted manifest (Variable[])
  json_schema       jsonb                   # auto-generated JSON Schema
  page_settings     jsonb                   # paper size, margin, orientation
  published_at      timestamp
  unique (template_id, version_number)

# API keys

API keys authenticate the public REST API. Stored hashed, prefix retained for identification.

Copyapi_keys
  id                uuid pk
  project_id        uuid fk projects
  name              text                   # human label
  prefix            text                   # first 8 chars after ck_live_
  key_hash          text                   # bcrypt or argon2
  last_used_at      timestamp
  created_at        timestamp
  revoked_at        timestamp nullable

Format: ck_live_<24-char-base32>. The full key is shown once on creation.

# Webhooks

Copywebhooks
  id                uuid pk
  project_id        uuid fk projects
  url               text
  secret            text                    # used for HMAC signature
  events            text[]                  # subscribed event types
  active            boolean
  created_at        timestamp

webhook_deliveries
  id                uuid pk
  webhook_id        uuid fk webhooks
  render_id         uuid fk renders nullable
  embed_session_id  uuid fk embed_sessions nullable
  event_type        text
  payload           jsonb
  attempts          integer
  status            enum (pending|delivered|failed|abandoned)
  response_status   integer nullable
  response_body     text nullable
  next_retry_at     timestamp nullable
  delivered_at      timestamp nullable

# Renders

Copyrenders
  id                uuid pk
  project_id        uuid fk projects
  template_version_id uuid fk template_versions
  api_key_id        uuid fk api_keys nullable    # null for embed-triggered
  embed_session_id  uuid fk embed_sessions nullable  # if from embed
  tenant_id         uuid fk tenants nullable     # for tenant attribution
  input_data        jsonb
  status            enum (queued|rendering|succeeded|failed)
  asset_url         text nullable
  asset_size_bytes  integer nullable
  error             text nullable
  created_at        timestamp
  started_at        timestamp nullable
  completed_at      timestamp nullable

# Tenancy & embed (v0.2)

This is the new layer added in v0.2 to support partner SaaS embedding Craftkit. See architecture/06-tenancy-model.md for the conceptual model.

Copyembed_partners                              # one project becomes a "partner"
  id                uuid pk
  project_id        uuid fk projects unique
  display_name      text
  status            enum (active|suspended)
  created_at        timestamp
  updated_at        timestamp

embed_partner_keys
  id                uuid pk
  partner_id        uuid fk embed_partners
  kind              enum (publishable|secret|signing_public|signing_private)
  prefix            text
  hash              text nullable           # for secret keys; null for public
  public_key_pem    text nullable           # for signing keys
  kid               text nullable           # JWT key id for signing keys
  status            enum (active|revoked|rotating)
  created_at        timestamp
  revoked_at        timestamp nullable

embed_partner_origins
  id                uuid pk
  partner_id        uuid fk embed_partners
  origin            text                    # may include wildcards (*.example.com)
  environment       enum (production|staging|development)
  created_at        timestamp

embed_partner_permission_presets
  id                uuid pk
  partner_id        uuid fk embed_partners
  name              text                    # 'admin', 'editor', 'viewer', or custom
  permissions       jsonb                   # { publish: bool, save_draft: bool, ... }
  created_at        timestamp

tenants                                     # partner's customers (e.g. an org)
  id                uuid pk
  partner_id        uuid fk embed_partners
  external_id       text                    # partner-supplied
  display_name      text
  brand_color       text nullable
  brand_logo_url    text nullable
  status            enum (active|paused|disabled)
  created_at        timestamp
  unique (partner_id, external_id)

actors                                      # end-users from partner's customers
  id                uuid pk
  partner_id        uuid fk embed_partners
  tenant_id         uuid fk tenants
  external_id       text                    # partner-supplied user id
  display_name      text
  email             citext nullable
  preferences       jsonb                   # ui prefs, hint dismissal state
  first_seen_at     timestamp
  last_seen_at      timestamp
  unique (partner_id, tenant_id, external_id)

variable_catalogs                           # named, versioned per-partner
  id                uuid pk
  partner_id        uuid fk embed_partners
  name              text                    # 'kleesto-org-default'
  version           integer
  content           jsonb                   # CatalogSpec
  status            enum (draft|current|archived)
  created_at        timestamp
  unique (partner_id, name, version)

template_external_links                     # maps a craftkit template to a partner's external id
  template_id       uuid fk templates
  partner_id        uuid fk embed_partners
  tenant_id         uuid fk tenants
  external_id       text                    # partner's id for this template
  primary key (template_id, partner_id)
  unique (partner_id, tenant_id, external_id)

embed_sessions
  id                uuid pk                 # ck_sess_…
  partner_id        uuid fk embed_partners
  tenant_id         uuid fk tenants
  actor_id          uuid fk actors
  template_id       uuid fk templates nullable    # null when mode=create
  catalog_id        uuid fk variable_catalogs nullable
  mode              enum (edit|create|view)
  permissions       jsonb                   # snapshot of preset (or inline)
  branding          jsonb                   # snapshot of branding tokens
  callbacks         jsonb                   # on_published, on_close_url
  limits            jsonb                   # per-session abuse guards
  renew_token_hash  text                    # rotates each refresh
  jti_seen          text[]                  # replay protection (in-memory cache also)
  status            enum (active|expired|revoked|terminal)
  created_at        timestamp
  last_token_issued_at timestamp
  expires_at        timestamp               # of the latest token
  ended_at          timestamp nullable
  end_reason        text nullable

embed_session_events                        # full audit trail for the session inspector
  id                uuid pk
  session_id        uuid fk embed_sessions
  type              text                    # 'token.issued', 'iframe.loaded', 'variable.inserted', ...
  payload           jsonb
  occurred_at       timestamp

# Telemetry & billing (v0.2)

Append-only ledger of billable events. See embed/09-billing-telemetry.md.

Copyusage_events
  id                uuid pk
  partner_id        uuid fk embed_partners nullable
  tenant_id         uuid fk tenants nullable
  project_id        uuid fk projects nullable
  unit              enum (render|embed_session|active_editor|ai_generation)
  quantity          integer default 1
  metadata          jsonb
  occurred_at       timestamp

usage_adjustments                           # corrections / refunds / credits
  id                uuid pk
  partner_id        uuid fk embed_partners
  unit              enum
  delta             integer
  reason            text
  created_by        uuid fk users
  created_at        timestamp

# AI generations (v0.2)

Copyai_generations
  id                uuid pk
  project_id        uuid fk projects
  embed_session_id  uuid fk embed_sessions nullable
  kind              enum (template|edit|workflow)
  prompt            text
  model             text
  input_tokens      integer
  output_tokens     integer
  output            jsonb
  accepted          boolean nullable
  created_at        timestamp

# Indices we always want

• (project_id, created_at desc) on renders — dashboard list view
• (template_id, version_number desc) on template_versions — history
• (partner_id, status, expires_at) on embed_sessions — live console
• (unit, occurred_at) on usage_events — billing rollups
• Unique partial: (project_id, slug) where deleted_at is null on templates

# Soft deletes

Templates and projects use soft delete (deleted_at). Renders, sessions, events, usage are append-only and never deleted (only archived after the retention window — TBD per plan tier).

---

Last revised: 2026-05-02