Database CDC Service

The Database Service streams changes from your application database to real-time subscribers using PostgreSQL Change Data Capture (CDC). Administrators define domain mappings that control which table changes produce events and how payloads are constructed.

How It Works

PostgreSQL WAL
  → Logical Replication Slot
  → CDC Reader (worker)
  → Mapping Evaluator
  → Event Router
  → Redis Pub/Sub
  → Socket Gateway
  → Connected Clients

Two Databases

Target Database Preparation

Before the CDC reader can monitor your application database, the target database needs to be configured with the correct settings and user privileges.

SHOW wal_level;
-- If not 'logical':
ALTER SYSTEM SET wal_level = 'logical';
-- Then restart PostgreSQL

-- Create the CDC user
CREATE ROLE realtime_cdc WITH LOGIN PASSWORD 'your-secure-password' REPLICATION;

-- Grant connect access to the target database
GRANT CONNECT ON DATABASE your_app_db TO realtime_cdc;

-- Grant usage on all schemas the CDC reader will monitor
GRANT USAGE ON SCHEMA public TO realtime_cdc;
GRANT USAGE ON SCHEMA salesforce TO realtime_cdc;  -- if using Salesforce schema
-- Repeat for any additional schemas

-- Grant SELECT on all tables in those schemas
GRANT SELECT ON ALL TABLES IN SCHEMA public TO realtime_cdc;
GRANT SELECT ON ALL TABLES IN SCHEMA salesforce TO realtime_cdc;
-- Repeat for any additional schemas

-- IMPORTANT: Grant superuser so the CDC user can manage publications
-- on tables it doesn't own (e.g. tables managed by Salesforce, ORM migrations, etc.)
ALTER ROLE realtime_cdc WITH SUPERUSER;

-- PostgreSQL 15+ only — grants publication management without full superuser
GRANT pg_publication_owner TO realtime_cdc;

-- Should return 'logical'
SHOW wal_level;

-- Should show the CDC user with replication privilege
SELECT rolname, rolreplication, rolsuper
FROM pg_roles
WHERE rolname = 'realtime_cdc';

-- Check max_replication_slots (default is 10, increase if needed)
SHOW max_replication_slots;

-- Check max_wal_senders (must be > 0)
SHOW max_wal_senders;

Connecting in Application Settings

Domain Mappings

Mappings define which table changes become realtime events. Tables do not automatically stream events — every mapping must be explicitly configured.

Mapping Structure

{
  "id": "map_session_status_v1",
  "table": "sessions",
  "events": ["insert", "update"],
  "topic": "session.status",
  "triggerColumns": ["status", "ended_at"],
  "when": {
    "or": [
      { "changed": "status" },
      { "changed": "ended_at" }
    ]
  },
  "payload": {
    "sessionId": "$row.id",
    "status": "$row.status",
    "endedAt": "$row.ended_at",
    "campusId": "$row.campus_id"
  }
}

Configuration Fields

Creating Mappings

curl -X POST http://localhost:3000/api/database/mappings \
  -H 'Content-Type: application/json' \
  -d '{
    "table": "sessions",
    "topic": "session.status",
    "events": ["insert", "update"],
    "triggerColumns": ["status", "ended_at"],
    "payload": {
      "sessionId": "$row.id",
      "status": "$row.status",
      "endedAt": "$row.ended_at"
    }
  }'

Mapping Conditions

Mappings support conditional rules to control when events fire:

Changed Column

Fire only when a specific column changes:

{ "changed": "status" }

Equality Check

Fire only when a column equals a specific value:

{ "eq": ["$row.status", "active"] }

Logical Operators

Combine conditions with and / or:

{
  "and": [
    { "changed": "status" },
    { "eq": ["$row.status", "active"] }
  ]
}

{
  "or": [
    { "changed": "status" },
    { "changed": "ended_at" }
  ]
}

Payload Templates

Payload fields use $row.* references to extract values from the changed row:

{
  "sessionId": "$row.id",
  "status": "$row.status",
  "endedAt": "$row.ended_at",
  "campusId": "$row.campus_id"
}

The mapping evaluator resolves these references at runtime using the actual row data from the CDC event.

Mapping Validation

Mappings are validated against the target topic's schema:

• Required fields must be present in the payload template
• Field types must be compatible
• Schema violations are reported inline in the Admin UI

Environment Promotion

Mappings support a three-stage promotion workflow:

Development → Staging → Production

Environment Grid

The Environment Grid page (/environments) shows all mappings across all environments with color-coded sync indicators:

• Green — in sync across environments
• Yellow — newer version available for promotion
• Red — missing in target environment

Outbox Pattern

In addition to CDC, the platform supports the transactional outbox pattern:

Application write
  → event_outbox table insert (in same transaction)
  → Outbox Worker polls for unprocessed rows
  → Event Router
  → Redis Pub/Sub
  → Clients

The event_outbox table uses a partial index on unprocessed rows for efficient polling.

Verifying CDC

1. Check the Database Pipeline dashboard in the Admin UI — the reader state should show Streaming
2. Make a change in a monitored table
3. Look for CDC event routed log entries in the backend logs
4. Check the Event History tab in the Database Pipeline page to see captured events
5. Use the Event Debugger in the Admin UI to inspect routed events

Troubleshooting

API Reference