Reference Data

Not every table is tenant-scoped. Some tables hold reference data shared across all tenants, and others hold user-scoped data that predates or spans tenant boundaries. These tables intentionally skip RLS.

Categories of Global Tables

Reference/Lookup Data

These tables contain system-wide configuration that all tenants read but none own.

ai_model -- The model registry. Stores every AI model the system can use, with provider, capabilities, context window size, and an active flag for soft-disabling models.

export const aiModel = pgTable(
  "ai_model",
  {
    id: text("id").primaryKey(), // e.g., "claude-opus-4-6"
    provider: aiProviderEnum("provider").notNull(),
    apiModelId: text("api_model_id").notNull(),
    displayName: text("display_name").notNull(),
    contextWindow: integer("context_window").notNull(),
    maxOutputTokens: integer("max_output_tokens").notNull(),
    supportedFeatures: jsonb("supported_features")
      .$type<{
        thinking?: boolean;
        vision?: boolean;
        streaming?: boolean;
        batch?: boolean;
        toolUse?: boolean;
        structuredOutputs?: boolean;
      }>()
      .notNull()
      .default({}),
    active: boolean("active").notNull().default(true),
    createdAt: timestamp("created_at", { withTimezone: true }).defaultNow().notNull(),
    updatedAt: timestamp("updated_at", { withTimezone: true }).defaultNow().notNull(),
  },
  (table) => [uniqueIndex("ai_model_apiModelId_uidx").on(table.apiModelId)],
);

Key design choices:

• id matches apiModelId -- for current models, the primary key is the same as the API identifier (e.g., "claude-opus-4-6"). This makes joins against aiUsage.model straightforward.
• supportedFeatures as typed JSONB -- a boolean feature map that avoids needing a separate feature junction table. New capabilities can be added without schema changes.
• active flag -- soft-deletes models without removing historical usage data that references them.

ai_model_pricing -- Historical pricing records with effective dates. Multiple rows per model, one per pricing type and date range.

export const aiModelPricing = pgTable(
  "ai_model_pricing",
  {
    id: text("id").primaryKey(), // e.g., "claude-opus-4-6-input-2026-03-27"
    modelId: text("model_id")
      .notNull()
      .references(() => aiModel.id, { onDelete: "cascade" }),
    pricingType: aiPricingTypeEnum("pricing_type").notNull(),
    unit: aiPricingUnitEnum("unit").notNull(),
    costAmount: numeric("cost_amount", { precision: 10, scale: 6 }).notNull(),
    effectiveFrom: date("effective_from").notNull(),
    effectiveUntil: date("effective_until"),
    createdAt: timestamp("created_at", { withTimezone: true }).defaultNow().notNull(),
  },
  // ...
);

Key design choices:

• Temporal pricing -- effectiveFrom/effectiveUntil allows tracking price changes over time without modifying existing rows. When a provider changes pricing, a new row is inserted with the new effective date and the old row gets an effectiveUntil value.
• numeric(10,6) for cost -- financial precision. Returns strings in JavaScript, which is acceptable since pricing lookups are infrequent and the values are used in cost calculation, not display.
• Deterministic IDs -- pricing row IDs follow the pattern {modelId}-{pricingType}-{effectiveFrom}, making seeding idempotent via ON CONFLICT DO NOTHING.

User-Scoped Data

These tables are filtered by user_id in application code rather than RLS.

personal_access_token -- MCP server authentication tokens. User-scoped because a PAT identifies a user, and the MCP server resolves the active tenant from the user's session context.

export const personalAccessToken = pgTable(
  "personal_access_token",
  {
    id: text("id").primaryKey(),
    userId: text("user_id")
      .notNull()
      .references(() => user.id, { onDelete: "cascade" }),
    name: text("name").notNull(),
    tokenHash: text("token_hash").notNull().unique(),
    tokenPrefix: text("token_prefix").notNull(),
    lastUsedAt: timestamp("last_used_at", { withTimezone: true }),
    expiresAt: timestamp("expires_at", { withTimezone: true }),
    revokedAt: timestamp("revoked_at", { withTimezone: true }),
    createdAt: timestamp("created_at", { withTimezone: true }).defaultNow().notNull(),
  },
  // ...
);

Key design choices:

• Hash-only storage -- the plain token is shown once at creation and never stored. The SHA-256 hash is used for lookup.
• tokenPrefix for identification -- the first 8 characters of the token, stored separately so users can identify which token is which in the UI without seeing the full value.
• Soft revocation -- revokedAt marks a token as revoked without deleting it, preserving the audit trail.
• PAT data access functions live in @repo/db -- listUserPATs(), createPAT(), and revokePAT() are exported from the db package to avoid ESLint no-restricted-imports exceptions in the API layer.

Identity Tables (Better Auth)

The user, session, account, verification, and organization tables are managed by Better Auth's schema. They follow Better Auth's conventions rather than Trovella's schema conventions:

• Timestamps lack { withTimezone: true } (Better Auth convention)
• No tenantColumns spread (managed by the plugin)
• organization uses text("type") instead of tenantTypeEnum

These tables are the tenant boundary itself -- organization.id is the value stored in app.tenant_id session variable. They cannot be tenant-scoped because they define what a tenant is.

Access Patterns for Global Tables

Global tables are queried using the bare db connection from @repo/db/client, not withTenantContext():

import { db } from "@repo/db/client";
import { aiModel } from "@repo/db/schema";

// Global reference data -- no tenant context needed
const models = await db.select().from(aiModel).where(eq(aiModel.active, true));

This is one of the few legitimate uses of the bare db import. The ESLint restriction on @repo/db/client is intentionally permissive in specific files:

• AI usage recording (system-level, writes to aiUsage with known org context)
• MCP auth resolution (resolves PAT before tenant context exists)
• Health checks (database connectivity)
• Migrations and seeds (superuser operations)

Deciding: Tenant-Scoped or Global?

When designing a new table, ask:

1. Does this data belong to a specific organization? If yes, it is tenant-scoped.
2. Would different organizations see different rows? If yes, tenant-scoped.
3. Is this system configuration that all tenants share? If yes, global reference data.
4. Does this data belong to a user regardless of which organization they are in? If yes, user-scoped (global, filtered by application code).

Examples of each decision:

Table	Decision	Reasoning
researchPlan	Tenant-scoped	Org A's research must be invisible to Org B
aiUsage	Tenant-scoped	Usage records are per-org for billing
aiModel	Global	All orgs use the same model registry
personalAccessToken	User-scoped	PATs identify a user across organizations
documentChunk	Tenant-scoped	Embeddings contain tenant-specific content