drizzle-patterns

Drizzle ORM Patterns

Schema Definition

import {
  pgTable,
  uuid,
  text,
  timestamp,
  boolean,
  jsonb,
  integer,
} from 'drizzle-orm/pg-core';
import { relations } from 'drizzle-orm';

export const teams = pgTable('teams', {
  id: uuid('id').primaryKey().defaultRandom(),
  name: text('name').notNull(),
  createdAt: timestamp('created_at').defaultNow().notNull(),
  updatedAt: timestamp('updated_at').defaultNow().notNull(),
});

export const users = pgTable('users', {
  id: uuid('id').primaryKey().defaultRandom(),
  email: text('email'),
  isAnonymous: boolean('is_anonymous').default(true).notNull(),
  teamId: uuid('team_id').references(() => teams.id),
  createdAt: timestamp('created_at').defaultNow().notNull(),
});

export const sequences = pgTable('sequences', {
  id: uuid('id').primaryKey().defaultRandom(),
  name: text('name').notNull(),
  script: text('script'),
  teamId: uuid('team_id')
    .references(() => teams.id)
    .notNull(),
  createdBy: uuid('created_by')
    .references(() => users.id)
    .notNull(),
  styleId: uuid('style_id').references(() => styles.id),
  metadata: jsonb('metadata'),
  createdAt: timestamp('created_at').defaultNow().notNull(),
  updatedAt: timestamp('updated_at').defaultNow().notNull(),
});

export const frames = pgTable('frames', {
  id: uuid('id').primaryKey().defaultRandom(),
  sequenceId: uuid('sequence_id')
    .references(() => sequences.id, { onDelete: 'cascade' })
    .notNull(),
  order: integer('order').notNull(),
  description: text('description').notNull(),
  thumbnailUrl: text('thumbnail_url'),
  status: text('status').notNull().default('pending'), // pending, processing, completed, failed
  error: text('error'),
  createdBy: uuid('created_by')
    .references(() => users.id)
    .notNull(),
  createdAt: timestamp('created_at').defaultNow().notNull(),
  updatedAt: timestamp('updated_at').defaultNow().notNull(),
});

export const styles = pgTable('styles', {
  id: uuid('id').primaryKey().defaultRandom(),
  name: text('name').notNull(),
  description: text('description'),
  teamId: uuid('team_id')
    .references(() => teams.id)
    .notNull(),
  styleData: jsonb('style_data').notNull(), // Style Stack JSON
  createdBy: uuid('created_by')
    .references(() => users.id)
    .notNull(),
  createdAt: timestamp('created_at').defaultNow().notNull(),
  updatedAt: timestamp('updated_at').defaultNow().notNull(),
});

// Relations
export const teamsRelations = relations(teams, ({ many }) => ({
  users: many(users),
  sequences: many(sequences),
  styles: many(styles),
}));

export const usersRelations = relations(users, ({ one, many }) => ({
  team: one(teams, {
    fields: [users.teamId],
    references: [teams.id],
  }),
  sequences: many(sequences),
  frames: many(frames),
  styles: many(styles),
}));

export const sequencesRelations = relations(sequences, ({ one, many }) => ({
  team: one(teams, {
    fields: [sequences.teamId],
    references: [teams.id],
  }),
  frames: many(frames),
  style: one(styles, {
    fields: [sequences.styleId],
    references: [styles.id],
  }),
  creator: one(users, {
    fields: [sequences.createdBy],
    references: [users.id],
  }),
}));

export const framesRelations = relations(frames, ({ one }) => ({
  sequence: one(sequences, {
    fields: [frames.sequenceId],
    references: [sequences.id],
  }),
  creator: one(users, {
    fields: [frames.createdBy],
    references: [users.id],
  }),
}));

export const stylesRelations = relations(styles, ({ one, many }) => ({
  team: one(teams, {
    fields: [styles.teamId],
    references: [teams.id],
  }),
  creator: one(users, {
    fields: [styles.createdBy],
    references: [users.id],
  }),
  sequences: many(sequences),
}));