Database Schema

Alokai CMS uses Cloudflare D1 (SQLite) as its primary database. Migrations live in apps/cms/migrations/.

Tables

organizations

Top-level multi-tenancy container.

CREATE TABLE organizations (
  id         TEXT PRIMARY KEY,
  name       TEXT NOT NULL,
  slug       TEXT NOT NULL UNIQUE,
  created_at TEXT NOT NULL DEFAULT (datetime('now')),
  updated_at TEXT NOT NULL DEFAULT (datetime('now'))
);

spaces

Content silo within an organization.

CREATE TABLE spaces (
  id         TEXT PRIMARY KEY,
  org_id     TEXT NOT NULL REFERENCES organizations(id),
  name       TEXT NOT NULL,
  slug       TEXT NOT NULL,
  created_at TEXT NOT NULL DEFAULT (datetime('now')),
  updated_at TEXT NOT NULL DEFAULT (datetime('now'))
);

editions

An isolated container of an entire site within a space (formerly environments). See Editions & Markets for the full isolation model.

CREATE TABLE editions (
  id          TEXT PRIMARY KEY,
  space_id    TEXT NOT NULL REFERENCES spaces(id),
  name        TEXT NOT NULL,
  description TEXT,
  is_default  INTEGER NOT NULL DEFAULT 0,
  created_at  TEXT NOT NULL DEFAULT (datetime('now')),
  updated_at  TEXT NOT NULL DEFAULT (datetime('now'))
);

markets

A storefront variant scoped to one edition (formerly stores). Each edition has exactly one default market; non-default markets inherit it and override per page. Slug uniqueness is per (space_id, edition_id).

CREATE TABLE markets (
  id         TEXT PRIMARY KEY,
  space_id   TEXT NOT NULL REFERENCES spaces(id),
  edition_id TEXT NOT NULL REFERENCES editions(id),
  name       TEXT NOT NULL,
  slug       TEXT NOT NULL,
  is_default INTEGER NOT NULL DEFAULT 0,
  created_at TEXT NOT NULL DEFAULT (datetime('now')),
  UNIQUE (space_id, edition_id, slug)
);

Edition-scoping

Cloning an edition copies its markets into the new edition with new IDs (default-market-<editionId>, <oldMarketId>__<editionId>). A page’s market_id always belongs to the same edition as the page’s edition_id.

users

CREATE TABLE users (
  id            TEXT PRIMARY KEY,
  email         TEXT NOT NULL UNIQUE,
  name          TEXT,
  password_hash TEXT,
  role          TEXT NOT NULL DEFAULT 'viewer',
  created_at    TEXT NOT NULL DEFAULT (datetime('now'))
);

Global roles: super_admin, admin, editor, viewer.

org_members / space_members

CREATE TABLE org_members (
  id         TEXT PRIMARY KEY,
  org_id     TEXT NOT NULL REFERENCES organizations(id),
  user_id    TEXT NOT NULL REFERENCES users(id),
  role       TEXT NOT NULL,  -- owner | admin | developer | member
  created_at TEXT NOT NULL DEFAULT (datetime('now'))
);

CREATE TABLE space_members (
  id         TEXT PRIMARY KEY,
  space_id   TEXT NOT NULL REFERENCES spaces(id),
  user_id    TEXT NOT NULL REFERENCES users(id),
  role       TEXT NOT NULL,  -- admin | editor | author
  created_at TEXT NOT NULL DEFAULT (datetime('now'))
);

content_models

CREATE TABLE content_models (
  id              TEXT PRIMARY KEY,
  space_id        TEXT NOT NULL,
  edition_id      TEXT NOT NULL,
  name            TEXT NOT NULL,
  api_id          TEXT NOT NULL,
  description     TEXT,
  fields          TEXT NOT NULL DEFAULT '[]',  -- JSON array of field definitions
  is_builtin      INTEGER NOT NULL DEFAULT 0,
  duplicated_from TEXT,
  created_by      TEXT,
  created_at      TEXT NOT NULL DEFAULT (datetime('now')),
  updated_at      TEXT NOT NULL DEFAULT (datetime('now'))
);

pages

CREATE TABLE pages (
  id                TEXT PRIMARY KEY,
  space_id          TEXT NOT NULL,
  edition_id        TEXT NOT NULL,
  market_id         TEXT,           -- NULL = the edition's default market
  content_type      TEXT NOT NULL,  -- page | categoryPage | productPage | layout
  path              TEXT NOT NULL,
  status            TEXT NOT NULL DEFAULT 'draft',
  published_version INTEGER,
  scheduled_at      TEXT,
  layout_id         TEXT REFERENCES pages(id),
  is_default_layout INTEGER NOT NULL DEFAULT 0,
  created_by        TEXT,
  created_at        TEXT NOT NULL DEFAULT (datetime('now')),
  updated_at        TEXT NOT NULL DEFAULT (datetime('now'))
);

page_versions

Immutable version snapshots.

CREATE TABLE page_versions (
  id         TEXT PRIMARY KEY,
  space_id   TEXT NOT NULL,
  page_id    TEXT NOT NULL REFERENCES pages(id),
  version    INTEGER NOT NULL,
  locale     TEXT NOT NULL DEFAULT 'en-US',
  data       TEXT NOT NULL DEFAULT '{}',  -- JSON page content
  created_by TEXT,
  created_at TEXT NOT NULL DEFAULT (datetime('now'))
);

components / component_versions

Same structure as pages/page_versions but for reusable components.

assets

Assets are space-scoped — shared across every edition in the space (no edition_id). The R2 object key is {orgId}/{spaceId}/{assetId}/{filename}.

CREATE TABLE assets (
  id            TEXT PRIMARY KEY,
  space_id      TEXT NOT NULL,
  filename      TEXT NOT NULL,
  content_type  TEXT NOT NULL,
  size          INTEGER NOT NULL,
  r2_key        TEXT NOT NULL,
  alt_text      TEXT,
  width         INTEGER,
  height        INTEGER,
  created_by    TEXT,
  created_at    TEXT NOT NULL DEFAULT (datetime('now'))
);

webhooks

CREATE TABLE webhooks (
  id             TEXT PRIMARY KEY,
  space_id       TEXT NOT NULL,
  edition_id     TEXT NOT NULL,
  name           TEXT NOT NULL,
  url            TEXT NOT NULL,
  events         TEXT NOT NULL DEFAULT '[]',  -- JSON string array
  headers        TEXT NOT NULL DEFAULT '{}',  -- JSON object
  is_active      INTEGER NOT NULL DEFAULT 1,
  secret         TEXT,
  created_at     TEXT NOT NULL DEFAULT (datetime('now'))
);

api_keys

CREATE TABLE api_keys (
  id           TEXT PRIMARY KEY,
  space_id     TEXT NOT NULL,
  name         TEXT NOT NULL,
  description  TEXT,
  prefix       TEXT NOT NULL,
  key_hash     TEXT NOT NULL UNIQUE,
  permissions  TEXT NOT NULL DEFAULT '[]',  -- JSON string array
  created_by   TEXT,
  created_at   TEXT NOT NULL DEFAULT (datetime('now')),
  last_used_at TEXT
);

locales

CREATE TABLE locales (
  id             TEXT PRIMARY KEY,
  space_id       TEXT NOT NULL,
  edition_id     TEXT NOT NULL,
  code           TEXT NOT NULL,  -- e.g. en-US
  name           TEXT NOT NULL,
  is_default     INTEGER NOT NULL DEFAULT 0
);

preview_urls

CREATE TABLE preview_urls (
  id             TEXT PRIMARY KEY,
  space_id       TEXT NOT NULL,
  edition_id     TEXT NOT NULL,
  name           TEXT NOT NULL,
  url            TEXT NOT NULL,
  is_default     INTEGER NOT NULL DEFAULT 0,
  created_at     TEXT NOT NULL DEFAULT (datetime('now'))
);

audit_log

CREATE TABLE audit_log (
  id            TEXT PRIMARY KEY,
  space_id      TEXT NOT NULL,
  user_id       TEXT,
  action        TEXT NOT NULL,
  resource_type TEXT NOT NULL,
  resource_id   TEXT NOT NULL,
  details       TEXT,
  created_at    TEXT NOT NULL DEFAULT (datetime('now'))
);

Additional tables

The schema has grown well beyond the core tables above. The following also exist; see the migrations for exact columns rather than relying on this list:

Table	Purpose
page_locale_published_versions	Per-locale published-version tracking (a locale can publish independently).
locale_settings	Locale-detection configuration (path segment, query param, cookie, domain, TLD).
component_source / component_templates	Runtime-compiled remote components (Tier-1 sources, Tier-2 primitives).
translation_jobs	One row per AI translation request; tracks status and target locale. See Translations.
glossary	Per-space do-not-translate terms and per-locale overrides.
scheduled_actions	Scheduled publish/unpublish for a page at a future time. See Scheduling & Approvals.
approval_requests	Publish-approval workflow rows, optionally linked to a scheduled action.
experiences	Per-page A/B test definitions (variants, impressions). See A/B Testing.
audiences	Rule-based visitor segments with priority.
ab_visitor_aliases	Sticky variant assignment across sessions/login.
design_system_settings	Per-edition design tokens (color, typography, spacing).

Localized content storage

page_versions stores all locales for a page in a single row under locale = '_all'; per-field localized values live inside the data JSON as { _localized: true, "<locale>": { value, _source } }. The _source marker (human / draft / reviewed) drives the translation review workflow.

Migrations

Migrations live in apps/cms/migrations/ and are numbered sequentially (001_initial.sql … through the 046–048 rename + edition-scoping set). The list is not exhaustive here — read the directory for the current state. Notable recent ones:

File	Description
043_translation_jobs_env_scope.sql	Scope translation jobs to the edition.
044_assets_space_scoped.sql	Make assets space-scoped (drop edition from the key).
046_rename_environments_to_editions.sql	Rename environments → editions, environment_id → edition_id.
047_rename_stores_to_markets.sql	Rename stores → markets, store_id → market_id.
048_markets_edition_scoped.sql	Replicate markets per edition; add edition_id; per-(space, edition) slug uniqueness.

Migrations also run idempotently on worker cold-start, so a fresh deploy self-heals its schema.

Run locally: yarn db:migrate Run on remote: wrangler d1 migrations apply alokai-cms-db --remote