> ## Documentation Index
> Fetch the complete documentation index at: https://docs.causeloop.ai/llms.txt
> Use this file to discover all available pages before exploring further.

# Database Setup

> Postgres setup, schema loading, migrations, RLS tenant isolation, and the required two-role model.

Causeloop uses PostgreSQL 16 with Row-Level Security (RLS) as the primary tenant isolation mechanism. When `DATABASE_URL` is not set the app runs with an in-memory store — suitable for development and demos. Set `DATABASE_URL` when you want persistent data.

## Supported databases

| Option                        | Notes                                                                                                            |
| ----------------------------- | ---------------------------------------------------------------------------------------------------------------- |
| **Neon** (managed)            | Recommended for production. Serverless Postgres with branching. Use `?sslmode=require` in the connection string. |
| **PostgreSQL 16** self-hosted | Docker Compose (included), bare metal, or any managed Postgres (RDS, Cloud SQL, Azure Database).                 |
| **Railway Postgres**          | Works, but see the RLS warning below — Railway's default user has BYPASSRLS.                                     |

## Database files

| File                    | Purpose                                                       | When to run                               |
| ----------------------- | ------------------------------------------------------------- | ----------------------------------------- |
| `db/schema.sql`         | All tables, constraints, indexes, triggers, RLS policies      | Once per database (fresh setup)           |
| `db/seed_reference.sql` | Global reference data — the connector catalogue               | Once per database                         |
| `db/onboard_client.sql` | `onboard_client()` and `offboard_client()` PL/pgSQL functions | Once (defines functions); call per client |
| `db/migrations/`        | Forward-only numbered SQL migrations                          | In order after initial schema             |

## Applying the schema

<Steps>
  <Step title="Set DATABASE_URL">
    ```bash theme={null}
    export DATABASE_URL=postgresql://causeloop_app:password@host:5432/causeloop
    ```

    For Neon:

    ```bash theme={null}
    export DATABASE_URL=postgresql://causeloop_app:password@ep-xxx.neon.tech/causeloop?sslmode=require
    ```
  </Step>

  <Step title="Load schema, seed data, and functions">
    ```bash theme={null}
    psql "$DATABASE_URL" -f db/schema.sql
    psql "$DATABASE_URL" -f db/seed_reference.sql
    psql "$DATABASE_URL" -f db/onboard_client.sql
    ```
  </Step>

  <Step title="Apply migrations">
    ```bash theme={null}
    make migrate
    # or directly:
    python scripts/migrate.py
    ```

    Migrations are numbered SQL files in `db/migrations/` and are applied in order. The script is idempotent — it tracks applied migrations and skips already-applied ones.
  </Step>
</Steps>

## Migrations

The `db/migrations/` directory contains forward-only numbered migrations. Do not modify existing migration files after they have been applied.

| Migration                           | Description                                                                    |
| ----------------------------------- | ------------------------------------------------------------------------------ |
| `0001_force_rls.sql`                | Enables `FORCE ROW LEVEL SECURITY` on all tenant tables (including audit\_log) |
| `0002_audit_trace_append_only.sql`  | Makes `audit_log` append-only via trigger                                      |
| `0003_workspace_keys.sql`           | Adds `workspace_keys` table for per-workspace DEK storage                      |
| `0004_webhook_secret_encrypted.sql` | Adds `secret_encrypted` column on `webhooks`                                   |
| `0005_compliance_log.sql`           | Adds compliance event log table                                                |
| `0006_resolve_inbound_webhook.sql`  | Extends inbound webhook resolution                                             |

Apply all migrations after the initial schema load:

```bash theme={null}
make migrate
```

## RLS two-role model

<Warning>
  **This is the most critical database security step.** The Postgres superuser bypasses Row-Level Security. If your application connects as the superuser (or as `neondb_owner` on Neon, which has `BYPASSRLS`), every RLS policy is silently ignored and tenant data is not isolated.

  Always connect the application as a dedicated non-superuser role.
</Warning>

Causeloop requires two database roles:

| Role                                  | Purpose                            | Permissions                                                                       |
| ------------------------------------- | ---------------------------------- | --------------------------------------------------------------------------------- |
| `causeloop_owner` (or `neondb_owner`) | Schema management: migrations, DDL | Superuser or `BYPASSRLS`. **Never used by the application at runtime.**           |
| `causeloop_app`                       | Application runtime connection     | `NOSUPERUSER`, `NOBYPASSRLS`, granted `SELECT/INSERT/UPDATE/DELETE` on all tables |

### Create the application role

```sql theme={null}
-- Run as the database owner / superuser
CREATE ROLE causeloop_app WITH LOGIN PASSWORD 'strong-password' NOSUPERUSER NOCREATEDB NOCREATEROLE;

-- Grant usage on the schema
GRANT USAGE ON SCHEMA public TO causeloop_app;

-- Grant DML on all existing tables
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO causeloop_app;

-- Grant usage on sequences (for ID generation if used)
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO causeloop_app;

-- Ensure future tables are also covered
ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO causeloop_app;
```

Then connect your application as `causeloop_app`:

```env theme={null}
DATABASE_URL=postgresql://causeloop_app:strong-password@host:5432/causeloop
```

### How RLS works

RLS policies use a session-local GUC (`app.current_workspace`) that the application sets at the start of each request:

```sql theme={null}
SELECT set_config('app.current_workspace', 'ws_01j...', true);
```

The `app_current_tenant()` function reads this GUC, and every tenant table has four policies:

```sql theme={null}
-- Example for the `issues` table:
ALTER TABLE issues ENABLE ROW LEVEL SECURITY;
ALTER TABLE issues FORCE  ROW LEVEL SECURITY;

CREATE POLICY tenant_select ON issues FOR SELECT
    USING (workspace_id = app_current_tenant());

CREATE POLICY tenant_insert ON issues FOR INSERT
    WITH CHECK (workspace_id = app_current_tenant());

CREATE POLICY tenant_update ON issues FOR UPDATE
    USING (workspace_id = app_current_tenant())
    WITH CHECK (workspace_id = app_current_tenant());

CREATE POLICY tenant_delete ON issues FOR DELETE
    USING (workspace_id = app_current_tenant());
```

`FORCE ROW LEVEL SECURITY` means even the table owner is subject to policies in the `causeloop_app` session (though not in a superuser session — which is why the non-superuser role is essential).

### Neon-specific note

On Neon, the default `neondb_owner` role has `BYPASSRLS`. **Do not use `neondb_owner` in `DATABASE_URL`.** Create a separate `causeloop_app` role as above and use that connection string instead.

## Schema conventions

Causeloop's schema follows consistent conventions:

| Convention   | Detail                                                                                                                                          |
| ------------ | ----------------------------------------------------------------------------------------------------------------------------------------------- |
| Primary keys | Prefixed, time-ordered TEXT (`iss_`, `pat_`, `ws_`, `org_`, ...). Generated by `app/ids.py`.                                                    |
| Timestamps   | `TIMESTAMPTZ` with `DEFAULT now()`. Soft-delete tables add `deleted_at TIMESTAMPTZ`.                                                            |
| Enums        | Modelled as `TEXT` with `CHECK` constraints, not native `ENUM` types (avoids `ALTER TYPE` migrations).                                          |
| JSONB        | Used for variable shapes: connector configs, audit `after`/`before` payloads, metadata.                                                         |
| Soft deletes | Rows are marked with `deleted_at` rather than physically deleted during normal operation. Hard deletes happen only in RTBF / offboarding flows. |
| Secrets      | Columns named `*_encrypted` hold ciphertext only. Plaintext secrets never touch the database.                                                   |

## Entity overview

The core tenancy model:

```
organizations (1) ──< workspaces (1) ──< [all tenant data]
                          │
                          └──< memberships ──< users
```

Key tables by category:

| Category      | Tables                                                                         |
| ------------- | ------------------------------------------------------------------------------ |
| Tenancy       | `organizations`, `workspaces`, `memberships`, `users`                          |
| Product core  | `issues`, `patterns`, `predictions`, `recommendations`, `clusters`             |
| Integrations  | `connectors`, `webhooks`, `inbound_webhooks`                                   |
| Auth surface  | `personal_access_tokens`, `service_accounts`, `mfa_factors`, `sso_connections` |
| Governance    | `audit_log`, `export_jobs`, `rtbf_requests`, `compliance_log`                  |
| Encryption    | `workspace_keys`                                                               |
| Orchestration | `workflow_runs`, `jobs`                                                        |

## Backup and restore

The built-in backup approach uses `pg_dump`:

```bash theme={null}
# Backup
pg_dump "$DATABASE_URL" --no-owner --no-acl -Fc -f causeloop_$(date +%Y%m%d).dump

# Restore
pg_restore --clean --no-owner --no-acl -d "$DATABASE_URL" causeloop_20260101.dump
```

<Note>
  Automated backup verification (scheduled backup + restore test + checksum validation) is on the SOC 2 roadmap but not yet implemented. Until then, run and verify backups manually on a schedule. See [SOC 2 readiness](/deploy-security/soc2) for the current gap status.
</Note>

For Neon, point-in-time restore is available through the Neon console without needing manual `pg_dump` jobs.