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
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
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 |
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.
| 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;
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);
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
| 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. pg_dump jobs. 
