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.

54.7 KB
Raw Blame
import { sql } from "drizzle-orm";
import {
  boolean,
  check,
  index,
  integer,
  pgTable,
  primaryKey,
  text,
  timestamp,
  uniqueIndex,
  uuid,
} from "drizzle-orm/pg-core";
import { agents, credentials, users } from "./core";
import { jsonb } from "./json";

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

/**
 * Schema owned by Plugins: MCP servers a deployment has added, the tools they offer, packaged
 * skills, and which Bots may use any of it.
 *
 * One surface for both. A tool from an MCP server and a
 * packaged skill are different things to build and the same thing to govern: somebody adds it once
 * for the deployment, and then decides which Bots may use it. Two tables with two grant surfaces
 * would give an operator two places to look and two ways to be wrong about what a Bot can reach.
 */

/**
 * An MCP server this deployment has added.
 *
 * Account-wide, not per-Bot. Adding a server is an administrative act with a
 * credential behind it; which Bots may use its tools is a separate decision, recorded in
 * {@link pluginGrants}. Conflating them would mean adding a server to a second Bot re-entered the
 * credential, and a deployment would end up with the same vendor authorised several times over with
 * no single place to revoke it.
 *
 * `id` is the slug and a contract: it prefixes every tool name the model is offered, so a tool
 * from two servers can never collide, and a rule written against `mcp.server == "atlassian"` keeps
 * meaning the same thing after somebody renames the display title.
 */
export const mcpServers = pgTable(
  "mcp_servers",
  {
    id: text("id").primaryKey(),
    title: text("title").notNull(),
    logo: text("logo"),
    /** The vendor this server is maintained by, which is what the first-party rule is checked against. */
    vendor: text("vendor").notNull(),
    url: text("url").notNull(),
    /**
     * `first-party` for a curated entry, `custom` for one an administrator added by URL, `composio`
     * for an app enabled through the broker.
     *
     * Recorded because the three are not the same risk. A curated entry has reviewed source provenance
     * and a pinned host. A custom one is a URL somebody typed, and every surface that lists it says so.
     * Storing which it is means the Plugins page, the audit trail and anybody reading the database
     * later all agree about how a server got here, rather than inferring it from whether the host
     * happens to still be in this build's catalogue.
     *
     * AND `composio` IS NOT MERELY A THIRD LABEL — it decides how the row is REACHED. `accessFor`
     * reads this column to answer that a call is brokered, which is what makes it run in the account
     * of the person asking rather than on the deployment's own credential, and `toolkitOf` then reads
     * which app out of {@link mcpServers.url}. So this column and that one are ONE FACT IN TWO PLACES,
     * and the invariant every writer keeps is that they are written together: a `composio://` url
     * carries `provenance = composio`, and a row saying `composio` carries a url naming an app. Half
     * of the pair is not a mislabelled row, it is a row dialled one way and governed another — see
     * `requireNotBrokered` in `plugins/store.ts` for which writes are refused to keep the pair whole,
     * and `addBrokeredApp` for the one that converts.
     */
    provenance: text("provenance").notNull().default("first-party"),
    /**
     * The vault row holding this server's credential, or null for a server that needs none.
     *
     * A pointer rather than the secret: the vault owns encryption, rotation and revocation, and a
     * second copy of a token here would be a second thing to remember to revoke.
     *
     * A REAL foreign key, where this was `text` against a `uuid` primary key with none. That is not a
     * typing nicety. The database was willing to hold a pointer to a credential row that did not
     * exist, and it did: a test deleted the credential an administrator had registered and left this
     * column addressing nothing, so the connector reported "no OAuth client registered yet" while the
     * row still looked configured. Nothing caught it because nothing was checking.
     *
     * `restrict`, not `cascade` or `set null`. A credential this server points at should not be
     * removable out from under it — the two legitimate ways to change it are replacing it, which
     * repoints this column first, and removing the server, which takes the row with it. Anything else
     * is a mistake, and should be refused rather than silently tidied into a working-looking state.
     */
    credentialId: uuid("credential_id").references(() => credentials.id, {
      onDelete: "restrict",
    }),
    /**
     * How this app connects, as it was resolved when somebody enabled it.
     *
     * Recorded rather than re-derived, because the catalogue is somebody else's and a vendor that
     * starts publishing a new scheme for an app must not move live connections onto a different
     * flow underneath them.
     *
     * THE VENDOR'S OWN SCHEME LITERAL, NOT A {@link BrokerConnection} KIND — `OAUTH2`, `DCR_OAUTH`,
     * `API_KEY`, `BASIC`, `BEARER_TOKEN`, `BASIC_WITH_JWT`, `NO_AUTH`. Those two vocabularies name one
     * fact, and this column is where a reader comes to find out which of them is written down, so it
     * says: somebody looking here for `consent` or `fields` is reading the other one. Migration 0038
     * backfilled every row whose provenance is `composio` to `OAUTH2`, because managed OAuth was the
     * only config this deployment ever created and `addBrokeredApp` writes the row only after that
     * config stands. A null is therefore not an older brokered row this deployment WROTE.
     *
     * WHICH IS NOT THE SAME AS A NULL BEING UNREACHABLE ON A BROKERED READ, and the difference has
     * cost a verdict already. Every reader finds an app's row by {@link mcpServers.url}, which carries
     * no unique index — two rows may name one app, and the one that answers is not always the one an
     * enable wrote a scheme onto. Add a row inserted by hand, a row restored from elsewhere, or an app
     * whose `BrokerConnection` was `unsupported`, and a brokered read really does meet a null here. So
     * a reader must have three answers and not two: a key, a consent, and a column it cannot act on.
     * `schemeKind` in `plugins/broker.ts` is that reading, and `confirmBrokeredConnection` is what
     * happened without it — a null read as consent, and `verified: true` written on every page load
     * over evidence nobody had.
     */
    authScheme: text("auth_scheme"),
    /**
     * Whose account a brokered app is reached through: each asking person's own (`personal`), or
     * one account that belongs to this deployment (`shared`).
     *
     * NULL ON EVERY ROW THAT IS NOT A BROKERED APP NEEDING AN ACCOUNT, and that is a statement rather
     * than a gap: a curated connector and a no-auth app have no account to choose between. A brokered
     * row that needs one and has NULL here is refused as ambiguous by `accountFor`, never guessed.
     */
    accountMode: text("account_mode").$type<"personal" | "shared">(),
    /**
     * The identity this deployment's own account is held under at the vendor, minted once when the
     * app is switched to Shared and cleared when it is switched back. A consent flow begins before
     * any connection row exists, so this is where `authorize` and `confirm` both read it from.
     */
    sharedVendorUserId: text("shared_vendor_user_id"),
    /**
     * Who may give this app to a Bot: any Bot's owner, for their own Bot (`any`), or only an
     * administrator (`admin`).
     *
     * NULL MEANS THE DEFAULT FOR WHOSE ACCOUNT THE APP RUNS ON, and that is a statement rather than
     * a gap. `ownerGrantsOf` in `plugins/app-access.ts` reads it: an app every person connects for
     * themselves, and the built-in Routines, default to `any`; an app on the deployment's token, a
     * Shared team account or no account at all defaults to `admin`. Stored only once an
     * administrator chooses, so an app switched from Personal to Shared falls back to `admin` by
     * itself instead of keeping a default somebody chose for the other mode.
     *
     * Nullable for the reason `docs/development.md` gives under Migrations: a replica from before
     * this column writes rows without it.
     */
    ownerGrants: text("owner_grants").$type<"any" | "admin">(),
    /** What the deployment last heard back from it. `null` until the first successful listing. */
    toolsRefreshedAt: timestamp("tools_refreshed_at", { withTimezone: true }),
    /** The last failure, kept so the Plugins page can say why a server has no tools. */
    lastError: text("last_error"),
    /**
     * The names the last stored listing left out because no model could be offered them, as the
     * vendor spelled them. A warning about a refresh that succeeded, not a failure, which is why it
     * is not `lastError`: a server that keeps listing a dotted name would otherwise read as broken
     * on the Plugins list forever. Null and empty both mean none; a replica that predates the column
     * writes nothing here.
     */
    skippedTools: text("skipped_tools").array(),
    addedBy: text("added_by"),
    createdAt: createdAt(),
    updatedAt: updatedAt(),
  },
  (table) => [
    check(
      "mcp_servers_account_mode_check",
      sql`${table.accountMode} IS NULL OR ${table.accountMode} IN ('personal', 'shared')`,
    ),
    check(
      "mcp_servers_owner_grants_check",
      sql`${table.ownerGrants} IS NULL OR ${table.ownerGrants} IN ('any', 'admin')`,
    ),
  ],
);

/**
 * A tool one server says it offers, as of the last listing.
 *
 * A cache of what the server said, never a source of truth about what it will accept. The row exists
 * so the Plugins page and the `@` menu can show a list without a network call per render, and so a
 * grant can name a tool that is not reachable this second. Every actual call re-reads the server.
 *
 * Rows are replaced wholesale on each refresh rather than merged, so a tool a vendor withdrew stops
 * being offered instead of lingering as a name the model will call and the server will reject.
 */
export const mcpTools = pgTable(
  "mcp_tools",
  {
    serverId: text("server_id")
      .notNull()
      .references(() => mcpServers.id, { onDelete: "cascade" }),
    name: text("name").notNull(),
    /**
     * THE VENDOR'S OWN SPELLING OF THIS ACTION, which is not always a name a model may be offered.
     *
     * `name` is this deployment's identifier for the action: half of the `<serverId>/<name>` ref a
     * grant is written against, and the half of `mcp__<serverId>__<name>` a model is offered. A
     * model tool name must match `^[a-zA-Z0-9_-]+$`, so `name` must too.
     *
     * THE TWO USED TO BE ONE COLUMN, AND ARCADE IS WHERE THAT BROKE. Its listing answers
     * `qualified_name` — `Gmail.ListEmails` — and the dot is legal at the vendor and illegal in a
     * tool name, so every Arcade action this deployment recorded produced an offered name the model
     * API rejects outright. Not the one tool: the request carries every tool together, so a single
     * granted Arcade action failed the whole call with `Invalid 'tools[47].name'`, and no Arcade
     * tool had ever been callable by any model.
     *
     * STORED RATHER THAN RECONSTRUCTED. `Gmail.ListEmails` is `<toolkit>.<action>` and the toolkit
     * is already the server id, so the vendor's spelling LOOKS derivable from `name` — but that is a
     * rule about the shape of today's listing, not a promise the vendor makes, and the one thing
     * that must never be guessed is the string an action is executed by. It is also what the
     * per-action authorization flow speaks end to end (`grantsFor`, the Authorize button, the
     * attempt row, `?action=`, the vendor's own `authorize({ action })`) and what this deployment's
     * refusal sentences quote back, so it is a fact the row has to hold.
     *
     * EQUAL TO `name` FOR EVERY OTHER TRANSPORT, and that is not a placeholder. Composio's
     * `GMAIL_FETCH_EMAILS` and an MCP server's names are already offerable as they stand, so there
     * are no two spellings to keep apart and the column says so. The `arcade_provider_backfill`
     * migration filled the rows that predate the column that way for the same reason.
     *
     * NULLABLE, AND NULL MEANS "THE SAME AS `name`". Every writer in this build sets it, but a
     * rolling deploy serves from the previous build's replicas after the migration has run, and
     * those insert rows without this column; under `NOT NULL` every listing they refreshed would
     * fail. So the column ships nullable (see `docs/development.md`, Migrations), every reader
     * resolves it through {@link vendorNameOf}, and tightening it belongs to a later release.
     */
    vendorName: text("vendor_name"),
    description: text("description").notNull().default(""),
    /** The tool's own JSON Schema, passed to the model unchanged. */
    inputSchema: jsonb("input_schema").notNull().default({}),
    /**
     * What this action does, as the vendor itself described it, or null when nothing said.
     *
     * Recorded here rather than derived per call because the source is the listing: Composio labels
     * every action, and those labels arrive with the tool list and nowhere else. A hand-written write
     * list per app — which is what {@link CatalogueEntry.writeTools} is — cannot be kept for a
     * catalogue of several hundred apps that changes weekly, and a list naming only the actions
     * somebody thought of reads as a guard while behaving like a gap.
     *
     * PLAIN TEXT RATHER THAN AN ENUM, deliberately. The value is somebody else's vocabulary, so a
     * database enum would need a migration every time a vendor invents a label, and the migration
     * would be the thing standing between a refresh and a correct classification. `classifyTool`
     * defends instead: only the exact string `read` produces a read, so an unrecognised value fails
     * closed. Same reasoning as `mcp_servers.provenance`, which is text for the same reason.
     *
     * NULLABLE, AND NOT DEFAULTED TO "write". Every row that already exists was listed before this
     * column did, and a default would reclassify every Notion read as a write when the migration ran.
     * Null means "nothing said", and the classifier decides that means write.
     */
    effect: text("effect"),
    /**
     * Whether the vendor marked this action as destroying something.
     *
     * Separate from {@link mcpTools.effect} rather than a third value in it, so the rule engine keeps
     * the two values every existing policy is written against and nobody's rules need migrating. It
     * is recorded now because the confirmation card is what needs it, and re-listing every app later
     * to backfill a column is worse than carrying it from the start.
     *
     * `false` for an action nothing said about — the same fail-closed direction as `effect` without
     * claiming a vendor said something it did not. An unclassified action is already gated as a
     * write; marking it destructive as well would paint every ordinary write as dangerous and teach
     * an approver to click through the colour.
     */
    destructive: boolean("destructive").notNull().default(false),
    /**
     * The vendor's version for this action, as the listing gave it — `20260903_00` and the like.
     *
     * NOT OPTIONAL BOOKKEEPING. Composio refuses to execute an action without a specific version,
     * and refuses the word `latest` too, so this column is what makes a call possible at all. It is
     * stored rather than fetched per call because it arrives free with the listing and fetching it
     * would be a second round trip on every single call.
     *
     * Null for every other transport, which publishes no such thing, and for rows listed before this
     * column existed. The Composio transport treats a missing version as a reason to refuse rather
     * than a reason to guess — a guessed version is a call against an action's other behaviour.
     */
    version: text("version"),
    createdAt: createdAt(),
  },
  (table) => [primaryKey({ columns: [table.serverId, table.name] })],
);

/**
 * The name an `mcp_tools` row's action is executed by: `vendor_name`, or `name` where a replica of
 * the previous build wrote the row without one. The one way a query reads that column, so a null
 * never reaches a vendor or fails a comparison. See {@link mcpTools.vendorName}.
 */
export const vendorNameOf = () =>
  sql<string>`coalesce(${mcpTools.vendorName}, ${mcpTools.name})`;

/**
 * One person's Composio connection to one app.
 *
 * WHY THIS IS NOT `mcp_user_credentials`. That table's whole guarantee is that a row means real held
 * access: it points at a vault row, not-null, and the vault is what offboarding scans. Composio holds
 * the account, so there is no secret to point at and none to scan for — and `retireConnectionsFor`
 * deliberately reads the VAULT rather than the join table, because the join row is deleted along with
 * the person while the vault row survives. Putting a Composio connection there would mean removing
 * somebody deletes the only record of it, leaving their mailbox connected at Composio with nothing
 * left to revoke it by, while an administrator has been told they removed it.
 *
 * So `user_id` is plain text with NO foreign key and no cascade. The row outliving the person is the
 * point, not an oversight: it is the only thing that lets offboarding say "this person had Gmail
 * connected, tell Composio to drop it". A scope column would be a lie — Composio returns no scope we
 * see, and the column on the other table exists precisely to record what the vendor said it granted —
 * so there is none.
 *
 * A CACHE, NOT THE TRUTH. Composio is authoritative about whether a connection is live; this row
 * exists so the settings page can be drawn without a network call per row, and so offboarding has
 * something to iterate. A call against an app the person never connected fails at Composio, and that
 * refusal is the answer rather than this table's absence.
 */
export const composioConnections = pgTable(
  "composio_connections",
  {
    /** The Composio app slug, lower case, as their directory spells it: `gmail`, `slack`. */
    toolkit: text("toolkit").notNull(),
    /**
     * The person, as `users.id`.
     *
     * The same value sent to Composio as the identity a call runs under, so the two cannot drift:
     * what this row says somebody connected is what a call will act as.
     */
    userId: text("user_id").notNull(),
    /** When they connected, shown on their own settings page. */
    connectedAt: timestamp("connected_at", { withTimezone: true })
      .notNull()
      .defaultNow(),
    /**
     * Whether a real call was made with this connection and answered. See the verify path.
     *
     * TRUE ON EVERY ROW THAT PREDATES THIS COLUMN WITHOUT A PROBE BEHIND IT. Migration 0038 —
     * `server/drizzle/0038_composio_schemes.sql`, which is where this pair and `auth_scheme` are
     * both added and both backfilled; every "0038" in this file names that one file —
     * backfilled them to true with `verified_at = connected_at`, and no call was made to earn it:
     * every one of the rows it touched is a consent connection, which is verified by construction,
     * because the only way it exists at all is that the vendor's own screen sent the person back
     * connected. So on a backfilled row the timestamp is the moment of consent, not the moment of a
     * check, and a reader treating every `verified_at` as "this connection answered then" would be
     * wrong about exactly the rows that were here first.
     *
     * AND THE SAME NOW HOLDS OF EVERY CONSENT ROW AND NOT ONLY THE BACKFILLED ONES, because the
     * writer that records a connection sets `verified` itself: `recordBrokeredConnection` is the
     * one place a row is written, and `confirmBrokeredConnection` calls it with `verified: true` on
     * the vendor's yes. So a consent connection made today carries the same true migration 0038
     * wrote and earns it the same way — the vendor answered that the account is attached — and its
     * `verified_at` is the moment of that answer: the consent itself on the first confirm, and the
     * vendor's yes again on every later one, because a confirm really does go and ask. What it used
     * to do was insert on the defaults, and until it stopped, every consent connection made since
     * the backfill read `false` beside a null `verified_at` — the very pair a key connection nobody
     * has ever checked reads — so nothing could tell the two apart, and the rows the migration
     * touched were the only ones in the table saying anything true.
     *
     * ON A CONSENT ROW, AND ONLY THERE. That confirm runs from an effect on mount, so what it
     * writes it writes on every page load — and the vendor's yes is evidence about a KEY of
     * nothing, because Composio takes a key when it is typed and never tests it again. So the
     * confirm branches on the scheme recorded on the app's row: it writes this flag where consent
     * IS the check, and leaves a key connection's recorded verdict exactly as the last real call
     * left it. Written across a key row instead, it restated the consent pair below — `verified`
     * true beside a null `probe_action` — over the record of a check that had actually happened,
     * and moved this timestamp to the page load, so the page's own sentence named a day on which
     * nothing was checked.
     *
     * AND A CHECK THAT COULD NOT BE MADE LEAVES THIS COLUMN ALONE, which is the one thing a writer
     * of it must not improvise. `false` here means "no evidence that this connection answers", and
     * a Composio outage produces no evidence in EITHER direction — so on a connect, whose row has
     * to exist whatever happens, the outage is written as the unchecked pair, and on a re-check,
     * which finds a row already standing, NOTHING IS WRITTEN AT ALL. Clearing a verification
     * because the vendor could not be reached destroys the only record anywhere that this
     * connection was ever checked, and the date beside it, on the word of an event that says
     * nothing about the key. See {@link composioConnections.probeAction}.
     *
     * WHAT THE PAIR SEPARATES IS A CHECKED CONNECTION FROM AN UNCHECKED ONE, and never one KIND of
     * connection from another. Which kind a row is comes from the app's own `auth_scheme`, which
     * the settings page branches on first; this column says only that somebody established the
     * account is live, and `verified_at` when that was last done.
     */
    verified: boolean("verified").notNull().default(false),
    /** When that check last passed, which is what the page reports instead of a present tense. */
    verifiedAt: timestamp("verified_at", { withTimezone: true }),
    /**
     * The action the last check SPENT on this connection, and null where it spent none.
     *
     * A RECORD OF WHAT HAPPENED, NOT A QUESTION ASKED OF TODAY'S METADATA — which is the whole
     * reason it is a column at all. `verified` is a fact about a check made against the app's action
     * listing as it stood THEN; this used to be derived on read from the listing as it stands NOW,
     * and the argument that the two agreed held only for as long as nothing changed in between.
     * Something can: `POST /servers/:id/refresh` is a generic administrator's route keyed on a
     * server id and `composio-<slug>` is one, so an ordinary press of Refresh re-lists a brokered
     * app's actions — the very press the Composio transport tells an operator to make when an
     * action gains the version that makes it callable. The moment it did, a row that truthfully
     * said "the key was accepted without being checked, because this app publishes nothing safe to
     * try one on" began reading as a NAMED probe beside `verified: false`, which the settings page
     * draws as "your key was checked and rejected, and the account still stands — disconnect it".
     * Every clause of that is false for somebody whose key was never tried, and it persisted: it is
     * what every page load said until they pressed Re-check.
     *
     * SO THE WRITER RECORDS IT, and the writer is the one place that cannot be wrong about it.
     * `recordBrokeredConnection` is the single writer of this row, every caller knows what it spent
     * — a probe's name, or nothing — and it is passed in beside `verified` because the two are one
     * fact: what was checked, and how it went.
     *
     * NULL MEANS NO ACTION WAS SPENT, WHICH IS NOT THE SAME AS UNCHECKED. Read beside `verified` it
     * says which:
     *
     *   null, not verified   — nothing was tried, so nothing is known about the key. EITHER the app
     *                          published nothing safe to spend one on at the moment of the check,
     *                          OR a check was attempted and could not be made — Composio
     *                          unreachable, a socket closed, an answer `@composio/core` could not
     *                          parse. A fact about the app or about the vendor's availability, and
     *                          in neither case about the key.
     *
     *                          THE OUTAGE WAS NOT ALWAYS HERE, and where it used to land is the
     *                          reason this clause is spelled out. `callTool` answers with a result
     *                          rather than throwing, so every failure it has wears `isError`, and
     *                          the probe read that as the vendor's verdict: an outage was written
     *                          down as the FOURTH state below — the accusation — while the connect
     *                          path deleted the account somebody had just made and told them their
     *                          key was refused. An unreachable vendor is not a fifth state and has
     *                          no column of its own, because the row records what is KNOWN about a
     *                          connection and an outage establishes nothing: the honest record is
     *                          the mildest pair, and the trail carries the attempt. See
     *                          {@link BrokeredProbe} in `plugins/store.ts` for the four answers a
     *                          check can reach and which of them may be written here.
     *   null, verified       — a CONSENT connection. The vendor's own yes at the end of its own
     *                          screen is the evidence, and no call was ever made against the
     *                          account, so there is no action to name and there never will be.
     *                          Which is why `confirmBrokeredConnection`, the one writer of this
     *                          pair, writes it only for a consent app: on a key row it is not a
     *                          heal but one of the other three states overwritten by a page load.
     *   a name, verified     — it ran in this person's account and the vendor took the key.
     *   a name, not verified — it ran and the vendor refused the key, and the account it ran in is
     *                          still standing. A live account with a bad key behind it.
     *
     *                          AND ONLY A CALL THE VENDOR ANSWERED MAY WRITE IT. This pair is an
     *                          accusation — the settings page draws it as "your key was checked and
     *                          rejected, disconnect it" — so the name is withheld from every
     *                          outcome that cannot be shown to have run in the account. That is
     *                          structural rather than a rule anybody has to remember:
     *                          {@link BrokeredProbe} carries no action name on the unreachable
     *                          outcome, so the writer has none to record.
     *
     * AND NULL ON A ROW WRITTEN BEFORE THIS COLUMN EXISTED, which is the same null and deliberately
     * so. No backfill is possible or wanted: what a check spent in March is not recoverable, and
     * today's chooser answering for it is exactly the inference this column retires. The rows
     * migration 0038 touched are consent rows, where null is permanently right; a key row that
     * predates it reads as unchecked, the mildest of the four states and the only safe direction to
     * be uncertain in — a name invented for it would be the false accusation above, written down.
     *
     * WHAT A CALLER MUST NOT READ IT AS is "the action this app could be checked with now". That is
     * a different question, asked of `probeActionFor`, and the two answers diverge exactly when
     * the app's listing has moved since the check.
     */
    probeAction: text("probe_action"),
    updatedAt: updatedAt(),
  },
  (table) => [
    primaryKey({ columns: [table.toolkit, table.userId] }),
    // "What has this person connected" is the settings page's only query, and offboarding's.
    index("composio_connections_user_idx").on(table.userId),
  ],
);

/**
 * Every account this deployment reaches a brokered app through: one per person per app, and at most
 * one per app that belongs to the deployment itself.
 *
 * THE SUCCESSOR TO `composio_connections`, WHICH IS LEFT IN PLACE AND NEVER WRITTEN, so that a
 * deployment rolled back to v0.1.0 still finds the table it expects. Every column that table had
 * means here what it meant there; see its docblock for `verified` and `probe_action`, which carry
 * over unchanged.
 *
 * `holder` IS EXPLICIT RATHER THAN A RESERVED VALUE IN `user_id`, so no query that means "this
 * person's accounts" can match the deployment's by forgetting a filter: a deployment row has no
 * `user_id` at all, and the CHECK below holds that.
 */
export const brokeredConnections = pgTable(
  "brokered_connections",
  {
    provider: text("provider").$type<"composio" | "arcade">().notNull(),
    app: text("app").notNull(),
    holder: text("holder").$type<"person" | "deployment">().notNull(),
    userId: text("user_id"),
    vendorUserId: text("vendor_user_id").notNull(),
    connectedBy: text("connected_by"),
    connectedAt: timestamp("connected_at", { withTimezone: true })
      .notNull()
      .defaultNow(),
    verified: boolean("verified").notNull().default(false),
    verifiedAt: timestamp("verified_at", { withTimezone: true }),
    probeAction: text("probe_action"),
    updatedAt: updatedAt(),
  },
  (table) => [
    check(
      "brokered_connections_holder_check",
      sql`(${table.holder} = 'person' AND ${table.userId} IS NOT NULL) OR (${table.holder} = 'deployment' AND ${table.userId} IS NULL)`,
    ),
    check(
      "brokered_connections_provider_check",
      sql`${table.provider} IN ('composio', 'arcade')`,
    ),
    uniqueIndex("brokered_connections_person_idx")
      .on(table.provider, table.app, table.userId)
      .where(sql`${table.holder} = 'person'`),
    uniqueIndex("brokered_connections_deployment_idx")
      .on(table.provider, table.app)
      .where(sql`${table.holder} = 'deployment'`),
    index("brokered_connections_user_idx").on(table.userId),
  ],
);

/**
 * One in-flight Arcade authorization: what the returning browser is verified against.
 *
 * `actor_id` IS THE SIGNED-IN PERSON'S `users.id` — the session subject — AND NEVER
 * `credentialActorId`. `store.ts` reads `input.credentialActorId ?? input.actorId`, and on a Team
 * Bot those differ, and neither is the person holding the browser. The verifier finds this attempt
 * from the returning browser's session, so any other key writes attempts a browser can never match.
 *
 * The key is the actor alone, so a person has at most one attempt at a time. A PRIMARY KEY with
 * "expired is treated as absent" would be a permanent 409, because the expired row still occupies
 * the key. Starting an attempt is therefore one `INSERT ... ON CONFLICT (actor_id) DO UPDATE ...
 * WHERE created_at < now() - interval '15 minutes' OR completed_at IS NOT NULL`: a live attempt is
 * refused, an expired or completed one is taken over, and nobody is ever locked out.
 *
 * `completed_at` is what makes completion happen exactly once: the verifier claims the row by
 * setting it, and a second return finds it set. `auth_id` is what binds a callback to this attempt;
 * an attempt holding null can never complete.
 *
 * THE ROW IS THE ACTOR'S, AND ONLY `actor_id` CARRIES THE KEY TO `users`. `actor_id` is `users.id`,
 * and a column that names a table without a key is a column the database will let drift: a deleted
 * person left attempt rows pointing at nobody, and the verifier's "find the attempt for this
 * session" would then be matching a key no session can ever present.
 *
 * `user_id` IS NOT A SECOND PERSON. It is whose account the attempt is for, and for a `person`
 * attempt that is the actor and nobody else — `accountRefFor` answers a Personal app with
 * `{ userId: actorId }`, and nothing lets one person consent into another's account. So the database
 * holds `user_id IS NULL OR user_id = actor_id` (`pending_authorizations_user_is_actor_check`), and
 * through that CHECK `user_id` names a person exactly when `actor_id` does. It carried a cascading
 * key of its own in an earlier draft of this table, and with nothing making the two columns equal that key let a
 * row name two people and die with EITHER: deleting one person took an attempt a DIFFERENT person
 * had started. An attempt is evidence of somebody's consent in progress, and the one person whose
 * deletion may end it is the one who started it.
 *
 * CASCADE RATHER THAN RESTRICT, AND THIS ROW IS NOT THE AUDIT TRAIL. An attempt is in-flight
 * working state with a fifteen-minute life and at most one row per actor, holding no grant and no
 * secret; what a person authorized is recorded in `audit_events`, which deliberately carries
 * NO key to `users` for exactly the reason the trail must outlive them. `RESTRICT` here would make
 * a person undeletable for fifteen minutes because they happened to have a browser mid-redirect,
 * which is a legitimate deletion blocked by ephemeral state. Note the contrast with
 * {@link brokeredConnections}, whose `user_id` is keyless ON PURPOSE: that row IS the record of
 * which apps somebody held, and `retireConnectionsFor` reads it AFTER the `users` row is gone.
 * Nothing reads an attempt after its person is gone.
 *
 * `server_id`'s key gets an index of its own. A foreign key is not indexed by postgres on the
 * referencing side, so deleting one `mcp_servers` row made the cascade scan this table, and so did
 * every lookup by server. `actor_id` is the primary key and indexed by it; `user_id`, no longer a
 * key and read by nothing, has none.
 */
export const pendingAuthorizations = pgTable(
  "pending_authorizations",
  {
    actorId: text("actor_id")
      .primaryKey()
      .references(() => users.id, { onDelete: "cascade" }),
    serverId: text("server_id")
      .notNull()
      .references(() => mcpServers.id, { onDelete: "cascade" }),
    action: text("action").notNull(),
    /**
     * Whose account this attempt is for, with the same meaning and the same two values as
     * {@link brokeredConnections.holder} — and held by the same CHECK, because `$type<>` is erased
     * at runtime and the row this attempt is reconciled into is held to that pair.
     */
    holder: text("holder").$type<"person" | "deployment">().notNull(),
    /** Always the actor or `null`; see `pending_authorizations_user_is_actor_check` below. */
    userId: text("user_id"),
    vendorUserId: text("vendor_user_id").notNull(),
    authId: text("auth_id"),
    createdAt: createdAt(),
    completedAt: timestamp("completed_at", { withTimezone: true }),
    /**
     * How the attempt ended, `null` while it is still in flight.
     *
     * The four names are a ROSTER the database holds, not a comment: every reader switches on them,
     * and a fifth value written by hand — or by a caller that cast past the declared union — would
     * reach a `switch` the compiler has already been told is exhaustive.
     */
    outcome: text("outcome").$type<
      "completed" | "failed" | "unreconciled" | "withdrawn"
    >(),
  },
  (table) => [
    check(
      "pending_authorizations_holder_check",
      sql`(${table.holder} = 'person' AND ${table.userId} IS NOT NULL) OR (${table.holder} = 'deployment' AND ${table.userId} IS NULL)`,
    ),
    check(
      "pending_authorizations_outcome_check",
      sql`${table.outcome} IS NULL OR ${table.outcome} IN ('completed', 'failed', 'unreconciled', 'withdrawn')`,
    ),
    /**
     * An attempt has ended, or it has not: `completed_at` and `outcome` are set together or
     * neither is.
     *
     * A ROW CARRYING AN `outcome` WITH `completed_at` STILL NULL IS A FINISHED ATTEMPT THAT EVERY
     * READER READS AS A LIVE ONE. `claimAuthorizationAttempt` frees a held key only
     * `WHERE created_at < now() - interval '15 minutes' OR completed_at IS NOT NULL`, and
     * `authorizationAttemptFor` answers on `completed_at IS NULL` alone; neither consults
     * `outcome`. So one such row locks its actor out of authorizing anything for the rest of the
     * 15-minute TTL, with a verdict already recorded against it. The mirror shape — a
     * `completed_at` with no `outcome` — is an attempt that ended with nothing saying how, and
     * `outcome` is what every operator-facing reading of this table is of.
     *
     * HERE AND NOT IN THE APPLICATION, although both writers already set the pair in one
     * statement. That those two agree today is precisely what makes the invariant invisible to the
     * next writer, to a hand-run UPDATE and to a partial restore, and none of those three passes
     * through the two functions that currently get it right. Written as a boolean equality rather
     * than two `OR`-ed clauses because `(x IS NULL) = (y IS NULL)` is never itself NULL, so the
     * CHECK can only pass or refuse — it has no third, silently-accepting verdict.
     */
    check(
      "pending_authorizations_completion_check",
      sql`(${table.completedAt} IS NULL) = (${table.outcome} IS NULL)`,
    ),
    /**
     * A `person` attempt is for the actor's own account, so the row names one person, not two.
     *
     * HERE AND NOT ONLY IN `accountRefFor`, because this is what keeps a cascade on `users` from
     * reaching a row somebody else started — the guarantee is the database's or it is nobody's: a
     * hand-run INSERT, a restore, or the next writer does not pass through `accountRefFor`. The
     * `IS NULL` arm is the `deployment` attempt, whose shape `pending_authorizations_holder_check`
     * already decides; spelled out so this CHECK says what it admits rather than leaning on a NULL
     * comparison passing.
     */
    check(
      "pending_authorizations_user_is_actor_check",
      sql`${table.userId} IS NULL OR ${table.userId} = ${table.actorId}`,
    ),
    index("pending_authorizations_server_idx").on(table.serverId),
  ],
);

/**
 * One person's grant on one MCP server: the row that makes a Bot answer as the asker.
 *
 * A table rather than a column, and this is the whole architectural point of the knowledge lane.
 * `mcp_servers.credential_id` holds what the DEPLOYMENT has — for a `user-oauth` vendor that is the
 * OAuth client, which reaches nobody's documents by itself. What reaches somebody's documents is
 * here, one row per person, and a call picks the row belonging to whoever asked. Two people asking
 * the same question therefore get the answers their own accounts can see, and neither can be served
 * the other's.
 *
 * The key is the pair. "Which credential serves this server for this person" must have exactly one
 * answer: with a surrogate id and no unique constraint, two rows for one pair are legal, and then
 * the answer is whichever the query happened to order first — so somebody who reconnected could keep
 * being served the grant they thought they had replaced.
 *
 * A pointer to the vault, never the secret, the same as everywhere else. The vault owns encryption,
 * rotation and revocation, and a second copy of a refresh token here would be a second thing to
 * remember to revoke when somebody disconnects.
 */
export const mcpUserCredentials = pgTable(
  "mcp_user_credentials",
  {
    serverId: text("server_id")
      .notNull()
      .references(() => mcpServers.id, { onDelete: "cascade" }),
    userId: text("user_id")
      .notNull()
      .references(() => users.id, { onDelete: "cascade" }),
    /**
     * The vault row holding this person's refresh token.
     *
     * A real foreign key, unlike {@link mcpServers.credentialId}, which is `text` against a `uuid`
     * primary key and so references nothing the database will check. The new table does not copy
     * that.
     *
     * Deliberately not cascading. A revoked credential row is kept for the trail, and deleting the
     * row that says whose it was would take the trail with it.
     */
    credentialId: uuid("credential_id")
      .notNull()
      .references(() => credentials.id),
    /**
     * What the vendor actually granted, as it said it — not what we asked for.
     *
     * The two differ in practice: a person can decline part of a consent screen. Storing the reply
     * rather than the request means a tool failing for want of a scope can be explained instead of
     * being a mystery about a permission we assumed we had.
     */
    scope: text("scope").notNull(),
    /**
     * When this person connected.
     *
     * Written out rather than using the shared `createdAt()` helper, which fixes the column name to
     * `created_at`. This row records an act somebody performed and a date they are shown on their
     * own settings page, so it is worth the column saying which act.
     */
    connectedAt: timestamp("connected_at", { withTimezone: true })
      .notNull()
      .defaultNow(),
    updatedAt: updatedAt(),
  },
  (table) => [
    primaryKey({ columns: [table.serverId, table.userId] }),
    index("mcp_user_credentials_user_idx").on(table.userId),
  ],
);

/**
 * A packaged skill: a named instruction a person invokes with `/` and a Bot follows.
 *
 * Not a tool, and the difference matters. A tool is something a Bot calls; a skill is something a
 * Bot is told. Writing one adds no capability at all: it can only ask a Bot to use what that Bot was
 * already granted, and every one of those calls is still decided, policy-checked and audited. The
 * firewall is at the tool call, not at the prose, which is why anybody may write a skill while
 * adding an MCP server stays an administrator's decision.
 */
export const skills = pgTable(
  "skills",
  {
    id: text("id").primaryKey(),
    /**
     * Whose skill this is. Null means the deployment's: written by an administrator, or shipped,
     * and offered to everybody.
     *
     * A person's own skill is theirs alone. They may write one freely and put it on a Bot they own,
     * and nobody else sees it in their `/` menu or their list.
     */
    ownerUserId: text("owner_user_id").references(() => users.id, {
      onDelete: "cascade",
    }),
    /**
     * What a person types after `/`, and unique across the deployment rather than per person.
     *
     * The `/` namespace is shared because Bots are shared: two different behaviours answering to
     * `/standup` in one deployment is confusing wherever the second one came from. First to take a
     * name keeps it, and the refusal says so.
     */
    slug: text("slug").notNull(),
    title: text("title").notNull(),
    /** One line, shown in the catalogue and in the `/` menu. */
    summary: text("summary").notNull(),
    /** The instruction itself, prepended to the run when the skill is invoked. */
    instructions: text("instructions").notNull(),
    /** Where it came from: `catalogue` for one we ship, `yours` for one somebody wrote here. */
    origin: text("origin").notNull().default("yours"),
    installedBy: text("installed_by"),
    createdAt: createdAt(),
    updatedAt: updatedAt(),
  },
  (table) => [
    uniqueIndex("skills_slug_key").on(table.slug),
    index("skills_owner_idx").on(table.ownerUserId),
  ],
);

/**
 * The tools a skill says it needs. A row is a declaration, and a declaration is not a grant.
 *
 * WHY THIS EXISTS. Choosing between a thousand tools is a retrieval problem, and the unit being
 * retrieved has to be the skill rather than the tool: a model picks the skill from its summary, and
 * the skill says which tools to load. Without this table there is no such unit. See K3.
 *
 * IT GRANTS NOTHING, AND THAT IS LOAD-BEARING RATHER THAN TIDY. Anybody signed in may write a skill,
 * precisely because a skill adds no capability — `plugins/routes.ts` says so where it declines to
 * require an administrator. If naming a tool here could make it callable, then writing a skill would
 * be a way to grant yourself a tool, and the one surface in this deployment that is deliberately not
 * an administrator's would become the way around every surface that is. What a Bot may call stays
 * `plugin_grants`; this only ever narrows what is offered out of what was already granted.
 *
 * NO FOREIGN KEY TO `mcp_tools`, on purpose. Refreshing a server deletes every one of its tool rows
 * and writes them again (`plugins/store.ts`), so a composite key with `on delete cascade` would empty
 * every skill's declarations on a routine refresh. `plugin_grants.ref` is plain text for the same
 * reason, and holding the two in the same shape is what lets them be compared without either side
 * parsing the other's format. The cost is a ref that can outlive the tool it names, which is the
 * price grants already pay, and is why a missing tool must read as "load nothing" rather than as an
 * error at run time.
 */
export const skillTools = pgTable(
  "skill_tools",
  {
    skillId: text("skill_id")
      .notNull()
      .references(() => skills.id, { onDelete: "cascade" }),
    /** `<serverId>/<toolName>`, the same key a grant is written against. */
    ref: text("ref").notNull(),
    declaredBy: text("declared_by"),
    createdAt: createdAt(),
  },
  (table) => [
    primaryKey({ columns: [table.skillId, table.ref] }),
    // Answering "which skills want this tool" without scanning, for the withdrawal question in #106.
    index("skill_tools_ref_idx").on(table.ref),
  ],
);

/**
 * One Bot's hold on one plugin, whether that plugin is an MCP tool or a skill. A row is the grant.
 *
 * Absence is the refusal, the same shape component grants use and for the same reason. A Bot
 * created before a server was added, a deployment that lost this table, a tool nobody ever enabled:
 * all of them land on "not granted", which is a refusal. An `enabled` boolean would make a missing
 * row undefined behaviour, and undefined behaviour in a grant table resolves to "allowed" the first
 * time somebody is in a hurry.
 *
 * One table for both kinds. `kind` says which, and `ref` names it: `<serverId>/<toolName>` for an
 * MCP tool, the slug for a skill. A second table would mean two code paths asking the same question
 * and two chances for them to disagree.
 */
export const pluginGrants = pgTable(
  "plugin_grants",
  {
    kind: text("kind").notNull(),
    ref: text("ref").notNull(),
    agentId: text("agent_id")
      .notNull()
      .references(() => agents.id, { onDelete: "cascade" }),
    grantedBy: text("granted_by"),
    /**
     * Whether an administrator made this grant (`admin`, or null on every row from before this
     * column) or the Bot's owner gave one of their own apps to their own Bot (`owner`).
     *
     * NOT BOOKKEEPING: IT DECIDES WHOSE ACCOUNT A CALL USES. On a published Team Bot, an
     * administrator's grant of a personal app goes out on the owner's account wherever the owner
     * connected it — `credentialActorFor` in `team-bots/team-bots.ts`. An owner's grant never does:
     * it always uses the asking person's own account. Otherwise publishing a Bot would hand every
     * teammate the owner's mailbox, which nobody decided except by giving the Bot their own Gmail.
     *
     * Nullable, null read as `admin`, for the rolling deploy `docs/development.md` describes: a
     * replica from before this column only ever wrote an administrator's grant.
     */
    grantedAs: text("granted_as").$type<"admin" | "owner">(),
    grantedAt: createdAt(),
    updatedAt: updatedAt(),
  },
  (table) => [
    primaryKey({ columns: [table.kind, table.ref, table.agentId] }),
    index("plugin_grants_agent_idx").on(table.agentId),
    check(
      "plugin_grants_granted_as_check",
      sql`${table.grantedAs} IS NULL OR ${table.grantedAs} IN ('admin', 'owner')`,
    ),
  ],
);

/**
 * A component authored in the browser rather than compiled into the build.
 *
 * This is not a column on `components`. That table governs components the build ships: its rows
 * describe something the code already owns, and a fork that deletes the React file leaves a row the
 * Admin page reports as missing. A sandboxed component has no React file and never will, so the
 * source is the row. Putting the source on the same table would mean every compiled component
 * carried three permanently empty columns, and "is this row backed by code" would become a question
 * about whether a text field happened to be blank.
 *
 * The two share the grant surface and the publish gate, because an operator deciding what a Bot may
 * answer with should not have to know which of the two they are looking at.
 */
export const sandboxedComponents = pgTable("sandboxed_components", {
  /** The tool name the model calls. Namespaced on save so it can never collide with a compiled one. */
  name: text("name").primaryKey(),
  title: text("title").notNull(),

  /**
   * The draft, which is what the playground edits, and the published copy, which is the only version
   * that ever renders or reaches a model.
   *
   * Separate columns rather than one live body. Publishing without a rebuild is the whole point of
   * this table, and it is also what makes an editor one keystroke away from changing what every Bot
   * draws in production. A draft absorbs that: it is edited freely, previewed against sample
   * arguments, and changes nothing until somebody publishes it. Same reason the catalogue splits a compiled
   * component's description, and the same fail-closed property: null published means no model is
   * ever offered this component, so a half-written draft cannot be called.
   */
  draftDescription: text("draft_description").notNull().default(""),
  draftHtml: text("draft_html").notNull().default(""),
  draftCss: text("draft_css").notNull().default(""),
  /**
   * Functions the body may call, as source. Runs inside `@jetbrains/websandbox`, so it reaches
   * neither the page, the session nor the network except through what the host hands it.
   */
  draftJsFunctions: text("draft_js_functions").notNull().default(""),
  /**
   * The arguments this component takes, as JSON Schema. This is what the model fills in, so it is
   * the difference between a component a model can use and one it will call wrongly forever.
   */
  draftArgumentSchema: jsonb("draft_argument_schema").notNull().default({}),

  publishedDescription: text("published_description"),
  publishedHtml: text("published_html"),
  publishedCss: text("published_css"),
  publishedJsFunctions: text("published_js_functions"),
  publishedArgumentSchema: jsonb("published_argument_schema"),

  /** Sample arguments the playground previews against, kept so the next editor sees what it draws. */
  sampleArguments: jsonb("sample_arguments").notNull().default({}),
  /** Bumped on every publish, so a reader can tell which version of a component drew something. */
  revision: integer("revision").notNull().default(0),
  published: boolean("published").notNull().default(false),
  publishedAt: timestamp("published_at", { withTimezone: true }),
  authoredBy: text("authored_by"),
  createdAt: createdAt(),
  updatedAt: updatedAt(),
});

/**
 * A person asking an administrator to make one work app usable through one Bot.
 *
 * WHAT IS ASKED FOR IS THE SERVER'S, NEVER THE BROWSER'S. `kind` is derived from the app's state for
 * the person and the Bot at the moment of asking — switched off is `enable`, held by no grant is
 * `grant`, a Shared app with no team account is `connect_shared` — so a request cannot be worded as
 * something the deployment does not actually need, and approving it does exactly the one thing
 * that state was missing.
 *
 * `app_key` IS THE SERVER ID THE APP HAS OR WOULD HAVE, and is not a foreign key. A request to
 * switch an app on names an app with no `mcp_servers` row yet — `composio-<slug>` for a Composio app,
 * the catalogue key for one this deployment ships — and is the row's id the moment it is enabled,
 * so one key follows the app across the change being asked for. `app_title` is the app's name as
 * the server resolved it when the request was filed, so an administrator's list can name an app
 * that has no row to read the name from.
 *
 * `agent_id` IS NULLABLE FOR A REQUEST ABOUT THE APP ALONE. Every path that files one today names
 * the Bot, so approving a request to switch an app on can grant it to that Bot in the same step;
 * the column admits none because "switch this on" is a complete question without one.
 *
 * ONE PENDING REQUEST PER (PERSON, APP, BOT, KIND), held by the partial unique index rather than by
 * a read-then-write, so two replicas filing the same request at once cannot both insert. The Bot is
 * read through `coalesce` because Postgres treats two NULLs as distinct, and an index on the bare
 * column would let the same app-only request pile up.
 *
 * NO ROW IS DELETED WHEN IT IS ANSWERED. Approved, dismissed and cancelled requests stay, with who
 * resolved them and when; the trail says the same thing in its own rows.
 */
export const appAccessRequests = pgTable(
  "app_access_requests",
  {
    id: text("id").primaryKey(),
    requestedBy: text("requested_by")
      .notNull()
      .references(() => users.id, { onDelete: "cascade" }),
    agentId: text("agent_id").references(() => agents.id, {
      onDelete: "cascade",
    }),
    appKey: text("app_key").notNull(),
    appTitle: text("app_title").notNull(),
    kind: text("kind").$type<"enable" | "grant" | "connect_shared">().notNull(),
    status: text("status")
      .$type<"pending" | "approved" | "dismissed" | "cancelled">()
      .notNull()
      .default("pending"),
    createdAt: createdAt(),
    resolvedAt: timestamp("resolved_at", { withTimezone: true }),
    resolvedBy: text("resolved_by"),
  },
  (table) => [
    uniqueIndex("app_access_requests_pending_idx")
      .on(
        table.requestedBy,
        table.appKey,
        sql`coalesce(${table.agentId}, '')`,
        table.kind,
      )
      .where(sql`${table.status} = 'pending'`),
    index("app_access_requests_status_idx").on(table.status, table.appKey),
    check(
      "app_access_requests_kind_check",
      sql`${table.kind} IN ('enable', 'grant', 'connect_shared')`,
    ),
    check(
      "app_access_requests_status_check",
      sql`${table.status} IN ('pending', 'approved', 'dismissed', 'cancelled')`,
    ),
  ],
);