Evaluated Drizzle metadata includes partial-index predicates, ordering and RLS policy clauses below. Liam draws one line per column in a composite foreign key; those lines represent one tuple constraint, not independent foreign keys. Migration SQL is shown verbatim from the same source snapshot. Triggers, grants and custom SQL overrides require reading the migration SQL; the diagram does not evaluate migration effects or prove database behavior.
Row-level security: enabled.
POLICY account_tenant_isolation: AS permissive FOR all TO authenticated USING (user_id = nullif(current_setting('app.user_id', true), '')::uuid) WITH CHECK (user_id = nullif(current_setting('app.user_id', true), '')::uuid)
INDEX account_user_id_idx USING btree (user_id ASC NULLS LAST)
Row-level security: enabled.
POLICY action_logs_tenant_isolation: AS permissive FOR all TO authenticated USING (user_id = nullif(current_setting('app.user_id', true), '')::uuid) WITH CHECK (user_id = nullif(current_setting('app.user_id', true), '')::uuid)
INDEX action_logs_user_id_idx USING btree (user_id ASC NULLS LAST)
INDEX action_logs_action_user_idx USING btree (action_id ASC NULLS LAST, user_id ASC NULLS LAST)
INDEX action_logs_session_user_idx USING btree (session_id ASC NULLS LAST, user_id ASC NULLS LAST)
INDEX action_logs_history_idx USING btree (action_id ASC NULLS LAST, recorded_at DESC NULLS LAST, id DESC NULLS LAST)
Row-level security: enabled.
POLICY actions_tenant_isolation: AS permissive FOR all TO authenticated USING (user_id = nullif(current_setting('app.user_id', true), '')::uuid) WITH CHECK (user_id = nullif(current_setting('app.user_id', true), '')::uuid)
UNIQUE INDEX actions_id_user_uidx USING btree (id ASC NULLS LAST, user_id ASC NULLS LAST)
INDEX actions_user_id_idx USING btree (user_id ASC NULLS LAST)
INDEX actions_cycle_user_idx USING btree (cycle_id ASC NULLS LAST, user_id ASC NULLS LAST)
INDEX actions_objective_cycle_user_idx USING btree (objective_id ASC NULLS LAST, cycle_id ASC NULLS LAST, user_id ASC NULLS LAST)
INDEX actions_session_user_idx USING btree (session_id ASC NULLS LAST, user_id ASC NULLS LAST)
INDEX actions_outstanding_order_idx USING btree (cycle_id ASC NULLS LAST, objective_id ASC NULLS LAST, sort_order ASC NULLS LAST, due_at ASC NULLS LAST)
Row-level security: enabled.
POLICY session_tenant_isolation: AS permissive FOR all TO authenticated USING (user_id = nullif(current_setting('app.user_id', true), '')::uuid) WITH CHECK (user_id = nullif(current_setting('app.user_id', true), '')::uuid)
INDEX session_user_id_idx USING btree (user_id ASC NULLS LAST)
Row-level security: enabled.
POLICY cycles_tenant_isolation: AS permissive FOR all TO authenticated USING (user_id = nullif(current_setting('app.user_id', true), '')::uuid) WITH CHECK (user_id = nullif(current_setting('app.user_id', true), '')::uuid)
UNIQUE INDEX cycles_id_user_uidx USING btree (id ASC NULLS LAST, user_id ASC NULLS LAST)
INDEX cycles_user_id_idx USING btree (user_id ASC NULLS LAST)
UNIQUE INDEX cycles_one_active_uidx USING btree (user_id ASC NULLS LAST) WHERE "cycles"."status" = 'active'
CHECK cycles_date_range_check: "cycles"."end_at" > "cycles"."start_at"Row-level security: enabled.
POLICY example_tenant_isolation: AS permissive FOR all TO authenticated USING (user_id = nullif(current_setting('app.user_id', true), '')::uuid) WITH CHECK (user_id = nullif(current_setting('app.user_id', true), '')::uuid)
INDEX example_user_id_idx USING btree (user_id ASC NULLS LAST)
Row-level security: enabled.
POLICY habit_logs_tenant_isolation: AS permissive FOR all TO authenticated USING (user_id = nullif(current_setting('app.user_id', true), '')::uuid) WITH CHECK (user_id = nullif(current_setting('app.user_id', true), '')::uuid)
INDEX habit_logs_user_id_idx USING btree (user_id ASC NULLS LAST)
INDEX habit_logs_result_user_idx USING btree (result_id ASC NULLS LAST, user_id ASC NULLS LAST)
INDEX habit_logs_session_user_idx USING btree (session_id ASC NULLS LAST, user_id ASC NULLS LAST)
UNIQUE INDEX habit_logs_result_day_uidx USING btree (result_id ASC NULLS LAST, day ASC NULLS LAST)
Row-level security: enabled.
POLICY idempotency_keys_tenant_isolation: AS permissive FOR all TO authenticated USING (user_id = nullif(current_setting('app.user_id', true), '')::uuid) WITH CHECK (user_id = nullif(current_setting('app.user_id', true), '')::uuid)
UNIQUE INDEX idempotency_keys_user_op_key_uidx USING btree (user_id ASC NULLS LAST, operation ASC NULLS LAST, idempotency_key ASC NULLS LAST)
Row-level security: disabled.
Row-level security: enabled.
POLICY notes_tenant_isolation: AS permissive FOR all TO authenticated USING (user_id = nullif(current_setting('app.user_id', true), '')::uuid) WITH CHECK (user_id = nullif(current_setting('app.user_id', true), '')::uuid)
INDEX notes_history_idx USING btree (user_id ASC NULLS LAST, recorded_at DESC NULLS LAST, id DESC NULLS LAST)
CHECK notes_nonblank_text_check: "notes"."text" ~ '[^[:space:]]'Row-level security: enabled.
POLICY oauth_access_token_tenant_isolation: AS permissive FOR all TO authenticated USING (user_id = nullif(current_setting('app.user_id', true), '')::uuid) WITH CHECK (user_id = nullif(current_setting('app.user_id', true), '')::uuid)
INDEX oauth_access_token_client_id_idx USING btree (client_id ASC NULLS LAST)
INDEX oauth_access_token_session_id_idx USING btree (session_id ASC NULLS LAST)
INDEX oauth_access_token_user_id_idx USING btree (user_id ASC NULLS LAST)
INDEX oauth_access_token_authorization_code_id_idx USING btree (authorization_code_id ASC NULLS LAST)
INDEX oauth_access_token_refresh_id_idx USING btree (refresh_id ASC NULLS LAST)
Row-level security: disabled. INDEX oauth_client_user_id_idx USING btree (user_id ASC NULLS LAST) INDEX oauth_client_client_id_idx USING btree (client_id ASC NULLS LAST)
Row-level security: disabled.
Row-level security: disabled. INDEX oauth_client_resource_resource_id_idx USING btree (resource_id ASC NULLS LAST) UNIQUE INDEX oauth_client_resource_client_id_resource_id_uidx USING btree (client_id ASC NULLS LAST, resource_id ASC NULLS LAST)
Row-level security: enabled.
POLICY oauth_consent_tenant_isolation: AS permissive FOR all TO authenticated USING (user_id = nullif(current_setting('app.user_id', true), '')::uuid) WITH CHECK (user_id = nullif(current_setting('app.user_id', true), '')::uuid)
INDEX oauth_consent_client_id_idx USING btree (client_id ASC NULLS LAST)
INDEX oauth_consent_user_id_idx USING btree (user_id ASC NULLS LAST)
Row-level security: enabled.
POLICY oauth_refresh_token_tenant_isolation: AS permissive FOR all TO authenticated USING (user_id = nullif(current_setting('app.user_id', true), '')::uuid) WITH CHECK (user_id = nullif(current_setting('app.user_id', true), '')::uuid)
INDEX oauth_refresh_token_client_id_idx USING btree (client_id ASC NULLS LAST)
INDEX oauth_refresh_token_session_id_idx USING btree (session_id ASC NULLS LAST)
INDEX oauth_refresh_token_user_id_idx USING btree (user_id ASC NULLS LAST)
INDEX oauth_refresh_token_authorization_code_id_idx USING btree (authorization_code_id ASC NULLS LAST)
Row-level security: disabled.
Row-level security: enabled.
POLICY objectives_tenant_isolation: AS permissive FOR all TO authenticated USING (user_id = nullif(current_setting('app.user_id', true), '')::uuid) WITH CHECK (user_id = nullif(current_setting('app.user_id', true), '')::uuid)
UNIQUE INDEX objectives_id_user_uidx USING btree (id ASC NULLS LAST, user_id ASC NULLS LAST)
UNIQUE INDEX objectives_id_cycle_user_uidx USING btree (id ASC NULLS LAST, cycle_id ASC NULLS LAST, user_id ASC NULLS LAST)
INDEX objectives_user_id_idx USING btree (user_id ASC NULLS LAST)
INDEX objectives_cycle_user_idx USING btree (cycle_id ASC NULLS LAST, user_id ASC NULLS LAST)
Row-level security: enabled.
POLICY product_events_tenant_insert: AS permissive FOR insert TO authenticated WITH CHECK ("product_events"."user_id" = nullif(current_setting('app.user_id', true), '')::uuid)
INDEX product_events_user_id_idx USING btree (user_id ASC NULLS LAST)
INDEX product_events_event_type_idx USING btree (event_type ASC NULLS LAST)
Row-level security: enabled.
POLICY result_logs_tenant_isolation: AS permissive FOR all TO authenticated USING (user_id = nullif(current_setting('app.user_id', true), '')::uuid) WITH CHECK (user_id = nullif(current_setting('app.user_id', true), '')::uuid)
INDEX result_logs_user_id_idx USING btree (user_id ASC NULLS LAST)
INDEX result_logs_result_user_idx USING btree (result_id ASC NULLS LAST, user_id ASC NULLS LAST)
INDEX result_logs_session_user_idx USING btree (session_id ASC NULLS LAST, user_id ASC NULLS LAST)
INDEX result_logs_history_idx USING btree (result_id ASC NULLS LAST, recorded_at DESC NULLS LAST, id DESC NULLS LAST)
CHECK result_logs_finite_value_check: "result_logs"."value" IS NULL OR "result_logs"."value" NOT IN ('NaN'::numeric, 'Infinity'::numeric, '-Infinity'::numeric)Row-level security: enabled.
POLICY results_tenant_isolation: AS permissive FOR all TO authenticated USING (user_id = nullif(current_setting('app.user_id', true), '')::uuid) WITH CHECK (user_id = nullif(current_setting('app.user_id', true), '')::uuid)
UNIQUE INDEX results_id_user_uidx USING btree (id ASC NULLS LAST, user_id ASC NULLS LAST)
INDEX results_user_id_idx USING btree (user_id ASC NULLS LAST)
INDEX results_objective_user_idx USING btree (objective_id ASC NULLS LAST, user_id ASC NULLS LAST)
CHECK results_number_check: "results"."metric_type" <> 'number' OR ("results"."start_value" IS NOT NULL AND "results"."target_value" IS NOT NULL AND "results"."cadence" IS NULL AND "results"."per" IS NULL AND "results"."since" IS NULL)
CHECK results_percentage_check: "results"."metric_type" <> 'percentage' OR ("results"."start_value" IS NOT NULL AND "results"."target_value" IS NOT NULL AND "results"."start_value" BETWEEN 0 AND 100 AND "results"."target_value" BETWEEN 0 AND 100 AND "results"."cadence" IS NULL AND "results"."per" IS NULL AND "results"."since" IS NULL AND "results"."unit" IS NULL)
CHECK results_milestone_check: "results"."metric_type" <> 'milestone' OR ("results"."start_value" IS NOT NULL AND "results"."start_value" = 0 AND "results"."target_value" IS NOT NULL AND "results"."target_value" = 1 AND "results"."cadence" IS NULL AND "results"."per" IS NULL AND "results"."since" IS NULL AND "results"."unit" IS NULL)
CHECK results_habit_check: "results"."metric_type" <> 'habit' OR ("results"."cadence" IS NOT NULL AND "results"."per" IS NOT NULL AND "results"."per" >= 1 AND "results"."since" IS NOT NULL AND "results"."start_value" IS NULL AND "results"."target_value" IS NULL AND "results"."unit" IS NULL)
CHECK results_performance_check: "results"."metric_type" <> 'performance' OR ("results"."target_value" IS NOT NULL AND "results"."since" IS NOT NULL AND "results"."cadence" IS NULL AND "results"."per" IS NULL)
CHECK results_distinct_target_check: "results"."metric_type" NOT IN ('number', 'percentage') OR "results"."target_value" <> "results"."start_value"
CHECK results_finite_values_check: ("results"."start_value" IS NULL OR "results"."start_value" NOT IN ('NaN'::numeric, 'Infinity'::numeric, '-Infinity'::numeric)) AND ("results"."target_value" IS NULL OR "results"."target_value" NOT IN ('NaN'::numeric, 'Infinity'::numeric, '-Infinity'::numeric))Row-level security: enabled.
POLICY sessions_tenant_isolation: AS permissive FOR all TO authenticated USING (user_id = nullif(current_setting('app.user_id', true), '')::uuid) WITH CHECK (user_id = nullif(current_setting('app.user_id', true), '')::uuid)
UNIQUE INDEX sessions_id_user_uidx USING btree (id ASC NULLS LAST, user_id ASC NULLS LAST)
INDEX sessions_user_id_idx USING btree (user_id ASC NULLS LAST)
UNIQUE INDEX sessions_one_open_uidx USING btree (user_id ASC NULLS LAST) WHERE "sessions"."ended_at" IS NULL
INDEX sessions_boundary_idx USING btree (user_id ASC NULLS LAST, ended_at DESC NULLS LAST) WHERE "sessions"."ended_at" IS NOT NULL
Row-level security: enabled.
POLICY subscription_tenant_isolation: AS permissive FOR all TO authenticated USING ("subscription"."reference_id" = nullif(current_setting('app.user_id', true), '')::uuid) WITH CHECK ("subscription"."reference_id" = nullif(current_setting('app.user_id', true), '')::uuid)
INDEX subscription_reference_id_idx USING btree (reference_id ASC NULLS LAST)
UNIQUE INDEX subscription_stripe_subscription_id_uidx USING btree (stripe_subscription_id ASC NULLS LAST)
INDEX subscription_stripe_customer_id_idx USING btree (stripe_customer_id ASC NULLS LAST)
Row-level security: enabled.
POLICY user_self_isolation: AS permissive FOR all TO authenticated USING ("user"."id" = nullif(current_setting('app.user_id', true), '')::uuid) WITH CHECK ("user"."id" = nullif(current_setting('app.user_id', true), '')::uuid)
Row-level security: disabled. INDEX verification_identifier_idx USING btree (identifier ASC NULLS LAST)
-- Ensure the `authenticated` role exists before policies reference it.
-- Drizzle Kit generates `CREATE POLICY … TO authenticated`; without this
-- guard the migration aborts with `role "authenticated" does not exist`
-- (verified failure on the prior attempt). The role is NOLOGIN +
-- NOBYPASSRLS — `coach_app_rt` is GRANTed into it by 0001.
DO $$
BEGIN
IF NOT EXISTS (SELECT 1 FROM pg_roles WHERE rolname = 'authenticated') THEN
CREATE ROLE "authenticated" NOLOGIN NOBYPASSRLS;
END IF;
END$$;
--> statement-breakpoint
CREATE TABLE "account" (
"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
"user_id" uuid NOT NULL,
"account_id" text NOT NULL,
"provider_id" text NOT NULL,
"access_token" text,
"refresh_token" text,
"access_token_expires_at" timestamp with time zone,
"refresh_token_expires_at" timestamp with time zone,
"scope" text,
"id_token" text,
"password" text,
"created_at" timestamp with time zone DEFAULT now() NOT NULL,
"updated_at" timestamp with time zone DEFAULT now() NOT NULL
);
--> statement-breakpoint
ALTER TABLE "account" ENABLE ROW LEVEL SECURITY;--> statement-breakpoint
CREATE TABLE "example" (
"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
"user_id" uuid NOT NULL,
"name" text NOT NULL,
"deleted_at" timestamp with time zone,
"created_at" timestamp with time zone DEFAULT now() NOT NULL,
"updated_at" timestamp with time zone DEFAULT now() NOT NULL
);
--> statement-breakpoint
ALTER TABLE "example" ENABLE ROW LEVEL SECURITY;--> statement-breakpoint
CREATE TABLE "idempotency_keys" (
"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
"user_id" uuid NOT NULL,
"operation" text NOT NULL,
"idempotency_key" text,
"request_hash" text NOT NULL,
"response_ref" text,
"expires_at" timestamp with time zone,
"created_at" timestamp with time zone DEFAULT now() NOT NULL,
"updated_at" timestamp with time zone DEFAULT now() NOT NULL
);
--> statement-breakpoint
ALTER TABLE "idempotency_keys" ENABLE ROW LEVEL SECURITY;--> statement-breakpoint
CREATE TABLE "jwks" (
"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
"public_key" text NOT NULL,
"private_key" text NOT NULL,
"created_at" timestamp with time zone DEFAULT now() NOT NULL,
"updated_at" timestamp with time zone DEFAULT now() NOT NULL
);
--> statement-breakpoint
CREATE TABLE "oauth_access_token" (
"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
"user_id" uuid NOT NULL,
"client_id" text NOT NULL,
"access_token" text NOT NULL,
"refresh_token" text,
"access_token_expires_at" timestamp with time zone NOT NULL,
"refresh_token_expires_at" timestamp with time zone,
"scopes" text NOT NULL,
"created_at" timestamp with time zone DEFAULT now() NOT NULL,
"updated_at" timestamp with time zone DEFAULT now() NOT NULL,
CONSTRAINT "oauth_access_token_access_token_unique" UNIQUE("access_token"),
CONSTRAINT "oauth_access_token_refresh_token_unique" UNIQUE("refresh_token")
);
--> statement-breakpoint
ALTER TABLE "oauth_access_token" ENABLE ROW LEVEL SECURITY;--> statement-breakpoint
CREATE TABLE "oauth_client" (
"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
"client_id" text NOT NULL,
"client_secret" text,
"name" text NOT NULL,
"redirect_uris" text NOT NULL,
"type" text NOT NULL,
"disabled" boolean DEFAULT false NOT NULL,
"metadata" jsonb,
"created_at" timestamp with time zone DEFAULT now() NOT NULL,
"updated_at" timestamp with time zone DEFAULT now() NOT NULL,
CONSTRAINT "oauth_client_client_id_unique" UNIQUE("client_id")
);
--> statement-breakpoint
CREATE TABLE "oauth_consent" (
"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
"user_id" uuid NOT NULL,
"client_id" text NOT NULL,
"scopes" text NOT NULL,
"created_at" timestamp with time zone DEFAULT now() NOT NULL,
"updated_at" timestamp with time zone DEFAULT now() NOT NULL
);
--> statement-breakpoint
ALTER TABLE "oauth_consent" ENABLE ROW LEVEL SECURITY;--> statement-breakpoint
CREATE TABLE "product_events" (
"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
"user_id" uuid,
"event_type" text NOT NULL,
"occurred_at" timestamp with time zone DEFAULT now() NOT NULL,
"properties" jsonb,
"stripe_customer_id" text,
"subscription_id" text,
"created_at" timestamp with time zone DEFAULT now() NOT NULL,
"updated_at" timestamp with time zone DEFAULT now() NOT NULL
);
--> statement-breakpoint
ALTER TABLE "product_events" ENABLE ROW LEVEL SECURITY;--> statement-breakpoint
CREATE TABLE "session" (
"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
"user_id" uuid NOT NULL,
"token" text NOT NULL,
"expires_at" timestamp with time zone NOT NULL,
"ip_address" text,
"user_agent" text,
"created_at" timestamp with time zone DEFAULT now() NOT NULL,
"updated_at" timestamp with time zone DEFAULT now() NOT NULL,
CONSTRAINT "session_token_unique" UNIQUE("token")
);
--> statement-breakpoint
ALTER TABLE "session" ENABLE ROW LEVEL SECURITY;--> statement-breakpoint
CREATE TABLE "user" (
"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
"name" text,
"email" text NOT NULL,
"email_verified" boolean DEFAULT false NOT NULL,
"image" text,
"subscription_status" text DEFAULT 'incomplete' NOT NULL,
"stripe_customer_id" text,
"created_at" timestamp with time zone DEFAULT now() NOT NULL,
"updated_at" timestamp with time zone DEFAULT now() NOT NULL,
CONSTRAINT "user_email_unique" UNIQUE("email"),
CONSTRAINT "user_stripe_customer_id_unique" UNIQUE("stripe_customer_id"),
CONSTRAINT "user_subscription_status_check" CHECK ("user"."subscription_status" in ('incomplete', 'active', 'past_due', 'canceled'))
);
--> statement-breakpoint
ALTER TABLE "user" ENABLE ROW LEVEL SECURITY;--> statement-breakpoint
CREATE TABLE "verification" (
"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
"identifier" text NOT NULL,
"value" text NOT NULL,
"expires_at" timestamp with time zone NOT NULL,
"created_at" timestamp with time zone DEFAULT now() NOT NULL,
"updated_at" timestamp with time zone DEFAULT now() NOT NULL
);
--> statement-breakpoint
ALTER TABLE "account" ADD CONSTRAINT "account_user_id_user_id_fk" FOREIGN KEY ("user_id") REFERENCES "public"."user"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "example" ADD CONSTRAINT "example_user_id_user_id_fk" FOREIGN KEY ("user_id") REFERENCES "public"."user"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "idempotency_keys" ADD CONSTRAINT "idempotency_keys_user_id_user_id_fk" FOREIGN KEY ("user_id") REFERENCES "public"."user"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "oauth_access_token" ADD CONSTRAINT "oauth_access_token_user_id_user_id_fk" FOREIGN KEY ("user_id") REFERENCES "public"."user"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "oauth_consent" ADD CONSTRAINT "oauth_consent_user_id_user_id_fk" FOREIGN KEY ("user_id") REFERENCES "public"."user"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "product_events" ADD CONSTRAINT "product_events_user_id_user_id_fk" FOREIGN KEY ("user_id") REFERENCES "public"."user"("id") ON DELETE set null ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "session" ADD CONSTRAINT "session_user_id_user_id_fk" FOREIGN KEY ("user_id") REFERENCES "public"."user"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
CREATE INDEX "account_user_id_idx" ON "account" USING btree ("user_id");--> statement-breakpoint
CREATE INDEX "example_user_id_idx" ON "example" USING btree ("user_id");--> statement-breakpoint
CREATE INDEX "idempotency_keys_user_id_idx" ON "idempotency_keys" USING btree ("user_id");--> statement-breakpoint
CREATE UNIQUE INDEX "idempotency_keys_user_op_key_uidx" ON "idempotency_keys" USING btree ("user_id","operation","idempotency_key");--> statement-breakpoint
CREATE INDEX "oauth_access_token_user_id_idx" ON "oauth_access_token" USING btree ("user_id");--> statement-breakpoint
CREATE INDEX "oauth_client_client_id_idx" ON "oauth_client" USING btree ("client_id");--> statement-breakpoint
CREATE INDEX "oauth_consent_user_id_idx" ON "oauth_consent" USING btree ("user_id");--> statement-breakpoint
CREATE UNIQUE INDEX "oauth_consent_user_client_uidx" ON "oauth_consent" USING btree ("user_id","client_id");--> statement-breakpoint
CREATE INDEX "product_events_user_id_idx" ON "product_events" USING btree ("user_id");--> statement-breakpoint
CREATE INDEX "product_events_event_type_idx" ON "product_events" USING btree ("event_type");--> statement-breakpoint
CREATE INDEX "session_user_id_idx" ON "session" USING btree ("user_id");--> statement-breakpoint
CREATE POLICY "account_tenant_isolation" ON "account" AS PERMISSIVE FOR ALL TO "authenticated" USING (user_id = current_setting('app.user_id', true)::uuid) WITH CHECK (user_id = current_setting('app.user_id', true)::uuid);--> statement-breakpoint
CREATE POLICY "example_tenant_isolation" ON "example" AS PERMISSIVE FOR ALL TO "authenticated" USING (user_id = current_setting('app.user_id', true)::uuid) WITH CHECK (user_id = current_setting('app.user_id', true)::uuid);--> statement-breakpoint
CREATE POLICY "idempotency_keys_tenant_isolation" ON "idempotency_keys" AS PERMISSIVE FOR ALL TO "authenticated" USING (user_id = current_setting('app.user_id', true)::uuid) WITH CHECK (user_id = current_setting('app.user_id', true)::uuid);--> statement-breakpoint
CREATE POLICY "oauth_access_token_tenant_isolation" ON "oauth_access_token" AS PERMISSIVE FOR ALL TO "authenticated" USING (user_id = current_setting('app.user_id', true)::uuid) WITH CHECK (user_id = current_setting('app.user_id', true)::uuid);--> statement-breakpoint
CREATE POLICY "oauth_consent_tenant_isolation" ON "oauth_consent" AS PERMISSIVE FOR ALL TO "authenticated" USING (user_id = current_setting('app.user_id', true)::uuid) WITH CHECK (user_id = current_setting('app.user_id', true)::uuid);--> statement-breakpoint
CREATE POLICY "product_events_tenant_isolation" ON "product_events" AS PERMISSIVE FOR ALL TO "authenticated" USING ("product_events"."user_id" IS NULL OR "product_events"."user_id" = current_setting('app.user_id', true)::uuid) WITH CHECK ("product_events"."user_id" IS NULL OR "product_events"."user_id" = current_setting('app.user_id', true)::uuid);--> statement-breakpoint
CREATE POLICY "session_tenant_isolation" ON "session" AS PERMISSIVE FOR ALL TO "authenticated" USING (user_id = current_setting('app.user_id', true)::uuid) WITH CHECK (user_id = current_setting('app.user_id', true)::uuid);--> statement-breakpoint
CREATE POLICY "user_self_isolation" ON "user" AS PERMISSIVE FOR ALL TO "authenticated" USING ("user"."id" = current_setting('app.user_id', true)::uuid) WITH CHECK ("user"."id" = current_setting('app.user_id', true)::uuid);-- Custom SQL migration file, put your code below! --
-- 0001_coach_app_rt_grants.sql
--
-- Runtime role + grants + FORCE RLS, per scaffold-plan A6 (S1-6a / #9) and
-- architecture-doc §Security Model → Tenant Scoping ADR (2026-06-23).
--
-- Layered contract:
-- * `coach_app_rt` is a SQL-created `NOBYPASSRLS` role (pre-created in
-- preflight; `DATABASE_URL` already points to it). Neon console/API roles
-- join `neon_superuser` and carry `BYPASSRLS`, which silently disables RLS
-- — so the runtime role MUST be SQL-created. This migration is idempotent
-- about the role: it does NOT blindly `CREATE ROLE`.
-- * `authenticated` is created by 0000's prepended guard; this migration
-- `GRANT`s membership into it so policies bind at runtime.
-- * The orphan Neon-console `coach_app` role (BYPASSRLS) is dropped if
-- present (REASSIGN/DROP OWNED first to release any object ownership).
-- * Every tenant-owned table gets `FORCE ROW LEVEL SECURITY` so RLS applies
-- to the owner role too.
--
-- All statements are guarded so the migration is a no-op on re-run.
-- ---------------------------------------------------------------------------
-- 1. Ensure the runtime role exists (idempotent).
-- ---------------------------------------------------------------------------
-- `coach_app_rt` is created in preflight. This block is a fail-safe so the
-- migration succeeds on a fresh Neon branch (CI's per-PR branch) where the
-- role may need to be (re)created. Default attributes (`NOBYPASSRLS`,
-- `NOSUPERUSER`) are exactly what we want.
DO $$
BEGIN
IF NOT EXISTS (SELECT 1 FROM pg_roles WHERE rolname = 'coach_app_rt') THEN
CREATE ROLE "coach_app_rt" LOGIN NOBYPASSRLS;
END IF;
END$$;
--> statement-breakpoint
-- ---------------------------------------------------------------------------
-- 2. Bind `coach_app_rt` into `authenticated` so RLS policies match.
-- ---------------------------------------------------------------------------
GRANT "authenticated" TO "coach_app_rt";
--> statement-breakpoint
-- ---------------------------------------------------------------------------
-- 3. Schema + CRUD privileges on existing and future tables / sequences.
-- ---------------------------------------------------------------------------
GRANT USAGE ON SCHEMA "public" TO "coach_app_rt";
--> statement-breakpoint
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA "public" TO "coach_app_rt";
--> statement-breakpoint
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA "public" TO "coach_app_rt";
--> statement-breakpoint
ALTER DEFAULT PRIVILEGES IN SCHEMA "public" GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO "coach_app_rt";
--> statement-breakpoint
ALTER DEFAULT PRIVILEGES IN SCHEMA "public" GRANT USAGE, SELECT ON SEQUENCES TO "coach_app_rt";
--> statement-breakpoint
-- ---------------------------------------------------------------------------
-- 4. FORCE RLS on every tenant-owned table.
-- ---------------------------------------------------------------------------
-- Without FORCE, the owner role (the migration role) bypasses RLS. The 8
-- scaffold tenant tables are:
-- user, session, account, oauth_access_token, oauth_consent,
-- product_events, idempotency_keys, example
-- `verification`, `jwks`, `oauth_client` are pre-auth / global and carry no
-- RLS.
ALTER TABLE "user" FORCE ROW LEVEL SECURITY;
--> statement-breakpoint
ALTER TABLE "session" FORCE ROW LEVEL SECURITY;
--> statement-breakpoint
ALTER TABLE "account" FORCE ROW LEVEL SECURITY;
--> statement-breakpoint
ALTER TABLE "oauth_access_token" FORCE ROW LEVEL SECURITY;
--> statement-breakpoint
ALTER TABLE "oauth_consent" FORCE ROW LEVEL SECURITY;
--> statement-breakpoint
ALTER TABLE "product_events" FORCE ROW LEVEL SECURITY;
--> statement-breakpoint
ALTER TABLE "idempotency_keys" FORCE ROW LEVEL SECURITY;
--> statement-breakpoint
ALTER TABLE "example" FORCE ROW LEVEL SECURITY;
--> statement-breakpoint
-- ---------------------------------------------------------------------------
-- 5. Drop the orphan Neon-console `coach_app` role (idempotent).
-- ---------------------------------------------------------------------------
-- Pre-spike Neon-console roles join `neon_superuser` and carry `BYPASSRLS`,
-- which silently defeats RLS. The leftover `coach_app` role (verified absent
-- on production 2026-06-24, but absent guard is fine) is REASSIGNED-and-DROPed
-- if present so production / CI branches converge on the SQL-created runtime
-- role only.
DO $$
BEGIN
IF EXISTS (SELECT 1 FROM pg_roles WHERE rolname = 'coach_app') THEN
EXECUTE 'REASSIGN OWNED BY "coach_app" TO CURRENT_USER';
EXECUTE 'DROP OWNED BY "coach_app"';
EXECUTE 'DROP ROLE "coach_app"';
END IF;
END$$;
ALTER POLICY "account_tenant_isolation" ON "account" TO authenticated USING (user_id = nullif(current_setting('app.user_id', true), '')::uuid) WITH CHECK (user_id = nullif(current_setting('app.user_id', true), '')::uuid);--> statement-breakpoint
ALTER POLICY "example_tenant_isolation" ON "example" TO authenticated USING (user_id = nullif(current_setting('app.user_id', true), '')::uuid) WITH CHECK (user_id = nullif(current_setting('app.user_id', true), '')::uuid);--> statement-breakpoint
ALTER POLICY "idempotency_keys_tenant_isolation" ON "idempotency_keys" TO authenticated USING (user_id = nullif(current_setting('app.user_id', true), '')::uuid) WITH CHECK (user_id = nullif(current_setting('app.user_id', true), '')::uuid);--> statement-breakpoint
ALTER POLICY "oauth_access_token_tenant_isolation" ON "oauth_access_token" TO authenticated USING (user_id = nullif(current_setting('app.user_id', true), '')::uuid) WITH CHECK (user_id = nullif(current_setting('app.user_id', true), '')::uuid);--> statement-breakpoint
ALTER POLICY "oauth_consent_tenant_isolation" ON "oauth_consent" TO authenticated USING (user_id = nullif(current_setting('app.user_id', true), '')::uuid) WITH CHECK (user_id = nullif(current_setting('app.user_id', true), '')::uuid);--> statement-breakpoint
ALTER POLICY "product_events_tenant_isolation" ON "product_events" TO authenticated USING ("product_events"."user_id" IS NULL OR "product_events"."user_id" = nullif(current_setting('app.user_id', true), '')::uuid) WITH CHECK ("product_events"."user_id" IS NULL OR "product_events"."user_id" = nullif(current_setting('app.user_id', true), '')::uuid);--> statement-breakpoint
ALTER POLICY "session_tenant_isolation" ON "session" TO authenticated USING (user_id = nullif(current_setting('app.user_id', true), '')::uuid) WITH CHECK (user_id = nullif(current_setting('app.user_id', true), '')::uuid);--> statement-breakpoint
ALTER POLICY "user_self_isolation" ON "user" TO authenticated USING ("user"."id" = nullif(current_setting('app.user_id', true), '')::uuid) WITH CHECK ("user"."id" = nullif(current_setting('app.user_id', true), '')::uuid);CREATE TABLE "oauth_refresh_token" (
"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
"token" text NOT NULL,
"client_id" text NOT NULL,
"session_id" uuid,
"user_id" uuid NOT NULL,
"reference_id" text,
"scopes" text[] NOT NULL,
"expires_at" timestamp with time zone NOT NULL,
"created_at" timestamp with time zone DEFAULT now() NOT NULL,
"revoked" timestamp with time zone,
"auth_time" timestamp with time zone,
CONSTRAINT "oauth_refresh_token_token_unique" UNIQUE("token")
);
--> statement-breakpoint
ALTER TABLE "oauth_refresh_token" ENABLE ROW LEVEL SECURITY;--> statement-breakpoint
ALTER TABLE "oauth_client" ALTER COLUMN "name" DROP NOT NULL;--> statement-breakpoint
ALTER TABLE "oauth_client" ALTER COLUMN "redirect_uris" SET DATA TYPE text[] USING string_to_array("redirect_uris", ',');--> statement-breakpoint
ALTER TABLE "oauth_client" ALTER COLUMN "type" DROP NOT NULL;--> statement-breakpoint
ALTER TABLE "oauth_consent" ALTER COLUMN "scopes" SET DATA TYPE text[] USING string_to_array("scopes", ',');--> statement-breakpoint
ALTER TABLE "oauth_access_token" ADD COLUMN "token" text;--> statement-breakpoint
ALTER TABLE "oauth_access_token" ADD COLUMN "session_id" uuid;--> statement-breakpoint
ALTER TABLE "oauth_access_token" ADD COLUMN "refresh_id" uuid;--> statement-breakpoint
ALTER TABLE "oauth_access_token" ADD COLUMN "reference_id" text;--> statement-breakpoint
ALTER TABLE "oauth_access_token" ADD COLUMN "expires_at" timestamp with time zone NOT NULL;--> statement-breakpoint
ALTER TABLE "oauth_client" ADD COLUMN "user_id" uuid;--> statement-breakpoint
ALTER TABLE "oauth_client" ADD COLUMN "reference_id" text;--> statement-breakpoint
ALTER TABLE "oauth_client" ADD COLUMN "skip_consent" boolean;--> statement-breakpoint
ALTER TABLE "oauth_client" ADD COLUMN "enable_end_session" boolean;--> statement-breakpoint
ALTER TABLE "oauth_client" ADD COLUMN "subject_type" text;--> statement-breakpoint
ALTER TABLE "oauth_client" ADD COLUMN "scopes" text[];--> statement-breakpoint
ALTER TABLE "oauth_client" ADD COLUMN "post_logout_redirect_uris" text[];--> statement-breakpoint
ALTER TABLE "oauth_client" ADD COLUMN "grant_types" text[];--> statement-breakpoint
ALTER TABLE "oauth_client" ADD COLUMN "response_types" text[];--> statement-breakpoint
ALTER TABLE "oauth_client" ADD COLUMN "token_endpoint_auth_method" text;--> statement-breakpoint
ALTER TABLE "oauth_client" ADD COLUMN "public" boolean;--> statement-breakpoint
ALTER TABLE "oauth_client" ADD COLUMN "require_pkce" boolean;--> statement-breakpoint
ALTER TABLE "oauth_client" ADD COLUMN "uri" text;--> statement-breakpoint
ALTER TABLE "oauth_client" ADD COLUMN "icon" text;--> statement-breakpoint
ALTER TABLE "oauth_client" ADD COLUMN "contacts" text[];--> statement-breakpoint
ALTER TABLE "oauth_client" ADD COLUMN "tos" text;--> statement-breakpoint
ALTER TABLE "oauth_client" ADD COLUMN "policy" text;--> statement-breakpoint
ALTER TABLE "oauth_client" ADD COLUMN "software_id" text;--> statement-breakpoint
ALTER TABLE "oauth_client" ADD COLUMN "software_version" text;--> statement-breakpoint
ALTER TABLE "oauth_client" ADD COLUMN "software_statement" text;--> statement-breakpoint
ALTER TABLE "oauth_consent" ADD COLUMN "reference_id" text;--> statement-breakpoint
ALTER TABLE "oauth_refresh_token" ADD CONSTRAINT "oauth_refresh_token_user_id_user_id_fk" FOREIGN KEY ("user_id") REFERENCES "public"."user"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
CREATE INDEX "oauth_refresh_token_user_id_idx" ON "oauth_refresh_token" USING btree ("user_id");--> statement-breakpoint
CREATE INDEX "oauth_refresh_token_client_id_idx" ON "oauth_refresh_token" USING btree ("client_id");--> statement-breakpoint
CREATE INDEX "oauth_refresh_token_session_id_idx" ON "oauth_refresh_token" USING btree ("session_id");--> statement-breakpoint
CREATE INDEX "oauth_access_token_client_id_idx" ON "oauth_access_token" USING btree ("client_id");--> statement-breakpoint
CREATE INDEX "oauth_access_token_session_id_idx" ON "oauth_access_token" USING btree ("session_id");--> statement-breakpoint
CREATE INDEX "oauth_access_token_refresh_id_idx" ON "oauth_access_token" USING btree ("refresh_id");--> statement-breakpoint
CREATE INDEX "oauth_client_user_id_idx" ON "oauth_client" USING btree ("user_id");--> statement-breakpoint
CREATE INDEX "oauth_consent_client_id_idx" ON "oauth_consent" USING btree ("client_id");--> statement-breakpoint
ALTER TABLE "oauth_access_token" ADD CONSTRAINT "oauth_access_token_token_unique" UNIQUE("token");--> statement-breakpoint
CREATE POLICY "oauth_refresh_token_tenant_isolation" ON "oauth_refresh_token" AS PERMISSIVE FOR ALL TO "authenticated" USING (user_id = nullif(current_setting('app.user_id', true), '')::uuid) WITH CHECK (user_id = nullif(current_setting('app.user_id', true), '')::uuid);--> statement-breakpoint
-- FORCE so RLS applies to the table owner too (migrations/system-context run as
-- owner); drizzle-kit does not emit FORCE. Mirrors drizzle/0001. Not tracked in
-- the meta snapshot, so this hand-appended block does not cause future drift.
ALTER TABLE "oauth_refresh_token" FORCE ROW LEVEL SECURITY;--> statement-breakpoint
-- Runtime role grant (0001's ALTER DEFAULT PRIVILEGES already covers owner-created
-- tables; explicit here as belt-and-suspenders, matching 0001's grant pattern).
GRANT SELECT, INSERT, UPDATE, DELETE ON "oauth_refresh_token" TO "coach_app_rt";ALTER TABLE "oauth_access_token" DROP CONSTRAINT "oauth_access_token_access_token_unique";--> statement-breakpoint
ALTER TABLE "oauth_access_token" DROP CONSTRAINT "oauth_access_token_refresh_token_unique";--> statement-breakpoint
ALTER TABLE "oauth_access_token" ALTER COLUMN "user_id" DROP NOT NULL;--> statement-breakpoint
ALTER TABLE "oauth_access_token" ALTER COLUMN "scopes" SET DATA TYPE text[] USING string_to_array("scopes", ',');--> statement-breakpoint
ALTER TABLE "oauth_access_token" DROP COLUMN "access_token";--> statement-breakpoint
ALTER TABLE "oauth_access_token" DROP COLUMN "refresh_token";--> statement-breakpoint
ALTER TABLE "oauth_access_token" DROP COLUMN "access_token_expires_at";--> statement-breakpoint
ALTER TABLE "oauth_access_token" DROP COLUMN "refresh_token_expires_at";--> statement-breakpoint
ALTER TABLE "oauth_access_token" DROP COLUMN "updated_at";CREATE TYPE "public"."action_status" AS ENUM('open', 'in_progress', 'done');--> statement-breakpoint
CREATE TYPE "public"."cycle_status" AS ENUM('active', 'archived');--> statement-breakpoint
CREATE TYPE "public"."habit_cadence" AS ENUM('weekly', 'monthly');--> statement-breakpoint
CREATE TYPE "public"."metric_type" AS ENUM('number', 'percentage', 'milestone', 'habit', 'performance');--> statement-breakpoint
CREATE TYPE "public"."objective_status" AS ENUM('on_track', 'off_track', 'completed');--> statement-breakpoint
CREATE TYPE "public"."result_status" AS ENUM('in_progress', 'completed');--> statement-breakpoint
CREATE TYPE "public"."session_type" AS ENUM('goal_setup', 'check_in', 'review', 'open_coaching');--> statement-breakpoint
CREATE TYPE "public"."session_vendor" AS ENUM('claude', 'openai');--> statement-breakpoint
CREATE TYPE "public"."subscription_status" AS ENUM('incomplete', 'active', 'past_due', 'canceled');--> statement-breakpoint
CREATE TABLE "action_logs" (
"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
"user_id" uuid NOT NULL,
"action_id" uuid NOT NULL,
"status" "action_status" NOT NULL,
"comment" text,
"session_id" uuid,
"recorded_at" timestamp with time zone DEFAULT now() NOT NULL,
"created_at" timestamp with time zone DEFAULT now() NOT NULL
);
--> statement-breakpoint
ALTER TABLE "action_logs" ENABLE ROW LEVEL SECURITY;--> statement-breakpoint
CREATE TABLE "actions" (
"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
"user_id" uuid NOT NULL,
"cycle_id" uuid NOT NULL,
"objective_id" uuid,
"title" text NOT NULL,
"description" text,
"due_at" date,
"status" "action_status" DEFAULT 'open' NOT NULL,
"sort_order" numeric,
"session_id" uuid,
"created_at" timestamp with time zone DEFAULT now() NOT NULL,
"updated_at" timestamp with time zone DEFAULT now() NOT NULL
);
--> statement-breakpoint
ALTER TABLE "actions" ENABLE ROW LEVEL SECURITY;--> statement-breakpoint
CREATE TABLE "cycles" (
"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
"user_id" uuid NOT NULL,
"intention" text,
"start_at" date NOT NULL,
"end_at" date NOT NULL,
"status" "cycle_status" DEFAULT 'active' NOT NULL,
"created_at" timestamp with time zone DEFAULT now() NOT NULL,
"updated_at" timestamp with time zone DEFAULT now() NOT NULL,
CONSTRAINT "cycles_date_range_check" CHECK ("cycles"."end_at" > "cycles"."start_at")
);
--> statement-breakpoint
ALTER TABLE "cycles" ENABLE ROW LEVEL SECURITY;--> statement-breakpoint
CREATE TABLE "habit_marks" (
"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
"user_id" uuid NOT NULL,
"result_id" uuid NOT NULL,
"day" date NOT NULL,
"session_id" uuid,
"created_at" timestamp with time zone DEFAULT now() NOT NULL
);
--> statement-breakpoint
ALTER TABLE "habit_marks" ENABLE ROW LEVEL SECURITY;--> statement-breakpoint
CREATE TABLE "notes" (
"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
"user_id" uuid NOT NULL,
"text" text NOT NULL,
"recorded_at" timestamp with time zone DEFAULT now() NOT NULL,
"created_at" timestamp with time zone DEFAULT now() NOT NULL,
"updated_at" timestamp with time zone DEFAULT now() NOT NULL,
CONSTRAINT "notes_nonblank_text_check" CHECK ("notes"."text" ~ '[^[:space:]]')
);
--> statement-breakpoint
ALTER TABLE "notes" ENABLE ROW LEVEL SECURITY;--> statement-breakpoint
CREATE TABLE "objectives" (
"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
"user_id" uuid NOT NULL,
"cycle_id" uuid NOT NULL,
"title" text NOT NULL,
"description" text DEFAULT '' NOT NULL,
"deadline_at" date,
"status" "objective_status" DEFAULT 'on_track' NOT NULL,
"created_at" timestamp with time zone DEFAULT now() NOT NULL,
"updated_at" timestamp with time zone DEFAULT now() NOT NULL
);
--> statement-breakpoint
ALTER TABLE "objectives" ENABLE ROW LEVEL SECURITY;--> statement-breakpoint
CREATE TABLE "result_updates" (
"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
"user_id" uuid NOT NULL,
"result_id" uuid NOT NULL,
"value" numeric,
"comment" text,
"session_id" uuid,
"recorded_at" timestamp with time zone DEFAULT now() NOT NULL,
"created_at" timestamp with time zone DEFAULT now() NOT NULL,
CONSTRAINT "result_updates_finite_value_check" CHECK ("result_updates"."value" IS NULL OR "result_updates"."value" NOT IN ('NaN'::numeric, 'Infinity'::numeric, '-Infinity'::numeric))
);
--> statement-breakpoint
ALTER TABLE "result_updates" ENABLE ROW LEVEL SECURITY;--> statement-breakpoint
CREATE TABLE "results" (
"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
"user_id" uuid NOT NULL,
"objective_id" uuid NOT NULL,
"title" text NOT NULL,
"description" text,
"metric_type" "metric_type" NOT NULL,
"start_value" numeric,
"target_value" numeric,
"unit" text,
"cadence" "habit_cadence",
"per" integer,
"since" date,
"status" "result_status" DEFAULT 'in_progress' NOT NULL,
"created_at" timestamp with time zone DEFAULT now() NOT NULL,
"updated_at" timestamp with time zone DEFAULT now() NOT NULL,
CONSTRAINT "results_number_check" CHECK ("results"."metric_type" <> 'number' OR ("results"."start_value" IS NOT NULL AND "results"."target_value" IS NOT NULL AND "results"."cadence" IS NULL AND "results"."per" IS NULL AND "results"."since" IS NULL)),
CONSTRAINT "results_percentage_check" CHECK ("results"."metric_type" <> 'percentage' OR ("results"."start_value" IS NOT NULL AND "results"."target_value" IS NOT NULL AND "results"."start_value" BETWEEN 0 AND 100 AND "results"."target_value" BETWEEN 0 AND 100 AND "results"."cadence" IS NULL AND "results"."per" IS NULL AND "results"."since" IS NULL AND "results"."unit" IS NULL)),
CONSTRAINT "results_milestone_check" CHECK ("results"."metric_type" <> 'milestone' OR ("results"."start_value" IS NOT NULL AND "results"."start_value" = 0 AND "results"."target_value" IS NOT NULL AND "results"."target_value" = 1 AND "results"."cadence" IS NULL AND "results"."per" IS NULL AND "results"."since" IS NULL AND "results"."unit" IS NULL)),
CONSTRAINT "results_habit_check" CHECK ("results"."metric_type" <> 'habit' OR ("results"."cadence" IS NOT NULL AND "results"."per" IS NOT NULL AND "results"."per" >= 1 AND "results"."since" IS NOT NULL AND "results"."start_value" IS NULL AND "results"."target_value" IS NULL AND "results"."unit" IS NULL)),
CONSTRAINT "results_performance_check" CHECK ("results"."metric_type" <> 'performance' OR ("results"."target_value" IS NOT NULL AND "results"."since" IS NOT NULL AND "results"."cadence" IS NULL AND "results"."per" IS NULL)),
CONSTRAINT "results_distinct_target_check" CHECK ("results"."metric_type" NOT IN ('number', 'percentage') OR "results"."target_value" <> "results"."start_value"),
CONSTRAINT "results_finite_values_check" CHECK (("results"."start_value" IS NULL OR "results"."start_value" NOT IN ('NaN'::numeric, 'Infinity'::numeric, '-Infinity'::numeric)) AND ("results"."target_value" IS NULL OR "results"."target_value" NOT IN ('NaN'::numeric, 'Infinity'::numeric, '-Infinity'::numeric)))
);
--> statement-breakpoint
ALTER TABLE "results" ENABLE ROW LEVEL SECURITY;--> statement-breakpoint
CREATE TABLE "sessions" (
"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
"user_id" uuid NOT NULL,
"vendor" "session_vendor" NOT NULL,
"source_url" text,
"type" "session_type",
"briefing_notes" text NOT NULL,
"headline" text,
"summary_notes" text,
"started_at" timestamp with time zone DEFAULT now() NOT NULL,
"ended_at" timestamp with time zone,
"created_at" timestamp with time zone DEFAULT now() NOT NULL,
"updated_at" timestamp with time zone DEFAULT now() NOT NULL
);
--> statement-breakpoint
ALTER TABLE "sessions" ENABLE ROW LEVEL SECURITY;--> statement-breakpoint
ALTER TABLE "idempotency_keys" ALTER COLUMN "idempotency_key" SET NOT NULL;--> statement-breakpoint
-- Drop the text default/check before converting the scaffold column to an enum.
ALTER TABLE "user" DROP CONSTRAINT "user_subscription_status_check";--> statement-breakpoint
ALTER TABLE "user" ALTER COLUMN "subscription_status" DROP DEFAULT;--> statement-breakpoint
ALTER TABLE "user" ALTER COLUMN "subscription_status" SET DATA TYPE "public"."subscription_status" USING "subscription_status"::"public"."subscription_status";--> statement-breakpoint
ALTER TABLE "user" ALTER COLUMN "subscription_status" SET DEFAULT 'incomplete'::"public"."subscription_status";--> statement-breakpoint
ALTER TABLE "user" ADD CONSTRAINT "user_subscription_status_check" CHECK (subscription_status IN ('incomplete', 'active', 'past_due', 'canceled'));--> statement-breakpoint
ALTER TABLE "user" ADD COLUMN "first_name" text;--> statement-breakpoint
ALTER TABLE "user" ADD COLUMN "last_name" text;--> statement-breakpoint
ALTER TABLE "user" ADD COLUMN "about_me" text DEFAULT '' NOT NULL;--> statement-breakpoint
ALTER TABLE "user" ADD COLUMN "timezone" text DEFAULT 'UTC' NOT NULL;--> statement-breakpoint
ALTER TABLE "user" ADD COLUMN "subscription_cancel_at_period_end" boolean DEFAULT false NOT NULL;--> statement-breakpoint
ALTER TABLE "user" ADD COLUMN "subscription_current_period_end" timestamp with time zone;--> statement-breakpoint
-- Composite reference keys precede their foreign keys.
CREATE UNIQUE INDEX "actions_id_user_uidx" ON "actions" USING btree ("id","user_id");--> statement-breakpoint
CREATE UNIQUE INDEX "cycles_id_user_uidx" ON "cycles" USING btree ("id","user_id");--> statement-breakpoint
CREATE UNIQUE INDEX "objectives_id_user_uidx" ON "objectives" USING btree ("id","user_id");--> statement-breakpoint
CREATE UNIQUE INDEX "objectives_id_cycle_user_uidx" ON "objectives" USING btree ("id","cycle_id","user_id");--> statement-breakpoint
CREATE UNIQUE INDEX "results_id_user_uidx" ON "results" USING btree ("id","user_id");--> statement-breakpoint
CREATE UNIQUE INDEX "sessions_id_user_uidx" ON "sessions" USING btree ("id","user_id");--> statement-breakpoint
ALTER TABLE "action_logs" ADD CONSTRAINT "action_logs_user_id_user_id_fk" FOREIGN KEY ("user_id") REFERENCES "public"."user"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "action_logs" ADD CONSTRAINT "action_logs_action_tenant_fk" FOREIGN KEY ("action_id","user_id") REFERENCES "public"."actions"("id","user_id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "action_logs" ADD CONSTRAINT "action_logs_session_tenant_fk" FOREIGN KEY ("session_id","user_id") REFERENCES "public"."sessions"("id","user_id") ON DELETE no action ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "actions" ADD CONSTRAINT "actions_user_id_user_id_fk" FOREIGN KEY ("user_id") REFERENCES "public"."user"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "actions" ADD CONSTRAINT "actions_cycle_tenant_fk" FOREIGN KEY ("cycle_id","user_id") REFERENCES "public"."cycles"("id","user_id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "actions" ADD CONSTRAINT "actions_objective_cycle_tenant_fk" FOREIGN KEY ("objective_id","cycle_id","user_id") REFERENCES "public"."objectives"("id","cycle_id","user_id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "actions" ADD CONSTRAINT "actions_session_tenant_fk" FOREIGN KEY ("session_id","user_id") REFERENCES "public"."sessions"("id","user_id") ON DELETE set null ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "cycles" ADD CONSTRAINT "cycles_user_id_user_id_fk" FOREIGN KEY ("user_id") REFERENCES "public"."user"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "habit_marks" ADD CONSTRAINT "habit_marks_user_id_user_id_fk" FOREIGN KEY ("user_id") REFERENCES "public"."user"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "habit_marks" ADD CONSTRAINT "habit_marks_result_tenant_fk" FOREIGN KEY ("result_id","user_id") REFERENCES "public"."results"("id","user_id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "habit_marks" ADD CONSTRAINT "habit_marks_session_tenant_fk" FOREIGN KEY ("session_id","user_id") REFERENCES "public"."sessions"("id","user_id") ON DELETE no action ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "notes" ADD CONSTRAINT "notes_user_id_user_id_fk" FOREIGN KEY ("user_id") REFERENCES "public"."user"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "objectives" ADD CONSTRAINT "objectives_user_id_user_id_fk" FOREIGN KEY ("user_id") REFERENCES "public"."user"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "objectives" ADD CONSTRAINT "objectives_cycle_tenant_fk" FOREIGN KEY ("cycle_id","user_id") REFERENCES "public"."cycles"("id","user_id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "result_updates" ADD CONSTRAINT "result_updates_user_id_user_id_fk" FOREIGN KEY ("user_id") REFERENCES "public"."user"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "result_updates" ADD CONSTRAINT "result_updates_result_tenant_fk" FOREIGN KEY ("result_id","user_id") REFERENCES "public"."results"("id","user_id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "result_updates" ADD CONSTRAINT "result_updates_session_tenant_fk" FOREIGN KEY ("session_id","user_id") REFERENCES "public"."sessions"("id","user_id") ON DELETE no action ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "results" ADD CONSTRAINT "results_user_id_user_id_fk" FOREIGN KEY ("user_id") REFERENCES "public"."user"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "results" ADD CONSTRAINT "results_objective_tenant_fk" FOREIGN KEY ("objective_id","user_id") REFERENCES "public"."objectives"("id","user_id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "sessions" ADD CONSTRAINT "sessions_user_id_user_id_fk" FOREIGN KEY ("user_id") REFERENCES "public"."user"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
CREATE INDEX "action_logs_user_id_idx" ON "action_logs" USING btree ("user_id");--> statement-breakpoint
CREATE INDEX "action_logs_action_user_idx" ON "action_logs" USING btree ("action_id","user_id");--> statement-breakpoint
CREATE INDEX "action_logs_session_user_idx" ON "action_logs" USING btree ("session_id","user_id");--> statement-breakpoint
CREATE INDEX "action_logs_history_idx" ON "action_logs" USING btree ("action_id","recorded_at" DESC NULLS LAST,"id" DESC NULLS LAST);--> statement-breakpoint
CREATE INDEX "actions_user_id_idx" ON "actions" USING btree ("user_id");--> statement-breakpoint
CREATE INDEX "actions_cycle_user_idx" ON "actions" USING btree ("cycle_id","user_id");--> statement-breakpoint
CREATE INDEX "actions_objective_cycle_user_idx" ON "actions" USING btree ("objective_id","cycle_id","user_id");--> statement-breakpoint
CREATE INDEX "actions_session_user_idx" ON "actions" USING btree ("session_id","user_id");--> statement-breakpoint
CREATE INDEX "actions_outstanding_order_idx" ON "actions" USING btree ("cycle_id","objective_id","sort_order","due_at");--> statement-breakpoint
CREATE INDEX "cycles_user_id_idx" ON "cycles" USING btree ("user_id");--> statement-breakpoint
CREATE UNIQUE INDEX "cycles_one_active_uidx" ON "cycles" USING btree ("user_id") WHERE "cycles"."status" = 'active';--> statement-breakpoint
CREATE INDEX "habit_marks_user_id_idx" ON "habit_marks" USING btree ("user_id");--> statement-breakpoint
CREATE INDEX "habit_marks_result_user_idx" ON "habit_marks" USING btree ("result_id","user_id");--> statement-breakpoint
CREATE INDEX "habit_marks_session_user_idx" ON "habit_marks" USING btree ("session_id","user_id");--> statement-breakpoint
CREATE UNIQUE INDEX "habit_marks_result_day_uidx" ON "habit_marks" USING btree ("result_id","day");--> statement-breakpoint
CREATE INDEX "notes_user_id_idx" ON "notes" USING btree ("user_id");--> statement-breakpoint
CREATE INDEX "notes_history_idx" ON "notes" USING btree ("user_id","recorded_at" DESC NULLS LAST,"id" DESC NULLS LAST);--> statement-breakpoint
CREATE INDEX "objectives_user_id_idx" ON "objectives" USING btree ("user_id");--> statement-breakpoint
CREATE INDEX "objectives_cycle_user_idx" ON "objectives" USING btree ("cycle_id","user_id");--> statement-breakpoint
CREATE INDEX "result_updates_user_id_idx" ON "result_updates" USING btree ("user_id");--> statement-breakpoint
CREATE INDEX "result_updates_result_user_idx" ON "result_updates" USING btree ("result_id","user_id");--> statement-breakpoint
CREATE INDEX "result_updates_session_user_idx" ON "result_updates" USING btree ("session_id","user_id");--> statement-breakpoint
CREATE INDEX "result_updates_history_idx" ON "result_updates" USING btree ("result_id","recorded_at" DESC NULLS LAST,"id" DESC NULLS LAST);--> statement-breakpoint
CREATE INDEX "results_user_id_idx" ON "results" USING btree ("user_id");--> statement-breakpoint
CREATE INDEX "results_objective_user_idx" ON "results" USING btree ("objective_id","user_id");--> statement-breakpoint
CREATE INDEX "sessions_user_id_idx" ON "sessions" USING btree ("user_id");--> statement-breakpoint
CREATE UNIQUE INDEX "sessions_one_open_uidx" ON "sessions" USING btree ("user_id") WHERE "sessions"."ended_at" IS NULL;--> statement-breakpoint
CREATE INDEX "sessions_boundary_idx" ON "sessions" USING btree ("user_id","ended_at" DESC NULLS LAST) WHERE "sessions"."ended_at" IS NOT NULL;--> statement-breakpoint
CREATE POLICY "action_logs_tenant_isolation" ON "action_logs" AS PERMISSIVE FOR ALL TO "authenticated" USING (user_id = nullif(current_setting('app.user_id', true), '')::uuid) WITH CHECK (user_id = nullif(current_setting('app.user_id', true), '')::uuid);--> statement-breakpoint
CREATE POLICY "actions_tenant_isolation" ON "actions" AS PERMISSIVE FOR ALL TO "authenticated" USING (user_id = nullif(current_setting('app.user_id', true), '')::uuid) WITH CHECK (user_id = nullif(current_setting('app.user_id', true), '')::uuid);--> statement-breakpoint
CREATE POLICY "cycles_tenant_isolation" ON "cycles" AS PERMISSIVE FOR ALL TO "authenticated" USING (user_id = nullif(current_setting('app.user_id', true), '')::uuid) WITH CHECK (user_id = nullif(current_setting('app.user_id', true), '')::uuid);--> statement-breakpoint
CREATE POLICY "habit_marks_tenant_isolation" ON "habit_marks" AS PERMISSIVE FOR ALL TO "authenticated" USING (user_id = nullif(current_setting('app.user_id', true), '')::uuid) WITH CHECK (user_id = nullif(current_setting('app.user_id', true), '')::uuid);--> statement-breakpoint
CREATE POLICY "notes_tenant_isolation" ON "notes" AS PERMISSIVE FOR ALL TO "authenticated" USING (user_id = nullif(current_setting('app.user_id', true), '')::uuid) WITH CHECK (user_id = nullif(current_setting('app.user_id', true), '')::uuid);--> statement-breakpoint
CREATE POLICY "objectives_tenant_isolation" ON "objectives" AS PERMISSIVE FOR ALL TO "authenticated" USING (user_id = nullif(current_setting('app.user_id', true), '')::uuid) WITH CHECK (user_id = nullif(current_setting('app.user_id', true), '')::uuid);--> statement-breakpoint
CREATE POLICY "result_updates_tenant_isolation" ON "result_updates" AS PERMISSIVE FOR ALL TO "authenticated" USING (user_id = nullif(current_setting('app.user_id', true), '')::uuid) WITH CHECK (user_id = nullif(current_setting('app.user_id', true), '')::uuid);--> statement-breakpoint
CREATE POLICY "results_tenant_isolation" ON "results" AS PERMISSIVE FOR ALL TO "authenticated" USING (user_id = nullif(current_setting('app.user_id', true), '')::uuid) WITH CHECK (user_id = nullif(current_setting('app.user_id', true), '')::uuid);--> statement-breakpoint
CREATE POLICY "sessions_tenant_isolation" ON "sessions" AS PERMISSIVE FOR ALL TO "authenticated" USING (user_id = nullif(current_setting('app.user_id', true), '')::uuid) WITH CHECK (user_id = nullif(current_setting('app.user_id', true), '')::uuid);-- F5 Actions: a session is a reference, not the action parent. PostgreSQL
-- column-specific SET NULL preserves the composite tenant key; Drizzle's
-- generated metadata can express only the action, not its column list.
ALTER TABLE actions DROP CONSTRAINT actions_session_tenant_fk;
--> statement-breakpoint
ALTER TABLE actions ADD CONSTRAINT actions_session_tenant_fk
FOREIGN KEY (session_id, user_id) REFERENCES sessions(id, user_id)
ON DELETE SET NULL (session_id);
--> statement-breakpoint
-- Product database behavior which Drizzle metadata cannot express.
-- Architecture: Deletion and Cascade / Insert-only Evidence. Approved
-- CB-033 FORCE ROW LEVEL SECURITY versus cascade semantics removes FORCE from
-- existing scaffold and product tables while runtime remains NOBYPASSRLS.
DO $$
DECLARE target record;
BEGIN
FOR target IN SELECT tablename FROM pg_tables WHERE schemaname = 'public' LOOP
EXECUTE format('ALTER TABLE public.%I NO FORCE ROW LEVEL SECURITY', target.tablename);
END LOOP;
END $$;
--> statement-breakpoint
GRANT SELECT, INSERT, UPDATE, DELETE ON cycles, objectives, results, sessions,
actions, notes, result_updates, action_logs, habit_marks TO coach_app_rt;
--> statement-breakpoint
REVOKE UPDATE, DELETE ON result_updates, action_logs FROM coach_app_rt;
--> statement-breakpoint
GRANT UPDATE (comment) ON result_updates, action_logs TO coach_app_rt;
--> statement-breakpoint
GRANT DELETE ON result_updates TO coach_app_rt;
--> statement-breakpoint
-- F6 Notes: only prose is editable. Capture time is immutable by grant;
-- the BEFORE UPDATE trigger still maintains the technical updated_at column.
REVOKE UPDATE ON notes FROM coach_app_rt;
--> statement-breakpoint
GRANT UPDATE (text) ON notes TO coach_app_rt;
--> statement-breakpoint
-- Habit marks are toggle state; a replacement is delete + insert, never edit.
REVOKE UPDATE ON habit_marks FROM coach_app_rt;
--> statement-breakpoint
CREATE FUNCTION public.coach_touch_updated_at() RETURNS trigger
LANGUAGE plpgsql SET search_path = pg_catalog, public AS $$
BEGIN
NEW.updated_at := clock_timestamp();
RETURN NEW;
END $$;
--> statement-breakpoint
DO $$
DECLARE target text;
BEGIN
FOREACH target IN ARRAY ARRAY['user', 'cycles', 'objectives', 'results', 'actions', 'sessions', 'notes'] LOOP
EXECUTE format('CREATE TRIGGER %I BEFORE UPDATE ON public.%I FOR EACH ROW EXECUTE FUNCTION public.coach_touch_updated_at()', target || '_touch_updated_at', target);
END LOOP;
END $$;
--> statement-breakpoint
CREATE FUNCTION public.coach_guard_metric_type() RETURNS trigger
LANGUAGE plpgsql SET search_path = pg_catalog, public AS $$
BEGIN
IF NEW.metric_type IS DISTINCT FROM OLD.metric_type AND (
EXISTS (SELECT 1 FROM public.result_updates WHERE result_id = OLD.id AND user_id = OLD.user_id)
OR EXISTS (SELECT 1 FROM public.habit_marks WHERE result_id = OLD.id AND user_id = OLD.user_id)
) THEN
RAISE EXCEPTION 'Result metric type cannot change after evidence exists'
USING ERRCODE = '23514', CONSTRAINT = 'results_metric_type_immutable';
END IF;
RETURN NEW;
END $$;
--> statement-breakpoint
CREATE TRIGGER results_metric_type_immutable BEFORE UPDATE OF metric_type ON results
FOR EACH ROW EXECUTE FUNCTION public.coach_guard_metric_type();
--> statement-breakpoint
-- Serialize evidence inserts with changes to the parent measure. An FK's KEY
-- SHARE lock alone is insufficient: a metric_type update takes NO KEY UPDATE.
-- Touch the parent's existing updated_at as well: a lock without a new row
-- version lets a stale REPEATABLE READ snapshot miss newly committed evidence.
-- The timestamp trigger advances it; concurrent stale writers serialize/retry.
-- These are invoker functions; tenant policies apply to every parent update.
CREATE FUNCTION public.coach_validate_result_update() RETURNS trigger
LANGUAGE plpgsql SET search_path = pg_catalog, public AS $$
DECLARE kind public.metric_type;
BEGIN
UPDATE public.results SET updated_at = updated_at
WHERE id = NEW.result_id AND user_id = NEW.user_id RETURNING metric_type INTO kind;
IF NOT FOUND THEN
RAISE EXCEPTION 'Result not found' USING ERRCODE = '23503';
END IF;
IF (kind = 'habit' AND NEW.value IS NOT NULL)
OR (kind <> 'habit' AND NEW.value IS NULL)
OR (kind = 'percentage' AND NEW.value NOT BETWEEN 0 AND 100)
OR (kind = 'milestone' AND NEW.value NOT IN (0, 1))
OR (kind = 'performance' AND NEW.value <> round(NEW.value, 1)) THEN
RAISE EXCEPTION 'Evidence value does not match result metric type'
USING ERRCODE = '23514', CONSTRAINT = 'result_updates_value_type_check';
END IF;
IF kind = 'habit' AND (NEW.comment IS NULL OR NEW.comment !~ '[^[:space:]]') THEN
RAISE EXCEPTION 'Habit comment entry requires text'
USING ERRCODE = '23514', CONSTRAINT = 'result_updates_habit_comment_check';
END IF;
RETURN NEW;
END $$;
--> statement-breakpoint
CREATE TRIGGER result_updates_value_type_check BEFORE INSERT ON result_updates
FOR EACH ROW EXECUTE FUNCTION public.coach_validate_result_update();
--> statement-breakpoint
CREATE FUNCTION public.coach_validate_habit_mark() RETURNS trigger
LANGUAGE plpgsql SET search_path = pg_catalog, public AS $$
DECLARE parent public.results%ROWTYPE; zone text;
BEGIN
UPDATE public.results SET updated_at = updated_at
WHERE id = NEW.result_id AND user_id = NEW.user_id RETURNING * INTO parent;
IF NOT FOUND THEN
RAISE EXCEPTION 'Result not found' USING ERRCODE = '23503';
END IF;
SELECT timezone INTO zone FROM public."user" WHERE id = NEW.user_id;
IF parent.metric_type <> 'habit' OR NEW.day < parent.since
OR NEW.day > (CURRENT_TIMESTAMP AT TIME ZONE zone)::date THEN
RAISE EXCEPTION 'Habit mark must reference a habit and fall between its start and today'
USING ERRCODE = '23514', CONSTRAINT = 'habit_marks_day_type_check';
END IF;
RETURN NEW;
END $$;
--> statement-breakpoint
CREATE TRIGGER habit_marks_day_type_check BEFORE INSERT ON habit_marks
FOR EACH ROW EXECUTE FUNCTION public.coach_validate_habit_mark();
-- Solo retains the existing scaffold until implementation has a replacement. -- This draft migration never ran against a persistent database. Preserve existing rows. SELECT 1;
ALTER TABLE "result_updates" RENAME TO "result_logs";--> statement-breakpoint
ALTER TABLE "result_logs" DROP CONSTRAINT "result_updates_finite_value_check";--> statement-breakpoint
ALTER TABLE "result_logs" DROP CONSTRAINT "result_updates_user_id_user_id_fk";
--> statement-breakpoint
ALTER TABLE "result_logs" DROP CONSTRAINT "result_updates_result_tenant_fk";
--> statement-breakpoint
ALTER TABLE "result_logs" DROP CONSTRAINT "result_updates_session_tenant_fk";
--> statement-breakpoint
DROP INDEX "result_updates_user_id_idx";--> statement-breakpoint
DROP INDEX "result_updates_result_user_idx";--> statement-breakpoint
DROP INDEX "result_updates_session_user_idx";--> statement-breakpoint
DROP INDEX "result_updates_history_idx";--> statement-breakpoint
ALTER TABLE "result_logs" ADD CONSTRAINT "result_logs_user_id_user_id_fk" FOREIGN KEY ("user_id") REFERENCES "public"."user"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "result_logs" ADD CONSTRAINT "result_logs_result_tenant_fk" FOREIGN KEY ("result_id","user_id") REFERENCES "public"."results"("id","user_id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "result_logs" ADD CONSTRAINT "result_logs_session_tenant_fk" FOREIGN KEY ("session_id","user_id") REFERENCES "public"."sessions"("id","user_id") ON DELETE no action ON UPDATE no action;--> statement-breakpoint
CREATE INDEX "result_logs_user_id_idx" ON "result_logs" USING btree ("user_id");--> statement-breakpoint
CREATE INDEX "result_logs_result_user_idx" ON "result_logs" USING btree ("result_id","user_id");--> statement-breakpoint
CREATE INDEX "result_logs_session_user_idx" ON "result_logs" USING btree ("session_id","user_id");--> statement-breakpoint
CREATE INDEX "result_logs_history_idx" ON "result_logs" USING btree ("result_id","recorded_at" DESC NULLS LAST,"id" DESC NULLS LAST);--> statement-breakpoint
ALTER TABLE "result_logs" ADD CONSTRAINT "result_logs_finite_value_check" CHECK ("result_logs"."value" IS NULL OR "result_logs"."value" NOT IN ('NaN'::numeric, 'Infinity'::numeric, '-Infinity'::numeric));--> statement-breakpoint
ALTER POLICY "result_updates_tenant_isolation" ON "result_logs" RENAME TO "result_logs_tenant_isolation";-- Table renaming preserves rows, grants and trigger bindings, but PL/pgSQL
-- bodies use SQL names at runtime. Update those bodies in this migration.
ALTER TABLE result_logs RENAME CONSTRAINT result_updates_pkey TO result_logs_pkey;
--> statement-breakpoint
ALTER TRIGGER result_updates_value_type_check ON result_logs RENAME TO result_logs_value_type_check;
--> statement-breakpoint
CREATE OR REPLACE FUNCTION public.coach_guard_metric_type() RETURNS trigger
LANGUAGE plpgsql SET search_path = pg_catalog, public AS $$
BEGIN
IF NEW.metric_type IS DISTINCT FROM OLD.metric_type AND (
EXISTS (SELECT 1 FROM public.result_logs WHERE result_id = OLD.id AND user_id = OLD.user_id)
OR EXISTS (SELECT 1 FROM public.habit_marks WHERE result_id = OLD.id AND user_id = OLD.user_id)
) THEN
RAISE EXCEPTION 'Result metric type cannot change after evidence exists'
USING ERRCODE = '23514', CONSTRAINT = 'results_metric_type_immutable';
END IF;
RETURN NEW;
END $$;
--> statement-breakpoint
CREATE OR REPLACE FUNCTION public.coach_validate_result_update() RETURNS trigger
LANGUAGE plpgsql SET search_path = pg_catalog, public AS $$
DECLARE kind public.metric_type;
BEGIN
UPDATE public.results SET updated_at = updated_at
WHERE id = NEW.result_id AND user_id = NEW.user_id RETURNING metric_type INTO kind;
IF NOT FOUND THEN
RAISE EXCEPTION 'Result not found' USING ERRCODE = '23503';
END IF;
IF (kind = 'habit' AND NEW.value IS NOT NULL)
OR (kind <> 'habit' AND NEW.value IS NULL)
OR (kind = 'percentage' AND NEW.value NOT BETWEEN 0 AND 100)
OR (kind = 'milestone' AND NEW.value NOT IN (0, 1))
OR (kind = 'performance' AND NEW.value <> round(NEW.value, 1)) THEN
RAISE EXCEPTION 'Evidence value does not match result metric type'
USING ERRCODE = '23514', CONSTRAINT = 'result_logs_value_type_check';
END IF;
IF kind = 'habit' AND (NEW.comment IS NULL OR NEW.comment !~ '[^[:space:]]') THEN
RAISE EXCEPTION 'Habit comment entry requires text'
USING ERRCODE = '23514', CONSTRAINT = 'result_logs_habit_comment_check';
END IF;
RETURN NEW;
END $$;
ALTER TABLE "habit_marks" RENAME TO "habit_logs";--> statement-breakpoint
ALTER TABLE "habit_logs" DROP CONSTRAINT "habit_marks_user_id_user_id_fk";
--> statement-breakpoint
ALTER TABLE "habit_logs" DROP CONSTRAINT "habit_marks_result_tenant_fk";
--> statement-breakpoint
ALTER TABLE "habit_logs" DROP CONSTRAINT "habit_marks_session_tenant_fk";
--> statement-breakpoint
DROP INDEX "habit_marks_user_id_idx";--> statement-breakpoint
DROP INDEX "habit_marks_result_user_idx";--> statement-breakpoint
DROP INDEX "habit_marks_session_user_idx";--> statement-breakpoint
DROP INDEX "habit_marks_result_day_uidx";--> statement-breakpoint
ALTER TABLE "habit_logs" ADD CONSTRAINT "habit_logs_user_id_user_id_fk" FOREIGN KEY ("user_id") REFERENCES "public"."user"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "habit_logs" ADD CONSTRAINT "habit_logs_result_tenant_fk" FOREIGN KEY ("result_id","user_id") REFERENCES "public"."results"("id","user_id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "habit_logs" ADD CONSTRAINT "habit_logs_session_tenant_fk" FOREIGN KEY ("session_id","user_id") REFERENCES "public"."sessions"("id","user_id") ON DELETE no action ON UPDATE no action;--> statement-breakpoint
CREATE INDEX "habit_logs_user_id_idx" ON "habit_logs" USING btree ("user_id");--> statement-breakpoint
CREATE INDEX "habit_logs_result_user_idx" ON "habit_logs" USING btree ("result_id","user_id");--> statement-breakpoint
CREATE INDEX "habit_logs_session_user_idx" ON "habit_logs" USING btree ("session_id","user_id");--> statement-breakpoint
CREATE UNIQUE INDEX "habit_logs_result_day_uidx" ON "habit_logs" USING btree ("result_id","day");--> statement-breakpoint
ALTER POLICY "habit_marks_tenant_isolation" ON "habit_logs" RENAME TO "habit_logs_tenant_isolation";-- Physical rename only: checked-day state, row identity and toggle behavior remain.
ALTER TABLE habit_logs RENAME CONSTRAINT habit_marks_pkey TO habit_logs_pkey;
--> statement-breakpoint
ALTER TRIGGER habit_marks_day_type_check ON habit_logs RENAME TO habit_logs_day_type_check;
--> statement-breakpoint
CREATE OR REPLACE FUNCTION public.coach_guard_metric_type() RETURNS trigger
LANGUAGE plpgsql SET search_path = pg_catalog, public AS $$
BEGIN
IF NEW.metric_type IS DISTINCT FROM OLD.metric_type AND (
EXISTS (SELECT 1 FROM public.result_logs WHERE result_id = OLD.id AND user_id = OLD.user_id)
OR EXISTS (SELECT 1 FROM public.habit_logs WHERE result_id = OLD.id AND user_id = OLD.user_id)
) THEN
RAISE EXCEPTION 'Result metric type cannot change after evidence exists'
USING ERRCODE = '23514', CONSTRAINT = 'results_metric_type_immutable';
END IF;
RETURN NEW;
END $$;
--> statement-breakpoint
CREATE OR REPLACE FUNCTION public.coach_validate_habit_mark() RETURNS trigger
LANGUAGE plpgsql SET search_path = pg_catalog, public AS $$
DECLARE parent public.results%ROWTYPE; zone text;
BEGIN
UPDATE public.results SET updated_at = updated_at
WHERE id = NEW.result_id AND user_id = NEW.user_id RETURNING * INTO parent;
IF NOT FOUND THEN
RAISE EXCEPTION 'Result not found' USING ERRCODE = '23503';
END IF;
SELECT timezone INTO zone FROM public."user" WHERE id = NEW.user_id;
IF parent.metric_type <> 'habit' OR NEW.day < parent.since
OR NEW.day > (CURRENT_TIMESTAMP AT TIME ZONE zone)::date THEN
RAISE EXCEPTION 'Habit mark must reference a habit and fall between its start and today'
USING ERRCODE = '23514', CONSTRAINT = 'habit_logs_day_type_check';
END IF;
RETURN NEW;
END $$;
-- Preserve deletion semantics: old tombstones must not become visible rows. DELETE FROM "example" WHERE "deleted_at" IS NOT NULL;--> statement-breakpoint ALTER TABLE "idempotency_keys" DROP COLUMN "expires_at";--> statement-breakpoint ALTER TABLE "idempotency_keys" DROP COLUMN "updated_at";--> statement-breakpoint ALTER TABLE "example" DROP COLUMN "deleted_at";
-- Controlled auth upgrade: stop old API writers before applying. This rename is not
-- compatible with the old binary. Existing MCP grants require an explicit reconnect
-- cutover; do not silently reinterpret pre-1.7 grants as resource-bound grants.
DO $$ BEGIN
IF EXISTS (SELECT 1 FROM oauth_access_token) OR EXISTS (SELECT 1 FROM oauth_refresh_token)
OR EXISTS (SELECT 1 FROM oauth_consent) THEN
RAISE EXCEPTION 'Auth upgrade requires reviewed MCP grant revocation before migration; see OPERATOR-RUNBOOK auth cutover';
END IF;
END $$;
--> statement-breakpoint
UPDATE "user" SET name = COALESCE(NULLIF(trim(concat_ws(' ', first_name, last_name)), ''), email)
WHERE name IS NULL;
--> statement-breakpoint
CREATE TABLE "oauth_client_assertion" (
"id" text PRIMARY KEY NOT NULL,
"expires_at" timestamp with time zone NOT NULL
);
--> statement-breakpoint
CREATE TABLE "oauth_client_resource" (
"id" uuid PRIMARY KEY DEFAULT pg_catalog.gen_random_uuid() NOT NULL,
"client_id" text NOT NULL,
"resource_id" text NOT NULL,
"metadata" jsonb,
"created_at" timestamp with time zone
);
--> statement-breakpoint
CREATE TABLE "oauth_resource" (
"id" uuid PRIMARY KEY DEFAULT pg_catalog.gen_random_uuid() NOT NULL,
"identifier" text NOT NULL,
"name" text NOT NULL,
"access_token_ttl" integer,
"refresh_token_ttl" integer,
"signing_algorithm" text,
"signing_key_id" text,
"allowed_scopes" text[],
"custom_claims" jsonb,
"dpop_bound_access_tokens_required" boolean DEFAULT false,
"disabled" boolean DEFAULT false,
"created_at" timestamp with time zone,
"updated_at" timestamp with time zone,
"policy_version" integer DEFAULT 1,
"metadata" jsonb,
CONSTRAINT "oauth_resource_identifier_unique" UNIQUE("identifier")
);
--> statement-breakpoint
CREATE TABLE "subscription" (
"id" uuid PRIMARY KEY DEFAULT pg_catalog.gen_random_uuid() NOT NULL,
"plan" text NOT NULL,
"reference_id" uuid NOT NULL,
"stripe_customer_id" text,
"stripe_subscription_id" text,
"status" text DEFAULT 'incomplete' NOT NULL,
"period_start" timestamp with time zone,
"period_end" timestamp with time zone,
"trial_start" timestamp with time zone,
"trial_end" timestamp with time zone,
"cancel_at_period_end" boolean DEFAULT false,
"cancel_at" timestamp with time zone,
"canceled_at" timestamp with time zone,
"ended_at" timestamp with time zone,
"seats" integer,
"billing_interval" text,
"stripe_schedule_id" text
);
--> statement-breakpoint
ALTER TABLE "subscription" ENABLE ROW LEVEL SECURITY;--> statement-breakpoint
ALTER TABLE "session" RENAME TO "auth_session";--> statement-breakpoint
ALTER TABLE "auth_session" DROP CONSTRAINT "session_token_unique";--> statement-breakpoint
ALTER TABLE "user" DROP CONSTRAINT "user_subscription_status_check";--> statement-breakpoint
ALTER TABLE "auth_session" DROP CONSTRAINT "session_user_id_user_id_fk";
--> statement-breakpoint
DROP INDEX "idempotency_keys_user_id_idx";--> statement-breakpoint
DROP INDEX "notes_user_id_idx";--> statement-breakpoint
DROP INDEX "oauth_consent_user_client_uidx";--> statement-breakpoint
ALTER TABLE "oauth_access_token" ALTER COLUMN "token" SET NOT NULL;--> statement-breakpoint
ALTER TABLE "oauth_client" ALTER COLUMN "disabled" DROP NOT NULL;--> statement-breakpoint
ALTER TABLE "oauth_client" ALTER COLUMN "created_at" DROP NOT NULL;--> statement-breakpoint
ALTER TABLE "oauth_client" ALTER COLUMN "updated_at" DROP NOT NULL;--> statement-breakpoint
ALTER TABLE "oauth_consent" ALTER COLUMN "user_id" DROP NOT NULL;--> statement-breakpoint
ALTER TABLE "user" ALTER COLUMN "name" SET NOT NULL;--> statement-breakpoint
ALTER TABLE "jwks" ADD COLUMN "expires_at" timestamp with time zone;--> statement-breakpoint
ALTER TABLE "jwks" ADD COLUMN "alg" text;--> statement-breakpoint
ALTER TABLE "jwks" ADD COLUMN "crv" text;--> statement-breakpoint
ALTER TABLE "oauth_access_token" ADD COLUMN "authorization_code_id" text;--> statement-breakpoint
ALTER TABLE "oauth_access_token" ADD COLUMN "resources" text[];--> statement-breakpoint
ALTER TABLE "oauth_access_token" ADD COLUMN "requested_user_info_claims" text[];--> statement-breakpoint
ALTER TABLE "oauth_access_token" ADD COLUMN "revoked" timestamp with time zone;--> statement-breakpoint
ALTER TABLE "oauth_access_token" ADD COLUMN "confirmation" jsonb;--> statement-breakpoint
ALTER TABLE "oauth_client" ADD COLUMN "client_discovery_id" text;--> statement-breakpoint
ALTER TABLE "oauth_client" ADD COLUMN "client_credentials_scopes" text[] DEFAULT '{}';--> statement-breakpoint
ALTER TABLE "oauth_client" ADD COLUMN "backchannel_logout_uri" text;--> statement-breakpoint
ALTER TABLE "oauth_client" ADD COLUMN "backchannel_logout_session_required" boolean;--> statement-breakpoint
ALTER TABLE "oauth_client" ADD COLUMN "application_type" text;--> statement-breakpoint
ALTER TABLE "oauth_client" ADD COLUMN "jwks" text;--> statement-breakpoint
ALTER TABLE "oauth_client" ADD COLUMN "jwks_uri" text;--> statement-breakpoint
ALTER TABLE "oauth_client" ADD COLUMN "dpop_bound_access_tokens" boolean DEFAULT false;--> statement-breakpoint
ALTER TABLE "oauth_consent" ADD COLUMN "resources" text[];--> statement-breakpoint
ALTER TABLE "oauth_consent" ADD COLUMN "requested_user_info_claims" text[];--> statement-breakpoint
ALTER TABLE "oauth_refresh_token" ADD COLUMN "authorization_code_id" text;--> statement-breakpoint
ALTER TABLE "oauth_refresh_token" ADD COLUMN "resources" text[];--> statement-breakpoint
ALTER TABLE "oauth_refresh_token" ADD COLUMN "requested_user_info_claims" text[];--> statement-breakpoint
ALTER TABLE "oauth_refresh_token" ADD COLUMN "rotated_at" timestamp with time zone;--> statement-breakpoint
ALTER TABLE "oauth_refresh_token" ADD COLUMN "rotation_replay_response" text;--> statement-breakpoint
ALTER TABLE "oauth_refresh_token" ADD COLUMN "rotation_replay_expires_at" timestamp with time zone;--> statement-breakpoint
ALTER TABLE "oauth_refresh_token" ADD COLUMN "confirmation" jsonb;--> statement-breakpoint
ALTER TABLE "oauth_client_resource" ADD CONSTRAINT "oauth_client_resource_client_id_oauth_client_client_id_fk" FOREIGN KEY ("client_id") REFERENCES "public"."oauth_client"("client_id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "oauth_client_resource" ADD CONSTRAINT "oauth_client_resource_resource_id_oauth_resource_identifier_fk" FOREIGN KEY ("resource_id") REFERENCES "public"."oauth_resource"("identifier") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "subscription" ADD CONSTRAINT "subscription_reference_id_user_id_fk" FOREIGN KEY ("reference_id") REFERENCES "public"."user"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
CREATE INDEX "oauth_client_resource_resource_id_idx" ON "oauth_client_resource" USING btree ("resource_id");--> statement-breakpoint
CREATE UNIQUE INDEX "oauth_client_resource_client_id_resource_id_uidx" ON "oauth_client_resource" USING btree ("client_id","resource_id");--> statement-breakpoint
CREATE INDEX "subscription_reference_id_idx" ON "subscription" USING btree ("reference_id");--> statement-breakpoint
CREATE UNIQUE INDEX "subscription_stripe_subscription_id_uidx" ON "subscription" USING btree ("stripe_subscription_id");--> statement-breakpoint
CREATE INDEX "subscription_stripe_customer_id_idx" ON "subscription" USING btree ("stripe_customer_id");--> statement-breakpoint
ALTER TABLE "oauth_access_token" ADD CONSTRAINT "oauth_access_token_client_id_oauth_client_client_id_fk" FOREIGN KEY ("client_id") REFERENCES "public"."oauth_client"("client_id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "oauth_access_token" ADD CONSTRAINT "oauth_access_token_session_id_auth_session_id_fk" FOREIGN KEY ("session_id") REFERENCES "public"."auth_session"("id") ON DELETE set null ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "oauth_access_token" ADD CONSTRAINT "oauth_access_token_refresh_id_oauth_refresh_token_id_fk" FOREIGN KEY ("refresh_id") REFERENCES "public"."oauth_refresh_token"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "oauth_client" ADD CONSTRAINT "oauth_client_user_id_user_id_fk" FOREIGN KEY ("user_id") REFERENCES "public"."user"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "oauth_consent" ADD CONSTRAINT "oauth_consent_client_id_oauth_client_client_id_fk" FOREIGN KEY ("client_id") REFERENCES "public"."oauth_client"("client_id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "oauth_refresh_token" ADD CONSTRAINT "oauth_refresh_token_client_id_oauth_client_client_id_fk" FOREIGN KEY ("client_id") REFERENCES "public"."oauth_client"("client_id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "oauth_refresh_token" ADD CONSTRAINT "oauth_refresh_token_session_id_auth_session_id_fk" FOREIGN KEY ("session_id") REFERENCES "public"."auth_session"("id") ON DELETE set null ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "auth_session" ADD CONSTRAINT "auth_session_user_id_user_id_fk" FOREIGN KEY ("user_id") REFERENCES "public"."user"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
CREATE INDEX "oauth_access_token_authorization_code_id_idx" ON "oauth_access_token" USING btree ("authorization_code_id");--> statement-breakpoint
CREATE INDEX "oauth_refresh_token_authorization_code_id_idx" ON "oauth_refresh_token" USING btree ("authorization_code_id");--> statement-breakpoint
CREATE INDEX "verification_identifier_idx" ON "verification" USING btree ("identifier");--> statement-breakpoint
ALTER TABLE "auth_session" ADD CONSTRAINT "auth_session_token_unique" UNIQUE("token");--> statement-breakpoint
DROP POLICY "product_events_tenant_isolation" ON "product_events" CASCADE;--> statement-breakpoint
CREATE POLICY "product_events_tenant_insert" ON "product_events" AS PERMISSIVE FOR INSERT TO "authenticated" WITH CHECK ("product_events"."user_id" = nullif(current_setting('app.user_id', true), '')::uuid);--> statement-breakpoint
CREATE POLICY "subscription_tenant_isolation" ON "subscription" AS PERMISSIVE FOR ALL TO "authenticated" USING ("subscription"."reference_id" = nullif(current_setting('app.user_id', true), '')::uuid) WITH CHECK ("subscription"."reference_id" = nullif(current_setting('app.user_id', true), '')::uuid);
--> statement-breakpoint
-- Preserve legacy client classification in the new standard fields.
UPDATE oauth_client SET application_type = CASE WHEN type = 'native' THEN 'native' ELSE 'web' END,
token_endpoint_auth_method = COALESCE(token_endpoint_auth_method, CASE WHEN public THEN 'none' ELSE 'client_secret_basic' END);
--> statement-breakpoint
ALTER TABLE subscription NO FORCE ROW LEVEL SECURITY;
--> statement-breakpoint
-- Auth adapter owns these tables; application tenants cannot mint credentials.
REVOKE ALL ON auth_session, account, verification, jwks, oauth_client, oauth_client_assertion,
oauth_client_resource, oauth_resource, oauth_access_token, oauth_refresh_token, oauth_consent
FROM coach_app_rt, authenticated, PUBLIC;
--> statement-breakpoint
REVOKE ALL ON subscription FROM coach_app_rt, authenticated, PUBLIC;
--> statement-breakpoint
GRANT SELECT ON subscription TO coach_app_rt;
--> statement-breakpoint
REVOKE ALL ON product_events FROM coach_app_rt, authenticated, PUBLIC;
--> statement-breakpoint
GRANT INSERT ON product_events TO coach_app_rt;
-- Billing reconciliation must complete before retiring the old cache. This guard
-- intentionally blocks populated upgrades; fresh installations need no backfill.
DO $$ BEGIN
IF EXISTS (
SELECT 1 FROM "user" u WHERE
(u.subscription_status <> 'incomplete' OR u.subscription_cancel_at_period_end
OR u.subscription_current_period_end IS NOT NULL)
AND NOT EXISTS (SELECT 1 FROM (
SELECT * FROM subscription WHERE reference_id=u.id
ORDER BY CASE status WHEN 'active' THEN 0 WHEN 'past_due' THEN 1
WHEN 'unpaid' THEN 1 WHEN 'paused' THEN 1 WHEN 'canceled' THEN 2 ELSE 3 END,
period_end DESC NULLS LAST, id DESC LIMIT 1
) s WHERE s.stripe_customer_id IS NOT DISTINCT FROM u.stripe_customer_id
AND s.stripe_subscription_id IS NOT NULL
AND CASE s.status WHEN 'active' THEN 'active'
WHEN 'past_due' THEN 'past_due' WHEN 'unpaid' THEN 'past_due' WHEN 'paused' THEN 'past_due'
WHEN 'canceled' THEN 'canceled' ELSE 'incomplete' END = u.subscription_status::text
AND COALESCE(s.cancel_at_period_end,false) = u.subscription_cancel_at_period_end
AND s.period_end IS NOT DISTINCT FROM u.subscription_current_period_end)
) THEN RAISE EXCEPTION 'Reconcile legacy User billing to Stripe subscription rows before auth cutover'; END IF;
IF EXISTS (SELECT 1 FROM product_events WHERE stripe_customer_id IS NOT NULL OR subscription_id IS NOT NULL)
THEN RAISE EXCEPTION 'Archive and clear retired product event billing identifiers before auth cutover'; END IF;
END $$;
--> statement-breakpoint
ALTER TABLE "jwks" DROP COLUMN "updated_at";--> statement-breakpoint
ALTER TABLE "oauth_client" DROP COLUMN "type";--> statement-breakpoint
ALTER TABLE "oauth_client" DROP COLUMN "public";--> statement-breakpoint
ALTER TABLE "product_events" DROP COLUMN "stripe_customer_id";--> statement-breakpoint
ALTER TABLE "product_events" DROP COLUMN "subscription_id";--> statement-breakpoint
ALTER TABLE "product_events" DROP COLUMN "updated_at";--> statement-breakpoint
ALTER TABLE "user" DROP COLUMN "subscription_status";--> statement-breakpoint
ALTER TABLE "user" DROP COLUMN "subscription_cancel_at_period_end";--> statement-breakpoint
ALTER TABLE "user" DROP COLUMN "subscription_current_period_end";--> statement-breakpoint
DROP TYPE "public"."subscription_status";-- Preserve the name previously used for coaching, then the existing display name.
-- A display name matching an email/local part is not evidence that it was generated:
-- never erase it based on that heuristic. Do not derive a new name from email or surname.
WITH chosen_names AS (
SELECT id, COALESCE(
NULLIF(btrim(first_name), ''),
NULLIF(btrim(name), ''),
''
) AS chosen_name
FROM "user"
)
UPDATE "user" AS u SET name = c.chosen_name, updated_at = now()
FROM chosen_names AS c WHERE u.id = c.id AND u.name IS DISTINCT FROM c.chosen_name;
--> statement-breakpoint
ALTER TABLE "user" ALTER COLUMN "name" SET DEFAULT '';--> statement-breakpoint
ALTER TABLE "user" DROP COLUMN "first_name";--> statement-breakpoint
ALTER TABLE "user" DROP COLUMN "last_name";