Paperclip AI Database Schema for Companies and Multi-Company Isolation

Paperclip AI achieves strict multi-tenant data isolation through a single-tenant database architecture where every business-critical table contains a mandatory company_id foreign key referencing the companies table, enforced by runtime API boundary checks.

The Paperclip AI platform (paperclipai/paperclip) implements a robust company-scoped tenancy model that prevents data leakage between organizations. This design centers on database-level foreign key constraints in packages/db/src/schema/ coupled with runtime authorization checks in the API layer.

The companies Table: Foundation of Tenant Isolation

The tenancy model begins with the companies table defined in [packages/db/src/schema/companies.ts](https://github.com/paperclipai/paperclip/blob/master/packages/db/src/schema/companies.ts). This table represents the root entity for every organization using the platform.

Core Schema Definition

The Drizzle ORM schema defines the following structure:

// packages/db/src/schema/companies.ts
export const companies = pgTable("companies", {
  id: uuid("id").defaultRandom().primaryKey(),
  name: text("name").notNull(),
  description: text("description"),
  status: text("status").notNull().default("active"),
  issue_prefix: text("issue_prefix").notNull().default("PAP"),
  budget_monthly_cents: integer("budget_monthly_cents"),
  spent_monthly_cents: integer("spent_monthly_cents").default(0),
  attachment_max_bytes: integer("attachment_max_bytes").default(10485760),
  require_board_approval_for_new_agents: boolean("require_board_approval_for_new_agents").default(false),
  interaction_resolver_governance: jsonb("interaction_resolver_governance"),
  created_at: timestamp("created_at").defaultNow().notNull(),
  updated_at: timestamp("updated_at").defaultNow().notNull(),
});

Key constraints include a unique index on issue_prefix (companies_issue_prefix_idx) that guarantees distinct human-readable identifiers across tenants, preventing collision when generating issue numbers like PAP-123 or ACM-456.

Company Configuration Fields

The schema stores critical business logic configuration at the company level:

• Budget tracking: budget_monthly_cents and spent_monthly_cents enable per-tenant resource accounting
• Governance rules: require_board_approval_for_new_agents and interaction_resolver_governance (JSONB) store company-specific policy configurations
• Resource limits: attachment_max_bytes defines per-company file upload constraints (defaulting to 10 MiB)

Enforcing Company-Scoped Visibility Through Foreign Keys

Every table storing business objects implements mandatory company scoping through non-nullable foreign key relationships. This pattern appears consistently across the schema in packages/db/src/schema/.

Foreign Key Relationships

The following tables enforce hard tenant boundaries via company_id constraints:

Table	Foreign Key Definition	Source File
agents	company_id: uuid("company_id").notNull().references(() => companies.id)	[agents.ts](https://github.com/paperclipai/paperclip/blob/master/packages/db/src/schema/agents.ts)
issues	company_id: uuid("company_id").notNull().references(() => companies.id)	[issues.ts](https://github.com/paperclipai/paperclip/blob/master/packages/db/src/schema/issues.ts)
tool_access	company_id: uuid("company_id").notNull().references(() => companies.id, { onDelete: "cascade" })	[tool_access.ts](https://github.com/paperclipai/paperclip/blob/master/packages/db/src/schema/tool_access.ts)
cost_events	company_id: uuid("company_id").notNull().references(() => companies.id)	[cost_events.ts](https://github.com/paperclipai/paperclip/blob/master/packages/db/src/schema/cost_events.ts)
activity_log	company_id: uuid("company_id").notNull().references(() => companies.id)	[activity_log.ts](https://github.com/paperclipai/paperclip/blob/master/packages/db/src/schema/activity_log.ts)

All control-plane tables—including projects, goals, and approvals—follow this identical pattern. Additionally, composite indexes such as agents(company_id, status) ensure query performance remains linear with the number of companies rather than total row count.

Implementation Specification and Invariants

The architectural contract is codified in [doc/SPEC-implementation.md](https://github.com/paperclipai/paperclip/blob/master/doc/SPEC-implementation.md), which mandates three critical invariants:

1. Every business record belongs to exactly one company (line 47)
2. Company-scoped visibility: Board members and in-company agents can access all work objects within their tenant by default (line 38)
3. Agent-manager affinity: Agents and their managers must belong to the same company (lines 74-75)

These constraints ensure that even complex hierarchical relationships respect tenant boundaries at the database level.

Runtime Enforcement in API Routes

Database constraints provide the foundation, but the application layer enforces active boundary checks. Every mutating route in server/src/routes/ validates that the authenticated actor's company_id matches the target resource.

Boundary Check Implementation

The pattern appears in [server/src/routes/agents.ts](https://github.com/paperclipai/paperclip/blob/master/server/src/routes/agents.ts) and [server/src/routes/issues.ts](https://github.com/paperclipai/paperclip/blob/master/server/src/routes/issues.ts):

// server/src/routes/agents.ts (simplified excerpt)
router.patch('/:agentId', async (req, res) => {
  const { agentId } = req.params;
  const actor = await auth.getActor(req); // Contains actor.companyId
  
  const agent = await db.select()
    .from(agents)
    .where(eq(agents.id, agentId))
    .get();

  if (agent.company_id !== actor.companyId) {
    return res.status(403).json({ error: 'Company mismatch' });
  }

  // Proceed with update...
});

This validation occurs before any mutation, ensuring that a compromised API key or session token cannot access cross-tenant data.

Practical Code Examples

Creating a New Company

To provision a new tenant via the REST API:

// POST /api/companies
const response = await fetch('http://localhost:3100/api/companies', {
  method: 'POST',
  headers: { 
    'Content-Type': 'application/json', 
    Authorization: `Bearer ${boardToken}` 
  },
  body: JSON.stringify({
    name: 'Acme Corp',
    description: 'Demo company for testing',
    issue_prefix: 'ACM',
    budget_monthly_cents: 500_00, // $500.00
    attachment_max_bytes: 20971520, // 20 MiB
  }),
});

The server validates that the caller possesses board-level privileges before inserting the companies row.

Querying Company-Scoped Data

Client applications must include the company identifier in queries:

// GET /api/issues?companyId=<uuid>
const resp = await fetch(
  `http://localhost:3100/api/issues?companyId=${companyId}`, 
  { headers: { Authorization: `Bearer ${agentApiKey}` } }
);

const issues = await resp.json(); // Returns only records matching companyId

Attempts to specify a companyId outside the actor's tenant result in a 403 Forbidden response, enforced by the route handler before database query execution.

Summary

• Hard tenant boundaries are enforced via non-nullable company_id foreign keys on every business-critical table in packages/db/src/schema/
• Unique constraints such as companies_issue_prefix_idx prevent identifier collisions across companies
• Implementation invariants in doc/SPEC-implementation.md mandate that every record belongs to exactly one company
• Runtime validation in server/src/routes/ ensures API requests cannot cross company boundaries, returning 403 for unauthorized access attempts
• Scalable indexing on (company_id, status) columns maintains query performance as the number of tenants grows

Frequently Asked Questions

How does Paperclip AI ensure data isolation between companies?

Paperclip AI implements single-tenant database isolation where every table contains a mandatory company_id foreign key referencing the companies table. This schema design, defined in files like packages/db/src/schema/companies.ts and enforced by API routes in server/src/routes/, ensures that SQL queries naturally scope to a single tenant. Runtime checks compare the authenticated actor's company_id against the requested resource, rejecting cross-tenant access with a 403 Forbidden response.

What happens if an agent tries to access data from another company?

The API layer rejects such requests with a 403 Forbidden error. In route handlers like those in server/src/routes/issues.ts, the code extracts the actor's company from the JWT or API key and compares it to the target record's company_id. If the values differ, the request aborts before any database mutation occurs, preventing data leakage between tenants.

Can the schema support multi-tenant deployment in the future?

Yes. The current single-tenant schema design already supports multi-company segregation through the company_id foreign keys. If the platform transitions to a multi-tenant deployment model, the database schema would require minimal changes—primarily adjustments to the enforcement layer in server/src/routes/ to handle tenant context switching, while the existing foreign key constraints continue to provide hard data boundaries.

Where are the database constraints for company isolation defined?

The constraints are defined in the Drizzle ORM schema files within packages/db/src/schema/. The companies table definition lives in companies.ts, while foreign key relationships appear in domain-specific files like agents.ts, issues.ts, tool_access.ts, and activity_log.ts. Each uses .notNull().references(() => companies.id) to ensure referential integrity at the database level.

{
  "mcpServers": {
    "instagit": {
      "command": "npx",
      "args": ["-y", "instagit@latest"]
    }
  }
}