Skip to content

Where agent teams gather. Cross-platform team chat where AI agents are first-class teammates — with cloud or bring-your-own (Claude Code / Codex) brains.

TypeScript
7.0 KB
Raw Blame
import { sql } from 'drizzle-orm'
import { pgTable, text, integer, timestamp, jsonb, boolean, index, uniqueIndex } from 'drizzle-orm/pg-core'
import type { AttachmentDescriptor } from '../../../shared/attachments.js'

export const conversations = pgTable('conversations', {
  id: text('id').primaryKey(),
  kind: text('kind').notNull(), // 'group' | 'direct'
  title: text('title').notNull(),
  subtitle: text('subtitle'),
  members: jsonb('members').$type<string[]>().notNull(),
  pinned: boolean('pinned').default(false).notNull(),
  tag: text('tag'),
  pulledBy: jsonb('pulled_by').$type<{ agentId: string; at: string; reason: string } | null>(),
  createdAt: timestamp('created_at').defaultNow().notNull(),
  updatedAt: timestamp('updated_at').defaultNow().notNull(),
})

export const messages = pgTable(
  'messages',
  {
    id: text('id').primaryKey(),
    conversationId: text('conversation_id').notNull().references(() => conversations.id, { onDelete: 'cascade' }),
    authorId: text('author_id').notNull(),
    kind: text('kind').notNull(), // 'text' | 'tool' | 'attachment' | 'thought' | 'system'
    body: text('body').notNull(),
    sequence: integer('sequence').notNull(),
    reactions: jsonb('reactions').$type<Array<{ emoji: string; count: number; mine?: boolean }> | null>(),
    tool: jsonb('tool').$type<Record<string, unknown> | null>(),
    /** Legacy first-attachment projection. New code reads `attachments`. */
    attachment: jsonb('attachment').$type<AttachmentDescriptor | null>(),
    attachments: jsonb('attachments').$type<AttachmentDescriptor[] | null>(),
    clientId: text('client_id'),
    // Durable one-shot delivery for departure notices after membership is
    // removed. Ordinary messages leave this null.
    deliveryRecipientId: text('delivery_recipient_id'),
    poll: jsonb('poll').$type<PollPayload | null>(),
    // Reply-to / quote target: id of another message in THIS conversation that
    // this message is quoting. Soft FK to messages.id with ON DELETE SET NULL
    // so deleting an original leaves orphan replies as "[deleted]" stubs
    // rather than cascading. Null for non-reply messages (the vast majority).
    quotedMessageId: text('quoted_message_id'),
    createdAt: timestamp('created_at', { withTimezone: true }).defaultNow().notNull(),
  },
  (table) => ({
    convoSeqIdx: index('idx_messages_convo_seq').on(table.conversationId, table.sequence),
    convoCreatedIdx: index('idx_messages_convo_created').on(table.conversationId, table.createdAt),
    quotedIdx: index('idx_messages_quoted').on(table.quotedMessageId),
    clientIdIdx: uniqueIndex('uniq_messages_client_id')
      .on(table.conversationId, table.authorId, table.clientId)
      .where(sql`${table.clientId} IS NOT NULL`),
    deliveryRecipientIdx: index('idx_messages_delivery_recipient')
      .on(table.deliveryRecipientId, table.createdAt, table.id)
      .where(sql`${table.deliveryRecipientId} IS NOT NULL`),
  }),
)

export const participants = pgTable('participants', {
  id: text('id').primaryKey(),
  kind: text('kind').notNull(), // 'agent' | 'human'
  name: text('name').notNull(),
  role: text('role'),
  initial: text('initial').notNull(),
  avatarBg: text('avatar_bg').notNull(),
  status: text('status').notNull(),
  bio: text('bio'),
  tools: jsonb('tools').$type<string[] | null>(),
  systemPrompt: text('system_prompt'),
  nativeFastMode: boolean('native_fast_mode').default(false).notNull(),
})

export const conversationCounters = pgTable(
  'conversation_counters',
  {
    conversationId: text('conversation_id').primaryKey().references(() => conversations.id, { onDelete: 'cascade' }),
    nextSequence: integer('next_sequence').default(1).notNull(),
  },
)

export const calendarEvents = pgTable(
  'calendar_events',
  {
    id: text('id').primaryKey(),
    companyId: text('company_id').notNull(),
    createdBy: text('created_by').notNull(),
    kind: text('kind').notNull(), // 'personal' | 'agent_task'
    title: text('title').notNull(),
    description: text('description'),
    assigneeId: text('assignee_id'),
    targetConversationId: text('target_conversation_id'),
    agentPrompt: text('agent_prompt'),
    startAt: timestamp('start_at', { withTimezone: true }).notNull(),
    endAt: timestamp('end_at', { withTimezone: true }),
    allDay: boolean('all_day').default(false).notNull(),
    recurrence: jsonb('recurrence').$type<RecurrenceRule | null>(),
    status: text('status').notNull(), // 'active' | 'paused' | 'done' | 'cancelled'
    lastFiredAt: timestamp('last_fired_at', { withTimezone: true }),
    reminderMinutesBefore: integer('reminder_minutes_before'),
    reminderChannel: text('reminder_channel'), // 'toast' | 'email' | 'both'
    createdAt: timestamp('created_at', { withTimezone: true }).defaultNow().notNull(),
    updatedAt: timestamp('updated_at', { withTimezone: true }).defaultNow().notNull(),
  },
  (table) => ({
    companyStartIdx: index('idx_calendar_events_company_start').on(table.companyId, table.startAt),
    assigneeIdx: index('idx_calendar_events_assignee').on(table.assigneeId, table.startAt),
  }),
)

export const calendarReminders = pgTable(
  'calendar_reminders',
  {
    id: text('id').primaryKey(),
    eventId: text('event_id').notNull().references(() => calendarEvents.id, { onDelete: 'cascade' }),
    companyId: text('company_id').notNull(),
    scheduledFor: timestamp('scheduled_for', { withTimezone: true }).notNull(),
    firedAt: timestamp('fired_at', { withTimezone: true }).defaultNow().notNull(),
    channel: text('channel').notNull(),
    recipients: jsonb('recipients').$type<string[]>().notNull(),
    status: text('status').notNull(),
    error: text('error'),
  },
)

export const calendarDispatches = pgTable(
  'calendar_dispatches',
  {
    id: text('id').primaryKey(),
    eventId: text('event_id').notNull().references(() => calendarEvents.id, { onDelete: 'cascade' }),
    companyId: text('company_id').notNull(),
    scheduledFor: timestamp('scheduled_for', { withTimezone: true }).notNull(),
    dispatchedAt: timestamp('dispatched_at', { withTimezone: true }).defaultNow().notNull(),
    status: text('status').notNull(),
    conversationId: text('conversation_id'),
    messageId: text('message_id'),
    error: text('error'),
  },
)

export interface PollOption {
  id: string
  text: string
}

export interface PollPayload {
  question: string
  mode: 'single' | 'multi'
  options: PollOption[]
  expiresAt: string | null   // iso, null = no expiration
  closedAt: string | null    // iso when manually/auto closed
  closedReason: 'expired' | 'manual' | null
}

export interface RecurrenceRule {
  freq: 'daily' | 'weekly' | 'monthly' | 'yearly'
  interval: number
  byweekday?: number[]
  until?: string | null
  count?: number | null
}

export type DbMessage = typeof messages.$inferSelect
export type DbConversation = typeof conversations.$inferSelect
export type DbParticipant = typeof participants.$inferSelect
export type DbCalendarEvent = typeof calendarEvents.$inferSelect
export type DbCalendarDispatch = typeof calendarDispatches.$inferSelect
export type DbCalendarReminder = typeof calendarReminders.$inferSelect