Database Schema

Alokai CMS uses Cloudflare D1 (SQLite) as its primary database. Migrations live in src/db/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'))
);

environments

Deployment stage within a space.

CREATE TABLE environments (
  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'))
);

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,
  environment_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,
  environment_id    TEXT NOT NULL,
  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

CREATE TABLE assets (
  id            TEXT PRIMARY KEY,
  space_id      TEXT NOT NULL,
  environment_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,
  environment_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,
  environment_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,
  environment_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'))
);

Migrations

Migrations live in src/db/migrations/ and are numbered sequentially:

File	Description
001_initial.sql	Core tables
002_seed.sql	Default org, space, env, user
003_rename_env_master.sql	Rename master env to main
004_api_keys.sql	Add api_keys table
005_api_keys_v2.sql	Add permissions column
006_layouts.sql	Add layout_id and is_default_layout to pages

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