Skip to content

⭐️⭐️⭐️ Open-source, self-hosted alternative to Linear and Plane. Project management and issue tracking where teams and AI agents work side by side to plan and ship products.

120 KB
Raw Blame
// Task planner tables: projects, workflow columns, issue types, labels, custom
// fields, issues, and their dependent rows. Exposed to the web app over HTTP by
// the API.
import { sql } from 'drizzle-orm';
import {
  bigint,
  bigserial,
  boolean,
  check,
  type AnyPgColumn,
  date,
  doublePrecision,
  index,
  integer,
  jsonb,
  numeric,
  pgTable,
  primaryKey,
  serial,
  text,
  timestamp,
  unique,
  uniqueIndex,
  uuid,
} from 'drizzle-orm/pg-core';
import { user } from './auth';

// Global key-value settings for the instance, not scoped to a project. The value is
// a jsonb blob owned by whatever feature reads the key, so one table backs many
// settings.
export const appSetting = pgTable('app_setting', {
  key: text('key').primaryKey(),
  value: jsonb('value').notNull(),
  updatedAt: timestamp('updated_at', { withTimezone: true }).notNull().defaultNow(),
});

// Instance-wide secrets, the encrypted counterpart of app_setting. One row per key
// (e.g. 'auth.email' for the instance mail provider); the value is a JSON blob
// encrypted as a whole, so a key can hold several credentials. `redacted` mirrors the
// same blob with every secret replaced by a boolean, for the settings UI to read
// without decrypting. Encryption is AES-256-GCM with APP_ENCRYPTION_KEY — changing
// that env value makes stored rows undecryptable.
export const appSecret = pgTable('app_secret', {
  key: text('key').primaryKey(),
  ciphertext: text('ciphertext').notNull(),
  iv: text('iv').notNull(),
  authTag: text('auth_tag').notNull(),
  redacted: jsonb('redacted').notNull(),
  updatedAt: timestamp('updated_at', { withTimezone: true }).notNull().defaultNow(),
});

// A workspace owns teams; its members are derived from them. Every person, the instance
// owner included, gets one of their own at sign-up, and a team is always created in one.
export const workspace = pgTable(
  'workspace',
  {
    id: serial('id').primaryKey(),
    name: text('name').notNull(),
    // The tile colour in the workspace rails, as #rrggbb; null keeps the neutral tile.
    color: text('color'),
    // Who creates teams in it: the owner alone, the owner and admins (managers), or
    // anyone in one of its teams (members).
    teamCreation: text('team_creation').notNull().default('owner'),
    // Runs of its internal agents in flight at once; 0 sets no limit of its own.
    maxConcurrentRuns: integer('max_concurrent_runs').notNull().default(0),
    // Whether its projects may have public links. Its owner and admins set it.
    sharingEnabled: boolean('sharing_enabled').notNull().default(true),
    // Whether only the teams with team.sharing_selected may. Its owner sets it.
    sharingTeamsOnly: boolean('sharing_teams_only').notNull().default(false),
    createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
  },
  (t) => [
    check(
      'workspace_team_creation_check',
      sql`${t.teamCreation} IN ('owner', 'managers', 'members')`,
    ),
    check('workspace_max_concurrent_runs_check', sql`${t.maxConcurrentRuns} >= 0`),
  ],
);

// The people who administer a workspace. Nobody else is listed here: membership comes
// from team_member.
export const workspaceManager = pgTable(
  'workspace_manager',
  {
    workspaceId: integer('workspace_id')
      .notNull()
      .references(() => workspace.id, { onDelete: 'cascade' }),
    userId: text('user_id')
      .notNull()
      .references(() => user.id, { onDelete: 'cascade' }),
    role: text('role').notNull().default('admin'),
    createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
  },
  (t) => [
    primaryKey({ columns: [t.workspaceId, t.userId] }),
    check('workspace_manager_role_check', sql`${t.role} IN ('owner', 'admin')`),
    uniqueIndex('workspace_manager_owner_uq')
      .on(t.workspaceId)
      .where(sql`${t.role} = 'owner'`),
    index('workspace_manager_user_idx').on(t.userId),
  ],
);

// A team owns projects and holds its own member list. Every project belongs to exactly
// one team.
export const team = pgTable(
  'team',
  {
    id: serial('id').primaryKey(),
    workspaceId: integer('workspace_id')
      .notNull()
      .references(() => workspace.id, { onDelete: 'cascade' }),
    name: text('name').notNull(),
    // The team's segment in web URLs (/acme/MKT). Null until an owner sets one; the
    // team id stands in for it until then.
    slug: text('slug').unique(),
    // Whether the team is reachable through the MCP server at all. Off closes both the
    // team's own resources (agents, skills, tools, roles, integrations) and every
    // project it owns, whatever each project's own flag says.
    mcpEnabled: boolean('mcp_enabled').notNull().default(true),
    // Whether the workspace owner chose this team for sharing. Read only while
    // workspace.sharing_teams_only is on.
    sharingSelected: boolean('sharing_selected').notNull().default(false),
    defaultAgentIds: jsonb('default_agent_ids').$type<number[]>().notNull().default([]),
    createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
  },
  (t) => [index('team_workspace_idx').on(t.workspaceId)],
);

// Team membership and the role it carries. The roles are fixed, unlike the
// per-project ones: 'owner' is the account the team was created for, 'manager' and
// 'member' are the ranks below it, and 'agent' is the bot user of an ai_agent — it
// belongs to the team and shows up in its member list, but never manages it, so the
// owner and manager guards stay closed to it. What an agent may do comes from
// ai_agent.role_id, not from this column.
export const teamMember = pgTable(
  'team_member',
  {
    teamId: integer('team_id')
      .notNull()
      .references(() => team.id, { onDelete: 'cascade' }),
    userId: text('user_id')
      .notNull()
      .references(() => user.id, { onDelete: 'cascade' }),
    role: text('role').notNull().default('member'),
    // How this membership came about, read the same way as project_member.source:
    // 'scim' is a row the SCIM group reconciliation created and therefore owns, so
    // deprovisioning removes it again; everything else is 'invite' and a sync never
    // touches it.
    source: text('source').notNull().default('invite'),
    createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
  },
  (t) => [
    primaryKey({ columns: [t.teamId, t.userId] }),
    check('team_member_role_check', sql`${t.role} IN ('owner', 'manager', 'member', 'agent')`),
    check('team_member_source_check', sql`${t.source} IN ('invite', 'scim')`),
    index('team_member_user_idx').on(t.userId),
  ],
);

// A project groups its own columns, issue types, labels, custom fields, and
// issues. next_sequence is the atomic counter behind each issue's human
// identifier (e.g. "MKT-42"): incrementing it under a row lock keeps concurrent
// creates from colliding.
export const project = pgTable(
  'project',
  {
    id: serial('id').primaryKey(),
    teamId: integer('team_id')
      .notNull()
      .references(() => team.id, { onDelete: 'cascade' }),
    key: text('key').notNull(),
    name: text('name').notNull(),
    description: text('description').notNull().default(''),
    nextSequence: integer('next_sequence').notNull().default(1),
    // Whether this project is in the team's MCP reach. Managed from the team's MCP
    // settings, not from the project, and only counts while team.mcp_enabled is on.
    // The starting value is the instance-wide project default set in god mode.
    mcpEnabled: boolean('mcp_enabled').notNull().default(false),
    // Optional sections of the app, toggled per project in Settings -> Features. All
    // on by default. Turning one off only hides its UI; the rows it owns stay and
    // come back with it.
    initiativesEnabled: boolean('initiatives_enabled').notNull().default(true),
    dashboardsEnabled: boolean('dashboards_enabled').notNull().default(true),
    documentsEnabled: boolean('documents_enabled').notNull().default(true),
    notesEnabled: boolean('notes_enabled').notNull().default(true),
    cyclesEnabled: boolean('cycles_enabled').notNull().default(true),
    subtasksEnabled: boolean('subtasks_enabled').notNull().default(true),
    checklistsEnabled: boolean('checklists_enabled').notNull().default(true),
    issueStatsEnabled: boolean('issue_stats_enabled').notNull().default(true),
    // Which kinds of estimate the issues of this project carry, set in Settings ->
    // Configuration. Both off by default; turning one off hides its UI and keeps the
    // values, which show again when it is turned back on.
    pointsEstimateEnabled: boolean('points_estimate_enabled').notNull().default(false),
    timeEstimateEnabled: boolean('time_estimate_enabled').notNull().default(false),
    // Whether members log the time they spend on the issues of this project, set in
    // the same place. Independent of the time estimate: a team can log time without
    // estimating first. Turning it off hides the entries and keeps them.
    timeLoggingEnabled: boolean('time_logging_enabled').notNull().default(false),
    // When set, the project is archived: read-only, left out of the members' project
    // lists, and its agent schedules do not run. Its rows are kept and it can be restored.
    archivedAt: timestamp('archived_at', { withTimezone: true }),
    createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
  },
  (t) => [unique('project_team_key_uq').on(t.teamId, t.key)],
);

// A project template of a workspace: a snapshot of a project's configuration, without
// its issues, that new projects of its teams are created from. The snapshot's shape is
// ProjectSnapshot in the api (modules/projects/copy.ts). With all_teams off, only the
// teams in project_template_team see it. The flag is explicit so that a deleted team
// does not open a limited template to the whole workspace.
export const projectTemplate = pgTable(
  'project_template',
  {
    id: serial('id').primaryKey(),
    workspaceId: integer('workspace_id')
      .notNull()
      .references(() => workspace.id, { onDelete: 'cascade' }),
    name: text('name').notNull(),
    description: text('description').notNull().default(''),
    snapshot: jsonb('snapshot').notNull(),
    allTeams: boolean('all_teams').notNull().default(true),
    // The template's page in the gallery at itsaplan.dev, NULL while it is not published.
    publicationId: text('publication_id'),
    publicationUrl: text('publication_url'),
    publishedAt: timestamp('published_at', { withTimezone: true }),
    createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
  },
  (t) => [index('project_template_workspace_idx').on(t.workspaceId)],
);

// The teams a project template is limited to when its all_teams is off.
export const projectTemplateTeam = pgTable(
  'project_template_team',
  {
    templateId: integer('template_id')
      .notNull()
      .references(() => projectTemplate.id, { onDelete: 'cascade' }),
    teamId: integer('team_id')
      .notNull()
      .references(() => team.id, { onDelete: 'cascade' }),
  },
  (t) => [
    primaryKey({ columns: [t.templateId, t.teamId] }),
    index('project_template_team_team_idx').on(t.teamId),
  ],
);

// The gallery publisher a workspace publishes its project templates under: the email it
// confirmed with a code, and the key the gallery issued for it. The key is read back for
// every publish, so it is kept encrypted rather than hashed. The author is the name the
// gallery shows on every template of the address; the workspace keeps a copy to show in
// its dialogs and refuses a publish until it is set.
export const templatePublisher = pgTable('template_publisher', {
  workspaceId: integer('workspace_id')
    .primaryKey()
    .references(() => workspace.id, { onDelete: 'cascade' }),
  email: text('email').notNull(),
  keyCiphertext: text('key_ciphertext').notNull(),
  keyIv: text('key_iv').notNull(),
  keyAuthTag: text('key_auth_tag').notNull(),
  authorName: text('author_name').notNull().default(''),
  authorUrl: text('author_url'),
  createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
});

// Per-project key-value settings, mirroring app_setting but scoped to a project.
// The value is a jsonb blob owned by whatever feature reads the key, so one table
// backs many project settings (e.g. auto-archive thresholds under key
// 'auto_archive'). Composite PK (project_id, key).
export const projectSetting = pgTable(
  'project_setting',
  {
    projectId: integer('project_id')
      .notNull()
      .references(() => project.id, { onDelete: 'cascade' }),
    key: text('key').notNull(),
    value: jsonb('value').notNull(),
    updatedAt: timestamp('updated_at', { withTimezone: true }).notNull().defaultNow(),
  },
  (t) => [primaryKey({ columns: [t.projectId, t.key] })],
);

// A user's own interface preferences, held per account rather than per project so
// the same choices apply on every device. timezone is an IANA zone name used by the
// web app to render stored UTC timestamps; the API keeps storing and returning UTC.
// locale is the interface language, also used for the emails and the Telegram bot
// messages this user receives; it has no CHECK, unlike the columns below, so adding a
// language costs no migration.
// theme is 'light' | 'dark' | 'system', issue_open_mode is 'panel' | 'page' (how a
// clicked issue opens), start_page is the section the app root lands on. Absent row
// means the user has not changed anything and the defaults below apply.
// last_project_id is the project the user was in last, so the app root reopens it
// after signing in on any device; the FK clears it when that project is deleted.
// show_chat_by_default keeps the floating AI chat button on screen from the start,
// with the chat window collapsed. hotkeys holds the keyboard shortcuts this user
// rebound. issue_stats_open and issue_stats_view are how the status stats section of
// an issue starts out: expanded or collapsed, and 'compact' (one bar per status) or
// 'timeline' (a lane per status on a time axis). issue_activity_view is how the
// activity log below it starts out: 'flat' (every entry newest first) or 'grouped'
// (a block per stretch the issue spent in a status). Switching either on an issue is
// not stored — it lasts as long as that issue stays open. auto_watch is whether the
// user is subscribed to the issues they create, are assigned, comment on or are
// mentioned in (see issue_watcher); off means they only ever subscribe by hand.
export const userPreference = pgTable(
  'user_preference',
  {
    userId: text('user_id')
      .primaryKey()
      .references(() => user.id, { onDelete: 'cascade' }),
    timezone: text('timezone').notNull().default('UTC'),
    locale: text('locale').notNull().default('en'),
    theme: text('theme').notNull().default('system'),
    issueOpenMode: text('issue_open_mode').notNull().default('panel'),
    startPage: text('start_page').notNull().default('work-items'),
    showChatByDefault: boolean('show_chat_by_default').notNull().default(false),
    issueStatsOpen: boolean('issue_stats_open').notNull().default(true),
    issueStatsView: text('issue_stats_view').notNull().default('compact'),
    issueActivityView: text('issue_activity_view').notNull().default('flat'),
    autoWatch: boolean('auto_watch').notNull().default(true),
    // The user's own keyboard shortcut overrides, as { hotkeyId: combo }. Only the
    // bindings they changed are stored; the rest come from the instance defaults
    // (app_setting key 'hotkeys') and then the built-in ones.
    hotkeys: jsonb('hotkeys').$type<Record<string, string>>(),
    lastProjectId: integer('last_project_id').references(() => project.id, {
      onDelete: 'set null',
    }),
    // The release whose "what's new" screen this user has closed. Null until they
    // close one, which is what an account created before the screen existed reads as.
    seenVersion: text('seen_version'),
    createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
    updatedAt: timestamp('updated_at', { withTimezone: true }).notNull().defaultNow(),
  },
  (t) => [
    check('user_preference_theme_check', sql`${t.theme} IN ('light', 'dark', 'system')`),
    check('user_preference_issue_open_mode_check', sql`${t.issueOpenMode} IN ('panel', 'page')`),
    check(
      'user_preference_start_page_check',
      sql`${t.startPage} IN ('inbox', 'dashboard', 'work-items', 'initiatives')`,
    ),
    check(
      'user_preference_issue_stats_view_check',
      sql`${t.issueStatsView} IN ('compact', 'timeline')`,
    ),
    check(
      'user_preference_issue_activity_view_check',
      sql`${t.issueActivityView} IN ('flat', 'grouped')`,
    ),
  ],
);

// The roles a project member is put on. A role carries a permission matrix: for each
// resource (work_items, dashboards, ...) the create/edit/read/delete flags. The matrix
// is a jsonb blob owned and enforced by the API (see apps/api/src/shared/permissions.ts).
// A row with a team_id is a custom role of that team, shared by every project the team
// owns. A row with a NULL team_id is a system role: every team assigns it, and only the
// instance owner edits it, in god mode. The one system role marked default ("Member")
// is assigned to members that join without a chosen role. Owners bypass roles entirely
// (they always have full access), so their project_member.role_id stays NULL. A member
// of a project may only be put on a role of that project's team or a system role; the
// API checks it, no foreign key can.
export const teamRole = pgTable(
  'team_role',
  {
    id: serial('id').primaryKey(),
    teamId: integer('team_id').references(() => team.id, { onDelete: 'cascade' }),
    name: text('name').notNull(),
    isDefault: boolean('is_default').notNull().default(false),
    permissions: jsonb('permissions').notNull().default({}),
    createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
  },
  (t) => [
    unique().on(t.teamId, t.name),
    check('team_role_default_system_check', sql`${t.teamId} IS NULL OR NOT ${t.isDefault}`),
    uniqueIndex('team_role_system_name_uq')
      .on(t.name)
      .where(sql`${t.teamId} IS NULL`),
    uniqueIndex('team_role_system_default_uq')
      .on(t.isDefault)
      .where(sql`${t.teamId} IS NULL AND ${t.isDefault}`),
    index('team_role_team_idx').on(t.teamId),
  ],
);

// Project membership: which users can access a project and their role in it.
// A user reaches a project's columns, issues, labels, and every other
// project-scoped entity only through a row here. The creator is inserted as
// "owner"; a project can have several owners. Owners always have full access and
// manage the member list. A "member" row carries role_id pointing at a
// team_role of the project's team or a system role, whose permission matrix decides
// what that member may do; a NULL role_id falls back to the built-in member matrix
// (defaultMemberPermissions). Access checks resolve the owning project of any entity
// and look for the current user here.
export const projectMember = pgTable(
  'project_member',
  {
    projectId: integer('project_id')
      .notNull()
      .references(() => project.id, { onDelete: 'cascade' }),
    userId: text('user_id')
      .notNull()
      .references(() => user.id, { onDelete: 'cascade' }),
    role: text('role').notNull().default('member'),
    roleId: integer('role_id').references(() => teamRole.id, {
      onDelete: 'set null',
    }),
    // What this member does in the project. Free text set by an owner, shown on the
    // members page and given to agents so they can pick who to tag on an unassigned
    // issue. Empty string when unset.
    description: text('description').notNull().default(''),
    // How this membership came about. 'invite' is a person accepting an invite;
    // 'scim' is a row the SCIM group reconciliation created and therefore owns —
    // it only ever updates or removes its own rows, so a sync never undoes a
    // membership someone set up by hand.
    source: text('source').notNull().default('invite'),
    isFavorite: boolean('is_favorite').notNull().default(false),
    isHidden: boolean('is_hidden').notNull().default(false),
    createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
  },
  (t) => [
    primaryKey({ columns: [t.projectId, t.userId] }),
    check('project_member_role_check', sql`${t.role} IN ('owner', 'member')`),
    check('project_member_source_check', sql`${t.source} IN ('invite', 'scim')`),
    index('project_member_user_idx').on(t.userId),
  ],
);

// An invite is a token-addressed grant of membership: always into a team, and, when
// it names a project, straight into that project too. team_role is the rank the
// invitee joins the team on; project_role is whether they own or belong to the
// project, and role_id the team role a project member works under. Accepting creates
// the team_member row, plus the project_member row when a project is named; an
// invitee already in the team keeps the rank they have. At most one pending invite
// per email into a team and per email into a project. email is stored lowercased.
// Revoking a pending invite removes its row.
export const teamInvite = pgTable(
  'team_invite',
  {
    id: serial('id').primaryKey(),
    token: uuid('token').notNull().defaultRandom().unique(),
    teamId: integer('team_id')
      .notNull()
      .references(() => team.id, { onDelete: 'cascade' }),
    // NULL for an invite into the team alone.
    projectId: integer('project_id').references(() => project.id, { onDelete: 'cascade' }),
    email: text('email').notNull(),
    // An invite that names a project always brings its invitee into the team as a
    // plain member; a rank above that is granted by an invite into the team itself.
    teamRole: text('team_role').notNull().default('member'),
    projectRole: text('project_role'),
    // The role the invitee joins the project on when project_role is "member".
    // NULL falls back to the default system role. Project owners bypass roles, so an
    // owner invite keeps this NULL.
    roleId: integer('role_id').references(() => teamRole.id, {
      onDelete: 'set null',
    }),
    status: text('status').notNull().default('pending'),
    invitedByUserId: text('invited_by_user_id').references(() => user.id, {
      onDelete: 'set null',
    }),
    acceptedByUserId: text('accepted_by_user_id').references(() => user.id, {
      onDelete: 'set null',
    }),
    createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
    respondedAt: timestamp('responded_at', { withTimezone: true }),
  },
  (t) => [
    check('team_invite_team_role_check', sql`${t.teamRole} IN ('owner', 'manager', 'member')`),
    check(
      'team_invite_project_role_check',
      sql`(${t.projectId} IS NULL AND ${t.projectRole} IS NULL)
        OR (${t.projectId} IS NOT NULL AND ${t.projectRole} IN ('owner', 'member'))`,
    ),
    check('team_invite_status_check', sql`${t.status} IN ('pending', 'accepted', 'rejected')`),
    uniqueIndex('team_invite_team_pending_uq')
      .on(t.teamId, t.email)
      .where(sql`${t.status} = 'pending' AND ${t.projectId} IS NULL`),
    uniqueIndex('team_invite_project_pending_uq')
      .on(t.projectId, t.email)
      .where(sql`${t.status} = 'pending' AND ${t.projectId} IS NOT NULL`),
    index('team_invite_team_idx').on(t.teamId),
    index('team_invite_project_idx').on(t.projectId),
  ],
);

export const projectColumn = pgTable(
  'project_column',
  {
    id: serial('id').primaryKey(),
    projectId: integer('project_id')
      .notNull()
      .references(() => project.id, { onDelete: 'cascade' }),
    name: text('name').notNull(),
    stateType: text('state_type').notNull().default('unstarted'),
    color: text('color').notNull().default('#6b7280'),
    position: integer('position').notNull(),
    // The work-in-progress limit: how many issues the column should hold. NULL is
    // no limit, which is what every column starts as. wipMode decides what happens
    // at the limit — 'soft' only warns, 'hard' refuses further issues — and is only
    // read when wipLimit is set.
    wipLimit: integer('wip_limit'),
    wipMode: text('wip_mode').notNull().default('soft'),
    // The member an issue is assigned to when it enters this column, replacing
    // whoever held it. NULL leaves the assignee alone.
    autoAssignUserId: text('auto_assign_user_id').references(() => user.id, {
      onDelete: 'set null',
    }),
    createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
  },
  (t) => [
    check(
      'project_column_state_type_check',
      sql`${t.stateType} IN ('backlog', 'unstarted', 'started', 'completed', 'canceled')`,
    ),
    check('project_column_wip_mode_check', sql`${t.wipMode} IN ('soft', 'hard')`),
    check('project_column_wip_limit_check', sql`${t.wipLimit} IS NULL OR ${t.wipLimit} > 0`),
    unique().on(t.projectId, t.position),
  ],
);

export const issueType = pgTable(
  'issue_type',
  {
    id: serial('id').primaryKey(),
    projectId: integer('project_id')
      .notNull()
      .references(() => project.id, { onDelete: 'cascade' }),
    name: text('name').notNull(),
    icon: text('icon').notNull().default(''),
    color: text('color').notNull().default('#6b7280'),
    isDefault: boolean('is_default').notNull().default(false),
    position: integer('position').notNull().default(0),
    createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
  },
  (t) => [unique().on(t.projectId, t.name)],
);

// Optional container a label can belong to. A label has at most one group;
// deleting a group ungroups its labels (label.groupId -> SET NULL).
export const labelGroup = pgTable(
  'label_group',
  {
    id: serial('id').primaryKey(),
    projectId: integer('project_id')
      .notNull()
      .references(() => project.id, { onDelete: 'cascade' }),
    name: text('name').notNull(),
    color: text('color').notNull().default('#6b7280'),
    createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
  },
  (t) => [unique().on(t.projectId, t.name)],
);

export const label = pgTable(
  'label',
  {
    id: serial('id').primaryKey(),
    projectId: integer('project_id')
      .notNull()
      .references(() => project.id, { onDelete: 'cascade' }),
    groupId: integer('group_id').references(() => labelGroup.id, {
      onDelete: 'set null',
    }),
    name: text('name').notNull(),
    color: text('color').notNull().default('#6b7280'),
    createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
  },
  (t) => [unique().on(t.projectId, t.name)],
);

// AI agents owned by a team. Each agent is backed by a hidden bot user
// (user_id -> user.id): that user is what a work item is delegated to, what a
// comment/activity is authored by, and what owns the agent's API key (better-auth
// apikey.reference_id points at it). An external agent needs only a name (on the
// bot user) + username and a key; an internal agent additionally carries a model
// configuration (provider/model/instructions/tools) used to run it. Which projects
// of the team an agent works in is its project_member rows, written by the routes
// that attach it; what it may do there is the intersection of the tools it is
// granted and the role that membership carries, which is per project like a
// person's.
export const aiAgent = pgTable(
  'ai_agent',
  {
    id: serial('id').primaryKey(),
    teamId: integer('team_id')
      .notNull()
      .references(() => team.id, { onDelete: 'cascade' }),
    userId: text('user_id')
      .notNull()
      .references(() => user.id, { onDelete: 'cascade' }),
    username: text('username').notNull(),
    kind: text('kind').notNull(),
    // Internal-agent model configuration. NULL/empty for an external agent.
    // model_credential_id references the integration_credential (kind 'llm') the
    // runtime decrypts to address the model; model names the model id on it.
    modelCredentialId: integer('model_credential_id').references(() => integrationCredential.id, {
      onDelete: 'set null',
    }),
    model: text('model'),
    instructions: text('instructions'),
    // Enabled capability-tool keys (from the code tool registry). System tools
    // that act on the API with the agent's own token are implicit and not listed.
    tools: jsonb('tools').notNull().default([]),
    temperature: doublePrecision('temperature'),
    // NULL leaves the reasoning effort to the provider's default.
    reasoningEffort: text('reasoning_effort'),
    maxSteps: integer('max_steps'),
    // NULL gives the run the instance's maximum run time.
    runTimeoutSec: integer('run_timeout_sec'),
    // Internal-agent run triggers. A mention in a comment enqueues a run when
    // trigger_on_mention is set; being set as an issue's delegate enqueues one when
    // trigger_on_assign is set.
    triggerOnMention: boolean('trigger_on_mention').notNull().default(true),
    triggerOnAssign: boolean('trigger_on_assign').notNull().default(false),
    // Gates the test chat and the external-agent chat only; the triggers above and
    // schedules still run an agent with chat off.
    chatEnabled: boolean('chat_enabled').notNull().default(true),
    // How long a delegation run waits before it becomes claimable, which leaves time
    // to keep editing the issue after delegating it. Applies to delegation only: a
    // mention is a question already asked, and its author waits for the reply.
    delegationDelaySec: integer('delegation_delay_sec').notNull().default(120),
    // The agent's own API key, encrypted at rest (AES-256-GCM, see shared/crypto).
    // An internal agent replays it on every tool call, so unlike better-auth's
    // hashed apikey row it has to stay recoverable. Set for internal agents only:
    // an external agent's key is held by whoever drives it and is never stored here.
    apiKeyCiphertext: text('api_key_ciphertext'),
    apiKeyIv: text('api_key_iv'),
    apiKeyAuthTag: text('api_key_auth_tag'),
    // Conversation memory: when enabled, the agent recalls the last
    // memory_last_messages messages of a thread (persisted by Mastra's Postgres
    // store). memory_last_messages is NULL when memory is off.
    memoryEnabled: boolean('memory_enabled').notNull().default(false),
    memoryLastMessages: integer('memory_last_messages'),
    // The member who created the agent. An external agent's runner authenticates
    // with the agent's key, so `owner` scope means the runner only receives runs
    // this member triggered; `team` scope, the default, means any member's.
    ownerUserId: text('owner_user_id').references(() => user.id, { onDelete: 'set null' }),
    runnerScope: text('runner_scope').notNull().default('team'),
    // Last time a runner claimed work or sent a heartbeat for this agent, which is
    // what the UI shows as its presence. NULL for an agent no runner ever polled.
    lastSeenAt: timestamp('last_seen_at', { withTimezone: true }),
    // Where an external agent receives its work instead of from a runner: each of its
    // runs and chat answers is POSTed here, and the endpoint reports back through the
    // routes a runner uses. NULL for an agent a runner drains, and for an internal one.
    invocationUrl: text('invocation_url'),
    // The key those requests are signed with, encrypted at rest like the API key above
    // because every request reads it back. It outlives a cleared URL, so pointing the
    // agent at a URL again needs no change on the receiving side.
    invocationSecretCiphertext: text('invocation_secret_ciphertext'),
    invocationSecretIv: text('invocation_secret_iv'),
    invocationSecretAuthTag: text('invocation_secret_auth_tag'),
    createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
  },
  (t) => [
    uniqueIndex('ai_agent_team_username_uq').on(t.teamId, sql`lower(${t.username})`),
    unique().on(t.userId),
    check('ai_agent_kind_check', sql`${t.kind} IN ('external', 'internal')`),
    check('ai_agent_runner_scope_check', sql`${t.runnerScope} IN ('owner', 'team')`),
    check(
      'ai_agent_reasoning_effort_check',
      sql`${t.reasoningEffort} IN ('low', 'medium', 'high')`,
    ),
    check(
      'ai_agent_delegation_delay_check',
      sql`${t.delegationDelaySec} >= 0 AND ${t.delegationDelaySec} <= 86400`,
    ),
    index('ai_agent_team_idx').on(t.teamId),
  ],
);

// Recurring autonomous tasks for internal agents. The worker claims rows whose
// next_run_at is due, advances the cadence, and inserts an agent_run snapshot.
export const agentSchedule = pgTable(
  'agent_schedule',
  {
    id: serial('id').primaryKey(),
    agentId: integer('agent_id')
      .notNull()
      .references(() => aiAgent.id, { onDelete: 'cascade' }),
    // The project the schedule's runs work in. An agent belongs to a team and works
    // in several of its projects, so the schedule names which one; the operator picks
    // it when they create the schedule.
    projectId: integer('project_id')
      .notNull()
      .references(() => project.id, { onDelete: 'cascade' }),
    name: text('name').notNull(),
    // Empty on a 'status' schedule that sends no task of its own.
    prompt: text('prompt').notNull(),
    // 'cron' runs on `cron` and carries `next_run_at`; 'status' runs on an issue each
    // time one enters `column_id`, `delay_sec` after it does. Any other type is one an
    // extension of the api adds (modules/agents/schedules/extension.ts), which queues
    // its runs itself.
    type: text('type').notNull().default('cron'),
    cron: text('cron'),
    timezone: text('timezone').notNull(),
    status: text('status').notNull().default('active'),
    nextRunAt: timestamp('next_run_at', { withTimezone: true }),
    columnId: integer('column_id').references(() => projectColumn.id, { onDelete: 'cascade' }),
    delaySec: integer('delay_sec').notNull().default(0),
    // Settings an extension of the api keeps on the schedule. The core stores and
    // copies them and reads none.
    options: jsonb('options').$type<Record<string, unknown>>().notNull().default({}),
    lastRunAt: timestamp('last_run_at', { withTimezone: true }),
    createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
    updatedAt: timestamp('updated_at', { withTimezone: true }).notNull().defaultNow(),
  },
  (t) => [
    check('agent_schedule_status_check', sql`${t.status} IN ('active', 'paused')`),
    check(
      'agent_schedule_type_check',
      sql`(${t.type} = 'cron' AND ${t.cron} IS NOT NULL AND ${t.nextRunAt} IS NOT NULL AND ${t.columnId} IS NULL)
        OR (${t.type} = 'status' AND ${t.columnId} IS NOT NULL AND ${t.cron} IS NULL AND ${t.nextRunAt} IS NULL)
        OR (${t.type} NOT IN ('cron', 'status') AND ${t.cron} IS NULL AND ${t.nextRunAt} IS NULL AND ${t.columnId} IS NULL)`,
    ),
    check('agent_schedule_delay_check', sql`${t.delaySec} >= 0 AND ${t.delaySec} <= 86400`),
    // A schedule works in one project, and one agent works in several projects of
    // its team, so the same name is free again in each of them.
    unique().on(t.projectId, t.agentId, t.name),
    index('agent_schedule_due_idx').on(t.status, t.nextRunAt),
    index('agent_schedule_agent_idx').on(t.agentId),
    index('agent_schedule_project_idx').on(t.projectId),
    index('agent_schedule_column_idx').on(t.columnId),
  ],
);

// A price a workspace set for one model, in USD per million tokens. The agent dashboard
// reads it before the built-in price table (apps/api/src/modules/agents/core/runtime/
// model-pricing.ts), so a model the table lacks, or one bought at another rate, is
// costed as the workspace pays for it. Provider and model are kept lowercased, as the
// table compares them. A cache rate left null bills those tokens at the input rate.
export const modelPrice = pgTable(
  'model_price',
  {
    id: serial('id').primaryKey(),
    workspaceId: integer('workspace_id')
      .notNull()
      .references(() => workspace.id, { onDelete: 'cascade' }),
    provider: text('provider').notNull(),
    model: text('model').notNull(),
    input: doublePrecision('input').notNull(),
    output: doublePrecision('output').notNull(),
    cacheRead: doublePrecision('cache_read'),
    cacheWrite: doublePrecision('cache_write'),
    updatedAt: timestamp('updated_at', { withTimezone: true }).notNull().defaultNow(),
  },
  (t) => [
    check(
      'model_price_rates_check',
      sql`${t.input} >= 0 AND ${t.output} >= 0 AND coalesce(${t.cacheRead}, 0) >= 0 AND coalesce(${t.cacheWrite}, 0) >= 0`,
    ),
    unique().on(t.workspaceId, t.provider, t.model),
  ],
);

// Queued autonomous runs of an internal agent. A run triggered on an issue carries it;
// a cron schedule's run and a manual one do not. The worker claims due rows with a lease,
// runs the agent, and records the result for history and retries.
export const agentRun = pgTable(
  'agent_run',
  {
    id: serial('id').primaryKey(),
    agentId: integer('agent_id')
      .notNull()
      .references(() => aiAgent.id, { onDelete: 'cascade' }),
    // The project the run works in, taken from what triggered it: the issue for a
    // mention or a delegation, the schedule for a scheduled run, the call for a manual
    // one. Stored on the row so the run keeps its project after the agent leaves that
    // project, and so the worker hands one to the runtime without reading the agent.
    projectId: integer('project_id')
      .notNull()
      .references(() => project.id, { onDelete: 'cascade' }),
    issueId: integer('issue_id').references(() => issue.id, { onDelete: 'cascade' }),
    scheduleId: integer('schedule_id').references(() => agentSchedule.id, { onDelete: 'cascade' }),
    // 'event' is a run an extension queued on an issue for a schedule of its own type.
    trigger: text('trigger').notNull().default('delegation'),
    scheduledFor: timestamp('scheduled_for', { withTimezone: true }),
    // The comment that mentioned the agent, kept for traceability. The prompt is
    // snapshotted into `prompt` so a run still works if the comment is later deleted.
    sourceActivityId: integer('source_activity_id').references(() => issueActivity.id, {
      onDelete: 'set null',
    }),
    // A mention in a document comment: the document and the comment. Such a run has no
    // issue.
    documentId: integer('document_id').references(() => projectDocument.id, {
      onDelete: 'cascade',
    }),
    sourceDocumentCommentId: uuid('source_document_comment_id').references(
      () => documentComment.id,
      { onDelete: 'set null' },
    ),
    // The mention comment body at enqueue time, framed into the agent's prompt.
    prompt: text('prompt').notNull(),
    // pending -> success | failed | canceled. Like webhook_delivery, a claim keeps the row
    // 'pending' and pushes next_attempt_at forward by a lease, so a run whose poller
    // crashes mid-flight becomes claimable again after the lease expires.
    status: text('status').notNull().default('pending'),
    attempts: integer('attempts').notNull().default(0),
    nextAttemptAt: timestamp('next_attempt_at', { withTimezone: true }).notNull().defaultNow(),
    lastError: text('last_error'),
    output: text('output'),
    // What the last model call of the run read and wrote, cache included. Null for a run
    // that finished before this was recorded, and for one whose agent reports no counts;
    // the run history shows nothing for either.
    inputTokens: integer('input_tokens'),
    outputTokens: integer('output_tokens'),
    startedAt: timestamp('started_at', { withTimezone: true }),
    executionStartedAt: timestamp('execution_started_at', { withTimezone: true }),
    // The api's current claim of an internal agent's run; null while the run waits,
    // deferred or in retry backoff. The instance's run limit counts the claimed runs.
    claimedAt: timestamp('claimed_at', { withTimezone: true }),
    finishedAt: timestamp('finished_at', { withTimezone: true }),
    createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
  },
  (t) => [
    check(
      'agent_run_status_check',
      sql`${t.status} IN ('pending', 'success', 'failed', 'canceled')`,
    ),
    check(
      'agent_run_trigger_check',
      sql`${t.trigger} IN ('mention', 'delegation', 'field', 'schedule', 'manual', 'status', 'event')`,
    ),
    uniqueIndex('agent_run_schedule_fire_uq').on(t.scheduleId, t.scheduledFor),
    index('agent_run_due_idx').on(t.status, t.nextAttemptAt),
    index('agent_run_schedule_idx').on(t.scheduleId),
    index('agent_run_project_idx').on(t.projectId),
    // Back an issue's run list and the cascades of an issue or comment delete.
    index('agent_run_issue_idx')
      .on(t.issueId, t.id.desc())
      .where(sql`${t.issueId} IS NOT NULL`),
    index('agent_run_source_activity_idx')
      .on(t.sourceActivityId)
      .where(sql`${t.sourceActivityId} IS NOT NULL`),
  ],
);

// A bounded execution transcript for one external runner attempt.
export const agentRunTrace = pgTable(
  'agent_run_trace',
  {
    runId: integer('run_id')
      .notNull()
      .references(() => agentRun.id, { onDelete: 'cascade' }),
    attempt: integer('attempt').notNull(),
    sessionId: text('session_id'),
    events: jsonb('events').notNull().default([]),
    nextOffset: integer('next_offset').notNull().default(0),
    truncated: boolean('truncated').notNull().default(false),
    createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
  },
  (t) => [primaryKey({ columns: [t.runId, t.attempt] })],
);

// A conversation between one member and one external agent. The internal agents'
// threads are held by Mastra's own store; an external agent is driven by a runner on
// its operator's machine, which has no memory of its own, so the conversation lives
// here. The id carries the same shape as a Mastra chat thread id (see
// runtime/thread-ids), which is what lets the chat UI address both kinds the same way.
export const agentChatThread = pgTable(
  'agent_chat_thread',
  {
    id: text('id').primaryKey(),
    agentId: integer('agent_id')
      .notNull()
      .references(() => aiAgent.id, { onDelete: 'cascade' }),
    userId: text('user_id')
      .notNull()
      .references(() => user.id, { onDelete: 'cascade' }),
    title: text('title'),
    // The session the runner's coding agent keeps for this thread on its own machine.
    // Set once the runner reports the session it started; null means the next message
    // starts a fresh one and is sent with the conversation framed into its prompt.
    cliSessionId: text('cli_session_id'),
    createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
    updatedAt: timestamp('updated_at', { withTimezone: true }).notNull().defaultNow(),
  },
  (t) => [index('agent_chat_thread_agent_user_idx').on(t.agentId, t.userId, t.updatedAt.desc())],
);

// One turn of a chat thread, and for an agent turn also the queue row the runner
// drains. A member's message is written 'success' with its text; the agent's answer is
// inserted empty and 'pending', goes 'streaming' when a runner claims it, and is filled
// in from the events that runner reports. A claim pushes next_attempt_at forward by a
// lease, so an answer whose runner dies is claimable again once the lease expires —
// which is why the due index covers both live states. agent_id repeats the thread's
// agent so a runner finds its due turns with one index.
export const agentChatMessage = pgTable(
  'agent_chat_message',
  {
    id: serial('id').primaryKey(),
    threadId: text('thread_id')
      .notNull()
      .references(() => agentChatThread.id, { onDelete: 'cascade' }),
    agentId: integer('agent_id')
      .notNull()
      .references(() => aiAgent.id, { onDelete: 'cascade' }),
    role: text('role').notNull(),
    // The turn's text: what the member wrote, or what the agent has said so far. It is
    // appended to as text events arrive, so the transcript reads correctly mid-answer.
    content: text('content').notNull().default(''),
    status: text('status').notNull().default('pending'),
    attempts: integer('attempts').notNull().default(0),
    nextAttemptAt: timestamp('next_attempt_at', { withTimezone: true }).notNull().defaultNow(),
    lastError: text('last_error'),
    startedAt: timestamp('started_at', { withTimezone: true }),
    finishedAt: timestamp('finished_at', { withTimezone: true }),
    createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
  },
  (t) => [
    check('agent_chat_message_role_check', sql`${t.role} IN ('user', 'assistant')`),
    check(
      'agent_chat_message_status_check',
      sql`${t.status} IN ('pending', 'streaming', 'success', 'failed', 'canceled')`,
    ),
    index('agent_chat_message_thread_idx').on(t.threadId, t.id),
    index('agent_chat_message_due_idx')
      .on(t.agentId, t.nextAttemptAt)
      .where(sql`${t.status} IN ('pending', 'streaming')`),
  ],
);

// What the runner reported while producing one agent turn, as AG-UI events (text
// deltas, tool calls, the run's lifecycle). Append-only: the id is the cursor a reader
// resumes from, which is what lets the browser reconnect mid-answer without replaying
// what it already has.
export const agentChatEvent = pgTable(
  'agent_chat_event',
  {
    id: bigserial('id', { mode: 'number' }).primaryKey(),
    messageId: integer('message_id')
      .notNull()
      .references(() => agentChatMessage.id, { onDelete: 'cascade' }),
    payload: jsonb('payload').notNull(),
    createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
  },
  (t) => [index('agent_chat_event_message_idx').on(t.messageId, t.id)],
);

// The token counts of the last completed answer of one chat thread, which is what the
// chat panel shows as the size of that conversation's context. One row per thread,
// overwritten by every answer: only the last number says how close the conversation is
// to the agent's limit. Null counts mean the agent reports none that can be read as a
// context size, which the panel shows as a dash. An autonomous run keeps its own counts
// on agent_run instead, one row per run.
//
// A thread of an external agent is stored in agent_chat_thread and one of an internal
// agent in Mastra's own tables, so the row carries no foreign key to a thread. Every id
// is minted by runtime/thread-ids, which prefixes each kind of thread with what it is
// scoped to, so no two kinds collide. The row is deleted where the thread is deleted,
// and the cascade covers the deletion of the agent.
export const agentChatUsage = pgTable(
  'agent_chat_usage',
  {
    threadId: text('thread_id').primaryKey(),
    agentId: integer('agent_id')
      .notNull()
      .references(() => aiAgent.id, { onDelete: 'cascade' }),
    inputTokens: integer('input_tokens'),
    outputTokens: integer('output_tokens'),
    updatedAt: timestamp('updated_at', { withTimezone: true }).notNull().defaultNow(),
  },
  (t) => [index('agent_chat_usage_agent_idx').on(t.agentId)],
);

// The conversations a member has starred, so they stay within reach in the chat
// history. Like agent_chat_usage the row carries no foreign key to the thread: an
// internal agent's thread lives in Mastra's own tables. Deleting a thread deletes its
// row; a row left behind is harmless, since the history is built from the threads.
export const agentChatFavorite = pgTable(
  'agent_chat_favorite',
  {
    userId: text('user_id')
      .notNull()
      .references(() => user.id, { onDelete: 'cascade' }),
    agentId: integer('agent_id')
      .notNull()
      .references(() => aiAgent.id, { onDelete: 'cascade' }),
    threadId: text('thread_id').notNull(),
  },
  (t) => [primaryKey({ columns: [t.userId, t.threadId] })],
);

// Stored credentials for a team's integrations, shared by every project it owns. One
// store for every secret: the API keys of LLM providers (kind 'llm', addressed by an
// internal agent's model) and the credentials of tool integrations (kind 'tool',
// bound to configured tools). integration_key names the integration in the catalog;
// the credential's fields (and which are secret) come from that integration's
// credentialSchema. The full credential object is stored encrypted (AES-256-GCM, see
// apps/api/src/shared/crypto.ts): ciphertext + iv + auth_tag. `redacted` is the same
// object with secret fields masked, kept in plaintext for a masked display. The
// secret is never returned to the client. A team may hold several credentials per
// integration (e.g. two Jina keys), told apart by `label`.
export const integrationCredential = pgTable(
  'integration_credential',
  {
    id: serial('id').primaryKey(),
    teamId: integer('team_id')
      .notNull()
      .references(() => team.id, { onDelete: 'cascade' }),
    integrationKey: text('integration_key').notNull(),
    label: text('label'),
    ciphertext: text('ciphertext').notNull(),
    iv: text('iv').notNull(),
    authTag: text('auth_tag').notNull(),
    // The credential with secret fields masked; non-secret fields verbatim. Owned by
    // the store, derived from the integration's credential schema.
    redacted: jsonb('redacted').notNull().default({}),
    createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
  },
  (t) => [index('integration_credential_team_idx').on(t.teamId)],
);

export const gitProviderConnection = pgTable(
  'git_provider_connection',
  {
    id: serial('id').primaryKey(),
    teamId: integer('team_id')
      .notNull()
      .references(() => team.id, { onDelete: 'cascade' }),
    provider: text('provider').notNull(),
    baseUrl: text('base_url').notNull(),
    accountLogin: text('account_login').notNull(),
    ciphertext: text('ciphertext').notNull(),
    iv: text('iv').notNull(),
    authTag: text('auth_tag').notNull(),
    createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
    updatedAt: timestamp('updated_at', { withTimezone: true }).notNull().defaultNow(),
  },
  (t) => [index('git_provider_connection_team_idx').on(t.teamId)],
);

export const gitManagedRepository = pgTable(
  'git_managed_repository',
  {
    id: serial('id').primaryKey(),
    projectId: integer('project_id')
      .notNull()
      .references(() => project.id, { onDelete: 'cascade' }),
    connectionId: integer('connection_id')
      .notNull()
      .references(() => gitProviderConnection.id, { onDelete: 'cascade' }),
    externalId: text('external_id').notNull(),
    fullName: text('full_name').notNull(),
    webUrl: text('web_url').notNull(),
    webhookExternalId: text('webhook_external_id').notNull(),
    status: text('status').notNull().default('connected'),
    lastError: text('last_error'),
    createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
    updatedAt: timestamp('updated_at', { withTimezone: true }).notNull().defaultNow(),
  },
  (t) => [
    unique('git_managed_repository_project_connection_external_unique').on(
      t.projectId,
      t.connectionId,
      t.externalId,
    ),
    index('git_managed_repository_project_connection_idx').on(t.projectId, t.connectionId),
  ],
);

// Per-team notification provider credentials: the outbound channels every project
// of the team delivers through (SMTP or Resend for email, a Telegram bot). One row
// per team, managed by its owner. The config carries secrets (SMTP password, Resend
// API key, Telegram bot token), so it is stored encrypted (AES-256-GCM, see
// apps/api/src/shared/crypto.ts): ciphertext + iv + auth_tag. `redacted` is the
// same config with secret values dropped, kept in plaintext so the settings UI can
// render the non-secret fields and show which secrets are set. Secrets are never
// returned to the client. The plaintext config is read only by the delivery sender.
// Which events reach a given member is a per-user choice held in
// user_notification_preference, not here. The Telegram bot token here is optional: a
// team that sets one delivers through its own bot, otherwise delivery falls back
// to the instance bot in app_secret key 'telegram.bot'.
export const teamNotificationSetting = pgTable('team_notification_setting', {
  teamId: integer('team_id')
    .primaryKey()
    .references(() => team.id, { onDelete: 'cascade' }),
  ciphertext: text('ciphertext').notNull(),
  iv: text('iv').notNull(),
  authTag: text('auth_tag').notNull(),
  redacted: jsonb('redacted').notNull().default({}),
  updatedAt: timestamp('updated_at', { withTimezone: true }).notNull().defaultNow(),
});

// A member's own notification preferences for one project: for each issue event
// type, whether they want it by email and/or Telegram. One row per (user, project);
// absent means the member has not opted in and receives nothing.
// email_events/telegram_events are EventToggles jsonb keyed by the four inbox
// notification types (assigned/mentioned/commented/state_changed). Email is sent to
// the member's account address; Telegram to the chat of the account they linked in
// user_telegram_account, which is instance-wide rather than per project.
export const userNotificationPreference = pgTable(
  'user_notification_preference',
  {
    id: serial('id').primaryKey(),
    userId: text('user_id')
      .notNull()
      .references(() => user.id, { onDelete: 'cascade' }),
    projectId: integer('project_id')
      .notNull()
      .references(() => project.id, { onDelete: 'cascade' }),
    emailEvents: jsonb('email_events').notNull().default({}),
    telegramEvents: jsonb('telegram_events').notNull().default({}),
    createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
    updatedAt: timestamp('updated_at', { withTimezone: true }).notNull().defaultNow(),
  },
  (t) => [unique('user_notification_pref_user_project_unique').on(t.userId, t.projectId)],
);

// The Telegram account a user has linked, instance-wide (one row per user, whatever
// the project). Linking runs through the instance bot: the user asks for a link, the
// row is created with a one-time link_code, and the bot fills chat_id when that code
// arrives as `/start <code>`. So the row is "pending" while chat_id is null and
// "linked" once it is set — link_code is cleared at that point. chat_id is unique, so
// one Telegram account cannot serve two product accounts.
export const userTelegramAccount = pgTable(
  'user_telegram_account',
  {
    userId: text('user_id')
      .primaryKey()
      .references(() => user.id, { onDelete: 'cascade' }),
    chatId: text('chat_id'),
    // Display only, refreshed on every link: what to show the user so they can tell
    // which Telegram account this is. A Telegram account may have no @username.
    username: text('username'),
    firstName: text('first_name'),
    linkCode: text('link_code'),
    linkCodeExpiresAt: timestamp('link_code_expires_at', { withTimezone: true }),
    linkedAt: timestamp('linked_at', { withTimezone: true }),
    createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
  },
  (t) => [
    uniqueIndex('user_telegram_account_chat_id_unique')
      .on(t.chatId)
      .where(sql`${t.chatId} IS NOT NULL`),
    uniqueIndex('user_telegram_account_link_code_unique')
      .on(t.linkCode)
      .where(sql`${t.linkCode} IS NOT NULL`),
  ],
);

// Outbox for outbound notification delivery. One row per (recipient, channel,
// message) to send for an issue event: an email or a Telegram message to one member.
// Rows are enqueued when inbox notifications are created (see
// apps/api/src/modules/notifications/outbound.ts) and drained by the worker
// following the same claim/retry pattern as webhook_delivery. The message text is
// composed at enqueue time and stored in `payload`; the channel credentials are read
// from team_notification_setting at send time. channel is 'email' | 'telegram'
// ('email' picks SMTP or Resend from the team config). recipient is the member's
// email address for email rows, or their Telegram chat id for telegram rows.
// The stored message on a notification_delivery row, composed at enqueue time by the
// api and read by the worker that sends it. Email uses `subject` and sends `html` as composed;
// for a row without `html` the sender appends `url` to `text` and wraps it. Telegram sends
// `html` (parse_mode HTML) and falls back to `text`. `dedupeKey` is what the enqueue side
// matches to avoid queuing the same message twice; `projectInviteId` ties an invite email to
// its invite, so a delivery whose invite is no longer pending is dropped instead of sent.
export interface DeliveryPayload {
  subject?: string;
  text: string;
  html?: string;
  url?: string;
  emailSource?: 'project' | 'instance';
  idempotencyKey?: string;
  dedupeKey?: string;
  projectInviteId?: number;
}

export const notificationDelivery = pgTable(
  'notification_delivery',
  {
    id: serial('id').primaryKey(),
    projectId: integer('project_id')
      .notNull()
      .references(() => project.id, { onDelete: 'cascade' }),
    channel: text('channel').notNull(),
    recipient: text('recipient'),
    payload: jsonb('payload').$type<DeliveryPayload>().notNull(),
    status: text('status').notNull().default('pending'),
    attempts: integer('attempts').notNull().default(0),
    nextAttemptAt: timestamp('next_attempt_at', { withTimezone: true }).notNull().defaultNow(),
    lastError: text('last_error'),
    createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
  },
  (t) => [
    check('notification_delivery_channel_check', sql`${t.channel} IN ('email', 'telegram')`),
    // Backs the worker's claim query: due pending rows ordered by next_attempt_at.
    index('notification_delivery_due_idx')
      .on(t.nextAttemptAt)
      .where(sql`${t.status} = 'pending'`),
  ],
);

// Skill library of a team, shared by every project it owns. A skill is a unit of
// knowledge given to an internal agent (Anthropic Agent Skill format): a SKILL.md
// with YAML frontmatter (name/description) plus optional reference files, no
// executable scripts. The markdown and reference bytes live in the S3 object store
// under s3_prefix; `files` lists the reference file paths and their object keys.
// Sourced from an upload, inline text, or a GitHub URL. Enabled on an agent via
// agent_skill_link.
export const agentSkill = pgTable(
  'agent_skill',
  {
    id: serial('id').primaryKey(),
    teamId: integer('team_id')
      .notNull()
      .references(() => team.id, { onDelete: 'cascade' }),
    name: text('name').notNull(),
    description: text('description').notNull().default(''),
    source: text('source').notNull(),
    sourceUrl: text('source_url'),
    // Object-store prefix holding SKILL.md and reference files for this skill.
    s3Prefix: text('s3_prefix').notNull(),
    // Reference files beyond SKILL.md: [{ path, s3Key, size }]. Owned by the store.
    files: jsonb('files').notNull().default([]),
    createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
  },
  (t) => [
    unique().on(t.teamId, t.name),
    check('agent_skill_source_check', sql`${t.source} IN ('upload', 'inline', 'github')`),
    index('agent_skill_team_idx').on(t.teamId),
  ],
);

// Which skills are enabled on which agents (many-to-many). Deleting an agent or a
// skill removes the link.
export const agentSkillLink = pgTable(
  'agent_skill_link',
  {
    agentId: integer('agent_id')
      .notNull()
      .references(() => aiAgent.id, { onDelete: 'cascade' }),
    skillId: integer('skill_id')
      .notNull()
      .references(() => agentSkill.id, { onDelete: 'cascade' }),
  },
  (t) => [
    primaryKey({ columns: [t.agentId, t.skillId] }),
    index('agent_skill_link_skill_idx').on(t.skillId),
  ],
);

// A tool configured for a team, shared by every project it owns: a tool from the
// catalog (tool_key) bound to one integration_credential. The tool's integration owns
// the secret, so the tool holds no secret of its own — it references the credential
// the runtime decrypts at call time. Different tools of the same integration may be
// bound to different credentials (e.g. two Jina keys). A tool of an integration with
// no credential (PDF) has a null credential_id and is configured once per team.
// Enabled on an agent via agent_tool_link.
export const agentTool = pgTable(
  'agent_tool',
  {
    id: serial('id').primaryKey(),
    teamId: integer('team_id')
      .notNull()
      .references(() => team.id, { onDelete: 'cascade' }),
    toolKey: text('tool_key').notNull(),
    credentialId: integer('credential_id').references(() => integrationCredential.id, {
      onDelete: 'cascade',
    }),
    createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
  },
  (t) => [
    unique().on(t.teamId, t.toolKey, t.credentialId),
    uniqueIndex('agent_tool_team_tool_keyless_uq')
      .on(t.teamId, t.toolKey)
      .where(sql`${t.credentialId} IS NULL`),
    index('agent_tool_team_idx').on(t.teamId),
    index('agent_tool_credential_idx').on(t.credentialId),
  ],
);

// Which configured tools are enabled on which agents (many-to-many). Deleting an
// agent or a configured tool removes the link.
export const agentToolLink = pgTable(
  'agent_tool_link',
  {
    agentId: integer('agent_id')
      .notNull()
      .references(() => aiAgent.id, { onDelete: 'cascade' }),
    agentToolId: integer('agent_tool_id')
      .notNull()
      .references(() => agentTool.id, { onDelete: 'cascade' }),
  },
  (t) => [
    primaryKey({ columns: [t.agentId, t.agentToolId] }),
    index('agent_tool_link_tool_idx').on(t.agentToolId),
  ],
);

// Custom fields. Always scoped to a project. A NULL issue_type_id applies the
// field to every issue in that project; a non-null issue_type_id scopes it to
// that one type.
export const customField = pgTable(
  'custom_field',
  {
    id: serial('id').primaryKey(),
    projectId: integer('project_id')
      .notNull()
      .references(() => project.id, { onDelete: 'cascade' }),
    issueTypeId: integer('issue_type_id').references(() => issueType.id, {
      onDelete: 'cascade',
    }),
    name: text('name').notNull(),
    fieldType: text('field_type').notNull(),
    // Who a 'member' field may hold: every candidate, the people only, or the
    // agents only. NULL for every other field type.
    memberScope: text('member_scope'),
    // When true the field renders in the issue body (under the description),
    // like a second description; when false it renders as a Properties row.
    showInBody: boolean('show_in_body').notNull().default(false),
    position: integer('position').notNull().default(0),
    createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
  },
  (t) => [
    check(
      'custom_field_field_type_check',
      sql`${t.fieldType} IN ('text', 'markdown', 'url', 'number', 'boolean', 'date', 'datetime', 'datetime_range', 'select', 'multi_select', 'member')`,
    ),
    check(
      'custom_field_member_scope_check',
      sql`(${t.fieldType} = 'member') = (${t.memberScope} IS NOT NULL) AND (${t.memberScope} IS NULL OR ${t.memberScope} IN ('all', 'humans', 'agents'))`,
    ),
  ],
);

export const customFieldOption = pgTable(
  'custom_field_option',
  {
    id: serial('id').primaryKey(),
    fieldId: integer('field_id')
      .notNull()
      .references(() => customField.id, { onDelete: 'cascade' }),
    value: text('value').notNull(),
    color: text('color').notNull().default('#6b7280'),
    position: integer('position').notNull().default(0),
  },
  (t) => [unique().on(t.fieldId, t.value)],
);

// Which member custom fields an agent reacts to. Setting the agent into one of the
// listed fields enqueues a run for it, the way being made an issue's delegate does
// with trigger_on_assign. A field the agent is not linked to holds it without
// starting anything.
export const agentFieldTrigger = pgTable(
  'agent_field_trigger',
  {
    agentId: integer('agent_id')
      .notNull()
      .references(() => aiAgent.id, { onDelete: 'cascade' }),
    fieldId: integer('field_id')
      .notNull()
      .references(() => customField.id, { onDelete: 'cascade' }),
    // How long this field's run waits before it becomes claimable. Each field carries
    // its own, the way delegation carries delegation_delay_sec.
    delaySec: integer('delay_sec').notNull().default(120),
  },
  (t) => [
    primaryKey({ columns: [t.agentId, t.fieldId] }),
    check('agent_field_trigger_delay_check', sql`${t.delaySec} >= 0 AND ${t.delaySec} <= 86400`),
    index('agent_field_trigger_field_idx').on(t.fieldId),
  ],
);

// A preset a new issue can be created from. It carries the title and description
// the issue starts with plus the properties applied on top of it — every one of
// them optional, and one left NULL leaves the create dialog on its own default.
// The labels are in issue_template_label.
export const issueTemplate = pgTable(
  'issue_template',
  {
    id: serial('id').primaryKey(),
    projectId: integer('project_id')
      .notNull()
      .references(() => project.id, { onDelete: 'cascade' }),
    name: text('name').notNull(),
    // What the template is for, shown under its name in the picker.
    description: text('description').notNull().default(''),
    // The title and body the issue starts with, both editable before it is created.
    titleTemplate: text('title_template').notNull().default(''),
    descriptionTemplate: text('description_template').notNull().default(''),
    typeId: integer('type_id').references(() => issueType.id, { onDelete: 'set null' }),
    columnId: integer('column_id').references(() => projectColumn.id, { onDelete: 'set null' }),
    priority: text('priority'),
    assigneeUserId: text('assignee_user_id').references(() => user.id, { onDelete: 'set null' }),
    createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
  },
  (t) => [unique().on(t.projectId, t.name)],
);

export const issueTemplateLabel = pgTable(
  'issue_template_label',
  {
    templateId: integer('template_id')
      .notNull()
      .references(() => issueTemplate.id, { onDelete: 'cascade' }),
    labelId: integer('label_id')
      .notNull()
      .references(() => label.id, { onDelete: 'cascade' }),
  },
  (t) => [primaryKey({ columns: [t.templateId, t.labelId] })],
);

// A strategic grouping of issues inside a project (project-scoped, not
// cross-project). Issues point at it through issue.initiative_id. status is a
// fixed lifecycle enum; health is not stored — it is computed on the fly from the
// initiative's issue progress against its timeline. owner_user_id is the person
// accountable. start_date/target_date bound the timeline (start defaults to
// created_at when null); priority mirrors issue.priority (free text).
export const initiative = pgTable(
  'initiative',
  {
    id: serial('id').primaryKey(),
    projectId: integer('project_id')
      .notNull()
      .references(() => project.id, { onDelete: 'cascade' }),
    title: text('title').notNull(),
    description: text('description').notNull().default(''),
    status: text('status').notNull().default('planned'),
    ownerUserId: text('owner_user_id').references(() => user.id, {
      onDelete: 'set null',
    }),
    priority: text('priority'),
    startDate: date('start_date'),
    targetDate: date('target_date'),
    position: doublePrecision('position').notNull().default(0),
    createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
    updatedAt: timestamp('updated_at', { withTimezone: true }).notNull().defaultNow(),
  },
  (t) => [
    check(
      'initiative_status_check',
      sql`${t.status} IN ('proposed', 'planned', 'active', 'completed', 'canceled')`,
    ),
    index('initiative_project_idx').on(t.projectId, t.position),
  ],
);

// Labels attached to an initiative. Reuses the project's labels (label table);
// mirrors issue_label. Composite PK, no id, no timestamps.
export const initiativeLabel = pgTable(
  'initiative_label',
  {
    initiativeId: integer('initiative_id')
      .notNull()
      .references(() => initiative.id, { onDelete: 'cascade' }),
    labelId: integer('label_id')
      .notNull()
      .references(() => label.id, { onDelete: 'cascade' }),
  },
  (t) => [primaryKey({ columns: [t.initiativeId, t.labelId] })],
);

// File attachments on an initiative. Mirrors issue_attachment: bytes live in the
// S3-compatible object store, this table holds the metadata and the object key,
// and public_id is the unguessable id used in the public download URL.
export const initiativeAttachment = pgTable(
  'initiative_attachment',
  {
    id: serial('id').primaryKey(),
    publicId: uuid('public_id').notNull().defaultRandom().unique(),
    initiativeId: integer('initiative_id')
      .notNull()
      .references(() => initiative.id, { onDelete: 'cascade' }),
    s3Key: text('s3_key').notNull(),
    filename: text('filename').notNull(),
    contentType: text('content_type').notNull(),
    sizeBytes: bigint('size_bytes', { mode: 'number' }).notNull(),
    createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
  },
  (t) => [index('initiative_attachment_initiative_idx').on(t.initiativeId)],
);

// A time-boxed period of work inside a project (a sprint). Issues point at it
// through issue.cycle_id. The state of a cycle — upcoming, active, completed — is
// not stored: it follows from start_date/end_date against the current date, so a
// cycle never has to be started or closed by hand. completed_at is the one override:
// a cycle finished ahead of its dates counts as completed from that moment on.
// Cycles of one project may not overlap, which is what makes at most one of them
// active; the API enforces that.
export const cycle = pgTable(
  'cycle',
  {
    id: serial('id').primaryKey(),
    projectId: integer('project_id')
      .notNull()
      .references(() => project.id, { onDelete: 'cascade' }),
    name: text('name').notNull(),
    // What the team commits to in this cycle (the sprint goal). Empty when unset.
    goal: text('goal').notNull().default(''),
    startDate: date('start_date').notNull(),
    endDate: date('end_date').notNull(),
    // When the cycle was finished before its planned end date. NULL while it still
    // runs on its dates; once set it is never cleared, and end_date keeps the date
    // the cycle was planned to run until.
    completedAt: timestamp('completed_at', { withTimezone: true }),
    createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
    updatedAt: timestamp('updated_at', { withTimezone: true }).notNull().defaultNow(),
  },
  (t) => [
    check('cycle_dates_check', sql`${t.endDate} >= ${t.startDate}`),
    index('cycle_project_idx').on(t.projectId, t.startDate),
  ],
);

export const issue = pgTable(
  'issue',
  {
    id: serial('id').primaryKey(),
    projectId: integer('project_id')
      .notNull()
      .references(() => project.id, { onDelete: 'cascade' }),
    sequenceNumber: integer('sequence_number').notNull(),
    typeId: integer('type_id').references(() => issueType.id, {
      onDelete: 'set null',
    }),
    // The initiative this issue belongs to (project-scoped). Nullable; deleting an
    // initiative unlinks its issues rather than deleting them (like type_id).
    initiativeId: integer('initiative_id').references(() => initiative.id, {
      onDelete: 'set null',
    }),
    // The cycle this issue is planned into (project-scoped). Nullable; deleting a
    // cycle unlinks its issues rather than deleting them.
    cycleId: integer('cycle_id').references(() => cycle.id, {
      onDelete: 'set null',
    }),
    columnId: integer('column_id')
      .notNull()
      .references(() => projectColumn.id),
    // The issue this one is a subtask of (same project). One level deep: an issue
    // with a parent cannot itself be a parent, which the API enforces. Deleting or
    // archiving a parent asks what to do with its subtasks, so the ON DELETE here
    // only covers the paths that bypass that choice (a deleted project).
    parentId: integer('parent_id').references((): AnyPgColumn => issue.id, {
      onDelete: 'set null',
    }),
    assigneeUserId: text('assignee_user_id').references(() => user.id, {
      onDelete: 'set null',
    }),
    // The AI agent an issue is delegated to. Like assignee this points at a bot
    // user (ai_agent.user_id); assignee holds a project member, delegate holds an
    // agent. Setting a delegate on an internal agent with trigger_on_assign enqueues
    // an agent run.
    delegateUserId: text('delegate_user_id').references(() => user.id, {
      onDelete: 'set null',
    }),
    title: text('title').notNull(),
    description: text('description').notNull().default(''),
    priority: text('priority'),
    // How big the work is, one column per estimate kind the project can turn on.
    // Time is held in minutes; the UI enters and shows it as hours and minutes.
    estimatePoints: numeric('estimate_points'),
    estimateMinutes: integer('estimate_minutes'),
    startDate: date('start_date'),
    dueDate: date('due_date'),
    position: doublePrecision('position').notNull().default(0),
    // When set, the issue is archived: hidden from the board and lists but kept and
    // restorable. Set manually (archive action) or by the worker's auto-archive
    // sweep for issues that sat in a completed/canceled column past the project's
    // configured threshold. NULL means active (on the board).
    archivedAt: timestamp('archived_at', { withTimezone: true }),
    // Unguessable token for the public read-only share link. NULL means the issue
    // is not shared; setting it enables the link, clearing it revokes access.
    shareToken: uuid('share_token').unique(),
    // How much the share link exposes, the same choice a shared view carries.
    shareExtended: boolean('share_extended').notNull().default(false),
    // When the share link stops working. NULL means it never expires. Past this
    // moment the link reads as revoked everywhere, and the api's share-expiry sweep
    // clears the token.
    shareExpiresAt: timestamp('share_expires_at', { withTimezone: true }),
    createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
    updatedAt: timestamp('updated_at', { withTimezone: true }).notNull().defaultNow(),
  },
  (t) => [
    unique().on(t.projectId, t.sequenceNumber),
    check('issue_estimate_points_check', sql`${t.estimatePoints} >= 0`),
    check('issue_estimate_minutes_check', sql`${t.estimateMinutes} >= 0`),
    // Backs the board/list read (active issues of a project) and the worker's
    // auto-archive sweep (still-active issues in a project).
    index('issue_project_active_idx')
      .on(t.projectId, t.columnId)
      .where(sql`${t.archivedAt} IS NULL`),
    // Backs the import duplicate check: the titles a project already holds,
    // archived ones included, normalised the way titleKey compares them.
    index('issue_project_title_idx').on(t.projectId, sql`lower(btrim(${t.title}))`),
    // Backs reading a parent's subtasks, on the issue page and on every write that
    // has to know whether an issue has any.
    index('issue_parent_idx')
      .on(t.parentId)
      .where(sql`${t.parentId} IS NOT NULL`),
    // Backs the progress counts of a cycles list, the transfer of a cycle's issues,
    // and the ON DELETE SET NULL a cycle delete runs.
    index('issue_cycle_idx')
      .on(t.cycleId)
      .where(sql`${t.cycleId} IS NOT NULL`),
    // Backs the progress counts of the initiatives list and of one initiative, and the
    // ON DELETE SET NULL an initiative delete runs.
    index('issue_initiative_idx')
      .on(t.initiativeId)
      .where(sql`${t.initiativeId} IS NOT NULL`),
  ],
);

// The number an issue held in a project it was moved out of, so a link to the old
// identifier still resolves. project.next_sequence never hands that number out again,
// so it cannot collide with an issue of that project.
export const issueKeyAlias = pgTable(
  'issue_key_alias',
  {
    projectId: integer('project_id')
      .notNull()
      .references(() => project.id, { onDelete: 'cascade' }),
    sequenceNumber: integer('sequence_number').notNull(),
    issueId: integer('issue_id')
      .notNull()
      .references(() => issue.id, { onDelete: 'cascade' }),
  },
  (t) => [
    primaryKey({ columns: [t.projectId, t.sequenceNumber] }),
    index('issue_key_alias_issue_idx').on(t.issueId),
  ],
);

// One stretch an issue spent on a cycle: a record is opened when the issue is
// planned into the cycle and closed when it leaves it. This is what the cycle
// history of an issue reads, and what carry-over metrics are counted from.
// Deleting a cycle removes its records, so an issue stops counting a cycle that no
// longer exists.
export const issueCycle = pgTable(
  'issue_cycle',
  {
    id: serial('id').primaryKey(),
    issueId: integer('issue_id')
      .notNull()
      .references(() => issue.id, { onDelete: 'cascade' }),
    cycleId: integer('cycle_id')
      .notNull()
      .references(() => cycle.id, { onDelete: 'cascade' }),
    enteredAt: timestamp('entered_at', { withTimezone: true }).notNull().defaultNow(),
    // NULL while the issue still sits on the cycle.
    leftAt: timestamp('left_at', { withTimezone: true }),
  },
  (t) => [
    index('issue_cycle_issue_idx').on(t.issueId, t.enteredAt),
    // The ON DELETE CASCADE a cycle delete runs.
    index('issue_cycle_cycle_idx').on(t.cycleId),
    // An issue sits on one cycle at a time, so it never holds two open records for
    // the same cycle.
    uniqueIndex('issue_cycle_open_idx')
      .on(t.issueId, t.cycleId)
      .where(sql`${t.leftAt} IS NULL`),
  ],
);

// One stretch an issue spent in one column: a row is opened when the issue enters
// the column and closed when it leaves it. This is what the status timeline reads,
// and what the closing metrics are counted from. The name and the state type are
// copied in rather than read through column_id, because both are editable: renaming
// a column or switching its type would otherwise rewrite what past stretches say.
export const issueStatus = pgTable(
  'issue_status',
  {
    id: serial('id').primaryKey(),
    issueId: integer('issue_id')
      .notNull()
      .references(() => issue.id, { onDelete: 'cascade' }),
    // NULL once the column is deleted, which keeps the stretches spent in it.
    columnId: integer('column_id').references(() => projectColumn.id, { onDelete: 'set null' }),
    // The name the column had at that moment. NULL only in a backfilled row whose
    // change-log entry recorded none.
    columnName: text('column_name'),
    // The type the column had at that moment. NULL only in a backfilled row whose
    // column could not be resolved; such a stretch never counts as completed.
    stateType: text('state_type'),
    enteredAt: timestamp('entered_at', { withTimezone: true }).notNull().defaultNow(),
    // NULL while the issue still sits in the column.
    leftAt: timestamp('left_at', { withTimezone: true }),
  },
  (t) => [
    index('issue_status_issue_idx').on(t.issueId, t.enteredAt),
    // The ON DELETE SET NULL a column delete runs.
    index('issue_status_column_idx')
      .on(t.columnId)
      .where(sql`${t.columnId} IS NOT NULL`),
    // An issue sits in one column at a time, so it never holds two open rows.
    uniqueIndex('issue_status_open_idx')
      .on(t.issueId)
      .where(sql`${t.leftAt} IS NULL`),
  ],
);

export const issueLabel = pgTable(
  'issue_label',
  {
    issueId: integer('issue_id')
      .notNull()
      .references(() => issue.id, { onDelete: 'cascade' }),
    labelId: integer('label_id')
      .notNull()
      .references(() => label.id, { onDelete: 'cascade' }),
  },
  (t) => [primaryKey({ columns: [t.issueId, t.labelId] })],
);

// A relation between two issues of the same project. One row per relation: the
// inverse side ("blocked by" for 'blocks', "duplicated by" for 'duplicates') is
// read from the same row by matching target_issue_id.
export const issueLink = pgTable(
  'issue_link',
  {
    id: serial('id').primaryKey(),
    sourceIssueId: integer('source_issue_id')
      .notNull()
      .references(() => issue.id, { onDelete: 'cascade' }),
    targetIssueId: integer('target_issue_id')
      .notNull()
      .references(() => issue.id, { onDelete: 'cascade' }),
    kind: text('kind').notNull(),
    createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
  },
  (t) => [
    check('issue_link_kind_check', sql`${t.kind} IN ('blocks', 'relates', 'duplicates')`),
    check('issue_link_self_check', sql`${t.sourceIssueId} <> ${t.targetIssueId}`),
    // A pair of issues carries a kind at most once, in either order: "A blocks B"
    // and "B blocks A" are the same relation stated twice and contradict each
    // other. Indexing the ordered pair makes the database reject the second one,
    // which a read-then-insert in the application cannot do without a race.
    uniqueIndex('issue_link_pair_kind_idx').on(
      sql`least(${t.sourceIssueId}, ${t.targetIssueId})`,
      sql`greatest(${t.sourceIssueId}, ${t.targetIssueId})`,
      t.kind,
    ),
    // The pair index is on least/greatest, so it serves neither column on its
    // own; these back the reads that match one side — an issue's own relations
    // (either side) and the board's marker (the source side).
    index('issue_link_source_idx').on(t.sourceIssueId),
    index('issue_link_target_idx').on(t.targetIssueId),
  ],
);

// Who follows an issue. A watcher receives every notification the issue produces;
// the assignment and mention notifications are addressed to one person and reach
// them whether they watch it or not. `subscribed` false is an unsubscription: the
// row stays so the auto-subscribe rules (see watchers.ts) cannot put the member
// back on the next comment they write.
export const issueWatcher = pgTable(
  'issue_watcher',
  {
    issueId: integer('issue_id')
      .notNull()
      .references(() => issue.id, { onDelete: 'cascade' }),
    userId: text('user_id')
      .notNull()
      .references(() => user.id, { onDelete: 'cascade' }),
    subscribed: boolean('subscribed').notNull().default(true),
    createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
  },
  (t) => [primaryKey({ columns: [t.issueId, t.userId] })],
);

export const issueFieldValue = pgTable(
  'issue_field_value',
  {
    id: serial('id').primaryKey(),
    issueId: integer('issue_id')
      .notNull()
      .references(() => issue.id, { onDelete: 'cascade' }),
    fieldId: integer('field_id')
      .notNull()
      .references(() => customField.id, { onDelete: 'cascade' }),
    valueText: text('value_text'),
    valueNumber: numeric('value_number'),
    valueBool: boolean('value_bool'),
    valueDate: date('value_date'),
    // datetime and datetime_range fields. A datetime_range keeps its end here;
    // for a datetime the end stays NULL.
    valueDatetime: timestamp('value_datetime', { withTimezone: true }),
    valueDatetimeEnd: timestamp('value_datetime_end', { withTimezone: true }),
    // member fields. Deleting the user clears the field rather than the row, so the
    // issue keeps the rest of its values.
    valueUserId: text('value_user_id').references(() => user.id, { onDelete: 'set null' }),
  },
  (t) => [unique().on(t.issueId, t.fieldId)],
);

export const issueFieldOption = pgTable(
  'issue_field_option',
  {
    issueId: integer('issue_id')
      .notNull()
      .references(() => issue.id, { onDelete: 'cascade' }),
    fieldId: integer('field_id')
      .notNull()
      .references(() => customField.id, { onDelete: 'cascade' }),
    optionId: integer('option_id')
      .notNull()
      .references(() => customFieldOption.id, { onDelete: 'cascade' }),
  },
  (t) => [primaryKey({ columns: [t.issueId, t.fieldId, t.optionId] })],
);

// File attachments on issues. Bytes live in the S3-compatible object store;
// this table holds metadata and the object key. public_id is the unguessable id
// used in the public download URL.
export const issueAttachment = pgTable(
  'issue_attachment',
  {
    id: serial('id').primaryKey(),
    publicId: uuid('public_id').notNull().defaultRandom().unique(),
    issueId: integer('issue_id')
      .notNull()
      .references(() => issue.id, { onDelete: 'cascade' }),
    s3Key: text('s3_key').notNull(),
    filename: text('filename').notNull(),
    contentType: text('content_type').notNull(),
    sizeBytes: bigint('size_bytes', { mode: 'number' }).notNull(),
    createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
  },
  (t) => [index('issue_attachment_issue_idx').on(t.issueId)],
);

export const issueDevelopmentLink = pgTable(
  'issue_development_link',
  {
    id: serial('id').primaryKey(),
    issueId: integer('issue_id')
      .notNull()
      .references(() => issue.id, { onDelete: 'cascade' }),
    provider: text('provider').notNull(),
    repository: text('repository').notNull(),
    kind: text('kind').notNull().default('pull_request'),
    externalKey: text('external_key').notNull(),
    number: integer('number'),
    title: text('title').notNull(),
    url: text('url'),
    state: text('state').notNull(),
    draft: boolean('draft').notNull().default(false),
    sourceBranch: text('source_branch'),
    targetBranch: text('target_branch').notNull(),
    headSha: text('head_sha'),
    pipelineStatus: text('pipeline_status'),
    pipelineUrl: text('pipeline_url'),
    updatedAt: timestamp('updated_at', { withTimezone: true }).notNull().defaultNow(),
  },
  (t) => [
    unique().on(t.issueId, t.provider, t.repository, t.externalKey),
    index('issue_development_link_issue_idx').on(t.issueId, t.updatedAt.desc()),
    index('issue_development_link_pr_idx').on(t.provider, t.repository, t.number),
    index('issue_development_link_sha_idx').on(t.provider, t.repository, t.headSha),
  ],
);

export const issueDevelopmentCheck = pgTable(
  'issue_development_check',
  {
    id: serial('id').primaryKey(),
    developmentLinkId: integer('development_link_id')
      .notNull()
      .references(() => issueDevelopmentLink.id, { onDelete: 'cascade' }),
    externalId: text('external_id').notNull(),
    appId: text('app_id').notNull(),
    name: text('name').notNull(),
    status: text('status').notNull(),
    url: text('url'),
    headSha: text('head_sha').notNull(),
    updatedAt: timestamp('updated_at', { withTimezone: true }).notNull().defaultNow(),
  },
  (t) => [
    unique().on(t.developmentLinkId, t.appId, t.name),
    index('issue_development_check_link_sha_idx').on(t.developmentLinkId, t.headSha),
  ],
);

// A file uploaded in an agent chat. Bytes live in the S3-compatible object store;
// this table holds the metadata and the object key. public_id is the unguessable
// id used in the public download URL. Kept free of any workflow state so an
// upload can serve more than one purpose (an issue import, a spec, a log).
export const chatAttachment = pgTable(
  'chat_attachment',
  {
    id: serial('id').primaryKey(),
    publicId: uuid('public_id').notNull().defaultRandom().unique(),
    projectId: integer('project_id')
      .notNull()
      .references(() => project.id, { onDelete: 'cascade' }),
    uploadedByUserId: text('uploaded_by_user_id').references(() => user.id, {
      onDelete: 'set null',
    }),
    s3Key: text('s3_key').notNull(),
    filename: text('filename').notNull(),
    contentType: text('content_type').notNull(),
    sizeBytes: bigint('size_bytes', { mode: 'number' }).notNull(),
    createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
  },
  (t) => [index('chat_attachment_project_idx').on(t.projectId)],
);

// An import of issues from a chat attachment: the column mapping an agent saved
// and the state of the draft. The file itself is the referenced chat_attachment;
// creating the issues happens only through the confirm route, never by the model
// itself.
export const issueImport = pgTable(
  'issue_import',
  {
    id: serial('id').primaryKey(),
    publicId: uuid('public_id').notNull().defaultRandom().unique(),
    projectId: integer('project_id')
      .notNull()
      .references(() => project.id, { onDelete: 'cascade' }),
    attachmentId: integer('attachment_id')
      .notNull()
      .references(() => chatAttachment.id, { onDelete: 'cascade' }),
    // mapped: an agent saved a column mapping. confirmed: the issues were
    // created. canceled and failed are terminal, with errorText on a failure.
    status: text('status').notNull().default('mapped'),
    mapping: jsonb('mapping'),
    errorText: text('error_text'),
    createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
    updatedAt: timestamp('updated_at', { withTimezone: true }).notNull().defaultNow(),
  },
  (t) => [index('issue_import_project_idx').on(t.projectId)],
);

// Checklists on an issue: a lightweight list of steps that does not warrant a
// subtask of its own. An issue holds several checklists, each ordered by position
// among the issue's checklists.
export const issueChecklist = pgTable(
  'issue_checklist',
  {
    id: serial('id').primaryKey(),
    issueId: integer('issue_id')
      .notNull()
      .references(() => issue.id, { onDelete: 'cascade' }),
    title: text('title').notNull(),
    position: doublePrecision('position').notNull().default(0),
    createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
  },
  (t) => [index('issue_checklist_issue_idx').on(t.issueId, t.position)],
);

// One checkbox line of a checklist, ordered by position within its checklist.
export const issueChecklistItem = pgTable(
  'issue_checklist_item',
  {
    id: serial('id').primaryKey(),
    checklistId: integer('checklist_id')
      .notNull()
      .references(() => issueChecklist.id, { onDelete: 'cascade' }),
    content: text('content').notNull(),
    done: boolean('done').notNull().default(false),
    position: doublePrecision('position').notNull().default(0),
    createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
  },
  (t) => [index('issue_checklist_item_checklist_idx').on(t.checklistId, t.position)],
);

// The time a member spent on an issue, one row per entry. The time an issue took is
// the sum of its entries and the time left is the estimate minus that sum; neither
// is stored, since two numbers kept next to the entries drift apart. Each entry
// belongs to the member who logged it, so several people log their own days on the
// same issue and one member's correction never overwrites another's.
export const issueWorklog = pgTable(
  'issue_worklog',
  {
    id: serial('id').primaryKey(),
    issueId: integer('issue_id')
      .notNull()
      .references(() => issue.id, { onDelete: 'cascade' }),
    // Who logged the entry. Their entries go with the account when it is deleted.
    userId: text('user_id')
      .notNull()
      .references(() => user.id, { onDelete: 'cascade' }),
    minutes: integer('minutes').notNull(),
    // The day the work happened on, which is not the day it was logged: a member
    // enters yesterday's work today.
    spentOn: date('spent_on').notNull(),
    note: text('note'),
    createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
  },
  (t) => [
    check('issue_worklog_minutes_check', sql`${t.minutes} > 0`),
    // Backs the entries of an issue, read newest day first, and the sums the issue
    // payload carries.
    index('issue_worklog_issue_idx').on(t.issueId, t.spentOn.desc()),
  ],
);

// One side of a change: the text the feed shows, and the id of the row behind it
// when the side names one. The text is a snapshot, so an entry still reads
// correctly after that row is renamed or deleted; the id is what makes the entry
// readable as data. Some actions carry more: a status change carries the state
// type its column had at the time, a pull request its repository and number.
export interface ActivitySide {
  value: string | null;
  id?: number | string | null;
  stateType?: string | null;
  repo?: string;
  number?: number;
  // A 'worklog' side carries the day its time was spent on: a change of an entry
  // can move it to another day.
  date?: string | null;
}

// What an activity entry says changed. `subject` names the sub-item the action
// applies to (the custom field for 'field', the checklist for a checklist item);
// `from` and `to` are the two sides of the change. A side the action does not
// have is absent, and an action that describes nothing carries {}.
export interface ActivityPayload {
  subject?: ActivitySide;
  from?: ActivitySide;
  to?: ActivitySide;
}

// Timeline of comments and change-log activity for issues and initiatives, in one
// table. Each row belongs to exactly one owner: an issue (issue_id) or an
// initiative (initiative_id) — enforced by owner_check. kind selects which columns
// a row uses — a comment sets body; activity sets action and payload.
// actor_user_id is the author, taken from the session user (a member or an
// agent's bot user). actor_name is a snapshot so an entry still reads correctly
// after that user is renamed or deleted.
export const issueActivity = pgTable(
  'issue_activity',
  {
    id: serial('id').primaryKey(),
    issueId: integer('issue_id').references(() => issue.id, {
      onDelete: 'cascade',
    }),
    initiativeId: integer('initiative_id').references(() => initiative.id, {
      onDelete: 'cascade',
    }),
    kind: text('kind').notNull(),
    // The comment this one replies to, for the threaded feed. Only comments carry
    // it, and the parent belongs to the same issue. Deleting a comment deletes its
    // replies with it.
    replyToId: integer('reply_to_id').references((): AnyPgColumn => issueActivity.id, {
      onDelete: 'cascade',
    }),
    actorUserId: text('actor_user_id').references(() => user.id, {
      onDelete: 'set null',
    }),
    actorName: text('actor_name'),
    body: text('body'),
    action: text('action'),
    payload: jsonb('payload').$type<ActivityPayload>().notNull().default({}),
    // Set when a comment is edited; null on entries never changed.
    editedAt: timestamp('edited_at', { withTimezone: true }),
    createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
  },
  (t) => [
    check('issue_activity_kind_check', sql`${t.kind} IN ('comment', 'activity')`),
    // Exactly one owner: an issue row or an initiative row, never both or neither.
    check(
      'issue_activity_owner_check',
      sql`(${t.issueId} IS NOT NULL) <> (${t.initiativeId} IS NOT NULL)`,
    ),
    index('issue_activity_issue_idx').on(t.issueId, t.createdAt.desc(), t.id.desc()),
    index('issue_activity_reply_idx').on(t.replyToId),
    index('issue_activity_initiative_idx').on(t.initiativeId, t.createdAt.desc(), t.id.desc()),
  ],
);

// Saved views (the tabs above a project's work items view). filters and display are jsonb
// blobs owned by the UI; the server stores and returns them without inspecting
// them. position orders the tabs.
export const projectView = pgTable(
  'project_view',
  {
    id: serial('id').primaryKey(),
    projectId: integer('project_id')
      .notNull()
      .references(() => project.id, { onDelete: 'cascade' }),
    name: text('name').notNull(),
    icon: text('icon'),
    filters: jsonb('filters').notNull().default({}),
    display: jsonb('display').notNull().default({}),
    position: doublePrecision('position').notNull().default(0),
    // Unguessable token for the public read-only share link of this view. NULL
    // means not shared; setting it enables the link, clearing it revokes access.
    shareToken: uuid('share_token').unique(),
    // How much of each issue the share link exposes. False keeps the public
    // payload to the issue's title, description, state, type, priority, dates,
    // subtasks and links; true adds the assignees, labels, custom fields and
    // activity the members see.
    shareExtended: boolean('share_extended').notNull().default(false),
    // When the share link stops working; NULL means never. See issue.shareExpiresAt.
    shareExpiresAt: timestamp('share_expires_at', { withTimezone: true }),
    // A private view is seen only by its owner, not even by a project owner. The owner
    // is set only on private views, so deleting an account takes its private views and
    // never a project's.
    isPrivate: boolean('is_private').notNull().default(false),
    ownerUserId: text('owner_user_id').references(() => user.id, { onDelete: 'cascade' }),
    createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
  },
  (t) => [
    index('project_view_project_idx').on(t.projectId, t.position),
    index('project_view_owner_idx').on(t.ownerUserId),
    check('project_view_private_owner_check', sql`${t.isPrivate} = (${t.ownerUserId} is not null)`),
  ],
);

// The saved views a user marked as favorite. Favorites are personal: they mark the
// view's tab with a star and list it under Work items in the sidebar, for that user
// only.
export const projectViewFavorite = pgTable(
  'project_view_favorite',
  {
    viewId: integer('view_id')
      .notNull()
      .references(() => projectView.id, { onDelete: 'cascade' }),
    userId: text('user_id')
      .notNull()
      .references(() => user.id, { onDelete: 'cascade' }),
  },
  (t) => [primaryKey({ columns: [t.viewId, t.userId] })],
);

// Each user's own tab row: where they placed a view and whether they hid it. A view
// without a position follows the last view of its kind in the row, so a view added to the
// project shows up in rows already arranged. Only a project's view can be hidden.
export const projectViewPreference = pgTable(
  'project_view_preference',
  {
    viewId: integer('view_id')
      .notNull()
      .references(() => projectView.id, { onDelete: 'cascade' }),
    userId: text('user_id')
      .notNull()
      .references(() => user.id, { onDelete: 'cascade' }),
    position: integer('position'),
    isHidden: boolean('is_hidden').notNull().default(false),
  },
  (t) => [primaryKey({ columns: [t.viewId, t.userId] })],
);

// Saved dashboards (the analytics tabs of a project). layout is a jsonb blob
// owned by the UI: an ordered list of widget entries, each carrying its type,
// width, title, and widget-specific config. The server stores and returns it
// without inspecting it. position orders the tabs.
export const projectDashboard = pgTable(
  'project_dashboard',
  {
    id: serial('id').primaryKey(),
    projectId: integer('project_id')
      .notNull()
      .references(() => project.id, { onDelete: 'cascade' }),
    name: text('name').notNull(),
    icon: text('icon'),
    layout: jsonb('layout').notNull().default([]),
    position: doublePrecision('position').notNull().default(0),
    createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
  },
  (t) => [index('project_dashboard_project_idx').on(t.projectId, t.position)],
);

// Shared Markdown pages arranged as a tree. Version rejects stale autosaves.
// Private pages are visible only to their owner; project permissions still gate
// access before that page-level rule is applied.
export const projectDocument = pgTable(
  'project_document',
  {
    id: serial('id').primaryKey(),
    projectId: integer('project_id')
      .notNull()
      .references(() => project.id, { onDelete: 'cascade' }),
    parentId: integer('parent_id').references((): AnyPgColumn => projectDocument.id, {
      onDelete: 'set null',
    }),
    title: text('title').notNull().default(''),
    content: text('content').notNull().default(''),
    contentJson: jsonb('content_json').$type<Record<string, unknown>>(),
    icon: text('icon'),
    metadata: jsonb('metadata').$type<Record<string, unknown>>().notNull().default({}),
    fullWidth: boolean('full_width').notNull().default(false),
    isPrivate: boolean('is_private').notNull().default(false),
    isLocked: boolean('is_locked').notNull().default(false),
    archivedAt: timestamp('archived_at', { withTimezone: true }),
    archivedByAncestorId: integer('archived_by_ancestor_id').references(
      (): AnyPgColumn => projectDocument.id,
      { onDelete: 'set null' },
    ),
    position: doublePrecision('position').notNull().default(0),
    version: integer('version').notNull().default(1),
    ownerUserId: text('owner_user_id').references(() => user.id, {
      onDelete: 'set null',
    }),
    createdByUserId: text('created_by_user_id').references(() => user.id, {
      onDelete: 'set null',
    }),
    updatedByUserId: text('updated_by_user_id').references(() => user.id, {
      onDelete: 'set null',
    }),
    // Unguessable token for the public read-only share link of this page. NULL
    // means not shared; setting it enables the link, clearing it revokes access.
    shareToken: uuid('share_token').unique(),
    // When the share link stops working; NULL means never. See issue.shareExpiresAt.
    shareExpiresAt: timestamp('share_expires_at', { withTimezone: true }),
    // Whether the share link also opens the page's subpages.
    shareIncludeChildren: boolean('share_include_children').notNull().default(false),
    // Whether the shared pages show the title and state of the issues they name.
    shareIncludeIssues: boolean('share_include_issues').notNull().default(false),
    createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
    updatedAt: timestamp('updated_at', { withTimezone: true }).notNull().defaultNow(),
  },
  (t) => [
    index('project_document_project_tree_idx').on(t.projectId, t.parentId, t.position, t.id),
    index('project_document_owner_idx').on(t.ownerUserId),
    index('project_document_project_archive_idx').on(t.projectId, t.archivedAt),
    check('project_document_version_check', sql`${t.version} > 0`),
  ],
);

// Immutable snapshots of every persisted document version. A database trigger
// fills this table so project copies and bulk tree operations receive the same
// history guarantees as writes made through the Documents API.
export const projectDocumentRevision = pgTable(
  'project_document_revision',
  {
    id: serial('id').primaryKey(),
    documentId: integer('document_id')
      .notNull()
      .references(() => projectDocument.id, { onDelete: 'cascade' }),
    version: integer('version').notNull(),
    parentId: integer('parent_id'),
    title: text('title').notNull(),
    content: text('content').notNull(),
    contentJson: jsonb('content_json').$type<Record<string, unknown>>(),
    icon: text('icon'),
    metadata: jsonb('metadata').$type<Record<string, unknown>>().notNull().default({}),
    fullWidth: boolean('full_width').notNull().default(false),
    isPrivate: boolean('is_private').notNull().default(false),
    isLocked: boolean('is_locked').notNull().default(false),
    archivedAt: timestamp('archived_at', { withTimezone: true }),
    position: doublePrecision('position').notNull(),
    ownerUserId: text('owner_user_id').references(() => user.id, {
      onDelete: 'set null',
    }),
    createdByUserId: text('created_by_user_id').references(() => user.id, {
      onDelete: 'set null',
    }),
    createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
  },
  (t) => [
    unique('project_document_revision_document_version_unique').on(t.documentId, t.version),
    index('project_document_revision_document_idx').on(t.documentId, t.version),
  ],
);

// Per-user page preferences. Keeping favorites outside the shared page row means
// one person's sidebar choices never affect another project member.
export const projectDocumentPreference = pgTable(
  'project_document_preference',
  {
    documentId: integer('document_id')
      .notNull()
      .references(() => projectDocument.id, { onDelete: 'cascade' }),
    userId: text('user_id')
      .notNull()
      .references(() => user.id, { onDelete: 'cascade' }),
    isFavorite: boolean('is_favorite').notNull().default(false),
    updatedAt: timestamp('updated_at', { withTimezone: true }).notNull().defaultNow(),
  },
  (t) => [
    primaryKey({ columns: [t.documentId, t.userId] }),
    index('project_document_preference_user_idx').on(t.userId),
  ],
);

// Explicit links between Docs pages and work items. The relation is intentionally
// separate from page content: renaming either side keeps the link intact, one page
// can provide context for several work items, and one work item can collect several
// specs or runbooks. The API verifies that both ends belong to the same project.
export const projectDocumentIssue = pgTable(
  'project_document_issue',
  {
    documentId: integer('document_id')
      .notNull()
      .references(() => projectDocument.id, { onDelete: 'cascade' }),
    issueId: integer('issue_id')
      .notNull()
      .references(() => issue.id, { onDelete: 'cascade' }),
    createdByUserId: text('created_by_user_id').references(() => user.id, {
      onDelete: 'set null',
    }),
    createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
  },
  (t) => [
    primaryKey({ columns: [t.documentId, t.issueId] }),
    index('project_document_issue_issue_idx').on(t.issueId, t.documentId),
  ],
);

// Docs pages linked to an initiative. Mirrors project_document_issue; both sides
// must belong to the same project, which a trigger enforces.
export const projectDocumentInitiative = pgTable(
  'project_document_initiative',
  {
    documentId: integer('document_id')
      .notNull()
      .references(() => projectDocument.id, { onDelete: 'cascade' }),
    initiativeId: integer('initiative_id')
      .notNull()
      .references(() => initiative.id, { onDelete: 'cascade' }),
    createdByUserId: text('created_by_user_id').references(() => user.id, {
      onDelete: 'set null',
    }),
    createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
  },
  (t) => [
    primaryKey({ columns: [t.documentId, t.initiativeId] }),
    index('project_document_initiative_initiative_idx').on(t.initiativeId, t.documentId),
  ],
);

// Files embedded in Docs pages. Bytes use the same S3-compatible object store as
// issue/chat attachments; only authenticated document routes and the live share link
// of a page expose them, so an unguessable asset id alone never acts as a public
// capability URL.
export const documentAsset = pgTable(
  'document_asset',
  {
    id: serial('id').primaryKey(),
    publicId: uuid('public_id').notNull().defaultRandom().unique(),
    documentId: integer('document_id')
      .notNull()
      .references(() => projectDocument.id, { onDelete: 'cascade' }),
    uploadedByUserId: text('uploaded_by_user_id').references(() => user.id, {
      onDelete: 'set null',
    }),
    s3Key: text('s3_key').notNull(),
    filename: text('filename').notNull(),
    contentType: text('content_type').notNull(),
    sizeBytes: bigint('size_bytes', { mode: 'number' }).notNull(),
    createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
  },
  (t) => [index('document_asset_document_idx').on(t.documentId, t.createdAt)],
);

// Note boards: a freeform canvas of sticky notes. canvas is a jsonb blob owned by
// the UI (React Flow nodes + edges + viewport), stored and returned verbatim.
// owner_user_id NULL means a public board visible to every project member; a set
// owner_user_id means a private board, seen by its owner and by the members listed
// in note_board_member. Only the creator (created_by_user_id) may change any of it.
export const noteBoard = pgTable(
  'note_board',
  {
    id: serial('id').primaryKey(),
    projectId: integer('project_id')
      .notNull()
      .references(() => project.id, { onDelete: 'cascade' }),
    ownerUserId: text('owner_user_id').references(() => user.id, { onDelete: 'cascade' }),
    createdByUserId: text('created_by_user_id').references(() => user.id, { onDelete: 'set null' }),
    name: text('name').notNull(),
    canvas: jsonb('canvas').notNull().default({}),
    createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
    updatedAt: timestamp('updated_at', { withTimezone: true }).notNull().defaultNow(),
  },
  // Listed by updatedAt within a project; the index covers the project filter.
  (t) => [index('note_board_project_idx').on(t.projectId, t.updatedAt)],
);

// The members granted access to a private board besides its owner. A private board
// with at least one row here is what the UI calls "restricted".
export const noteBoardMember = pgTable(
  'note_board_member',
  {
    boardId: integer('board_id')
      .notNull()
      .references(() => noteBoard.id, { onDelete: 'cascade' }),
    userId: text('user_id')
      .notNull()
      .references(() => user.id, { onDelete: 'cascade' }),
  },
  // The board list filters by the viewer, so it reads this table by user first.
  (t) => [
    primaryKey({ columns: [t.boardId, t.userId] }),
    index('note_board_member_user_idx').on(t.userId),
  ],
);

// Manual actions: saved macros on a project. condition is a filter set deciding
// which issues the action applies to (empty = always); effect is a partial
// issue patch applied in one update. Both jsonb blobs are owned by the UI.
export const projectAction = pgTable(
  'project_action',
  {
    id: serial('id').primaryKey(),
    projectId: integer('project_id')
      .notNull()
      .references(() => project.id, { onDelete: 'cascade' }),
    name: text('name').notNull(),
    // Icon key for the action, resolved to a lucide icon by the UI (empty = default).
    icon: text('icon').notNull().default(''),
    condition: jsonb('condition').notNull().default({}),
    effect: jsonb('effect').notNull().default({}),
    position: doublePrecision('position').notNull().default(0),
    createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
  },
  (t) => [index('project_action_project_idx').on(t.projectId, t.position)],
);

// Outgoing webhook subscription. On a subscribed event the API posts the event
// payload to `url`, signed with `secret` (HMAC-SHA256). `events` is the list of
// event types this subscription wants (e.g. "issue.created"); `is_active` gates
// delivery without deleting the row. Delivery itself is handled separately.
export const webhook = pgTable(
  'webhook',
  {
    id: serial('id').primaryKey(),
    projectId: integer('project_id')
      .notNull()
      .references(() => project.id, { onDelete: 'cascade' }),
    url: text('url').notNull(),
    secret: text('secret').notNull(),
    events: jsonb('events').notNull().default([]),
    isActive: boolean('is_active').notNull().default(true),
    // Count of consecutive failed deliveries. Reset to 0 on a successful delivery.
    // When it crosses the worker's threshold the webhook is auto-disabled
    // (is_active set false) so one dead endpoint cannot occupy the worker.
    consecutiveFailures: integer('consecutive_failures').notNull().default(0),
    createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
  },
  (t) => [index('webhook_project_idx').on(t.projectId)],
);

// Delivery queue (transactional outbox) for webhooks. One row per (event ×
// subscribed webhook), inserted in the same transaction as the domain change so
// it is atomic with it. The worker claims due rows, posts the payload to the
// webhook, and records the outcome. event_id is stable across retries so the
// receiver can deduplicate. status: pending | success | failed.
export const webhookDelivery = pgTable(
  'webhook_delivery',
  {
    id: serial('id').primaryKey(),
    webhookId: integer('webhook_id')
      .notNull()
      .references(() => webhook.id, { onDelete: 'cascade' }),
    eventId: uuid('event_id').notNull(),
    eventType: text('event_type').notNull(),
    payload: jsonb('payload').notNull(),
    status: text('status').notNull().default('pending'),
    attempts: integer('attempts').notNull().default(0),
    nextAttemptAt: timestamp('next_attempt_at', { withTimezone: true }).notNull().defaultNow(),
    lastError: text('last_error'),
    // Response from the last delivery attempt, for the delivery history view.
    responseStatus: integer('response_status'),
    responseBody: text('response_body'),
    createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
  },
  (t) => [
    // Backs the worker's claim query: due pending rows ordered by next_attempt_at.
    index('webhook_delivery_due_idx')
      .on(t.nextAttemptAt)
      .where(sql`${t.status} = 'pending'`),
    index('webhook_delivery_webhook_idx').on(t.webhookId),
  ],
);

// Background job that imports issues from an external tracker into a project. The
// worker claims due rows the same way as webhook_delivery, and cursor is the
// job's own per-phase resumability checkpoint, not the source API's pagination
// cursor. The credential columns are cleared once the job reaches a terminal
// status. `source` names a source adapter of the worker (apps/worker/src/import-sources.ts);
// the database takes any name, so a build can add sources of its own.

export const importJob = pgTable(
  'import_job',
  {
    id: serial('id').primaryKey(),
    projectId: integer('project_id')
      .notNull()
      .references(() => project.id, { onDelete: 'cascade' }),
    createdByUserId: text('created_by_user_id')
      .notNull()
      .references(() => user.id, { onDelete: 'cascade' }),
    source: text('source').notNull(),
    phase: text('phase').notNull().default('discover'),
    status: text('status').notNull().default('pending'),
    config: jsonb('config').notNull().default({}),
    cursor: jsonb('cursor').notNull().default({}),
    credentialCiphertext: text('credential_ciphertext'),
    credentialIv: text('credential_iv'),
    credentialAuthTag: text('credential_auth_tag'),
    attempts: integer('attempts').notNull().default(0),
    nextAttemptAt: timestamp('next_attempt_at', { withTimezone: true }).notNull().defaultNow(),
    lastError: text('last_error'),
    createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
    updatedAt: timestamp('updated_at', { withTimezone: true }).notNull().defaultNow(),
  },
  (t) => [
    check(
      'import_job_phase_check',
      sql`${t.phase} IN ('discover', 'create', 'link', 'rewrite', 'attachments', 'done')`,
    ),
    check(
      'import_job_status_check',
      sql`${t.status} IN ('pending', 'running', 'paused', 'completed', 'failed')`,
    ),
    index('import_job_project_idx').on(t.projectId),
    // Backs the worker's claim query: due pending rows ordered by next_attempt_at.
    index('import_job_due_idx')
      .on(t.nextAttemptAt)
      .where(sql`${t.status} = 'pending'`),
  ],
);

// Source-id-to-local-id mapping for one import job. The unique index on
// (import_job_id, source_entity_type, source_id) makes the upsert idempotent, so
// a re-run of a phase finds the existing row instead of creating another. No
// foreign key on local_id: it spans every table an import can create, not one.
export const importRecord = pgTable(
  'import_record',
  {
    id: serial('id').primaryKey(),
    importJobId: integer('import_job_id')
      .notNull()
      .references(() => importJob.id, { onDelete: 'cascade' }),
    sourceEntityType: text('source_entity_type').notNull(),
    sourceId: text('source_id').notNull(),
    // The source's own human-readable number for the entity (Plane's work item
    // sequence_id, as a string) — only set for 'issue' rows, at Create time. Lets
    // the Rewrite phase resolve a cross-reference like "ROOMS-524" back to this
    // job's mapping without re-fetching every issue a second time to learn it.
    sourceDisplayId: text('source_display_id'),
    localEntityType: text('local_entity_type'),
    localId: integer('local_id'),
    createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
  },
  (t) => [
    check(
      'import_record_source_entity_type_check',
      sql`${t.sourceEntityType} IN ('issue', 'comment', 'label', 'state', 'cycle', 'attachment', 'checklist', 'custom_field')`,
    ),
    check(
      'import_record_local_entity_type_check',
      sql`${t.localEntityType} IS NULL OR ${t.localEntityType} IN ('issue', 'comment', 'label', 'state', 'cycle', 'attachment', 'checklist', 'custom_field')`,
    ),
    uniqueIndex('import_record_job_source_uq').on(t.importJobId, t.sourceEntityType, t.sourceId),
    index('import_record_job_local_idx').on(t.importJobId, t.localEntityType, t.localId),
    // Backs the Rewrite phase's cross-reference lookup (job, entity type, display id).
    index('import_record_job_display_idx').on(t.importJobId, t.sourceEntityType, t.sourceDisplayId),
  ],
);

// Per-user inbox notifications. One row per (recipient, event): a user is notified
// about an issue they are involved in (assigned to them, mentioned, or watching it
// when it is commented on or moved), or about a document that mentions them in its
// text or in a comment. A row points at either an issue or a document. The actor's own
// actions never notify the actor. project_id is denormalized from the issue or the
// document so the inbox can filter and scope by project. source_activity_id points at
// the issue_activity row that produced the notification, document_comment_id at the
// document comment (each set null if that entry is later removed). type selects the kind.
// read_at NULL means unread; snoozed_until, when set and still in the future, hides
// the row from the default inbox until then.
export const notification = pgTable(
  'notification',
  {
    id: serial('id').primaryKey(),
    userId: text('user_id')
      .notNull()
      .references(() => user.id, { onDelete: 'cascade' }),
    projectId: integer('project_id')
      .notNull()
      .references(() => project.id, { onDelete: 'cascade' }),
    issueId: integer('issue_id').references(() => issue.id, { onDelete: 'cascade' }),
    sourceActivityId: integer('source_activity_id').references(() => issueActivity.id, {
      onDelete: 'set null',
    }),
    documentId: integer('document_id').references(() => projectDocument.id, {
      onDelete: 'cascade',
    }),
    documentCommentId: uuid('document_comment_id').references(() => documentComment.id, {
      onDelete: 'set null',
    }),
    type: text('type').notNull(),
    actorUserId: text('actor_user_id').references(() => user.id, {
      onDelete: 'set null',
    }),
    actorName: text('actor_name'),
    readAt: timestamp('read_at', { withTimezone: true }),
    snoozedUntil: timestamp('snoozed_until', { withTimezone: true }),
    createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
  },
  (t) => [
    check(
      'notification_type_check',
      sql`${t.type} IN ('assigned', 'mentioned', 'commented', 'state_changed')`,
    ),
    check('notification_target_check', sql`(${t.issueId} IS NULL) <> (${t.documentId} IS NULL)`),
    // Backs the inbox list: a user's notifications newest first.
    index('notification_user_idx').on(t.userId, t.createdAt.desc(), t.id.desc()),
    // Backs the unread count and the unread-only inbox view.
    index('notification_user_unread_idx')
      .on(t.userId, t.id.desc())
      .where(sql`${t.readAt} IS NULL`),
    // Back the cascades of an issue or comment delete.
    index('notification_issue_idx')
      .on(t.issueId)
      .where(sql`${t.issueId} IS NOT NULL`),
    index('notification_source_activity_idx')
      .on(t.sourceActivityId)
      .where(sql`${t.sourceActivityId} IS NOT NULL`),
  ],
);

// Change markers for live refresh: one counter per scope, bumped by the triggers
// in migration 0070 on every write to the tables that scope covers. A scope names
// what a screen watches — 'board:<projectId>', 'issue:<issueId>',
// 'initiative:<initiativeId>', 'inbox:<projectId>:<userId>'. Clients poll the
// counters they watch and refetch the matching queries when one moves; the value
// itself is opaque to them, only equality matters.
//
// project_id carries no foreign key on purpose. Deleting a project cascades into
// its issues, and each of those deletes fires a trigger that inserts a row here
// for a project that is already gone — a foreign key would abort the delete. The
// rows are removed by the trigger on project instead.
export const revision = pgTable(
  'revision',
  {
    scope: text('scope').primaryKey(),
    projectId: integer('project_id').notNull(),
    rev: bigint('rev', { mode: 'number' }).notNull().default(0),
  },
  // Backs both the project cleanup and the membership join the read does.
  (t) => [index('revision_project_idx').on(t.projectId)],
);

export const documentCollaboration = pgTable('document_collaboration', {
  documentId: integer('document_id')
    .primaryKey()
    .references(() => projectDocument.id, { onDelete: 'cascade' }),
  epoch: uuid('epoch').notNull().defaultRandom(),
  version: integer('version').notNull().default(0),
  contentJson: jsonb('content_json').$type<Record<string, unknown>>().notNull(),
});

export const documentStep = pgTable(
  'document_step',
  {
    documentId: integer('document_id')
      .notNull()
      .references(() => projectDocument.id, { onDelete: 'cascade' }),
    version: integer('version').notNull(),
    clientId: text('client_id').notNull(),
    step: jsonb('step').$type<Record<string, unknown>>().notNull(),
  },
  (t) => [primaryKey({ columns: [t.documentId, t.version] })],
);

export const documentComment = pgTable(
  'document_comment',
  {
    id: uuid('id').primaryKey().defaultRandom(),
    documentId: integer('document_id')
      .notNull()
      .references(() => projectDocument.id, { onDelete: 'cascade' }),
    parentId: uuid('parent_id').references((): AnyPgColumn => documentComment.id, {
      onDelete: 'cascade',
    }),
    authorId: text('author_id').references(() => user.id, { onDelete: 'set null' }),
    body: text('body').notNull(),
    quote: text('quote').notNull().default(''),
    from: integer('selection_from'),
    to: integer('selection_to'),
    orphaned: boolean('orphaned').notNull().default(false),
    resolvedAt: timestamp('resolved_at', { withTimezone: true }),
    createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
    updatedAt: timestamp('updated_at', { withTimezone: true }).notNull().defaultNow(),
  },
  (t) => [index('document_comment_document_idx').on(t.documentId, t.createdAt)],
);