| 1 | import { |
| 2 | boolean, |
| 3 | doublePrecision, |
| 4 | integer, |
| 5 | jsonb, |
| 6 | pgEnum, |
| 7 | pgTable, |
| 8 | text, |
| 9 | timestamp, |
| 10 | uuid, |
| 11 | } from "drizzle-orm/pg-core"; |
| 12 | import { |
| 13 | CONTENT_STATUS, |
| 14 | EVENT_TYPE, |
| 15 | FLAG_STATUS, |
| 16 | ORDER_PURPOSE, |
| 17 | ORDER_STATUS, |
| 18 | PRO_STATUS, |
| 19 | SPONSOR_STATUS, |
| 20 | SUBJECT_TYPE, |
| 21 | TICKET_STATUS, |
| 22 | TRAVEL_CATEGORY, |
| 23 | USER_ROLE, |
| 24 | VIDEO_PLATFORM, |
| 25 | } from "@stepping/types"; |
| 26 | |
| 27 | const tuple = <T extends readonly [string, ...string[]]>(arr: T): T => arr; |
| 28 | |
| 29 | export const contentStatus = pgEnum("content_status", tuple(CONTENT_STATUS)); |
| 30 | export const userRole = pgEnum("user_role", tuple(USER_ROLE)); |
| 31 | export const eventType = pgEnum("event_type", tuple(EVENT_TYPE)); |
| 32 | export const videoPlatform = pgEnum("video_platform", tuple(VIDEO_PLATFORM)); |
| 33 | export const subjectType = pgEnum("subject_type", tuple(SUBJECT_TYPE)); |
| 34 | export const travelCategory = pgEnum("travel_category", tuple(TRAVEL_CATEGORY)); |
| 35 | export const flagStatus = pgEnum("flag_status", tuple(FLAG_STATUS)); |
| 36 | export const orderPurpose = pgEnum("order_purpose", tuple(ORDER_PURPOSE)); |
| 37 | export const orderStatus = pgEnum("order_status", tuple(ORDER_STATUS)); |
| 38 | export const ticketStatus = pgEnum("ticket_status", tuple(TICKET_STATUS)); |
| 39 | export const proStatus = pgEnum("pro_status", tuple(PRO_STATUS)); |
| 40 | export const sponsorStatus = pgEnum("sponsor_status", tuple(SPONSOR_STATUS)); |
| 41 | |
| 42 | const timestamps = { |
| 43 | createdAt: timestamp("created_at", { withTimezone: true }).defaultNow().notNull(), |
| 44 | updatedAt: timestamp("updated_at", { withTimezone: true }).defaultNow().notNull(), |
| 45 | }; |
| 46 | |
| 47 | const ownership = { |
| 48 | ownerId: uuid("owner_id").references(() => users.id, { onDelete: "set null" }), |
| 49 | }; |
| 50 | |
| 51 | const moderatable = { |
| 52 | status: contentStatus("status").default("active").notNull(), |
| 53 | verifiedAt: timestamp("verified_at", { withTimezone: true }), |
| 54 | hiddenAt: timestamp("hidden_at", { withTimezone: true }), |
| 55 | }; |
| 56 | |
| 57 | type LinkRef = { label: string; url: string }; |
| 58 | |
| 59 | export const users = pgTable("users", { |
| 60 | id: uuid("id").primaryKey().defaultRandom(), |
| 61 | email: text("email").unique(), |
| 62 | passwordHash: text("password_hash"), |
| 63 | handle: text("handle").unique(), |
| 64 | displayName: text("display_name"), |
| 65 | avatarUrl: text("avatar_url"), |
| 66 | role: userRole("role").default("member").notNull(), |
| 67 | trustTier: integer("trust_tier").default(0).notNull(), |
| 68 | ...timestamps, |
| 69 | }); |
| 70 | |
| 71 | export const sessions = pgTable("sessions", { |
| 72 | id: uuid("id").primaryKey().defaultRandom(), |
| 73 | tokenHash: text("token_hash").unique().notNull(), |
| 74 | userId: uuid("user_id") |
| 75 | .references(() => users.id, { onDelete: "cascade" }) |
| 76 | .notNull(), |
| 77 | expiresAt: timestamp("expires_at", { withTimezone: true }).notNull(), |
| 78 | createdAt: timestamps.createdAt, |
| 79 | }); |
| 80 | |
| 81 | export const cities = pgTable("cities", { |
| 82 | id: uuid("id").primaryKey().defaultRandom(), |
| 83 | slug: text("slug").unique().notNull(), |
| 84 | name: text("name").notNull(), |
| 85 | metroLabel: text("metro_label"), |
| 86 | state: text("state"), |
| 87 | tier: integer("tier").default(2).notNull(), |
| 88 | isLaunched: boolean("is_launched").default(false).notNull(), |
| 89 | lat: doublePrecision("lat"), |
| 90 | lng: doublePrecision("lng"), |
| 91 | ...timestamps, |
| 92 | }); |
| 93 | |
| 94 | export const venues = pgTable("venues", { |
| 95 | id: uuid("id").primaryKey().defaultRandom(), |
| 96 | cityId: uuid("city_id") |
| 97 | .references(() => cities.id, { onDelete: "cascade" }) |
| 98 | .notNull(), |
| 99 | name: text("name").notNull(), |
| 100 | address: text("address"), |
| 101 | lat: doublePrecision("lat"), |
| 102 | lng: doublePrecision("lng"), |
| 103 | ...ownership, |
| 104 | ...moderatable, |
| 105 | ...timestamps, |
| 106 | }); |
| 107 | |
| 108 | export const steppingGroups = pgTable("stepping_groups", { |
| 109 | id: uuid("id").primaryKey().defaultRandom(), |
| 110 | cityId: uuid("city_id") |
| 111 | .references(() => cities.id, { onDelete: "cascade" }) |
| 112 | .notNull(), |
| 113 | name: text("name").notNull(), |
| 114 | bio: text("bio"), |
| 115 | links: jsonb("links").$type<LinkRef[]>(), |
| 116 | ...ownership, |
| 117 | ...moderatable, |
| 118 | ...timestamps, |
| 119 | }); |
| 120 | |
| 121 | export const promoters = pgTable("promoters", { |
| 122 | id: uuid("id").primaryKey().defaultRandom(), |
| 123 | cityId: uuid("city_id") |
| 124 | .references(() => cities.id, { onDelete: "cascade" }) |
| 125 | .notNull(), |
| 126 | name: text("name").notNull(), |
| 127 | bio: text("bio"), |
| 128 | links: jsonb("links").$type<LinkRef[]>(), |
| 129 | ...ownership, |
| 130 | ...moderatable, |
| 131 | ...timestamps, |
| 132 | }); |
| 133 | |
| 134 | export const djs = pgTable("djs", { |
| 135 | id: uuid("id").primaryKey().defaultRandom(), |
| 136 | cityId: uuid("city_id") |
| 137 | .references(() => cities.id, { onDelete: "cascade" }) |
| 138 | .notNull(), |
| 139 | name: text("name").notNull(), |
| 140 | bio: text("bio"), |
| 141 | links: jsonb("links").$type<LinkRef[]>(), |
| 142 | ...ownership, |
| 143 | ...moderatable, |
| 144 | ...timestamps, |
| 145 | }); |
| 146 | |
| 147 | export const instructors = pgTable("instructors", { |
| 148 | id: uuid("id").primaryKey().defaultRandom(), |
| 149 | cityId: uuid("city_id") |
| 150 | .references(() => cities.id, { onDelete: "cascade" }) |
| 151 | .notNull(), |
| 152 | name: text("name").notNull(), |
| 153 | bio: text("bio"), |
| 154 | links: jsonb("links").$type<LinkRef[]>(), |
| 155 | ...ownership, |
| 156 | ...moderatable, |
| 157 | ...timestamps, |
| 158 | }); |
| 159 | |
| 160 | export const classes = pgTable("classes", { |
| 161 | id: uuid("id").primaryKey().defaultRandom(), |
| 162 | cityId: uuid("city_id") |
| 163 | .references(() => cities.id, { onDelete: "cascade" }) |
| 164 | .notNull(), |
| 165 | title: text("title").notNull(), |
| 166 | instructor: text("instructor"), |
| 167 | schedule: text("schedule"), |
| 168 | venueId: uuid("venue_id").references(() => venues.id, { onDelete: "set null" }), |
| 169 | priceText: text("price_text"), |
| 170 | isFree: boolean("is_free").default(false).notNull(), |
| 171 | ...ownership, |
| 172 | ...moderatable, |
| 173 | ...timestamps, |
| 174 | }); |
| 175 | |
| 176 | export const events = pgTable("events", { |
| 177 | id: uuid("id").primaryKey().defaultRandom(), |
| 178 | cityId: uuid("city_id") |
| 179 | .references(() => cities.id, { onDelete: "cascade" }) |
| 180 | .notNull(), |
| 181 | venueId: uuid("venue_id").references(() => venues.id, { onDelete: "set null" }), |
| 182 | title: text("title").notNull(), |
| 183 | type: eventType("type").notNull(), |
| 184 | description: text("description"), |
| 185 | startsAt: timestamp("starts_at", { withTimezone: true }), |
| 186 | endsAt: timestamp("ends_at", { withTimezone: true }), |
| 187 | externalUrl: text("external_url"), |
| 188 | isPromoted: boolean("is_promoted").default(false).notNull(), |
| 189 | boostedUntil: timestamp("boosted_until", { withTimezone: true }), |
| 190 | isLive: boolean("is_live").default(false).notNull(), |
| 191 | liveUrl: text("live_url"), |
| 192 | ticketUrl: text("ticket_url"), |
| 193 | priceText: text("price_text"), |
| 194 | isFree: boolean("is_free").default(false).notNull(), |
| 195 | flyerUrl: text("flyer_url"), |
| 196 | programUrl: text("program_url"), |
| 197 | ...ownership, |
| 198 | ...moderatable, |
| 199 | ...timestamps, |
| 200 | }); |
| 201 | |
| 202 | export const eventPhotos = pgTable("event_photos", { |
| 203 | id: uuid("id").primaryKey().defaultRandom(), |
| 204 | eventId: uuid("event_id") |
| 205 | .references(() => events.id, { onDelete: "cascade" }) |
| 206 | .notNull(), |
| 207 | url: text("url").notNull(), |
| 208 | caption: text("caption"), |
| 209 | ...ownership, |
| 210 | ...moderatable, |
| 211 | status: contentStatus("status").default("pending").notNull(), |
| 212 | ...timestamps, |
| 213 | }); |
| 214 | |
| 215 | export const vendors = pgTable("vendors", { |
| 216 | id: uuid("id").primaryKey().defaultRandom(), |
| 217 | cityId: uuid("city_id") |
| 218 | .references(() => cities.id, { onDelete: "cascade" }) |
| 219 | .notNull(), |
| 220 | eventId: uuid("event_id").references(() => events.id, { onDelete: "set null" }), |
| 221 | name: text("name").notNull(), |
| 222 | description: text("description"), |
| 223 | category: text("category"), |
| 224 | linkUrl: text("link_url"), |
| 225 | ...ownership, |
| 226 | ...moderatable, |
| 227 | ...timestamps, |
| 228 | }); |
| 229 | |
| 230 | export const videos = pgTable("videos", { |
| 231 | id: uuid("id").primaryKey().defaultRandom(), |
| 232 | cityId: uuid("city_id") |
| 233 | .references(() => cities.id, { onDelete: "cascade" }) |
| 234 | .notNull(), |
| 235 | title: text("title").notNull(), |
| 236 | embedUrl: text("embed_url").notNull(), |
| 237 | platform: videoPlatform("platform").default("other").notNull(), |
| 238 | subjectType: subjectType("subject_type"), |
| 239 | subjectId: uuid("subject_id"), |
| 240 | ...ownership, |
| 241 | ...moderatable, |
| 242 | ...timestamps, |
| 243 | }); |
| 244 | |
| 245 | export const comments = pgTable("comments", { |
| 246 | id: uuid("id").primaryKey().defaultRandom(), |
| 247 | subjectType: subjectType("subject_type").notNull(), |
| 248 | subjectId: uuid("subject_id").notNull(), |
| 249 | body: text("body").notNull(), |
| 250 | ...ownership, |
| 251 | ...moderatable, |
| 252 | ...timestamps, |
| 253 | }); |
| 254 | |
| 255 | export const flags = pgTable("flags", { |
| 256 | id: uuid("id").primaryKey().defaultRandom(), |
| 257 | subjectType: subjectType("subject_type").notNull(), |
| 258 | subjectId: uuid("subject_id").notNull(), |
| 259 | reporterId: uuid("reporter_id").references(() => users.id, { onDelete: "set null" }), |
| 260 | reason: text("reason"), |
| 261 | status: flagStatus("status").default("open").notNull(), |
| 262 | createdAt: timestamps.createdAt, |
| 263 | }); |
| 264 | |
| 265 | export const travelPlaces = pgTable("travel_places", { |
| 266 | id: uuid("id").primaryKey().defaultRandom(), |
| 267 | cityId: uuid("city_id") |
| 268 | .references(() => cities.id, { onDelete: "cascade" }) |
| 269 | .notNull(), |
| 270 | category: travelCategory("category").notNull(), |
| 271 | name: text("name").notNull(), |
| 272 | address: text("address"), |
| 273 | lat: doublePrecision("lat"), |
| 274 | lng: doublePrecision("lng"), |
| 275 | source: text("source").default("manual").notNull(), |
| 276 | placeRef: text("place_ref"), |
| 277 | ...moderatable, |
| 278 | ...timestamps, |
| 279 | }); |
| 280 | |
| 281 | export const eventParticipants = pgTable("event_participants", { |
| 282 | id: uuid("id").primaryKey().defaultRandom(), |
| 283 | eventId: uuid("event_id") |
| 284 | .references(() => events.id, { onDelete: "cascade" }) |
| 285 | .notNull(), |
| 286 | steppingGroupId: uuid("stepping_group_id").references(() => steppingGroups.id, { |
| 287 | onDelete: "cascade", |
| 288 | }), |
| 289 | createdAt: timestamps.createdAt, |
| 290 | }); |
| 291 | |
| 292 | export const eventDjs = pgTable("event_djs", { |
| 293 | id: uuid("id").primaryKey().defaultRandom(), |
| 294 | eventId: uuid("event_id") |
| 295 | .references(() => events.id, { onDelete: "cascade" }) |
| 296 | .notNull(), |
| 297 | djId: uuid("dj_id") |
| 298 | .references(() => djs.id, { onDelete: "cascade" }) |
| 299 | .notNull(), |
| 300 | createdAt: timestamps.createdAt, |
| 301 | }); |
| 302 | |
| 303 | export const groupMemberships = pgTable("group_memberships", { |
| 304 | id: uuid("id").primaryKey().defaultRandom(), |
| 305 | steppingGroupId: uuid("stepping_group_id") |
| 306 | .references(() => steppingGroups.id, { onDelete: "cascade" }) |
| 307 | .notNull(), |
| 308 | userId: uuid("user_id") |
| 309 | .references(() => users.id, { onDelete: "cascade" }) |
| 310 | .notNull(), |
| 311 | roleInGroup: text("role_in_group"), |
| 312 | createdAt: timestamps.createdAt, |
| 313 | }); |
| 314 | |
| 315 | export const ticketTypes = pgTable("ticket_types", { |
| 316 | id: uuid("id").primaryKey().defaultRandom(), |
| 317 | eventId: uuid("event_id") |
| 318 | .references(() => events.id, { onDelete: "cascade" }) |
| 319 | .notNull(), |
| 320 | name: text("name").notNull(), |
| 321 | priceCents: integer("price_cents").default(0).notNull(), |
| 322 | currency: text("currency").default("usd").notNull(), |
| 323 | capacity: integer("capacity"), |
| 324 | sold: integer("sold").default(0).notNull(), |
| 325 | isActive: boolean("is_active").default(true).notNull(), |
| 326 | ...ownership, |
| 327 | ...timestamps, |
| 328 | }); |
| 329 | |
| 330 | export const orders = pgTable("orders", { |
| 331 | id: uuid("id").primaryKey().defaultRandom(), |
| 332 | purpose: orderPurpose("purpose").notNull(), |
| 333 | buyerId: uuid("buyer_id").references(() => users.id, { onDelete: "set null" }), |
| 334 | eventId: uuid("event_id").references(() => events.id, { onDelete: "set null" }), |
| 335 | ticketTypeId: uuid("ticket_type_id").references(() => ticketTypes.id, { |
| 336 | onDelete: "set null", |
| 337 | }), |
| 338 | quantity: integer("quantity").default(1).notNull(), |
| 339 | amountCents: integer("amount_cents").default(0).notNull(), |
| 340 | status: orderStatus("status").default("reserved").notNull(), |
| 341 | meta: jsonb("meta").$type<Record<string, unknown>>(), |
| 342 | ...timestamps, |
| 343 | }); |
| 344 | |
| 345 | export const tickets = pgTable("tickets", { |
| 346 | id: uuid("id").primaryKey().defaultRandom(), |
| 347 | orderId: uuid("order_id") |
| 348 | .references(() => orders.id, { onDelete: "cascade" }) |
| 349 | .notNull(), |
| 350 | eventId: uuid("event_id") |
| 351 | .references(() => events.id, { onDelete: "cascade" }) |
| 352 | .notNull(), |
| 353 | ticketTypeId: uuid("ticket_type_id").references(() => ticketTypes.id, { |
| 354 | onDelete: "set null", |
| 355 | }), |
| 356 | holderId: uuid("holder_id").references(() => users.id, { onDelete: "set null" }), |
| 357 | code: text("code").unique().notNull(), |
| 358 | status: ticketStatus("status").default("valid").notNull(), |
| 359 | ...timestamps, |
| 360 | }); |
| 361 | |
| 362 | export const sponsors = pgTable("sponsors", { |
| 363 | id: uuid("id").primaryKey().defaultRandom(), |
| 364 | cityId: uuid("city_id") |
| 365 | .references(() => cities.id, { onDelete: "cascade" }) |
| 366 | .notNull(), |
| 367 | name: text("name").notNull(), |
| 368 | blurb: text("blurb"), |
| 369 | linkUrl: text("link_url"), |
| 370 | imageUrl: text("image_url"), |
| 371 | status: sponsorStatus("status").default("pending").notNull(), |
| 372 | startsAt: timestamp("starts_at", { withTimezone: true }), |
| 373 | endsAt: timestamp("ends_at", { withTimezone: true }), |
| 374 | ...ownership, |
| 375 | ...timestamps, |
| 376 | }); |
| 377 | |
| 378 | export const proAccounts = pgTable("pro_accounts", { |
| 379 | id: uuid("id").primaryKey().defaultRandom(), |
| 380 | userId: uuid("user_id") |
| 381 | .references(() => users.id, { onDelete: "cascade" }) |
| 382 | .notNull(), |
| 383 | tier: text("tier").default("pro").notNull(), |
| 384 | status: proStatus("status").default("pending").notNull(), |
| 385 | startedAt: timestamp("started_at", { withTimezone: true }), |
| 386 | expiresAt: timestamp("expires_at", { withTimezone: true }), |
| 387 | ...timestamps, |
| 388 | }); |
| 389 | |
| 390 | export const auditLog = pgTable("audit_log", { |
| 391 | id: uuid("id").primaryKey().defaultRandom(), |
| 392 | actorId: uuid("actor_id").references(() => users.id, { onDelete: "set null" }), |
| 393 | action: text("action").notNull(), |
| 394 | subjectType: text("subject_type"), |
| 395 | subjectId: uuid("subject_id"), |
| 396 | meta: jsonb("meta").$type<Record<string, unknown>>(), |
| 397 | createdAt: timestamps.createdAt, |
| 398 | }); |