packages/db/drizzle/0000_dusty_dreaming_celestial.sql

322 lines · sql

CREATE TYPE "public"."content_status" AS ENUM('active', 'pending', 'hidden', 'removed');
CREATE TYPE "public"."event_type" AS ENUM('set', 'weekender', 'class', 'workshop', 'competition', 'special', 'social');
CREATE TYPE "public"."flag_status" AS ENUM('open', 'reviewed', 'actioned', 'dismissed');
CREATE TYPE "public"."order_purpose" AS ENUM('ticket', 'boost', 'pro', 'sponsor');
CREATE TYPE "public"."order_status" AS ENUM('reserved', 'paid', 'refunded', 'cancelled');
CREATE TYPE "public"."pro_status" AS ENUM('pending', 'active', 'expired', 'cancelled');
CREATE TYPE "public"."sponsor_status" AS ENUM('pending', 'active', 'ended');
CREATE TYPE "public"."subject_type" AS ENUM('event', 'video', 'promoter', 'dj', 'stepping_group', 'class');
CREATE TYPE "public"."ticket_status" AS ENUM('valid', 'used', 'void');
CREATE TYPE "public"."travel_category" AS ENUM('event_center', 'hotel', 'restaurant', 'mall', 'attraction', 'entertainment', 'hospital');
CREATE TYPE "public"."user_role" AS ENUM('member', 'moderator', 'admin');
CREATE TYPE "public"."video_platform" AS ENUM('youtube', 'instagram', 'tiktok', 'other');
CREATE TABLE "audit_log" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"actor_id" uuid,
	"action" text NOT NULL,
	"subject_type" text,
	"subject_id" uuid,
	"meta" jsonb,
	"created_at" timestamp with time zone DEFAULT now() NOT NULL
);
CREATE TABLE "cities" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"slug" text NOT NULL,
	"name" text NOT NULL,
	"metro_label" text,
	"state" text,
	"tier" integer DEFAULT 2 NOT NULL,
	"is_launched" boolean DEFAULT false NOT NULL,
	"lat" double precision,
	"lng" double precision,
	"created_at" timestamp with time zone DEFAULT now() NOT NULL,
	"updated_at" timestamp with time zone DEFAULT now() NOT NULL,
	CONSTRAINT "cities_slug_unique" UNIQUE("slug")
);
CREATE TABLE "classes" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"city_id" uuid NOT NULL,
	"title" text NOT NULL,
	"instructor" text,
	"schedule" text,
	"venue_id" uuid,
	"price_text" text,
	"is_free" boolean DEFAULT false NOT NULL,
	"owner_id" uuid,
	"status" "content_status" DEFAULT 'active' NOT NULL,
	"verified_at" timestamp with time zone,
	"hidden_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
);
CREATE TABLE "comments" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"subject_type" "subject_type" NOT NULL,
	"subject_id" uuid NOT NULL,
	"body" text NOT NULL,
	"owner_id" uuid,
	"status" "content_status" DEFAULT 'active' NOT NULL,
	"verified_at" timestamp with time zone,
	"hidden_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
);
CREATE TABLE "djs" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"city_id" uuid NOT NULL,
	"name" text NOT NULL,
	"bio" text,
	"links" jsonb,
	"owner_id" uuid,
	"status" "content_status" DEFAULT 'active' NOT NULL,
	"verified_at" timestamp with time zone,
	"hidden_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
);
CREATE TABLE "event_djs" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"event_id" uuid NOT NULL,
	"dj_id" uuid NOT NULL,
	"created_at" timestamp with time zone DEFAULT now() NOT NULL
);
CREATE TABLE "event_participants" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"event_id" uuid NOT NULL,
	"stepping_group_id" uuid,
	"created_at" timestamp with time zone DEFAULT now() NOT NULL
);
CREATE TABLE "events" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"city_id" uuid NOT NULL,
	"venue_id" uuid,
	"title" text NOT NULL,
	"type" "event_type" NOT NULL,
	"description" text,
	"starts_at" timestamp with time zone,
	"ends_at" timestamp with time zone,
	"external_url" text,
	"is_promoted" boolean DEFAULT false NOT NULL,
	"boosted_until" timestamp with time zone,
	"is_live" boolean DEFAULT false NOT NULL,
	"live_url" text,
	"ticket_url" text,
	"price_text" text,
	"is_free" boolean DEFAULT false NOT NULL,
	"owner_id" uuid,
	"status" "content_status" DEFAULT 'active' NOT NULL,
	"verified_at" timestamp with time zone,
	"hidden_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
);
CREATE TABLE "flags" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"subject_type" "subject_type" NOT NULL,
	"subject_id" uuid NOT NULL,
	"reporter_id" uuid,
	"reason" text,
	"status" "flag_status" DEFAULT 'open' NOT NULL,
	"created_at" timestamp with time zone DEFAULT now() NOT NULL
);
CREATE TABLE "group_memberships" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"stepping_group_id" uuid NOT NULL,
	"user_id" uuid NOT NULL,
	"role_in_group" text,
	"created_at" timestamp with time zone DEFAULT now() NOT NULL
);
CREATE TABLE "orders" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"purpose" "order_purpose" NOT NULL,
	"buyer_id" uuid,
	"event_id" uuid,
	"ticket_type_id" uuid,
	"quantity" integer DEFAULT 1 NOT NULL,
	"amount_cents" integer DEFAULT 0 NOT NULL,
	"status" "order_status" DEFAULT 'reserved' NOT NULL,
	"meta" jsonb,
	"created_at" timestamp with time zone DEFAULT now() NOT NULL,
	"updated_at" timestamp with time zone DEFAULT now() NOT NULL
);
CREATE TABLE "pro_accounts" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"user_id" uuid NOT NULL,
	"tier" text DEFAULT 'pro' NOT NULL,
	"status" "pro_status" DEFAULT 'pending' NOT NULL,
	"started_at" timestamp with time zone,
	"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
);
CREATE TABLE "promoters" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"city_id" uuid NOT NULL,
	"name" text NOT NULL,
	"bio" text,
	"links" jsonb,
	"owner_id" uuid,
	"status" "content_status" DEFAULT 'active' NOT NULL,
	"verified_at" timestamp with time zone,
	"hidden_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
);
CREATE TABLE "sessions" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"token_hash" text NOT NULL,
	"user_id" uuid NOT NULL,
	"expires_at" timestamp with time zone NOT NULL,
	"created_at" timestamp with time zone DEFAULT now() NOT NULL,
	CONSTRAINT "sessions_token_hash_unique" UNIQUE("token_hash")
);
CREATE TABLE "sponsors" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"city_id" uuid NOT NULL,
	"name" text NOT NULL,
	"blurb" text,
	"link_url" text,
	"image_url" text,
	"status" "sponsor_status" DEFAULT 'pending' NOT NULL,
	"starts_at" timestamp with time zone,
	"ends_at" timestamp with time zone,
	"owner_id" uuid,
	"created_at" timestamp with time zone DEFAULT now() NOT NULL,
	"updated_at" timestamp with time zone DEFAULT now() NOT NULL
);
CREATE TABLE "stepping_groups" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"city_id" uuid NOT NULL,
	"name" text NOT NULL,
	"bio" text,
	"links" jsonb,
	"owner_id" uuid,
	"status" "content_status" DEFAULT 'active' NOT NULL,
	"verified_at" timestamp with time zone,
	"hidden_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
);
CREATE TABLE "ticket_types" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"event_id" uuid NOT NULL,
	"name" text NOT NULL,
	"price_cents" integer DEFAULT 0 NOT NULL,
	"currency" text DEFAULT 'usd' NOT NULL,
	"capacity" integer,
	"sold" integer DEFAULT 0 NOT NULL,
	"is_active" boolean DEFAULT true NOT NULL,
	"owner_id" uuid,
	"created_at" timestamp with time zone DEFAULT now() NOT NULL,
	"updated_at" timestamp with time zone DEFAULT now() NOT NULL
);
CREATE TABLE "tickets" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"order_id" uuid NOT NULL,
	"event_id" uuid NOT NULL,
	"ticket_type_id" uuid,
	"holder_id" uuid,
	"code" text NOT NULL,
	"status" "ticket_status" DEFAULT 'valid' NOT NULL,
	"created_at" timestamp with time zone DEFAULT now() NOT NULL,
	"updated_at" timestamp with time zone DEFAULT now() NOT NULL,
	CONSTRAINT "tickets_code_unique" UNIQUE("code")
);
CREATE TABLE "travel_places" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"city_id" uuid NOT NULL,
	"category" "travel_category" NOT NULL,
	"name" text NOT NULL,
	"address" text,
	"lat" double precision,
	"lng" double precision,
	"source" text DEFAULT 'manual' NOT NULL,
	"place_ref" text,
	"status" "content_status" DEFAULT 'active' NOT NULL,
	"verified_at" timestamp with time zone,
	"hidden_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
);
CREATE TABLE "users" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"email" text,
	"password_hash" text,
	"handle" text,
	"display_name" text,
	"avatar_url" text,
	"role" "user_role" DEFAULT 'member' NOT NULL,
	"trust_tier" integer DEFAULT 0 NOT NULL,
	"created_at" timestamp with time zone DEFAULT now() NOT NULL,
	"updated_at" timestamp with time zone DEFAULT now() NOT NULL,
	CONSTRAINT "users_email_unique" UNIQUE("email"),
	CONSTRAINT "users_handle_unique" UNIQUE("handle")
);
CREATE TABLE "venues" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"city_id" uuid NOT NULL,
	"name" text NOT NULL,
	"address" text,
	"lat" double precision,
	"lng" double precision,
	"owner_id" uuid,
	"status" "content_status" DEFAULT 'active' NOT NULL,
	"verified_at" timestamp with time zone,
	"hidden_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
);
CREATE TABLE "videos" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"city_id" uuid NOT NULL,
	"title" text NOT NULL,
	"embed_url" text NOT NULL,
	"platform" "video_platform" DEFAULT 'other' NOT NULL,
	"subject_type" "subject_type",
	"subject_id" uuid,
	"owner_id" uuid,
	"status" "content_status" DEFAULT 'active' NOT NULL,
	"verified_at" timestamp with time zone,
	"hidden_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
);
ALTER TABLE "audit_log" ADD CONSTRAINT "audit_log_actor_id_users_id_fk" FOREIGN KEY ("actor_id") REFERENCES "public"."users"("id") ON DELETE set null ON UPDATE no action;
ALTER TABLE "classes" ADD CONSTRAINT "classes_city_id_cities_id_fk" FOREIGN KEY ("city_id") REFERENCES "public"."cities"("id") ON DELETE cascade ON UPDATE no action;
ALTER TABLE "classes" ADD CONSTRAINT "classes_venue_id_venues_id_fk" FOREIGN KEY ("venue_id") REFERENCES "public"."venues"("id") ON DELETE set null ON UPDATE no action;
ALTER TABLE "classes" ADD CONSTRAINT "classes_owner_id_users_id_fk" FOREIGN KEY ("owner_id") REFERENCES "public"."users"("id") ON DELETE set null ON UPDATE no action;
ALTER TABLE "comments" ADD CONSTRAINT "comments_owner_id_users_id_fk" FOREIGN KEY ("owner_id") REFERENCES "public"."users"("id") ON DELETE set null ON UPDATE no action;
ALTER TABLE "djs" ADD CONSTRAINT "djs_city_id_cities_id_fk" FOREIGN KEY ("city_id") REFERENCES "public"."cities"("id") ON DELETE cascade ON UPDATE no action;
ALTER TABLE "djs" ADD CONSTRAINT "djs_owner_id_users_id_fk" FOREIGN KEY ("owner_id") REFERENCES "public"."users"("id") ON DELETE set null ON UPDATE no action;
ALTER TABLE "event_djs" ADD CONSTRAINT "event_djs_event_id_events_id_fk" FOREIGN KEY ("event_id") REFERENCES "public"."events"("id") ON DELETE cascade ON UPDATE no action;
ALTER TABLE "event_djs" ADD CONSTRAINT "event_djs_dj_id_djs_id_fk" FOREIGN KEY ("dj_id") REFERENCES "public"."djs"("id") ON DELETE cascade ON UPDATE no action;
ALTER TABLE "event_participants" ADD CONSTRAINT "event_participants_event_id_events_id_fk" FOREIGN KEY ("event_id") REFERENCES "public"."events"("id") ON DELETE cascade ON UPDATE no action;
ALTER TABLE "event_participants" ADD CONSTRAINT "event_participants_stepping_group_id_stepping_groups_id_fk" FOREIGN KEY ("stepping_group_id") REFERENCES "public"."stepping_groups"("id") ON DELETE cascade ON UPDATE no action;
ALTER TABLE "events" ADD CONSTRAINT "events_city_id_cities_id_fk" FOREIGN KEY ("city_id") REFERENCES "public"."cities"("id") ON DELETE cascade ON UPDATE no action;
ALTER TABLE "events" ADD CONSTRAINT "events_venue_id_venues_id_fk" FOREIGN KEY ("venue_id") REFERENCES "public"."venues"("id") ON DELETE set null ON UPDATE no action;
ALTER TABLE "events" ADD CONSTRAINT "events_owner_id_users_id_fk" FOREIGN KEY ("owner_id") REFERENCES "public"."users"("id") ON DELETE set null ON UPDATE no action;
ALTER TABLE "flags" ADD CONSTRAINT "flags_reporter_id_users_id_fk" FOREIGN KEY ("reporter_id") REFERENCES "public"."users"("id") ON DELETE set null ON UPDATE no action;
ALTER TABLE "group_memberships" ADD CONSTRAINT "group_memberships_stepping_group_id_stepping_groups_id_fk" FOREIGN KEY ("stepping_group_id") REFERENCES "public"."stepping_groups"("id") ON DELETE cascade ON UPDATE no action;
ALTER TABLE "group_memberships" ADD CONSTRAINT "group_memberships_user_id_users_id_fk" FOREIGN KEY ("user_id") REFERENCES "public"."users"("id") ON DELETE cascade ON UPDATE no action;
ALTER TABLE "orders" ADD CONSTRAINT "orders_buyer_id_users_id_fk" FOREIGN KEY ("buyer_id") REFERENCES "public"."users"("id") ON DELETE set null ON UPDATE no action;
ALTER TABLE "orders" ADD CONSTRAINT "orders_event_id_events_id_fk" FOREIGN KEY ("event_id") REFERENCES "public"."events"("id") ON DELETE set null ON UPDATE no action;
ALTER TABLE "orders" ADD CONSTRAINT "orders_ticket_type_id_ticket_types_id_fk" FOREIGN KEY ("ticket_type_id") REFERENCES "public"."ticket_types"("id") ON DELETE set null ON UPDATE no action;
ALTER TABLE "pro_accounts" ADD CONSTRAINT "pro_accounts_user_id_users_id_fk" FOREIGN KEY ("user_id") REFERENCES "public"."users"("id") ON DELETE cascade ON UPDATE no action;
ALTER TABLE "promoters" ADD CONSTRAINT "promoters_city_id_cities_id_fk" FOREIGN KEY ("city_id") REFERENCES "public"."cities"("id") ON DELETE cascade ON UPDATE no action;
ALTER TABLE "promoters" ADD CONSTRAINT "promoters_owner_id_users_id_fk" FOREIGN KEY ("owner_id") REFERENCES "public"."users"("id") ON DELETE set null ON UPDATE no action;
ALTER TABLE "sessions" ADD CONSTRAINT "sessions_user_id_users_id_fk" FOREIGN KEY ("user_id") REFERENCES "public"."users"("id") ON DELETE cascade ON UPDATE no action;
ALTER TABLE "sponsors" ADD CONSTRAINT "sponsors_city_id_cities_id_fk" FOREIGN KEY ("city_id") REFERENCES "public"."cities"("id") ON DELETE cascade ON UPDATE no action;
ALTER TABLE "sponsors" ADD CONSTRAINT "sponsors_owner_id_users_id_fk" FOREIGN KEY ("owner_id") REFERENCES "public"."users"("id") ON DELETE set null ON UPDATE no action;
ALTER TABLE "stepping_groups" ADD CONSTRAINT "stepping_groups_city_id_cities_id_fk" FOREIGN KEY ("city_id") REFERENCES "public"."cities"("id") ON DELETE cascade ON UPDATE no action;
ALTER TABLE "stepping_groups" ADD CONSTRAINT "stepping_groups_owner_id_users_id_fk" FOREIGN KEY ("owner_id") REFERENCES "public"."users"("id") ON DELETE set null ON UPDATE no action;
ALTER TABLE "ticket_types" ADD CONSTRAINT "ticket_types_event_id_events_id_fk" FOREIGN KEY ("event_id") REFERENCES "public"."events"("id") ON DELETE cascade ON UPDATE no action;
ALTER TABLE "ticket_types" ADD CONSTRAINT "ticket_types_owner_id_users_id_fk" FOREIGN KEY ("owner_id") REFERENCES "public"."users"("id") ON DELETE set null ON UPDATE no action;
ALTER TABLE "tickets" ADD CONSTRAINT "tickets_order_id_orders_id_fk" FOREIGN KEY ("order_id") REFERENCES "public"."orders"("id") ON DELETE cascade ON UPDATE no action;
ALTER TABLE "tickets" ADD CONSTRAINT "tickets_event_id_events_id_fk" FOREIGN KEY ("event_id") REFERENCES "public"."events"("id") ON DELETE cascade ON UPDATE no action;
ALTER TABLE "tickets" ADD CONSTRAINT "tickets_ticket_type_id_ticket_types_id_fk" FOREIGN KEY ("ticket_type_id") REFERENCES "public"."ticket_types"("id") ON DELETE set null ON UPDATE no action;
ALTER TABLE "tickets" ADD CONSTRAINT "tickets_holder_id_users_id_fk" FOREIGN KEY ("holder_id") REFERENCES "public"."users"("id") ON DELETE set null ON UPDATE no action;
ALTER TABLE "travel_places" ADD CONSTRAINT "travel_places_city_id_cities_id_fk" FOREIGN KEY ("city_id") REFERENCES "public"."cities"("id") ON DELETE cascade ON UPDATE no action;
ALTER TABLE "venues" ADD CONSTRAINT "venues_city_id_cities_id_fk" FOREIGN KEY ("city_id") REFERENCES "public"."cities"("id") ON DELETE cascade ON UPDATE no action;
ALTER TABLE "venues" ADD CONSTRAINT "venues_owner_id_users_id_fk" FOREIGN KEY ("owner_id") REFERENCES "public"."users"("id") ON DELETE set null ON UPDATE no action;
ALTER TABLE "videos" ADD CONSTRAINT "videos_city_id_cities_id_fk" FOREIGN KEY ("city_id") REFERENCES "public"."cities"("id") ON DELETE cascade ON UPDATE no action;
ALTER TABLE "videos" ADD CONSTRAINT "videos_owner_id_users_id_fk" FOREIGN KEY ("owner_id") REFERENCES "public"."users"("id") ON DELETE set null ON UPDATE no action;

1	CREATE TYPE "public"."content_status" AS ENUM('active', 'pending', 'hidden', 'removed');
2	CREATE TYPE "public"."event_type" AS ENUM('set', 'weekender', 'class', 'workshop', 'competition', 'special', 'social');
3	CREATE TYPE "public"."flag_status" AS ENUM('open', 'reviewed', 'actioned', 'dismissed');
4	CREATE TYPE "public"."order_purpose" AS ENUM('ticket', 'boost', 'pro', 'sponsor');
5	CREATE TYPE "public"."order_status" AS ENUM('reserved', 'paid', 'refunded', 'cancelled');
6	CREATE TYPE "public"."pro_status" AS ENUM('pending', 'active', 'expired', 'cancelled');
7	CREATE TYPE "public"."sponsor_status" AS ENUM('pending', 'active', 'ended');
8	CREATE TYPE "public"."subject_type" AS ENUM('event', 'video', 'promoter', 'dj', 'stepping_group', 'class');
9	CREATE TYPE "public"."ticket_status" AS ENUM('valid', 'used', 'void');
10	CREATE TYPE "public"."travel_category" AS ENUM('event_center', 'hotel', 'restaurant', 'mall', 'attraction', 'entertainment', 'hospital');
11	CREATE TYPE "public"."user_role" AS ENUM('member', 'moderator', 'admin');
12	CREATE TYPE "public"."video_platform" AS ENUM('youtube', 'instagram', 'tiktok', 'other');
13	CREATE TABLE "audit_log" (
14	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
15	"actor_id" uuid,
16	"action" text NOT NULL,
17	"subject_type" text,
18	"subject_id" uuid,
19	"meta" jsonb,
20	"created_at" timestamp with time zone DEFAULT now() NOT NULL
21	);
22	CREATE TABLE "cities" (
23	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
24	"slug" text NOT NULL,
25	"name" text NOT NULL,
26	"metro_label" text,
27	"state" text,
28	"tier" integer DEFAULT 2 NOT NULL,
29	"is_launched" boolean DEFAULT false NOT NULL,
30	"lat" double precision,
31	"lng" double precision,
32	"created_at" timestamp with time zone DEFAULT now() NOT NULL,
33	"updated_at" timestamp with time zone DEFAULT now() NOT NULL,
34	CONSTRAINT "cities_slug_unique" UNIQUE("slug")
35	);
36	CREATE TABLE "classes" (
37	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
38	"city_id" uuid NOT NULL,
39	"title" text NOT NULL,
40	"instructor" text,
41	"schedule" text,
42	"venue_id" uuid,
43	"price_text" text,
44	"is_free" boolean DEFAULT false NOT NULL,
45	"owner_id" uuid,
46	"status" "content_status" DEFAULT 'active' NOT NULL,
47	"verified_at" timestamp with time zone,
48	"hidden_at" timestamp with time zone,
49	"created_at" timestamp with time zone DEFAULT now() NOT NULL,
50	"updated_at" timestamp with time zone DEFAULT now() NOT NULL
51	);
52	CREATE TABLE "comments" (
53	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
54	"subject_type" "subject_type" NOT NULL,
55	"subject_id" uuid NOT NULL,
56	"body" text NOT NULL,
57	"owner_id" uuid,
58	"status" "content_status" DEFAULT 'active' NOT NULL,
59	"verified_at" timestamp with time zone,
60	"hidden_at" timestamp with time zone,
61	"created_at" timestamp with time zone DEFAULT now() NOT NULL,
62	"updated_at" timestamp with time zone DEFAULT now() NOT NULL
63	);
64	CREATE TABLE "djs" (
65	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
66	"city_id" uuid NOT NULL,
67	"name" text NOT NULL,
68	"bio" text,
69	"links" jsonb,
70	"owner_id" uuid,
71	"status" "content_status" DEFAULT 'active' NOT NULL,
72	"verified_at" timestamp with time zone,
73	"hidden_at" timestamp with time zone,
74	"created_at" timestamp with time zone DEFAULT now() NOT NULL,
75	"updated_at" timestamp with time zone DEFAULT now() NOT NULL
76	);
77	CREATE TABLE "event_djs" (
78	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
79	"event_id" uuid NOT NULL,
80	"dj_id" uuid NOT NULL,
81	"created_at" timestamp with time zone DEFAULT now() NOT NULL
82	);
83	CREATE TABLE "event_participants" (
84	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
85	"event_id" uuid NOT NULL,
86	"stepping_group_id" uuid,
87	"created_at" timestamp with time zone DEFAULT now() NOT NULL
88	);
89	CREATE TABLE "events" (
90	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
91	"city_id" uuid NOT NULL,
92	"venue_id" uuid,
93	"title" text NOT NULL,
94	"type" "event_type" NOT NULL,
95	"description" text,
96	"starts_at" timestamp with time zone,
97	"ends_at" timestamp with time zone,
98	"external_url" text,
99	"is_promoted" boolean DEFAULT false NOT NULL,
100	"boosted_until" timestamp with time zone,
101	"is_live" boolean DEFAULT false NOT NULL,
102	"live_url" text,
103	"ticket_url" text,
104	"price_text" text,
105	"is_free" boolean DEFAULT false NOT NULL,
106	"owner_id" uuid,
107	"status" "content_status" DEFAULT 'active' NOT NULL,
108	"verified_at" timestamp with time zone,
109	"hidden_at" timestamp with time zone,
110	"created_at" timestamp with time zone DEFAULT now() NOT NULL,
111	"updated_at" timestamp with time zone DEFAULT now() NOT NULL
112	);
113	CREATE TABLE "flags" (
114	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
115	"subject_type" "subject_type" NOT NULL,
116	"subject_id" uuid NOT NULL,
117	"reporter_id" uuid,
118	"reason" text,
119	"status" "flag_status" DEFAULT 'open' NOT NULL,
120	"created_at" timestamp with time zone DEFAULT now() NOT NULL
121	);
122	CREATE TABLE "group_memberships" (
123	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
124	"stepping_group_id" uuid NOT NULL,
125	"user_id" uuid NOT NULL,
126	"role_in_group" text,
127	"created_at" timestamp with time zone DEFAULT now() NOT NULL
128	);
129	CREATE TABLE "orders" (
130	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
131	"purpose" "order_purpose" NOT NULL,
132	"buyer_id" uuid,
133	"event_id" uuid,
134	"ticket_type_id" uuid,
135	"quantity" integer DEFAULT 1 NOT NULL,
136	"amount_cents" integer DEFAULT 0 NOT NULL,
137	"status" "order_status" DEFAULT 'reserved' NOT NULL,
138	"meta" jsonb,
139	"created_at" timestamp with time zone DEFAULT now() NOT NULL,
140	"updated_at" timestamp with time zone DEFAULT now() NOT NULL
141	);
142	CREATE TABLE "pro_accounts" (
143	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
144	"user_id" uuid NOT NULL,
145	"tier" text DEFAULT 'pro' NOT NULL,
146	"status" "pro_status" DEFAULT 'pending' NOT NULL,
147	"started_at" timestamp with time zone,
148	"expires_at" timestamp with time zone,
149	"created_at" timestamp with time zone DEFAULT now() NOT NULL,
150	"updated_at" timestamp with time zone DEFAULT now() NOT NULL
151	);
152	CREATE TABLE "promoters" (
153	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
154	"city_id" uuid NOT NULL,
155	"name" text NOT NULL,
156	"bio" text,
157	"links" jsonb,
158	"owner_id" uuid,
159	"status" "content_status" DEFAULT 'active' NOT NULL,
160	"verified_at" timestamp with time zone,
161	"hidden_at" timestamp with time zone,
162	"created_at" timestamp with time zone DEFAULT now() NOT NULL,
163	"updated_at" timestamp with time zone DEFAULT now() NOT NULL
164	);
165	CREATE TABLE "sessions" (
166	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
167	"token_hash" text NOT NULL,
168	"user_id" uuid NOT NULL,
169	"expires_at" timestamp with time zone NOT NULL,
170	"created_at" timestamp with time zone DEFAULT now() NOT NULL,
171	CONSTRAINT "sessions_token_hash_unique" UNIQUE("token_hash")
172	);
173	CREATE TABLE "sponsors" (
174	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
175	"city_id" uuid NOT NULL,
176	"name" text NOT NULL,
177	"blurb" text,
178	"link_url" text,
179	"image_url" text,
180	"status" "sponsor_status" DEFAULT 'pending' NOT NULL,
181	"starts_at" timestamp with time zone,
182	"ends_at" timestamp with time zone,
183	"owner_id" uuid,
184	"created_at" timestamp with time zone DEFAULT now() NOT NULL,
185	"updated_at" timestamp with time zone DEFAULT now() NOT NULL
186	);
187	CREATE TABLE "stepping_groups" (
188	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
189	"city_id" uuid NOT NULL,
190	"name" text NOT NULL,
191	"bio" text,
192	"links" jsonb,
193	"owner_id" uuid,
194	"status" "content_status" DEFAULT 'active' NOT NULL,
195	"verified_at" timestamp with time zone,
196	"hidden_at" timestamp with time zone,
197	"created_at" timestamp with time zone DEFAULT now() NOT NULL,
198	"updated_at" timestamp with time zone DEFAULT now() NOT NULL
199	);
200	CREATE TABLE "ticket_types" (
201	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
202	"event_id" uuid NOT NULL,
203	"name" text NOT NULL,
204	"price_cents" integer DEFAULT 0 NOT NULL,
205	"currency" text DEFAULT 'usd' NOT NULL,
206	"capacity" integer,
207	"sold" integer DEFAULT 0 NOT NULL,
208	"is_active" boolean DEFAULT true NOT NULL,
209	"owner_id" uuid,
210	"created_at" timestamp with time zone DEFAULT now() NOT NULL,
211	"updated_at" timestamp with time zone DEFAULT now() NOT NULL
212	);
213	CREATE TABLE "tickets" (
214	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
215	"order_id" uuid NOT NULL,
216	"event_id" uuid NOT NULL,
217	"ticket_type_id" uuid,
218	"holder_id" uuid,
219	"code" text NOT NULL,
220	"status" "ticket_status" DEFAULT 'valid' NOT NULL,
221	"created_at" timestamp with time zone DEFAULT now() NOT NULL,
222	"updated_at" timestamp with time zone DEFAULT now() NOT NULL,
223	CONSTRAINT "tickets_code_unique" UNIQUE("code")
224	);
225	CREATE TABLE "travel_places" (
226	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
227	"city_id" uuid NOT NULL,
228	"category" "travel_category" NOT NULL,
229	"name" text NOT NULL,
230	"address" text,
231	"lat" double precision,
232	"lng" double precision,
233	"source" text DEFAULT 'manual' NOT NULL,
234	"place_ref" text,
235	"status" "content_status" DEFAULT 'active' NOT NULL,
236	"verified_at" timestamp with time zone,
237	"hidden_at" timestamp with time zone,
238	"created_at" timestamp with time zone DEFAULT now() NOT NULL,
239	"updated_at" timestamp with time zone DEFAULT now() NOT NULL
240	);
241	CREATE TABLE "users" (
242	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
243	"email" text,
244	"password_hash" text,
245	"handle" text,
246	"display_name" text,
247	"avatar_url" text,
248	"role" "user_role" DEFAULT 'member' NOT NULL,
249	"trust_tier" integer DEFAULT 0 NOT NULL,
250	"created_at" timestamp with time zone DEFAULT now() NOT NULL,
251	"updated_at" timestamp with time zone DEFAULT now() NOT NULL,
252	CONSTRAINT "users_email_unique" UNIQUE("email"),
253	CONSTRAINT "users_handle_unique" UNIQUE("handle")
254	);
255	CREATE TABLE "venues" (
256	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
257	"city_id" uuid NOT NULL,
258	"name" text NOT NULL,
259	"address" text,
260	"lat" double precision,
261	"lng" double precision,
262	"owner_id" uuid,
263	"status" "content_status" DEFAULT 'active' NOT NULL,
264	"verified_at" timestamp with time zone,
265	"hidden_at" timestamp with time zone,
266	"created_at" timestamp with time zone DEFAULT now() NOT NULL,
267	"updated_at" timestamp with time zone DEFAULT now() NOT NULL
268	);
269	CREATE TABLE "videos" (
270	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
271	"city_id" uuid NOT NULL,
272	"title" text NOT NULL,
273	"embed_url" text NOT NULL,
274	"platform" "video_platform" DEFAULT 'other' NOT NULL,
275	"subject_type" "subject_type",
276	"subject_id" uuid,
277	"owner_id" uuid,
278	"status" "content_status" DEFAULT 'active' NOT NULL,
279	"verified_at" timestamp with time zone,
280	"hidden_at" timestamp with time zone,
281	"created_at" timestamp with time zone DEFAULT now() NOT NULL,
282	"updated_at" timestamp with time zone DEFAULT now() NOT NULL
283	);
284	ALTER TABLE "audit_log" ADD CONSTRAINT "audit_log_actor_id_users_id_fk" FOREIGN KEY ("actor_id") REFERENCES "public"."users"("id") ON DELETE set null ON UPDATE no action;
285	ALTER TABLE "classes" ADD CONSTRAINT "classes_city_id_cities_id_fk" FOREIGN KEY ("city_id") REFERENCES "public"."cities"("id") ON DELETE cascade ON UPDATE no action;
286	ALTER TABLE "classes" ADD CONSTRAINT "classes_venue_id_venues_id_fk" FOREIGN KEY ("venue_id") REFERENCES "public"."venues"("id") ON DELETE set null ON UPDATE no action;
287	ALTER TABLE "classes" ADD CONSTRAINT "classes_owner_id_users_id_fk" FOREIGN KEY ("owner_id") REFERENCES "public"."users"("id") ON DELETE set null ON UPDATE no action;
288	ALTER TABLE "comments" ADD CONSTRAINT "comments_owner_id_users_id_fk" FOREIGN KEY ("owner_id") REFERENCES "public"."users"("id") ON DELETE set null ON UPDATE no action;
289	ALTER TABLE "djs" ADD CONSTRAINT "djs_city_id_cities_id_fk" FOREIGN KEY ("city_id") REFERENCES "public"."cities"("id") ON DELETE cascade ON UPDATE no action;
290	ALTER TABLE "djs" ADD CONSTRAINT "djs_owner_id_users_id_fk" FOREIGN KEY ("owner_id") REFERENCES "public"."users"("id") ON DELETE set null ON UPDATE no action;
291	ALTER TABLE "event_djs" ADD CONSTRAINT "event_djs_event_id_events_id_fk" FOREIGN KEY ("event_id") REFERENCES "public"."events"("id") ON DELETE cascade ON UPDATE no action;
292	ALTER TABLE "event_djs" ADD CONSTRAINT "event_djs_dj_id_djs_id_fk" FOREIGN KEY ("dj_id") REFERENCES "public"."djs"("id") ON DELETE cascade ON UPDATE no action;
293	ALTER TABLE "event_participants" ADD CONSTRAINT "event_participants_event_id_events_id_fk" FOREIGN KEY ("event_id") REFERENCES "public"."events"("id") ON DELETE cascade ON UPDATE no action;
294	ALTER TABLE "event_participants" ADD CONSTRAINT "event_participants_stepping_group_id_stepping_groups_id_fk" FOREIGN KEY ("stepping_group_id") REFERENCES "public"."stepping_groups"("id") ON DELETE cascade ON UPDATE no action;
295	ALTER TABLE "events" ADD CONSTRAINT "events_city_id_cities_id_fk" FOREIGN KEY ("city_id") REFERENCES "public"."cities"("id") ON DELETE cascade ON UPDATE no action;
296	ALTER TABLE "events" ADD CONSTRAINT "events_venue_id_venues_id_fk" FOREIGN KEY ("venue_id") REFERENCES "public"."venues"("id") ON DELETE set null ON UPDATE no action;
297	ALTER TABLE "events" ADD CONSTRAINT "events_owner_id_users_id_fk" FOREIGN KEY ("owner_id") REFERENCES "public"."users"("id") ON DELETE set null ON UPDATE no action;
298	ALTER TABLE "flags" ADD CONSTRAINT "flags_reporter_id_users_id_fk" FOREIGN KEY ("reporter_id") REFERENCES "public"."users"("id") ON DELETE set null ON UPDATE no action;
299	ALTER TABLE "group_memberships" ADD CONSTRAINT "group_memberships_stepping_group_id_stepping_groups_id_fk" FOREIGN KEY ("stepping_group_id") REFERENCES "public"."stepping_groups"("id") ON DELETE cascade ON UPDATE no action;
300	ALTER TABLE "group_memberships" ADD CONSTRAINT "group_memberships_user_id_users_id_fk" FOREIGN KEY ("user_id") REFERENCES "public"."users"("id") ON DELETE cascade ON UPDATE no action;
301	ALTER TABLE "orders" ADD CONSTRAINT "orders_buyer_id_users_id_fk" FOREIGN KEY ("buyer_id") REFERENCES "public"."users"("id") ON DELETE set null ON UPDATE no action;
302	ALTER TABLE "orders" ADD CONSTRAINT "orders_event_id_events_id_fk" FOREIGN KEY ("event_id") REFERENCES "public"."events"("id") ON DELETE set null ON UPDATE no action;
303	ALTER TABLE "orders" ADD CONSTRAINT "orders_ticket_type_id_ticket_types_id_fk" FOREIGN KEY ("ticket_type_id") REFERENCES "public"."ticket_types"("id") ON DELETE set null ON UPDATE no action;
304	ALTER TABLE "pro_accounts" ADD CONSTRAINT "pro_accounts_user_id_users_id_fk" FOREIGN KEY ("user_id") REFERENCES "public"."users"("id") ON DELETE cascade ON UPDATE no action;
305	ALTER TABLE "promoters" ADD CONSTRAINT "promoters_city_id_cities_id_fk" FOREIGN KEY ("city_id") REFERENCES "public"."cities"("id") ON DELETE cascade ON UPDATE no action;
306	ALTER TABLE "promoters" ADD CONSTRAINT "promoters_owner_id_users_id_fk" FOREIGN KEY ("owner_id") REFERENCES "public"."users"("id") ON DELETE set null ON UPDATE no action;
307	ALTER TABLE "sessions" ADD CONSTRAINT "sessions_user_id_users_id_fk" FOREIGN KEY ("user_id") REFERENCES "public"."users"("id") ON DELETE cascade ON UPDATE no action;
308	ALTER TABLE "sponsors" ADD CONSTRAINT "sponsors_city_id_cities_id_fk" FOREIGN KEY ("city_id") REFERENCES "public"."cities"("id") ON DELETE cascade ON UPDATE no action;
309	ALTER TABLE "sponsors" ADD CONSTRAINT "sponsors_owner_id_users_id_fk" FOREIGN KEY ("owner_id") REFERENCES "public"."users"("id") ON DELETE set null ON UPDATE no action;
310	ALTER TABLE "stepping_groups" ADD CONSTRAINT "stepping_groups_city_id_cities_id_fk" FOREIGN KEY ("city_id") REFERENCES "public"."cities"("id") ON DELETE cascade ON UPDATE no action;
311	ALTER TABLE "stepping_groups" ADD CONSTRAINT "stepping_groups_owner_id_users_id_fk" FOREIGN KEY ("owner_id") REFERENCES "public"."users"("id") ON DELETE set null ON UPDATE no action;
312	ALTER TABLE "ticket_types" ADD CONSTRAINT "ticket_types_event_id_events_id_fk" FOREIGN KEY ("event_id") REFERENCES "public"."events"("id") ON DELETE cascade ON UPDATE no action;
313	ALTER TABLE "ticket_types" ADD CONSTRAINT "ticket_types_owner_id_users_id_fk" FOREIGN KEY ("owner_id") REFERENCES "public"."users"("id") ON DELETE set null ON UPDATE no action;
314	ALTER TABLE "tickets" ADD CONSTRAINT "tickets_order_id_orders_id_fk" FOREIGN KEY ("order_id") REFERENCES "public"."orders"("id") ON DELETE cascade ON UPDATE no action;
315	ALTER TABLE "tickets" ADD CONSTRAINT "tickets_event_id_events_id_fk" FOREIGN KEY ("event_id") REFERENCES "public"."events"("id") ON DELETE cascade ON UPDATE no action;
316	ALTER TABLE "tickets" ADD CONSTRAINT "tickets_ticket_type_id_ticket_types_id_fk" FOREIGN KEY ("ticket_type_id") REFERENCES "public"."ticket_types"("id") ON DELETE set null ON UPDATE no action;
317	ALTER TABLE "tickets" ADD CONSTRAINT "tickets_holder_id_users_id_fk" FOREIGN KEY ("holder_id") REFERENCES "public"."users"("id") ON DELETE set null ON UPDATE no action;
318	ALTER TABLE "travel_places" ADD CONSTRAINT "travel_places_city_id_cities_id_fk" FOREIGN KEY ("city_id") REFERENCES "public"."cities"("id") ON DELETE cascade ON UPDATE no action;
319	ALTER TABLE "venues" ADD CONSTRAINT "venues_city_id_cities_id_fk" FOREIGN KEY ("city_id") REFERENCES "public"."cities"("id") ON DELETE cascade ON UPDATE no action;
320	ALTER TABLE "venues" ADD CONSTRAINT "venues_owner_id_users_id_fk" FOREIGN KEY ("owner_id") REFERENCES "public"."users"("id") ON DELETE set null ON UPDATE no action;
321	ALTER TABLE "videos" ADD CONSTRAINT "videos_city_id_cities_id_fk" FOREIGN KEY ("city_id") REFERENCES "public"."cities"("id") ON DELETE cascade ON UPDATE no action;
322	ALTER TABLE "videos" ADD CONSTRAINT "videos_owner_id_users_id_fk" FOREIGN KEY ("owner_id") REFERENCES "public"."users"("id") ON DELETE set null ON UPDATE no action;