Schema Templates

Drizzle ORM table definitions for broker landing pages, IP tracking columns, and user role enums. Copy these directly into your drizzle/schema.ts file.

Broker Landing Pages Table

This table stores all broker-specific landing page content, branding, and analytics counters. Each broker can have one landing page identified by a unique URL slug.

drizzle/schema.ts — Broker Landing Pages

export const brokerLandingPages = mysqlTable("broker_landing_pages", {
  id: int("id").autoincrement().primaryKey(),
  brokerId: int("brokerId").notNull(),
  slug: varchar("slug", { length: 128 }).notNull().unique(),
  displayName: varchar("displayName", { length: 255 }),
  displayEmail: varchar("displayEmail", { length: 320 }),
  displayPhone: varchar("displayPhone", { length: 64 }),
  companyName: varchar("companyName", { length: 255 }),
  headline: text("headline"),
  subheadline: text("subheadline"),
  bio: text("bio"),
  profileImageUrl: text("profileImageUrl"),
  brandColor: varchar("brandColor", { length: 7 }).default("#1e3a5f"),
  services: json("services"),        // JSON array of {title, description}
  testimonials: json("testimonials"), // JSON array of {name, text, rating}
  isActive: boolean("isActive").default(true).notNull(),
  viewCount: int("viewCount").default(0).notNull(),
  applicationCount: int("applicationCount").default(0).notNull(),
  createdAt: timestamp("createdAt").defaultNow().notNull(),
  updatedAt: timestamp("updatedAt").defaultNow().onUpdateNow().notNull(),
});

export type BrokerLandingPage = typeof brokerLandingPages.$inferSelect;
export type InsertBrokerLandingPage = typeof brokerLandingPages.$inferInsert;

IP Tracking Columns

Add these columns to any task or action table to record the IP address of the user performing each action. The VARCHAR(45) length accommodates both IPv4 and IPv6 addresses.

drizzle/schema.ts — IP Tracking Columns

// Add these columns to any task/action table
createdByIp: varchar("created_by_ip", { length: 45 }),
lastUpdatedByIp: varchar("last_updated_by_ip", { length: 45 }),
completedByIp: varchar("completed_by_ip", { length: 45 }),

If the table already exists in production, use a direct SQL migration:

SQL — Add IP Columns to Existing Table

ALTER TABLE application_tasks
  ADD COLUMN created_by_ip VARCHAR(45) DEFAULT NULL,
  ADD COLUMN last_updated_by_ip VARCHAR(45) DEFAULT NULL,
  ADD COLUMN completed_by_ip VARCHAR(45) DEFAULT NULL;

User Roles Enum

drizzle/schema.ts — Role Enum

role: mysqlEnum("role", ["user", "admin", "broker", "applicant"])
  .default("broker").notNull(),

Role	Access
admin	Full: all users, apps, landing pages, audit, settings
broker	Own clients, applications, magic links, own landing page
applicant	Own application only