Skip to content

Open-source AI coworkers that each get a computer of their own: a browser, files and tools, with every action decided before it happens and recorded after. Bring any AG-UI agent.

25.4 KB
Raw Blame
import type { UserPreferences } from "@openbot/shared/user-preferences";
import { sql } from "drizzle-orm";
import {
  boolean,
  customType,
  index,
  integer,
  pgEnum,
  pgTable,
  primaryKey,
  text,
  timestamp,
  uniqueIndex,
  uuid,
} from "drizzle-orm/pg-core";
// NOT drizzle's `jsonb`: that one serialises, and so does the driver, so every object landed as a
// JSON string and nothing in this database could be queried by a JSON field. See ./json.ts.
import { jsonb } from "./json";

const bytea = customType<{ data: Buffer; driverData: Buffer }>({
  dataType: () => "bytea",
});

const createdAt = () =>
  timestamp("created_at", { withTimezone: true }).notNull().defaultNow();
const updatedAt = () =>
  timestamp("updated_at", { withTimezone: true }).notNull().defaultNow();

export const role = pgEnum("role", ["admin", "user"]);
export const agentType = pgEnum("agent_type", [
  "built_in",
  "remote_ag_ui",
  // A Mastra server, reached through `@ag-ui/mastra` rather than an AG-UI route of its own. Governed
  // identically: the difference ends at `remoteTransport`. See migration 0028.
  "remote_mastra",
]);
export const credentialKind = pgEnum("credential_kind", [
  "model",
  "connector",
  // A customer's own agent behind a key. Its own kind so "what does this deployment hold" stays true.
  "agent",
  // A token for an MCP server. Same vault and same revocation as everything else, so the server row
  // holds a pointer and never the secret.
  "mcp",
  /*
   * A deployment's OAuth client for an MCP server: the id and the secret an administrator registered
   * with the vendor.
   *
   * Its own kind rather than another `mcp`, because it is a different thing with different reach. A
   * client identifies this deployment to a vendor and can read nobody's data on its own; it is the
   * thing you must have before anybody can consent, and the thing you rotate when it leaks.
   */
  "mcp_oauth_client",
  /*
   * One person's refresh token for one MCP server.
   *
   * The far end of the same flow and the opposite risk: this reaches everything that person can see.
   * Distinct from the client so that "what does this deployment hold" stays answerable — one row
   * that speaks for the deployment, and one row per person that speaks for them.
   */
  "mcp_user_token",
]);

export const users = pgTable("users", {
  id: text("id").primaryKey(),
  email: text("email").notNull().unique(),
  name: text("name"),
  image: text("image"),
  emailVerified: boolean("email_verified").notNull().default(false),
  preferences: jsonb("preferences")
    .$type<Partial<UserPreferences>>()
    .notNull()
    .default({}),
  /**
   * The person's groups, for a group-based rule to be evaluated against.
   *
   * Empty on every row: no sign-in path, claim mapping or admin screen writes this, and nothing
   * reads it. It is the other half of `channels.allowedGroups`, and #82 is about the pair. Anything
   * that starts deciding access on a group has to populate this first, or it decides on an empty
   * list for everybody.
   */
  groups: text("groups").array().notNull().default([]),
  /**
   * Where this person is in first-run onboarding.
   *
   * The step is where the wizard resumes if they leave halfway; the null completion timestamp is
   * what gates the app into /onboarding. Set once — finishing again keeps the first timestamp.
   */
  onboardingStep: integer("onboarding_step").notNull().default(0),
  onboardingCompletedAt: timestamp("onboarding_completed_at", {
    withTimezone: true,
  }),
  lastSignedInAt: timestamp("last_signed_in_at", { withTimezone: true }),
  createdAt: createdAt(),
  updatedAt: updatedAt(),
});

export const sessions = pgTable("sessions", {
  id: text("id").primaryKey(),
  userId: text("user_id")
    .notNull()
    .references(() => users.id, { onDelete: "cascade" }),
  token: text("token").notNull().unique(),
  expiresAt: timestamp("expires_at", { withTimezone: true }).notNull(),
  ipAddress: text("ip_address"),
  userAgent: text("user_agent"),
  createdAt: createdAt(),
  updatedAt: updatedAt(),
});

export const accounts = pgTable(
  "accounts",
  {
    id: text("id").primaryKey(),
    accountId: text("account_id").notNull(),
    providerId: text("provider_id").notNull(),
    /*
     * Who vouched for this account, as the identity provider names itself.
     *
     * Required by Better Auth from 1.7. A real OIDC provider supplies its own
     * (`https://accounts.google.com`), and one without gets a synthetic
     * `local:oauth:<providerId>`, so nothing this deployment writes leaves it empty. It exists
     * because `providerId` alone stopped being enough once a deployment can register more than one
     * OIDC provider: two companies' Okta tenants are both "okta" and are not the same directory.
     *
     * Nullable in the database, deliberately, even though every write fills it. A rolling deploy runs
     * the migrations and then serves from old and new replicas at once, and an old replica inserts an
     * account without this column. Under `NOT NULL` that insert fails, so the release that adds the
     * column would break the first sign-in of everybody who landed on a replica that had not been
     * replaced yet. The constraint belongs to a later release, once no replica predates the column.
     */
    issuer: text("issuer"),
    userId: text("user_id")
      .notNull()
      .references(() => users.id, { onDelete: "cascade" }),
    accessToken: text("access_token"),
    refreshToken: text("refresh_token"),
    idToken: text("id_token"),
    accessTokenExpiresAt: timestamp("access_token_expires_at", {
      withTimezone: true,
    }),
    refreshTokenExpiresAt: timestamp("refresh_token_expires_at", {
      withTimezone: true,
    }),
    scope: text("scope"),
    password: text("password"),
    createdAt: createdAt(),
    updatedAt: updatedAt(),
  },
  (table) => [
    uniqueIndex("accounts_provider_account_idx").on(
      table.providerId,
      table.accountId,
    ),
  ],
);

export const verifications = pgTable("verifications", {
  id: text("id").primaryKey(),
  identifier: text("identifier").notNull(),
  value: text("value").notNull(),
  expiresAt: timestamp("expires_at", { withTimezone: true }).notNull(),
  createdAt: createdAt(),
  updatedAt: updatedAt(),
});

export const userRoles = pgTable(
  "user_roles",
  {
    userId: text("user_id")
      .notNull()
      .references(() => users.id, { onDelete: "cascade" }),
    role: role("role").notNull(),
    createdAt: createdAt(),
  },
  (table) => [primaryKey({ columns: [table.userId, table.role] })],
);

/**
 * One person's standing instructions, applied to every built-in coworker they run.
 *
 * The person-shaped half of a durable instruction. The other two carriers are both about the work
 * rather than about the person: a coworker's role belongs to the coworker and is the same for
 * everybody who talks to it, and a skill is invoked for one task. Neither can say "always write to
 * me in British English" or "we are a two-person company, never call us a team", which is a fact
 * about the person and true in every channel.
 *
 * The user id IS the primary key rather than a column beside a surrogate one. There is exactly one
 * of these per person, and a table that allowed two would make "what are this person's standing
 * instructions" depend on which row a query happened to order first.
 *
 * Empty is absence, not a row: the store deletes on an empty save rather than storing "". A row
 * holding an empty string would be a person with standing instructions that say nothing, which the
 * prompt seam then has to recognise and skip anyway — so there is one representation of "none", and
 * it is having no row.
 */
export const userInstructions = pgTable("user_instructions", {
  userId: text("user_id")
    .primaryKey()
    .references(() => users.id, { onDelete: "cascade" }),
  instructions: text("instructions").notNull(),
  createdAt: createdAt(),
  updatedAt: updatedAt(),
});

/**
 * An enterprise identity provider this deployment has been told about.
 *
 * The other three providers are configuration: one Google, one Entra, one Okta, named in the
 * environment. These are not, because a company's own IdP is not something a deployment can be
 * built knowing. It is registered while running, by an administrator holding the metadata their
 * identity team gave them, and there can be several.
 *
 * `oidcConfig` and `samlConfig` are JSON held as text because Better Auth writes them that way. They
 * carry a client secret or a signing certificate, so nothing here is ever projected to a browser.
 *
 * `domain` is what routes somebody to the right one: they type an email address, and the part after
 * the @ decides which identity provider is asked about them.
 */
export const ssoProviders = pgTable("sso_providers", {
  id: text("id").primaryKey(),
  issuer: text("issuer").notNull(),
  oidcConfig: text("oidc_config"),
  samlConfig: text("saml_config"),
  /**
   * Who registered it, as a note rather than as an owner.
   *
   * Better Auth writes this and then scopes its own listing and delete routes to it, which is the
   * right model for a personal integration and the wrong one here: a company's Okta tenant does not
   * belong to whichever administrator pasted the metadata in. Reads and removals go through
   * identity-provider-store.ts instead, against the whole table.
   *
   * `set null` and not `cascade`. Under cascade, removing the person who set sign-in up deleted the
   * deployment's sign-in with them, which is a bad afternoon for a company whose IT lead has left.
   */
  userId: text("user_id").references(() => users.id, { onDelete: "set null" }),
  providerId: text("provider_id").notNull().unique(),
  organizationId: text("organization_id"),
  domain: text("domain").notNull(),
});

/**
 * People an administrator has removed, by email address.
 *
 * Keyed on the address rather than the user id, because deleting the user row is not removal: the
 * next sign-in through the identity provider creates it again, with a fresh id and no memory of
 * having been removed. The address is the only thing that survives that.
 *
 * Lower-cased on the way in, since a provider is free to return whatever case it likes and two rows
 * differing only in case would be one person with one of them enforced.
 */
export const revokedAccess = pgTable("revoked_access", {
  email: text("email").primaryKey(),
  revokedAt: timestamp("revoked_at", { withTimezone: true })
    .notNull()
    .defaultNow(),
  /** Who did it, for the trail. Not a foreign key: an administrator may later be removed too. */
  revokedBy: text("revoked_by").notNull(),
});

export const deploymentPackages = pgTable("deployment_packages", {
  id: uuid("id").primaryKey().defaultRandom(),
  tenantId: text("tenant_id").notNull().unique(),
  sourcePath: text("source_path").notNull(),
  checksum: text("checksum").notNull(),
  loadedAt: timestamp("loaded_at", { withTimezone: true })
    .notNull()
    .defaultNow(),
});

export const agents = pgTable("agents", {
  id: text("id").primaryKey(),
  name: text("name").notNull(),
  type: agentType("type").notNull(),
  configuration: jsonb("configuration").notNull(),
  packageId: uuid("package_id").references(() => deploymentPackages.id, {
    onDelete: "set null",
  }),
  override: jsonb("override"),
  createdAt: createdAt(),
  updatedAt: updatedAt(),
});

export const channels = pgTable(
  "channels",
  {
    id: text("id").primaryKey(),
    name: text("name").notNull(),
    description: text("description").notNull(),
    suggestedPrompts: text("suggested_prompts").array().notNull().default([]),
    /**
     * Which groups the tenant package says this channel is for.
     *
     * Written by `synchronizeTenantPackage` and read by nothing. Channel access is membership: every
     * route resolves the caller in `channelMemberships` and refuses without a row, and this column is
     * not consulted on the way. It is not currently a hole, because package channels get no
     * membership rows either and so are unreachable rather than open, but it is a control the name
     * promises and nothing keeps. See #82, and `users.groups`, which is the half that has to arrive
     * from the identity provider before this one can decide anything.
     */
    allowedGroups: text("allowed_groups").array().notNull().default([]),
    packageId: uuid("package_id").references(() => deploymentPackages.id, {
      onDelete: "set null",
    }),
    override: jsonb("override"),
    /** A few words about the conversation. Channel grain like `last_message`; null is ordinary. */
    summary: text("summary"),
    /** When the summary above was written, so a later change can decide whether to redo it. */
    summaryAt: timestamp("summary_at", { withTimezone: true }),
    /**
     * The last thing said in this channel, denormalised so a roster is one indexed read.
     *
     * Channel grain, not per-member: what was said last is a property of the conversation, and a copy
     * per member is the same fact stored N times, drifting. Per-member state, what somebody has read
     *, belongs on the membership instead.
     *
     * Written by whoever ran the agent, from the client that already received the reply, so it is a
     * cache of what a client observed rather than an authoritative mirror of the thread.
     */
    lastMessage: text("last_message"),
    /** Internal source identity for enriching a delayed preview without replacing other activity. */
    lastMessageSourceId: text("last_message_source_id"),
    lastMessageAt: timestamp("last_message_at", { withTimezone: true }),
    /** Which agent spoke, so a channel with several can show the right one. Null for a person. */
    lastMessageAgentId: text("last_message_agent_id").references(
      () => agents.id,
      {
        onDelete: "set null",
      },
    ),
    /**
     * When this channel was deleted, or null. Soft: the row, the transcript, and the Intelligence
     * thread stay intact, and every read path filters on this instead. Channel grain, because
     * deleting is for everyone — per-member hiding would be a membership fact instead.
     */
    deletedAt: timestamp("deleted_at", { withTimezone: true }),
    createdAt: createdAt(),
    updatedAt: updatedAt(),
  },
  (table) => [
    /**
     * The order the channel list is drawn in.
     *
     * On the expression, not on the column, because the list sorts by the last thing said and falls
     * back to when the channel was made. An index on `last_message_at` alone does not serve that
     * ordering, so the sort would fall back to a scan on exactly the query drawn on every page.
     *
     * Declared here rather than only in a migration. An index that exists in the database and not in
     * the schema is invisible to `generate`, so the next generated migration proposes a schema
     * without it and it is silently dropped.
     */
    index("channels_recent_activity_idx").on(
      sql`COALESCE(${table.lastMessageAt}, ${table.createdAt}) DESC`,
    ),
    /**
     * The channels still waiting for a summary.
     *
     * Partial, on the condition rather than the column, because the sweep that offers this work asks
     * for exactly the rows this index holds and nothing else. Every channel that has been summarised
     * leaves the index, so it shrinks as the deployment settles rather than growing with it: a
     * question asked every couple of seconds on every replica should not be a scan of every
     * conversation anybody has ever had.
     */
    index("channels_awaiting_summary_idx")
      .on(table.id)
      .where(sql`${table.summary} is null and ${table.deletedAt} is null`),
  ],
);

export const channelMemberships = pgTable(
  "channel_memberships",
  {
    channelId: text("channel_id")
      .notNull()
      .references(() => channels.id, { onDelete: "cascade" }),
    userId: text("user_id")
      .notNull()
      .references(() => users.id, { onDelete: "cascade" }),
    /**
     * When this member pinned the channel, or null. On the membership, not the channel: a pin is
     * one person's marker, and the membership row is already the per-member half of a channel.
     */
    pinnedAt: timestamp("pinned_at", { withTimezone: true }),
    /**
     * When this member last had the channel open, or null for never. On the membership like the
     * pin: reading is one person's act, and the unread marker it feeds is that person's alone.
     */
    lastReadAt: timestamp("last_read_at", { withTimezone: true }),
    createdAt: createdAt(),
  },
  (table) => [primaryKey({ columns: [table.channelId, table.userId] })],
);

export const channelAgents = pgTable(
  "channel_agents",
  {
    channelId: text("channel_id")
      .notNull()
      .references(() => channels.id, { onDelete: "cascade" }),
    agentId: text("agent_id")
      .notNull()
      .references(() => agents.id, { onDelete: "cascade" }),
    createdAt: createdAt(),
  },
  (table) => [primaryKey({ columns: [table.channelId, table.agentId] })],
);

export const credentials = pgTable(
  "credentials",
  {
    id: uuid("id").primaryKey().defaultRandom(),
    kind: credentialKind("kind").notNull(),
    provider: text("provider").notNull(),
    encryptedValue: text("encrypted_value").notNull(),
    keyId: text("key_id").notNull(),
    metadata: jsonb("metadata").notNull(),
    revokedAt: timestamp("revoked_at", { withTimezone: true }),
    createdAt: createdAt(),
    updatedAt: updatedAt(),
  },
  (table) => [
    // At most one live credential per (kind, provider, key_id). Revoked rows are
    // excluded so history is preserved, and two replicas racing to rotate the
    // same secret cannot both insert a live row.
    uniqueIndex("credentials_active_key_idx")
      .on(table.kind, table.provider, table.keyId)
      .where(sql`${table.revokedAt} IS NULL`),
  ],
);

export const auditEvents = pgTable(
  "audit_events",
  {
    id: uuid("id").primaryKey().defaultRandom(),
    /**
     * Who did it, as an id rather than a reference. No foreign key: the trail is append-only, so any
     * cascade the database wanted to run against it would be an update the trigger refuses, and a
     * user who had done anything could never be deleted.
     */
    actorUserId: text("actor_user_id"),
    /** Defaulted rather than nullable, because every row written before this was a person's. */
    initiatorKind: text("initiator_kind").notNull().default("person"),
    /** Which routine, or which Bot handed the work on. Null when a person started it. */
    initiatorId: text("initiator_id"),
    eventType: text("event_type").notNull(),
    targetType: text("target_type").notNull(),
    targetId: text("target_id"),
    payload: jsonb("payload").notNull(),
    createdAt: createdAt(),
  },
  /*
   * The trail becomes the largest table in the deployment within weeks of real use: every click,
   * keystroke, scroll, tool call, refusal, command and sign-in is a row.
   *
   * One index on `created_at` served the unfiltered screen and nothing else. The audit screen filters
   * by event type, by who did it, and by what it was done to, and every one of those was a sequential
   * scan over the biggest table there is. Each filter therefore leads its own index and carries the
   * sort, so the filtered read is one index scan rather than a scan plus a sort.
   *
   * `id` is in each of them because the keyset pages on `(created_at, id)`: two rows written in the
   * same millisecond are ordered by id, and an index that stopped at the timestamp would leave the
   * tie to be broken by a sort.
   */
  (table) => [
    index("audit_events_created_at_idx").on(table.createdAt),
    index("audit_events_type_time_idx").on(
      table.eventType,
      table.createdAt.desc(),
      table.id.desc(),
    ),
    index("audit_events_actor_time_idx").on(
      table.actorUserId,
      table.createdAt.desc(),
      table.id.desc(),
    ),
    index("audit_events_target_time_idx").on(
      table.targetType,
      table.targetId,
      table.createdAt.desc(),
      table.id.desc(),
    ),
    index("audit_events_initiator_time_idx").on(
      table.initiatorKind,
      table.createdAt.desc(),
      table.id.desc(),
    ),
  ],
);

export const intelligenceChannelMappings = pgTable(
  "intelligence_channel_mappings",
  {
    userId: text("user_id")
      .notNull()
      .references(() => users.id, { onDelete: "cascade" }),
    channelId: text("channel_id")
      .notNull()
      .references(() => channels.id, { onDelete: "cascade" }),
    threadId: text("thread_id").notNull(),
    createdAt: createdAt(),
    updatedAt: updatedAt(),
  },
  (table) => [
    primaryKey({ columns: [table.userId, table.channelId] }),
    uniqueIndex("intelligence_channel_mappings_thread_idx").on(table.threadId),
  ],
);

/**
 * A file somebody attached to a message in a channel.
 *
 * The bytes live here rather than on a disk or in a bucket because this deployment is a compose
 * file: a volume would split the backup story in two, and an object store would put a bucket
 * between a self-hoster and a working install. One `pg_dump` restores a deployment, and that stays
 * true. Reads go through one endpoint, so moving the bytes later changes that endpoint and nothing
 * else.
 */
export const attachments = pgTable(
  "attachments",
  {
    id: uuid("id").primaryKey().defaultRandom(),
    channelId: text("channel_id")
      .notNull()
      .references(() => channels.id, { onDelete: "cascade" }),
    uploadedBy: text("uploaded_by")
      .notNull()
      .references(() => users.id, { onDelete: "cascade" }),
    name: text("name").notNull(),
    /**
     * What the server decided this is, never what the client claimed.
     *
     * A browser will happily report `text/plain` for a file it dragged out of another application,
     * and a client can send whatever it likes. This column is what the fetch endpoint serves as
     * `Content-Type`, so a wrong value here is a security bug rather than a cosmetic one.
     */
    mimeType: text("mime_type").notNull(),
    sizeBytes: integer("size_bytes").notNull(),
    bytes: bytea("bytes").notNull(),
    createdAt: createdAt(),
    /**
     * When this appeared in a sent message. Null means staged.
     *
     * Attach three files, change your mind and close the tab, and those rows would sit here forever
     * with nothing referring to them. The sweeper deletes staged rows past a few hours; a row with a
     * date is spoken for and is never swept.
     */
    attachedAt: timestamp("attached_at", { withTimezone: true }),
    /**
     * Which composer session staged this row, so the per-message cap counts the same set the
     * composer does.
     *
     * NOT A DRAFT ID, and the name is the whole of the distinction. Nothing about the message is
     * saved here: no text, no ordering, nothing that survives a reload. It is a grouping key over
     * rows this table already held, minted fresh by each composer instance and thrown away with it.
     *
     * The cap it exists for is per message, and the client can only ever see what is on its own
     * screen. Counted per channel instead — which is what this server did before this column — a
     * closed tab, a stopped run or a removed queued message left staged rows nobody could see, and
     * the client would then accept a pick the server refused with a 409 naming files that were on
     * nobody's screen. Eight such orphans locked uploads in that channel until the sweeper's
     * 24-hour window expired.
     *
     * NULLABLE, AND DELIBERATELY NOT BACKFILLED. Every row that predates this column has NULL here,
     * `null = <anything>` is never true in SQL, and so those rows match no live group and block no
     * upload. They are still the sweeper's to reclaim on its own schedule.
     *
     * `text` rather than `uuid` even though `newId()` mints a UUID: this value arrives as a form
     * field the browser chose, and comparing text that is not uuid-shaped against a `uuid` column
     * raises `22P02` and throws — the same trap `isUuidShaped` in channels/attachments.ts exists to
     * step around. As text, a nonsense group is simply a group with nothing in it.
     */
    uploadGroup: text("upload_group"),
  },
  (table) => [
    // Postgres does not index foreign key columns on its own. Deleting a
    // channel cascades here, and without this index that cascade is a
    // sequential scan of the one table in this deployment that holds blobs.
    index("attachments_channel_idx").on(table.channelId),
    // The same rationale as `attachments_channel_idx` above, for the other
    // cascading foreign key on this table. Removing a person deletes their
    // `users` row, and that cascade has to find every attachment they ever
    // uploaded; unindexed, it is the same sequential scan over the same blob
    // table, and it runs on the one operation a deployment cannot retry
    // halfway through.
    index("attachments_uploaded_by_idx").on(table.uploadedBy),
    // The cap's own predicate, and every upload runs it. Partial for the same
    // reason `attachments_staged_idx` below is: staged rows are a small,
    // short-lived minority, and an index over every row would grow with the
    // table for a query that only ever asks about the unstamped ones.
    index("attachments_upload_group_idx")
      .on(table.channelId, table.uploadedBy, table.uploadGroup)
      .where(sql`${table.attachedAt} is null`),
    // Partial, on the sweeper's own predicate rather than the whole column.
    // The sweeper only ever asks for staged rows (`attached_at is null`),
    // which are a small, short-lived minority of the table, so an index
    // covering every row would grow with the table for no query that exists.
    index("attachments_staged_idx")
      .on(table.createdAt)
      .where(sql`${table.attachedAt} is null`),
  ],
);