konnosDashboard
0048_auth_signup_geo.sql30 lines · main
1-- 0048_auth_signup_geo — control-plane sign-up IP + geo capture (admin-only SEO).
2--
3-- Backs services/signup-geo.ts (write, from the per-tenant Better Auth
4-- user.create hook) and services/signup-geo-admin.ts (read, admin cockpit).
5-- One row per end-user sign-up across ALL briven-auth tenant projects.
6--
7-- DELIBERATE: stores the RAW ip (flndrn-approved) in the control plane,
8-- admin-side only. Independent of Better Auth's own session ip tracking and
9-- of the per-project customer users page (which never reads this table).
10--
11-- All statements are IF NOT EXISTS so a partial/re-run apply is a no-op.
12-- Journal note: the newest journalled entry in this repo is 0033
13-- (when=1779570000000); this entry is dated well after it so the drizzle
14-- migrator applies it regardless of which older files ever landed.
15CREATE TABLE IF NOT EXISTS "auth_signup_geo" (
16 "id" text PRIMARY KEY NOT NULL,
17 "project_id" text NOT NULL,
18 "user_id" text,
19 "email" text,
20 "ip" text,
21 "country" text,
22 "city" text,
23 "region" text,
24 "created_at" timestamp with time zone DEFAULT now() NOT NULL,
25 CONSTRAINT "auth_signup_geo_project_id_projects_id_fk" FOREIGN KEY ("project_id") REFERENCES "public"."projects"("id") ON DELETE cascade ON UPDATE no action
26);
27--> statement-breakpoint
28CREATE INDEX IF NOT EXISTS "auth_signup_geo_created_idx" ON "auth_signup_geo" USING btree ("created_at");--> statement-breakpoint
29CREATE INDEX IF NOT EXISTS "auth_signup_geo_project_created_idx" ON "auth_signup_geo" USING btree ("project_id","created_at");--> statement-breakpoint
30CREATE INDEX IF NOT EXISTS "auth_signup_geo_country_idx" ON "auth_signup_geo" USING btree ("country");