ubiquitous-invention/docs/operations/2026-06-05-db-drift-reconcile-oauth-tables.sql

-- ============================================================================
-- One-shot drift reconciliation #2 against CT 102's `tasks` database.
--
-- Background (read 2026-06-02-db-drift-reconcile.sql first for full history):
--   The CT 102 DB was bootstrapped from `drizzle-kit push`, which writes
--   schema directly and never records ledger rows. The 2026-06-02 reconcile
--   stamped migrations 0000-0007 as applied AND created the two tables
--   that script could see were missing (markdown_backlog_items,
--   cursor_sync_mappings). It did NOT verify every other table from
--   every other migration was present — it trusted the historical
--   `drizzle-kit push` had landed them.
--
--   That assumption broke today (2026-06-05) when SSO via Authentik failed
--   on the OAuth callback with:
--     [auth][cause]: relation "accounts" does not exist
--   `accounts` is defined in migration 0000 (lines 67-80 of
--   0000_nervous_ogun.sql) but never made it onto CT 102. Credentials
--   login worked all along because it only reads `users`; OAuth providers
--   funnel through `resolveOAuthUser` in apps/web/lib/auth.ts, which
--   writes to `accounts` and `user_email_identities`.
--
--   Because the 2026-06-02 script stamped 0000 as applied, drizzle's
--   migrator will not replay it. The only safe fix is another idempotent
--   one-shot.
--
-- This script (idempotent, safe to run more than once):
--   1. Creates every auth-adjacent table that *might* be missing from
--      migrations 0000, 0005, 0006, 0007, 0009, at their final post-0009
--      shape. CREATE TABLE IF NOT EXISTS makes already-present tables a
--      no-op.
--   2. Adds the matching FKs and indexes, each guarded with a DO block so
--      duplicate_object / duplicate_table errors are swallowed.
--   3. Backfills user_email_identities from existing users (matches the
--      seed step in migration 0005).
--   4. Does NOT touch drizzle.__drizzle_migrations. The ledger is already
--      consistent — see step 3 of the operator notes at the bottom for
--      the verification query.
--
-- Run as:
--   psql "$DATABASE_URL" -f 2026-06-05-db-drift-reconcile-oauth-tables.sql
--
-- After running, `pnpm db:migrate` stays a clean no-op and Authentik /
-- GitHub / Google sign-in will work.
-- ============================================================================

BEGIN;

-- ----------------------------------------------------------------------------
-- 1. accounts (migration 0000) — the table whose absence is breaking SSO.
-- ----------------------------------------------------------------------------

CREATE TABLE IF NOT EXISTS "accounts" (
  "id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
  "user_id" uuid NOT NULL,
  "type" varchar(255) NOT NULL,
  "provider" varchar(255) NOT NULL,
  "provider_account_id" varchar(255) NOT NULL,
  "refresh_token" text,
  "access_token" text,
  "expires_at" integer,
  "token_type" varchar(255),
  "scope" varchar(255),
  "id_token" text,
  "session_state" varchar(255)
);

DO $$ BEGIN
  ALTER TABLE "accounts"
    ADD CONSTRAINT "accounts_user_id_users_id_fk"
    FOREIGN KEY ("user_id") REFERENCES "public"."users"("id")
    ON DELETE cascade ON UPDATE no action;
EXCEPTION WHEN duplicate_object THEN NULL; END $$;

CREATE UNIQUE INDEX IF NOT EXISTS "accounts_provider_provider_account_id_unique"
  ON "accounts" USING btree ("provider", "provider_account_id");
CREATE INDEX IF NOT EXISTS "accounts_user_id_idx"
  ON "accounts" USING btree ("user_id");

-- ----------------------------------------------------------------------------
-- 2. sessions + verification_tokens (migration 0000).
--    Not strictly required at runtime since session.strategy = "jwt", but
--    keeping the schema honest avoids the next "wait, that's missing too?"
--    moment. Skipped harmlessly if already present.
-- ----------------------------------------------------------------------------

CREATE TABLE IF NOT EXISTS "sessions" (
  "id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
  "session_token" varchar(255) NOT NULL,
  "user_id" uuid NOT NULL,
  "expires" timestamp with time zone NOT NULL,
  CONSTRAINT "sessions_session_token_unique" UNIQUE ("session_token")
);

DO $$ BEGIN
  ALTER TABLE "sessions"
    ADD CONSTRAINT "sessions_user_id_users_id_fk"
    FOREIGN KEY ("user_id") REFERENCES "public"."users"("id")
    ON DELETE cascade ON UPDATE no action;
EXCEPTION WHEN duplicate_object THEN NULL; END $$;

CREATE INDEX IF NOT EXISTS "sessions_user_id_idx"
  ON "sessions" USING btree ("user_id");

CREATE TABLE IF NOT EXISTS "verification_tokens" (
  "identifier" varchar(255) NOT NULL,
  "token" varchar(255) NOT NULL,
  "expires" timestamp with time zone NOT NULL,
  CONSTRAINT "verification_tokens_identifier_token_pk"
    PRIMARY KEY ("identifier", "token")
);

-- ----------------------------------------------------------------------------
-- 3. user_email_identities (migration 0005).
--    The OAuth path also writes here via ensureUserIdByVerifiedEmail, so
--    its absence would re-break SSO right after we fixed `accounts`.
-- ----------------------------------------------------------------------------

CREATE TABLE IF NOT EXISTS "user_email_identities" (
  "id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
  "user_id" uuid NOT NULL,
  "email" varchar(255) NOT NULL,
  "verified_at" timestamp with time zone,
  "source" varchar(30) NOT NULL,
  "created_at" timestamp with time zone DEFAULT now() NOT NULL,
  "last_used_at" timestamp with time zone
);

DO $$ BEGIN
  ALTER TABLE "user_email_identities"
    ADD CONSTRAINT "user_email_identities_user_id_users_id_fk"
    FOREIGN KEY ("user_id") REFERENCES "public"."users"("id")
    ON DELETE cascade ON UPDATE no action;
EXCEPTION WHEN duplicate_object THEN NULL; END $$;

CREATE INDEX IF NOT EXISTS "user_email_identities_user_id_idx"
  ON "user_email_identities" USING btree ("user_id");
CREATE INDEX IF NOT EXISTS "user_email_identities_email_idx"
  ON "user_email_identities" USING btree ("email");
CREATE UNIQUE INDEX IF NOT EXISTS "user_email_identities_user_id_email_unique"
  ON "user_email_identities" USING btree ("user_id", "email");
CREATE UNIQUE INDEX IF NOT EXISTS "user_email_identities_verified_email_unique"
  ON "user_email_identities" USING btree ("email")
  WHERE "user_email_identities"."verified_at" IS NOT NULL;

-- Backfill (matches the seed in migration 0005). ON CONFLICT makes this
-- safe to re-run.
INSERT INTO "user_email_identities" ("user_id", "email", "verified_at", "source", "created_at")
SELECT "id", lower("email"), "created_at", 'primary', "created_at"
FROM "users"
ON CONFLICT ("user_id", "email") DO NOTHING;

-- ----------------------------------------------------------------------------
-- 4. workspace_invites (migration 0006).
--    Not on the SSO critical path, but the invite-acceptance flow lands in
--    the same auth.ts module and will hit a missing-table error the first
--    time a workspace admin clicks "invite".
-- ----------------------------------------------------------------------------

CREATE TABLE IF NOT EXISTS "workspace_invites" (
  "id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
  "workspace_id" uuid NOT NULL,
  "email" varchar(255) NOT NULL,
  "role" varchar(20) NOT NULL,
  "invited_by_user_id" uuid NOT NULL,
  "token" varchar(128) NOT NULL,
  "expires_at" timestamp with time zone DEFAULT now() + interval '14 days' NOT NULL,
  "accepted_at" timestamp with time zone,
  "revoked_at" timestamp with time zone,
  "created_at" timestamp with time zone DEFAULT now() NOT NULL
);

DO $$ BEGIN
  ALTER TABLE "workspace_invites"
    ADD CONSTRAINT "workspace_invites_workspace_id_workspaces_id_fk"
    FOREIGN KEY ("workspace_id") REFERENCES "public"."workspaces"("id")
    ON DELETE cascade ON UPDATE no action;
EXCEPTION WHEN duplicate_object THEN NULL; END $$;

DO $$ BEGIN
  ALTER TABLE "workspace_invites"
    ADD CONSTRAINT "workspace_invites_invited_by_user_id_users_id_fk"
    FOREIGN KEY ("invited_by_user_id") REFERENCES "public"."users"("id")
    ON DELETE cascade ON UPDATE no action;
EXCEPTION WHEN duplicate_object THEN NULL; END $$;

CREATE INDEX IF NOT EXISTS "workspace_invites_workspace_id_idx"
  ON "workspace_invites" USING btree ("workspace_id");
CREATE UNIQUE INDEX IF NOT EXISTS "workspace_invites_token_unique"
  ON "workspace_invites" USING btree ("token");
CREATE UNIQUE INDEX IF NOT EXISTS "workspace_invites_open_email_unique"
  ON "workspace_invites" USING btree ("workspace_id", "email")
  WHERE "workspace_invites"."accepted_at" IS NULL
    AND "workspace_invites"."revoked_at" IS NULL;

-- ----------------------------------------------------------------------------
-- 5. audit_log (migration 0007).
--    The 2026-06-02 notes claimed audit_log was added manually, but if a
--    fresh DB ever skipped that step this guards it.
-- ----------------------------------------------------------------------------

CREATE TABLE IF NOT EXISTS "audit_log" (
  "id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
  "workspace_id" uuid NOT NULL,
  "actor_user_id" uuid,
  "action" varchar(100) NOT NULL,
  "target_type" varchar(50) NOT NULL,
  "target_id" uuid,
  "metadata" jsonb,
  "created_at" timestamp with time zone DEFAULT now() NOT NULL
);

DO $$ BEGIN
  ALTER TABLE "audit_log"
    ADD CONSTRAINT "audit_log_workspace_id_workspaces_id_fk"
    FOREIGN KEY ("workspace_id") REFERENCES "public"."workspaces"("id")
    ON DELETE cascade ON UPDATE no action;
EXCEPTION WHEN duplicate_object THEN NULL; END $$;

DO $$ BEGIN
  ALTER TABLE "audit_log"
    ADD CONSTRAINT "audit_log_actor_user_id_users_id_fk"
    FOREIGN KEY ("actor_user_id") REFERENCES "public"."users"("id")
    ON DELETE set null ON UPDATE no action;
EXCEPTION WHEN duplicate_object THEN NULL; END $$;

CREATE INDEX IF NOT EXISTS "audit_log_workspace_id_created_at_idx"
  ON "audit_log" USING btree ("workspace_id", "created_at");
CREATE INDEX IF NOT EXISTS "audit_log_actor_user_id_idx"
  ON "audit_log" USING btree ("actor_user_id");
CREATE INDEX IF NOT EXISTS "audit_log_action_idx"
  ON "audit_log" USING btree ("action");

-- ----------------------------------------------------------------------------
-- 6. agent_runs (migration 0009).
--    Added after the 2026-06-02 reconcile, so it *should* be present from
--    a normal `pnpm db:migrate` run. Guard it anyway since this script is
--    meant to leave the schema in a known state.
-- ----------------------------------------------------------------------------

CREATE TABLE IF NOT EXISTS "agent_runs" (
  "id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
  "workspace_id" uuid NOT NULL,
  "backlog_item_id" uuid NOT NULL,
  "actor_user_id" uuid,
  "started_at" timestamp with time zone DEFAULT now() NOT NULL,
  "finished_at" timestamp with time zone,
  "outcome" varchar(20),
  "error" text,
  "tokens_input" integer,
  "tokens_output" integer,
  "tokens_total" integer,
  "notes" text,
  "metadata" jsonb
);

DO $$ BEGIN
  ALTER TABLE "agent_runs"
    ADD CONSTRAINT "agent_runs_workspace_id_workspaces_id_fk"
    FOREIGN KEY ("workspace_id") REFERENCES "public"."workspaces"("id")
    ON DELETE cascade ON UPDATE no action;
EXCEPTION WHEN duplicate_object THEN NULL; END $$;

DO $$ BEGIN
  ALTER TABLE "agent_runs"
    ADD CONSTRAINT "agent_runs_backlog_item_id_markdown_backlog_items_id_fk"
    FOREIGN KEY ("backlog_item_id") REFERENCES "public"."markdown_backlog_items"("id")
    ON DELETE cascade ON UPDATE no action;
EXCEPTION WHEN duplicate_object THEN NULL; END $$;

DO $$ BEGIN
  ALTER TABLE "agent_runs"
    ADD CONSTRAINT "agent_runs_actor_user_id_users_id_fk"
    FOREIGN KEY ("actor_user_id") REFERENCES "public"."users"("id")
    ON DELETE set null ON UPDATE no action;
EXCEPTION WHEN duplicate_object THEN NULL; END $$;

CREATE INDEX IF NOT EXISTS "agent_runs_workspace_id_started_at_idx"
  ON "agent_runs" USING btree ("workspace_id", "started_at" DESC NULLS LAST);
CREATE INDEX IF NOT EXISTS "agent_runs_backlog_item_id_started_at_idx"
  ON "agent_runs" USING btree ("backlog_item_id", "started_at" DESC NULLS LAST);

COMMIT;

-- ============================================================================
-- Operator post-checks (run by hand, not part of the transaction above):
--
--   1. Confirm every expected table is present:
--        SELECT tablename FROM pg_tables
--        WHERE schemaname = 'public'
--          AND tablename IN ('accounts','sessions','verification_tokens',
--                            'user_email_identities','workspace_invites',
--                            'audit_log','agent_runs')
--        ORDER BY tablename;
--      Expect all 7.
--
--   2. Sanity-check the OAuth path by signing in via Authentik. The
--      callback at /api/auth/callback/authentik should now write a row
--      to `accounts` and `user_email_identities` instead of 500'ing.
--
--   3. Confirm the drizzle ledger is still consistent:
--        SELECT hash, created_at
--        FROM drizzle.__drizzle_migrations
--        ORDER BY created_at;
--      Expect 10 rows ending at 0009_loving_rogue's hash
--      (cf. packages/database/migrations/meta/_journal.json). If 0008 or
--      0009 are missing, run `pnpm db:migrate` from the repo root and
--      they will be applied + stamped normally.
-- ============================================================================