Skip to content

An AI-powered search engine with a generative UI

17.6 KB
Raw Blame
import { createId } from '@paralleldrive/cuid2'
import { InferSelectModel, sql } from 'drizzle-orm'
import {
  check,
  customType,
  index,
  integer,
  json,
  jsonb,
  pgPolicy,
  pgTable,
  text,
  timestamp,
  uniqueIndex,
  varchar
} from 'drizzle-orm/pg-core'

// Constants
const ID_LENGTH = 191
const USER_ID_LENGTH = 255
const VARCHAR_LENGTH = 256
const FILENAME_LENGTH = 1024

// Postgres tsvector type (no built-in drizzle type)
const tsvector = customType<{ data: string }>({
  dataType() {
    return 'tsvector'
  }
})

// ID generation function
export const generateId = () => createId()

// Chats table
export const chats = pgTable(
  'chats',
  {
    id: varchar('id', { length: ID_LENGTH })
      .primaryKey()
      .$defaultFn(() => generateId()),
    createdAt: timestamp('created_at').notNull().defaultNow(),
    title: text('title').notNull(),
    userId: varchar('user_id', { length: USER_ID_LENGTH }).notNull(),
    visibility: varchar('visibility', {
      length: VARCHAR_LENGTH,
      enum: ['public', 'private']
    })
      .notNull()
      .default('private')
  },
  table => [
    // Indexes
    index('chats_user_id_idx').on(table.userId),
    index('chats_user_id_created_at_idx').on(
      table.userId,
      table.createdAt.desc()
    ),
    index('chats_created_at_idx').on(table.createdAt.desc()),
    // Composite index for RLS subqueries in messages and parts tables
    index('chats_id_user_id_idx').on(table.id, table.userId),

    // RLS Policies
    pgPolicy('users_manage_own_chats', {
      as: 'permissive',
      for: 'all',
      to: 'public',
      using: sql`user_id = (select current_setting('app.current_user_id', true))`,
      withCheck: sql`user_id = (select current_setting('app.current_user_id', true))`
    }),
    pgPolicy('public_chats_readable', {
      as: 'permissive',
      for: 'select',
      to: 'public',
      using: sql`visibility = 'public'`
    })
  ]
).enableRLS()

export type Chat = InferSelectModel<typeof chats>

// Messages table (simplified)
export const messages = pgTable(
  'messages',
  {
    id: varchar('id', { length: ID_LENGTH })
      .primaryKey()
      .$defaultFn(() => generateId()),
    chatId: varchar('chat_id', { length: ID_LENGTH })
      .notNull()
      .references(() => chats.id, { onDelete: 'cascade' }),
    role: varchar('role', { length: VARCHAR_LENGTH }).notNull(),
    createdAt: timestamp('created_at').notNull().defaultNow(),
    updatedAt: timestamp('updated_at'),
    metadata: jsonb('metadata').$type<Record<string, any>>()
  },
  table => [
    index('messages_chat_id_idx').on(table.chatId),
    index('messages_chat_id_created_at_idx').on(table.chatId, table.createdAt),

    // RLS Policies - allow access to messages if user owns the chat
    pgPolicy('users_manage_chat_messages', {
      as: 'permissive',
      for: 'all',
      to: 'public',
      using: sql`EXISTS (
        SELECT 1 FROM ${chats}
        WHERE ${chats}.id = chat_id
        AND ${chats}.user_id = current_setting('app.current_user_id', true)
      )`,
      withCheck: sql`EXISTS (
        SELECT 1 FROM ${chats}
        WHERE ${chats}.id = chat_id
        AND ${chats}.user_id = current_setting('app.current_user_id', true)
      )`
    }),
    pgPolicy('public_chat_messages_readable', {
      as: 'permissive',
      for: 'select',
      to: 'public',
      using: sql`EXISTS (
        SELECT 1 FROM ${chats}
        WHERE ${chats}.id = chat_id
        AND ${chats}.visibility = 'public'
      )`
    })
  ]
).enableRLS()

export type Message = InferSelectModel<typeof messages>

// Parts table
export const parts = pgTable(
  'parts',
  {
    id: varchar('id', { length: ID_LENGTH })
      .primaryKey()
      .$defaultFn(() => generateId()),
    messageId: varchar('message_id', { length: ID_LENGTH })
      .notNull()
      .references(() => messages.id, { onDelete: 'cascade' }),
    order: integer('order').notNull(),
    type: varchar('type', { length: VARCHAR_LENGTH }).notNull(),

    // Text parts
    text_text: text('text_text'),

    // Reasoning parts
    reasoning_text: text('reasoning_text'),

    // File parts
    file_mediaType: varchar('file_media_type', { length: VARCHAR_LENGTH }),
    file_filename: varchar('file_filename', { length: FILENAME_LENGTH }),
    file_url: text('file_url'),
    file_key: text('file_key'),

    // Source URL parts
    source_url_sourceId: varchar('source_url_source_id', {
      length: VARCHAR_LENGTH
    }),
    source_url_url: text('source_url_url'),
    source_url_title: text('source_url_title'),

    // Source document parts
    source_document_sourceId: varchar('source_document_source_id', {
      length: VARCHAR_LENGTH
    }),
    source_document_mediaType: varchar('source_document_media_type', {
      length: VARCHAR_LENGTH
    }),
    source_document_title: text('source_document_title'),
    source_document_filename: varchar('source_document_filename', {
      length: FILENAME_LENGTH
    }),
    source_document_url: text('source_document_url'),
    source_document_snippet: text('source_document_snippet'),

    // Tool parts (generic)
    tool_toolCallId: varchar('tool_tool_call_id', { length: VARCHAR_LENGTH }),
    tool_state: varchar('tool_state', { length: VARCHAR_LENGTH }),
    tool_errorText: text('tool_error_text'),

    // Tool-specific columns (all Morphic tools)
    tool_search_input: json('tool_search_input').$type<any>(),
    tool_search_output: json('tool_search_output').$type<any>(),
    tool_fetch_input: json('tool_fetch_input').$type<any>(),
    tool_fetch_output: json('tool_fetch_output').$type<any>(),
    tool_question_input: json('tool_question_input').$type<any>(),
    tool_question_output: json('tool_question_output').$type<any>(),

    // Todo tool columns
    tool_todoWrite_input: json('tool_todoWrite_input').$type<any>(),
    tool_todoWrite_output: json('tool_todoWrite_output').$type<any>(),
    tool_todoRead_input: json('tool_todoRead_input').$type<any>(),
    tool_todoRead_output: json('tool_todoRead_output').$type<any>(),

    // Dynamic tools (includes MCP and other runtime-defined tools)
    tool_dynamic_input: json('tool_dynamic_input').$type<any>(),
    tool_dynamic_output: json('tool_dynamic_output').$type<any>(),
    tool_dynamic_name: varchar('tool_dynamic_name', { length: VARCHAR_LENGTH }),
    tool_dynamic_type: varchar('tool_dynamic_type', { length: VARCHAR_LENGTH }),

    // Data parts (generic support)
    data_prefix: varchar('data_prefix', { length: VARCHAR_LENGTH }),
    data_content: json('data_content').$type<any>(),
    data_id: varchar('data_id', { length: VARCHAR_LENGTH }),

    // Provider metadata
    providerMetadata: json('provider_metadata').$type<Record<string, any>>(),

    // Full-text search columns (maintained by Postgres, used by history
    // search). Input is capped at 100k characters: Postgres errors on any
    // tsvector over 1MB ("string is too long for tsvector"), which would
    // otherwise abort migration 0020 on existing rows and fail inserts of
    // oversized parts. Worst-case vector size at this cap is ~220KB.
    search: tsvector('search').generatedAlwaysAs(
      sql`to_tsvector('simple', left(coalesce(text_text, ''), 100000))`
    ),
    searchReasoning: tsvector('search_reasoning').generatedAlwaysAs(
      sql`to_tsvector('simple', left(coalesce(reasoning_text, ''), 100000))`
    ),

    createdAt: timestamp('created_at').notNull().defaultNow()
  },
  table => [
    // Indexes
    index('parts_message_id_idx').on(table.messageId),
    index('parts_message_id_order_idx').on(table.messageId, table.order),
    index('parts_search_idx').using('gin', table.search),
    index('parts_search_reasoning_idx').using('gin', table.searchReasoning),

    // Constraints
    check('text_text_required', sql`(type != 'text' OR text_text IS NOT NULL)`),
    check(
      'reasoning_text_required',
      sql`(type != 'reasoning' OR reasoning_text IS NOT NULL)`
    ),
    check(
      'file_fields_required',
      sql`(type != 'file' OR (file_media_type IS NOT NULL AND file_filename IS NOT NULL AND (file_key IS NOT NULL OR file_url IS NOT NULL)))`
    ),
    check(
      'tool_state_valid',
      sql`(tool_state IS NULL OR tool_state IN ('input-streaming', 'input-available', 'output-available', 'output-error'))`
    ),
    check(
      'tool_fields_required',
      sql`(type NOT LIKE 'tool-%' OR (tool_tool_call_id IS NOT NULL AND tool_state IS NOT NULL))`
    ),

    // RLS Policies - allow access to parts if user owns the related chat
    pgPolicy('users_manage_message_parts', {
      as: 'permissive',
      for: 'all',
      to: 'public',
      using: sql`EXISTS (
        SELECT 1 FROM ${messages}
        INNER JOIN ${chats} ON ${chats}.id = ${messages}.chat_id
        WHERE ${messages}.id = message_id
        AND ${chats}.user_id = current_setting('app.current_user_id', true)
      )`,
      withCheck: sql`EXISTS (
        SELECT 1 FROM ${messages}
        INNER JOIN ${chats} ON ${chats}.id = ${messages}.chat_id
        WHERE ${messages}.id = message_id
        AND ${chats}.user_id = current_setting('app.current_user_id', true)
      )`
    }),
    pgPolicy('public_chat_parts_readable', {
      as: 'permissive',
      for: 'select',
      to: 'public',
      using: sql`EXISTS (
        SELECT 1 FROM ${messages}
        INNER JOIN ${chats} ON ${chats}.id = ${messages}.chat_id
        WHERE ${messages}.id = message_id
        AND ${chats}.visibility = 'public'
      )`
    })
  ]
).enableRLS()

export type Part = InferSelectModel<typeof parts>
export type NewPart = typeof parts.$inferInsert

// Notes table
export const notes = pgTable(
  'notes',
  {
    id: varchar('id', { length: ID_LENGTH })
      .primaryKey()
      .$defaultFn(() => generateId()),
    userId: varchar('user_id', { length: USER_ID_LENGTH }).notNull(),
    chatId: varchar('chat_id', { length: ID_LENGTH }).references(
      () => chats.id,
      { onDelete: 'set null' }
    ),
    sourceMessageId: varchar('source_message_id', {
      length: ID_LENGTH
    }),
    title: text('title').notNull(),
    content: text('content').notNull(),
    createdAt: timestamp('created_at').notNull().defaultNow(),
    updatedAt: timestamp('updated_at').notNull().defaultNow()
  },
  table => [
    index('notes_user_id_idx').on(table.userId),
    index('notes_user_id_updated_at_idx').on(
      table.userId,
      table.updatedAt.desc()
    ),
    index('notes_chat_id_idx').on(table.chatId),
    index('notes_source_message_id_idx').on(table.sourceMessageId),

    pgPolicy('users_manage_own_notes', {
      as: 'permissive',
      for: 'all',
      to: 'public',
      using: sql`user_id = (select current_setting('app.current_user_id', true))`,
      withCheck: sql`user_id = (select current_setting('app.current_user_id', true))`
    })
  ]
).enableRLS()

export type Note = InferSelectModel<typeof notes>
export type NewNote = typeof notes.$inferInsert

// Library files table
export const libraryFiles = pgTable(
  'files',
  {
    id: varchar('id', { length: ID_LENGTH })
      .primaryKey()
      .$defaultFn(() => generateId()),
    userId: varchar('user_id', { length: USER_ID_LENGTH }).notNull(),
    chatId: varchar('chat_id', { length: ID_LENGTH }).references(
      () => chats.id,
      { onDelete: 'set null' }
    ),
    filename: text('filename').notNull(),
    objectKey: text('object_key').notNull(),
    mediaType: varchar('media_type', { length: VARCHAR_LENGTH }).notNull(),
    size: integer('size'),
    createdAt: timestamp('created_at').notNull().defaultNow(),
    updatedAt: timestamp('updated_at').notNull().defaultNow()
  },
  table => [
    index('files_user_id_idx').on(table.userId),
    index('files_user_id_updated_at_idx').on(
      table.userId,
      table.updatedAt.desc()
    ),
    index('files_chat_id_idx').on(table.chatId),
    index('files_media_type_idx').on(table.mediaType),
    index('files_object_key_idx').on(table.objectKey),

    pgPolicy('users_manage_own_files', {
      as: 'permissive',
      for: 'all',
      to: 'public',
      using: sql`user_id = (select current_setting('app.current_user_id', true))`,
      withCheck: sql`user_id = (select current_setting('app.current_user_id', true))`
    })
  ]
).enableRLS()

export type LibraryFile = InferSelectModel<typeof libraryFiles>
export type NewLibraryFile = typeof libraryFiles.$inferInsert

// Feedback table
export const feedback = pgTable(
  'feedback',
  {
    id: varchar('id', { length: ID_LENGTH })
      .primaryKey()
      .$defaultFn(() => generateId()),
    userId: varchar('user_id', { length: USER_ID_LENGTH }),
    sentiment: varchar('sentiment', {
      length: VARCHAR_LENGTH,
      enum: ['positive', 'neutral', 'negative']
    }).notNull(),
    message: text('message').notNull(),
    pageUrl: text('page_url').notNull(),
    userAgent: text('user_agent'),
    createdAt: timestamp('created_at').notNull().defaultNow()
  },
  table => [
    // Indexes
    index('feedback_user_id_idx').on(table.userId),
    index('feedback_created_at_idx').on(table.createdAt),

    // RLS Policies - Allow reads (for INSERT ... RETURNING and app visibility)
    pgPolicy('feedback_select_policy', {
      as: 'permissive',
      for: 'select',
      to: 'public',
      using: sql`true`
    }),

    // RLS Policy - Allow anyone to insert feedback
    pgPolicy('anyone_can_insert_feedback', {
      for: 'insert',
      to: 'public',
      withCheck: sql`true`
    }),

    // Allow users to remove the account link from their own feedback.
    pgPolicy('users_anonymize_own_feedback', {
      as: 'permissive',
      for: 'update',
      to: 'public',
      using: sql`user_id = (select current_setting('app.current_user_id', true))`,
      withCheck: sql`user_id IS NULL`
    })
  ]
).enableRLS()

export type Feedback = InferSelectModel<typeof feedback>

// Usage grants are the durable authority for allowances. Redis remains the
// runtime authority for spend and remaining usage.
export const usageGrants = pgTable(
  'usage_grants',
  {
    id: varchar('id', { length: ID_LENGTH })
      .primaryKey()
      .$defaultFn(() => generateId()),
    userId: varchar('user_id', { length: USER_ID_LENGTH }).notNull(),
    idempotencyKey: varchar('idempotency_key', {
      length: VARCHAR_LENGTH
    }).notNull(),
    kind: varchar('kind', {
      length: VARCHAR_LENGTH,
      enum: ['period', 'adjustment']
    }).notNull(),
    amount: integer('amount').notNull(),
    grantedAt: timestamp('granted_at').notNull().defaultNow(),
    expiresAt: timestamp('expires_at').notNull(),
    createdAt: timestamp('created_at').notNull().defaultNow()
  },
  table => [
    uniqueIndex('usage_grants_user_idempotency_idx').on(
      table.userId,
      table.idempotencyKey
    ),
    index('usage_grants_user_expires_idx').on(table.userId, table.expiresAt),
    pgPolicy('users_read_own_usage_grants', {
      as: 'permissive',
      for: 'select',
      to: 'public',
      using: sql`user_id = (select current_setting('app.current_user_id', true))`
    }),
    pgPolicy('users_insert_own_usage_grants', {
      as: 'permissive',
      for: 'insert',
      to: 'public',
      withCheck: sql`user_id = (select current_setting('app.current_user_id', true))`
    })
  ]
).enableRLS()

export type UsageGrant = InferSelectModel<typeof usageGrants>

// Usage events are best-effort analytics. They must never be used to rebuild
// balances or decide whether a request is allowed.
export const usageEvents = pgTable(
  'usage_events',
  {
    id: varchar('id', { length: ID_LENGTH })
      .primaryKey()
      .$defaultFn(() => generateId()),
    userId: varchar('user_id', { length: USER_ID_LENGTH }).notNull(),
    eventType: varchar('event_type', {
      length: VARCHAR_LENGTH,
      enum: ['spend', 'refund', 'limit_reached']
    }).notNull(),
    amount: integer('amount').notNull(),
    mode: varchar('mode', {
      length: VARCHAR_LENGTH,
      enum: ['quick', 'adaptive']
    }),
    attemptId: varchar('attempt_id', { length: ID_LENGTH }).notNull(),
    messageId: varchar('message_id', { length: ID_LENGTH }),
    remaining: integer('remaining'),
    createdAt: timestamp('created_at').notNull().defaultNow()
  },
  table => [
    uniqueIndex('usage_events_user_type_attempt_idx').on(
      table.userId,
      table.eventType,
      table.attemptId
    ),
    index('usage_events_user_created_idx').on(table.userId, table.createdAt),
    pgPolicy('users_read_own_usage_events', {
      as: 'permissive',
      for: 'select',
      to: 'public',
      using: sql`user_id = (select current_setting('app.current_user_id', true))`
    }),
    pgPolicy('users_insert_own_usage_events', {
      as: 'permissive',
      for: 'insert',
      to: 'public',
      withCheck: sql`user_id = (select current_setting('app.current_user_id', true))`
    })
  ]
).enableRLS()

export type UsageEvent = InferSelectModel<typeof usageEvents>

export const additionalUsageInterest = pgTable(
  'additional_usage_interest',
  {
    userId: varchar('user_id', { length: USER_ID_LENGTH }).primaryKey(),
    count: integer('count').notNull().default(1),
    firstClickedAt: timestamp('first_clicked_at').notNull().defaultNow(),
    lastClickedAt: timestamp('last_clicked_at').notNull().defaultNow()
  },
  () => [
    pgPolicy('users_write_own_additional_usage_interest', {
      as: 'permissive',
      for: 'all',
      to: 'public',
      using: sql`user_id = (select current_setting('app.current_user_id', true))`,
      withCheck: sql`user_id = (select current_setting('app.current_user_id', true))`
    })
  ]
).enableRLS()

export type AdditionalUsageInterest = InferSelectModel<
  typeof additionalUsageInterest
>

// Better Auth tables (user, session, account, verification, invitations)
// are defined in lib/auth/better-auth/schema.ts and re-exported here so the
// canonical schema module — and drizzle-kit generation anchored on it —
// covers every table in the database.
export * from '../auth/better-auth/schema'