# Paperclip AI Database Schema for Companies and Multi-Company Isolation

> Explore the Paperclip AI database schema for robust multi-company data isolation. Discover how a single-tenant architecture with mandatory company_id ensures secure data separation. Learn more.

- Repository: [Paperclip/paperclip](https://github.com/paperclipai/paperclip)
- Tags: architecture
- Published: 2026-08-12

---

**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](https://github.com/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/main/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:

```typescript
// 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/`](https://github.com/paperclipai/paperclip/blob/master/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/main/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/main/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/main/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/main/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/main/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/main/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/main/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/main/server/src/routes/issues.ts)](https://github.com/paperclipai/paperclip/blob/master/server/src/routes/issues.ts):

```typescript
// 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:

```typescript
// 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:

```typescript
// 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`](https://github.com/paperclipai/paperclip/blob/main/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`](https://github.com/paperclipai/paperclip/blob/main/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`](https://github.com/paperclipai/paperclip/blob/main/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/`](https://github.com/paperclipai/paperclip/blob/master/packages/db/src/schema/). The `companies` table definition lives in [`companies.ts`](https://github.com/paperclipai/paperclip/blob/main/companies.ts), while foreign key relationships appear in domain-specific files like [`agents.ts`](https://github.com/paperclipai/paperclip/blob/main/agents.ts), [`issues.ts`](https://github.com/paperclipai/paperclip/blob/main/issues.ts), [`tool_access.ts`](https://github.com/paperclipai/paperclip/blob/main/tool_access.ts), and [`activity_log.ts`](https://github.com/paperclipai/paperclip/blob/main/activity_log.ts). Each uses `.notNull().references(() => companies.id)` to ensure referential integrity at the database level.