gitfor/platform

-- core: squashed from 0001_init.sql, 0002_ci_approvals.sql, 0003_repo_provisioning.sql, 0004_import_source.sql.

-- Accounts are the shared owner namespace for users and organizations.
CREATE TABLE accounts (
  id TEXT PRIMARY KEY,
  name TEXT NOT NULL UNIQUE COLLATE NOCASE,
  kind TEXT NOT NULL CHECK (kind IN ('user', 'org')),
  display_name TEXT,
  bio TEXT,
  website TEXT,
  created_at INTEGER NOT NULL,
  avatar_key TEXT
);

CREATE TABLE users (
  account_id TEXT PRIMARY KEY REFERENCES accounts(id) ON DELETE CASCADE,
  email TEXT NOT NULL UNIQUE COLLATE NOCASE,
  password_hash TEXT,
  github_id TEXT UNIQUE,
  is_admin INTEGER NOT NULL DEFAULT 0,
  email_verified_at INTEGER,
  totp_secret TEXT,
  totp_iv TEXT,
  totp_enabled_at INTEGER,
  totp_last_step INTEGER,
  sessions_revoked_at INTEGER
);

CREATE TABLE org_members (
  org_id TEXT NOT NULL REFERENCES accounts(id) ON DELETE CASCADE,
  user_id TEXT NOT NULL REFERENCES accounts(id) ON DELETE CASCADE,
  role TEXT NOT NULL CHECK (role IN ('owner', 'member')),
  created_at INTEGER NOT NULL,
  PRIMARY KEY (org_id, user_id)
);

CREATE TABLE access_tokens (
  id TEXT PRIMARY KEY,
  user_id TEXT NOT NULL REFERENCES accounts(id) ON DELETE CASCADE,
  name TEXT NOT NULL,