Blame · Line-by-line history

`0020_analytics_and_admin.sql`

Each line is annotated with the commit that last touched it. Click any SHA to jump to that commit and see the surrounding change.

0020_analytics_and_admin.sqlBlame91 lines · 1 contributor

Normal view Raw

8f50ed0Claude	1	-- Gluecron migration 0020: Block F — Observability + admin.
	2	--
	3	-- Covers F1 (traffic analytics), F3 (admin panel), and F4 (billing/quotas).
	4	-- F2 (org insights) is computed live from existing tables.
	5	--
	6	-- Tables:
	7	-- repo_traffic_events — view/clone events, 1 row per event. Rolled up via
	8	-- GROUP BY for daily/weekly charts.
	9	-- system_flags — simple key/value state for site-admin (e.g.
	10	-- "site_banner_text", "registration_locked"). Only
	11	-- site admins can write.
	12	-- site_admins — explicit list of user ids that are global admins.
	13	-- Absence of any row means "first user is admin"
	14	-- bootstrap (handled in code).
	15	-- billing_plans — plan catalogue (name, limits). Seeded with free.
	16	-- user_quotas — per-user usage counters + plan assignment. Writes
	17	-- are bumped from action paths (push bytes, ai tokens).
	18
	19	--> statement-breakpoint
	20	CREATE TABLE IF NOT EXISTS "repo_traffic_events" (
	21	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	22	"repository_id" uuid NOT NULL,
	23	"kind" text NOT NULL, -- view \| clone \| api \| ui
	24	"path" text, -- path visited (first 256 chars)
	25	"user_id" uuid, -- null for anon
	26	"ip_hash" text, -- sha256(ip) prefix; best-effort uniq
	27	"user_agent" text, -- first 128 chars
	28	"referer" text, -- first 256 chars
	29	"created_at" timestamp DEFAULT now() NOT NULL,
	30	CONSTRAINT "repo_traffic_events_repo_fk" FOREIGN KEY ("repository_id") REFERENCES "repositories"("id") ON DELETE cascade,
	31	CONSTRAINT "repo_traffic_events_user_fk" FOREIGN KEY ("user_id") REFERENCES "users"("id") ON DELETE set null
	32	);
	33
	34	--> statement-breakpoint
	35	CREATE INDEX IF NOT EXISTS "repo_traffic_events_repo_time" ON "repo_traffic_events" ("repository_id", "created_at");
	36	--> statement-breakpoint
	37	CREATE INDEX IF NOT EXISTS "repo_traffic_events_kind" ON "repo_traffic_events" ("repository_id", "kind", "created_at");
	38
	39	--> statement-breakpoint
	40	CREATE TABLE IF NOT EXISTS "system_flags" (
	41	"key" text PRIMARY KEY NOT NULL,
	42	"value" text NOT NULL DEFAULT '',
	43	"updated_at" timestamp DEFAULT now() NOT NULL,
	44	"updated_by" uuid,
	45	CONSTRAINT "system_flags_updater_fk" FOREIGN KEY ("updated_by") REFERENCES "users"("id")
	46	);
	47
	48	--> statement-breakpoint
	49	CREATE TABLE IF NOT EXISTS "site_admins" (
	50	"user_id" uuid PRIMARY KEY NOT NULL,
	51	"granted_at" timestamp DEFAULT now() NOT NULL,
	52	"granted_by" uuid,
	53	CONSTRAINT "site_admins_user_fk" FOREIGN KEY ("user_id") REFERENCES "users"("id") ON DELETE cascade,
	54	CONSTRAINT "site_admins_granter_fk" FOREIGN KEY ("granted_by") REFERENCES "users"("id")
	55	);
	56
	57	--> statement-breakpoint
	58	CREATE TABLE IF NOT EXISTS "billing_plans" (
	59	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	60	"slug" text NOT NULL UNIQUE, -- free \| pro \| team \| enterprise
	61	"name" text NOT NULL,
	62	"price_cents" integer NOT NULL DEFAULT 0,
	63	"repo_limit" integer NOT NULL DEFAULT 10,
	64	"storage_mb_limit" integer NOT NULL DEFAULT 1024,
	65	"ai_tokens_monthly" integer NOT NULL DEFAULT 100000,
	66	"bandwidth_gb_monthly" integer NOT NULL DEFAULT 10,
	67	"private_repos" boolean NOT NULL DEFAULT false,
	68	"created_at" timestamp DEFAULT now() NOT NULL
	69	);
	70
	71	--> statement-breakpoint
	72	CREATE TABLE IF NOT EXISTS "user_quotas" (
	73	"user_id" uuid PRIMARY KEY NOT NULL,
	74	"plan_slug" text NOT NULL DEFAULT 'free',
	75	"storage_mb_used" integer NOT NULL DEFAULT 0,
	76	"ai_tokens_used_this_month" integer NOT NULL DEFAULT 0,
	77	"bandwidth_gb_used_this_month" integer NOT NULL DEFAULT 0,
	78	"cycle_start" timestamp DEFAULT now() NOT NULL,
	79	"updated_at" timestamp DEFAULT now() NOT NULL,
	80	CONSTRAINT "user_quotas_user_fk" FOREIGN KEY ("user_id") REFERENCES "users"("id") ON DELETE cascade
	81	);
	82
	83	--> statement-breakpoint
	84	-- Seed the default plans.
	85	INSERT INTO "billing_plans" ("slug","name","price_cents","repo_limit","storage_mb_limit","ai_tokens_monthly","bandwidth_gb_monthly","private_repos")
	86	VALUES
	87	('free','Free',0,10,1024,100000,10,false),
	88	('pro','Pro',900,200,10240,1000000,100,true),
	89	('team','Team',2400,1000,51200,5000000,500,true),
	90	('enterprise','Enterprise',9900,10000,512000,50000000,5000,true)
	91	ON CONFLICT (slug) DO NOTHING;