Database Setup

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

Set DATABASE_URL

export DATABASE_URL=postgresql://causeloop_app:password@host:5432/causeloop

For Neon:

export DATABASE_URL=postgresql://causeloop_app:password@ep-xxx.neon.tech/causeloop?sslmode=require

Load schema, seed data, and functions

psql "$DATABASE_URL" -f db/schema.sql
psql "$DATABASE_URL" -f db/seed_reference.sql
psql "$DATABASE_URL" -f db/onboard_client.sql

Apply migrations

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.

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:

make migrate

RLS two-role model

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.

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

-- 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:

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:

SELECT set_config('app.current_workspace', 'ws_01j...', true);

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

-- 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:

# 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

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 for the current gap status.

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

Self-Hosting

Database & Provisioning

Security & Compliance

Supported databases

Database files

Applying the schema

Migrations

RLS two-role model

Create the application role

How RLS works

Neon-specific note

Schema conventions

Entity overview

Backup and restore

​Supported databases

​Database files

​Applying the schema

​Migrations

​RLS two-role model

​Create the application role

​How RLS works

​Neon-specific note

​Schema conventions

​Entity overview

​Backup and restore

Supported databases

Database files

Applying the schema

Migrations

RLS two-role model

Create the application role

How RLS works

Neon-specific note

Schema conventions

Entity overview

Backup and restore