0032_project_auto_snapshot_settings.sql35 lines · main

1	-- 0032_project_auto_snapshot_settings — per-project automatic save-points.
2	-- One row per project that has automatic scheduled snapshots configured.
3	-- Drives the auto-snapshot worker (apps/api/src/workers/auto-snapshot.ts):
4	-- a project is "due" when enabled = true and next_run_at <= now(). The
5	-- worker then takes an `auto`-flagged snapshot and prunes auto snapshots
6	-- beyond retention_count — manual snapshots are never touched. The
7	-- per-project `_briven_snapshots` registry (in the data-plane schema)
8	-- gains an `auto` boolean at runtime via ensureRegistry, so no control-
9	-- plane migration is needed for that flag.
10
11	CREATE TABLE IF NOT EXISTS "project_auto_snapshot_settings" (
12	"id" text PRIMARY KEY NOT NULL,
13	"project_id" text NOT NULL,
14	"enabled" boolean DEFAULT false NOT NULL,
15	"frequency" text DEFAULT 'daily' NOT NULL,
16	"retention_count" integer DEFAULT 7 NOT NULL,
17	"next_run_at" timestamp with time zone NOT NULL,
18	"last_run_at" timestamp with time zone,
19	"last_run_status" text,
20	"last_run_error" text,
21	"created_by" text,
22	"created_at" timestamp with time zone DEFAULT now() NOT NULL,
23	"updated_at" timestamp with time zone DEFAULT now() NOT NULL,
24	CONSTRAINT "project_auto_snapshot_settings_project_id_fk"
25	FOREIGN KEY ("project_id") REFERENCES "projects"("id") ON DELETE CASCADE,
26	CONSTRAINT "project_auto_snapshot_settings_created_by_fk"
27	FOREIGN KEY ("created_by") REFERENCES "users"("id") ON DELETE SET NULL
28	);
29
30	CREATE UNIQUE INDEX IF NOT EXISTS "project_auto_snapshot_settings_project_idx"
31	ON "project_auto_snapshot_settings" USING btree ("project_id");
32
33	CREATE INDEX IF NOT EXISTS "project_auto_snapshot_settings_due_idx"
34	ON "project_auto_snapshot_settings" USING btree ("next_run_at")
35	WHERE "enabled" = true;