Company Schema & Data Boundaries

How company_id is enforced at query level to prevent cross-tenant data leaks, the role of foreign keys, and why every downstream entity inherits the boundary.

The Company Schema in Depth

What you'll learn

A thorough reference for the companies table, how Paperclip AI enforces the company_id boundary across every query, and what multi-tenancy means in practice.

The Full companies Table

CREATE TABLE public.companies (
  id                 uuid        PRIMARY KEY DEFAULT gen_random_uuid(),
  name               text        NOT NULL,
  slug               text        NOT NULL UNIQUE,
  monthly_budget_usd numeric(10,2) NOT NULL DEFAULT 0,
  paused             boolean     NOT NULL DEFAULT false,
  created_at         timestamptz NOT NULL DEFAULT now()
);

Paperclip AI keeps this lean on purpose. The company record is the anchor, not the configuration store — settings live on agents, tasks, and cost policies.

The company_id Boundary

Every subordinate table carries a company_id foreign key:

-- agents
ALTER TABLE public.agents
  ADD COLUMN company_id uuid NOT NULL REFERENCES public.companies(id);

-- issues
ALTER TABLE public.issues
  ADD COLUMN company_id uuid NOT NULL REFERENCES public.companies(id);

-- heartbeat_runs
ALTER TABLE public.heartbeat_runs
  ADD COLUMN company_id uuid NOT NULL REFERENCES public.companies(id);

The application layer enforces this at two points:

1. On write — every INSERT passes company_id from the authenticated context. The API never lets a caller set an arbitrary company_id; it reads it from the session/JWT.

2. On read — every SELECT adds WHERE company_id = $current_company_id. This is done by a helper function used in all repository files:

// packages/server/src/lib/db/repos/base.ts
export function withCompany<T extends { company_id: string }>(
  qb: SelectQueryBuilder<T>,
  companyId: string
) {
  return qb.where('company_id', '=', companyId);
}

Why This Matters

Without the boundary, a bug in a multi-company system can leak Agent A's tasks to Company B's queries. Paperclip AI makes this impossible at the repository layer — even if the HTTP route forgot to filter, the base query builder enforces it.

For development with a single company this is transparent. For production SaaS it means you can safely run many companies in one database.

Checking the Boundary in Practice

# See all companies (admin/dev only)
psql $DATABASE_URL -c "SELECT id, name, slug, paused FROM companies;"

# Count items per company
psql $DATABASE_URL -c "
  SELECT c.name,
         COUNT(DISTINCT a.id) AS agents,
         COUNT(DISTINCT i.id) AS issues
  FROM companies c
  LEFT JOIN agents a ON a.company_id = c.id
  LEFT JOIN issues i ON i.company_id = c.id
  GROUP BY c.name;"

The paused Flag

When cumulative spend for the month exceeds monthly_budget_usd, the heartbeat system sets paused = true. While paused:
- No new heartbeat runs start for this company
- Existing agent tasks are not picked up
- Approval workflows are unaffected (humans can still sign off)
- The UI shows a banner prompting you to raise the budget or wait for month rollover

To manually resume during development:

UPDATE companies SET paused = false WHERE slug = 'acme-automations';

Checkpoint ✓

• [ ] You can describe what company_id enforces and where it is applied
• [ ] You understand the paused flag and how to reset it
• [ ] You can query company stats from the database