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.

15.9 KB
Raw Blame
import { describe, expect, test } from "bun:test";
import { readFile } from "node:fs/promises";
import { getTableName, is } from "drizzle-orm";
import { getTableConfig, PgTable } from "drizzle-orm/pg-core";
import * as schema from "../src/db/schema";
import {
  accounts,
  agentPreferences,
  agentProfiles,
  agents,
  agentVisibility,
  auditEvents,
  channelAgents,
  channelMemberships,
  channels,
  credentialKind,
  credentials,
  intelligenceChannelMappings,
  mcpUserCredentials,
  sessions,
  userInstructions,
  userRoles,
  users,
  verifications,
} from "../src/db/schema";

describe("OpenBot database schema", () => {
  test("defines the core runtime records", () => {
    expect(
      [
        users,
        sessions,
        accounts,
        verifications,
        userRoles,
        agents,
        channels,
        channelMemberships,
        channelAgents,
        credentials,
        auditEvents,
        intelligenceChannelMappings,
        userInstructions,
      ].map(getTableName),
    ).toEqual([
      "users",
      "sessions",
      "accounts",
      "verifications",
      "user_roles",
      "agents",
      "channels",
      "channel_memberships",
      "channel_agents",
      "credentials",
      "audit_events",
      "intelligence_channel_mappings",
      "user_instructions",
    ]);
  });

  test("names the two kinds of OAuth secret separately from a shared token", () => {
    /*
     * Three different things, three names. `mcp` is one token an administrator holds for everybody.
     * An OAuth client belongs to the deployment and reaches nobody's data by itself; a refresh token
     * belongs to one person and reaches everything they can see. Filing all three under `mcp` would
     * make "what does this deployment hold" unanswerable without reading the metadata of every row,
     * and it is the question the vault exists to answer.
     */
    expect(credentialKind.enumValues).toEqual([
      "model",
      "connector",
      "agent",
      "mcp",
      "mcp_oauth_client",
      "mcp_user_token",
    ]);
  });

  test("gives one person one credential per server, and makes that the key", () => {
    expect(getTableName(mcpUserCredentials)).toBe("mcp_user_credentials");

    const config = getTableConfig(mcpUserCredentials);

    /*
     * A composite primary key, not a surrogate id.
     *
     * "Which credential serves this server for this person" must have exactly one answer. With an id
     * and no unique constraint, two rows for the same pair are legal, and then the answer depends on
     * whichever the query happened to order first — so a person who reconnected could keep being
     * served the grant they thought they had replaced.
     */
    expect(
      config.primaryKeys.flatMap((key) =>
        key.columns.map((column) => column.name),
      ),
    ).toEqual(["server_id", "user_id"]);

    expect(
      config.columns.map((column) => ({
        name: column.name,
        notNull: column.notNull,
      })),
    ).toEqual([
      { name: "server_id", notNull: true },
      { name: "user_id", notNull: true },
      { name: "credential_id", notNull: true },
      { name: "scope", notNull: true },
      { name: "connected_at", notNull: true },
      { name: "updated_at", notNull: true },
    ]);
  });

  test("gives one person one set of standing instructions, and makes that the key", () => {
    const config = getTableConfig(userInstructions);

    /*
     * The user id IS the primary key, not a column beside a surrogate one.
     *
     * "What has this person asked every coworker to do" must have exactly one answer, because the
     * answer goes into a prompt. With an id and no unique constraint two rows are legal, and then
     * which of them speaks for the person depends on whichever the query happened to order first.
     */
    expect(
      config.columns.map((column) => ({
        name: column.name,
        sqlType: column.getSQLType(),
        notNull: column.notNull,
        primary: column.primary,
      })),
    ).toEqual([
      { name: "user_id", sqlType: "text", notNull: true, primary: true },
      { name: "instructions", sqlType: "text", notNull: true, primary: false },
      {
        name: "created_at",
        sqlType: "timestamp with time zone",
        notNull: true,
        primary: false,
      },
      {
        name: "updated_at",
        sqlType: "timestamp with time zone",
        notNull: true,
        primary: false,
      },
    ]);

    /*
     * Cascade, because this is one person's own prose about themselves. A deleted account must not
     * leave behind a paragraph that would be read into somebody's prompt if the id were ever reused.
     */
    expect(
      config.foreignKeys.map((foreignKey) => {
        const reference = foreignKey.reference();
        return {
          sourceColumns: reference.columns.map((column) => column.name),
          targetTable: getTableName(reference.foreignTable),
          onDelete: foreignKey.onDelete,
        };
      }),
    ).toEqual([
      {
        sourceColumns: ["user_id"],
        targetTable: "users",
        onDelete: "cascade",
      },
    ]);
  });

  test("follows the person and the server when either goes away", () => {
    const config = getTableConfig(mcpUserCredentials);
    const cascading = config.foreignKeys.filter(
      (key) => key.onDelete === "cascade",
    );
    /*
     * Both the person and the server cascade: a deleted user must not leave a row pointing at a
     * vault secret held on their behalf, and a removed server must not leave rows nobody can reach
     * to disconnect. The credential reference deliberately does not cascade — a revoked credential
     * is kept for the trail, and losing the row that says whose it was would take the trail with it.
     */
    expect(cascading.length).toBe(2);
  });

  test("holds no copy of a customer's documents", () => {
    /*
     * The rule this schema is now built on: a Bot answers from a live system by calling that
     * system's own search as the person asking, so the vendor decides what they may see. A table
     * of documents here would be a second copy of somebody's corpus, with a second permission
     * model of our own to keep in step with theirs, and it would outlive their access to the
     * original. `documents`, `chunks` and `document_acls` were exactly that and are gone.
     *
     * This asserts on every table the schema exports rather than on named ones, so it catches a
     * reintroduction under a different name, and on column types rather than column names, so it
     * catches an embedding smuggled onto a table that sounds like something else.
     */
    const tables = Object.values(schema).filter((value): value is PgTable =>
      is(value, PgTable),
    );
    expect(tables.length).toBeGreaterThan(20);

    expect(tables.map(getTableName)).not.toContain("documents");
    expect(tables.map(getTableName)).not.toContain("chunks");
    expect(tables.map(getTableName)).not.toContain("document_acls");

    const vectorColumns = tables.flatMap((table) =>
      getTableConfig(table)
        .columns.filter((column) => column.getSQLType().startsWith("vector"))
        .map((column) => `${getTableName(table)}.${column.name}`),
    );
    expect(vectorColumns).toEqual([]);
  });

  test("includes Better Auth's verified Google identity records", () => {
    expect(Object.keys(users)).toContain("emailVerified");
    expect(Object.keys(sessions)).toEqual(
      expect.arrayContaining(["ipAddress", "userAgent"]),
    );
    expect(Object.keys(accounts)).toEqual(
      expect.arrayContaining(["userId", "providerId", "accountId"]),
    );
  });

  test("defines the exact agent profile and roster preference contracts", () => {
    expect([agentProfiles, agentPreferences].map(getTableName)).toEqual([
      "agent_profiles",
      "agent_preferences",
    ]);
    expect(agentVisibility.enumName).toBe("agent_visibility");
    expect(agentVisibility.enumValues).toEqual(["public", "private"]);

    const profileConfig = getTableConfig(agentProfiles);
    const preferenceConfig = getTableConfig(agentPreferences);

    expect(
      profileConfig.columns.map((column) => ({
        name: column.name,
        notNull: column.notNull,
        hasDefault: column.hasDefault,
        primary: column.primary,
      })),
    ).toEqual([
      { name: "agent_id", notNull: true, hasDefault: false, primary: true },
      {
        name: "owner_user_id",
        notNull: false,
        hasDefault: false,
        primary: false,
      },
      { name: "title", notNull: true, hasDefault: false, primary: false },
      {
        name: "role_description",
        notNull: true,
        hasDefault: false,
        primary: false,
      },
      {
        name: "avatar_seed",
        notNull: true,
        hasDefault: false,
        primary: false,
      },
      {
        name: "visibility",
        notNull: true,
        hasDefault: false,
        primary: false,
      },
      // Off unless the owner asked for a computer of its own and is allowed one.
      {
        name: "isolated",
        notNull: true,
        hasDefault: true,
        primary: false,
      },
      /*
       * Nullable, and that is the security property.
       *
       * Null means this agent holds no credential and may not call a tool back, which is what a URL
       * somebody pasted gets until an administrator hands it one.
       */
      {
        name: "callback_token_hash",
        notNull: false,
        hasDefault: false,
        primary: false,
      },
      {
        name: "callback_token_issued_at",
        notNull: false,
        hasDefault: false,
        primary: false,
      },
      {
        name: "deleted_at",
        notNull: false,
        hasDefault: false,
        primary: false,
      },
      {
        name: "created_at",
        notNull: true,
        hasDefault: true,
        primary: false,
      },
      {
        name: "updated_at",
        notNull: true,
        hasDefault: true,
        primary: false,
      },
    ]);

    expect(
      preferenceConfig.columns.map((column) => ({
        name: column.name,
        sqlType: column.getSQLType(),
        notNull: column.notNull,
        hasDefault: column.hasDefault,
        primary: column.primary,
      })),
    ).toEqual([
      {
        name: "user_id",
        sqlType: "text",
        notNull: true,
        hasDefault: false,
        primary: false,
      },
      {
        name: "agent_id",
        sqlType: "text",
        notNull: true,
        hasDefault: false,
        primary: false,
      },
      {
        name: "hidden_at",
        sqlType: "timestamp with time zone",
        notNull: false,
        hasDefault: false,
        primary: false,
      },
      {
        name: "pinned_at",
        sqlType: "timestamp with time zone",
        notNull: false,
        hasDefault: false,
        primary: false,
      },
    ]);

    expect(
      [...profileConfig.foreignKeys, ...preferenceConfig.foreignKeys].map(
        (foreignKey) => {
          const reference = foreignKey.reference();
          return {
            sourceColumns: reference.columns.map((column) => column.name),
            targetTable: getTableName(reference.foreignTable),
            targetColumns: reference.foreignColumns.map(
              (column) => column.name,
            ),
            onDelete: foreignKey.onDelete,
            onUpdate: foreignKey.onUpdate,
          };
        },
      ),
    ).toEqual([
      {
        sourceColumns: ["agent_id"],
        targetTable: "agents",
        targetColumns: ["id"],
        onDelete: "cascade",
        onUpdate: "no action",
      },
      {
        sourceColumns: ["owner_user_id"],
        targetTable: "users",
        targetColumns: ["id"],
        onDelete: "set null",
        onUpdate: "no action",
      },
      {
        sourceColumns: ["user_id"],
        targetTable: "users",
        targetColumns: ["id"],
        onDelete: "cascade",
        onUpdate: "no action",
      },
      {
        sourceColumns: ["agent_id"],
        targetTable: "agents",
        targetColumns: ["id"],
        onDelete: "cascade",
        onUpdate: "no action",
      },
    ]);

    expect(
      preferenceConfig.primaryKeys.map((primaryKey) => ({
        name: primaryKey.getName(),
        columns: primaryKey.columns.map((column) => column.name),
      })),
    ).toEqual([
      {
        name: "agent_preferences_user_id_agent_id_pk",
        columns: ["user_id", "agent_id"],
      },
    ]);

    expect(
      profileConfig.indexes.map((index) => ({
        name: index.config.name,
        columns: index.config.columns.map((column) =>
          "name" in column ? column.name : undefined,
        ),
        unique: index.config.unique,
        method: index.config.method,
      })),
    ).toEqual([
      {
        name: "agent_profiles_visibility_deleted_idx",
        columns: ["visibility", "deleted_at"],
        unique: false,
        method: "btree",
      },
    ]);
  });

  /*
   * The callback columns arrive as an alteration, not in the base schema.
   *
   * Every deployment of this has already applied 0000, so editing it in place changes a file the
   * database has recorded as run and the columns never appear. They are added by 0001, and this
   * says so, because the alternative failure is silent: the code reads a column the deployment
   * does not have.
   */
  test("adds the callback token columns in their own migration", async () => {
    const migration = await readFile(
      new URL("../drizzle/0001_swift_morph.sql", import.meta.url),
      "utf8",
    );
    expect(migration).toContain(
      `ALTER TABLE "agent_profiles" ADD COLUMN "callback_token_hash" text;`,
    );
    expect(migration).toContain(
      `ALTER TABLE "agent_profiles" ADD COLUMN "callback_token_issued_at" timestamp with time zone;`,
    );
  });

  test("keeps the agent profile migration aligned with the schema", async () => {
    const migration = await readFile(
      new URL("../drizzle/0000_schema.sql", import.meta.url),
      "utf8",
    );
    const normalizedMigration = migration.replace(/\s+/g, " ").trim();

    expect(normalizedMigration).toContain(
      `CREATE TYPE "public"."agent_visibility" AS ENUM('public', 'private')`,
    );
    expect(normalizedMigration).toContain(
      `"agent_id" text PRIMARY KEY NOT NULL, "owner_user_id" text, "title" text NOT NULL, "role_description" text NOT NULL, "avatar_seed" text NOT NULL, "visibility" "agent_visibility" NOT NULL, "deleted_at" timestamp with time zone, "created_at" timestamp with time zone DEFAULT now() NOT NULL, "updated_at" timestamp with time zone DEFAULT now() NOT NULL`,
    );
    expect(normalizedMigration).toContain(
      `CREATE TABLE "agent_preferences" ( "user_id" text NOT NULL, "agent_id" text NOT NULL, "hidden_at" timestamp with time zone,`,
    );
    expect(normalizedMigration).toContain(
      `CONSTRAINT "agent_preferences_user_id_agent_id_pk" PRIMARY KEY("user_id","agent_id")`,
    );
    expect(normalizedMigration).toContain(
      `ALTER TABLE "agent_profiles" ADD CONSTRAINT "agent_profiles_agent_id_agents_id_fk" FOREIGN KEY ("agent_id") REFERENCES "public"."agents"("id") ON DELETE cascade ON UPDATE no action`,
    );
    expect(normalizedMigration).toContain(
      `ALTER TABLE "agent_profiles" ADD CONSTRAINT "agent_profiles_owner_user_id_users_id_fk" FOREIGN KEY ("owner_user_id") REFERENCES "public"."users"("id") ON DELETE set null ON UPDATE no action`,
    );
    expect(normalizedMigration).toContain(
      `ALTER TABLE "agent_preferences" ADD CONSTRAINT "agent_preferences_user_id_users_id_fk" FOREIGN KEY ("user_id") REFERENCES "public"."users"("id") ON DELETE cascade ON UPDATE no action`,
    );
    expect(normalizedMigration).toContain(
      `ALTER TABLE "agent_preferences" ADD CONSTRAINT "agent_preferences_agent_id_agents_id_fk" FOREIGN KEY ("agent_id") REFERENCES "public"."agents"("id") ON DELETE cascade ON UPDATE no action`,
    );
    expect(normalizedMigration).toContain(
      `CREATE INDEX "agent_profiles_visibility_deleted_idx" ON "agent_profiles" USING btree ("visibility","deleted_at")`,
    );
  });
});