Database — Dolt Version Control

SureCentric uses Dolt as its metadata database in place of PostgreSQL. Dolt is a MySQL-compatible SQL database that adds Git-style version control natively to every table — branches, commits, diffs, tags, and rollback — making it the ideal foundation for a regulated clinical intelligence platform where every schema change must be auditable, reversible, and attributed to a specific actor.

"Dolt is to databases what Git is to code." — dolthub.com

Why Dolt for SureCentric

Clinical Requirement	Dolt Feature
Every schema change must be attributed	Each DDL COMMIT records committer name + email (set per-request)
Schema deployments must be reversible	CALL dolt_reset('--hard') or CALL dolt_revert('commitHash')
Parallel development without conflict	CALL dolt_checkout('-b', 'schema/edc-v1.1') — full branch isolation
Regulatory time-travel queries	SELECT * FROM my_table AS OF 'main~3' — point-in-time reads
Immutable audit trail backing	Content-addressed storage — any tampering is cryptographically detectable
Certified schema releases	CALL dolt_tag('edc-v1.1-certified') — immutable release tags

Two Databases on One Dolt Instance

Database	Purpose
surecentric	CARD API metadata (projects, card profiles, schemas) + all audit tables
superset	Apache Superset metadata (charts, dashboards, roles, datasources)

Superset connects to Dolt using the standard mysql+mysqlconnector SQLAlchemy driver — no Superset customisation required.

Dolt vs. PostgreSQL Migration

SureCentric migrated from PostgreSQL to Dolt/MySQL. Key driver changes:

Layer	Before	After
CARD API driver	pg (node-postgres)	mysql2
MikroORM driver	@mikro-orm/postgresql	@mikro-orm/mysql
Superset metadata driver	psycopg2	mysql-connector-python
Docker image	postgres:17	dolthub/dolt-sql-server:latest

Key SQL Operations

-- Commit a schema change with actor attribution
SET @@dolt_head_committer_email = '[email protected]';
SET @@dolt_head_committer_name  = 'Jane Smith';
CALL dolt_add('sb_card_profile', 'sq_schema');
CALL dolt_commit('-m', 'schema: deploy edc.v1.1 [[email protected]]');

-- Create a staging branch before applying DDL
CALL dolt_checkout('-b', 'schema/edc-v1.1-staging');

-- Apply DDL, verify, then merge to main
CALL dolt_checkout('main');
CALL dolt_merge('schema/edc-v1.1-staging');

-- Roll back a bad deployment
CALL dolt_reset('--hard');                    -- resets working set to HEAD
CALL dolt_revert('abc123commitHash');         -- inverts a specific commit

-- Tag a certified release
CALL dolt_tag('edc-v1.1-certified');

-- View commit history
SELECT commit_hash, committer, committer_email, message, date
FROM dolt_log ORDER BY date DESC LIMIT 10;

-- Diff between two branches
SELECT * FROM dolt_diff('main', 'schema/edc-v1.1-staging', 'sq_schema');

-- Point-in-time query
SELECT * FROM sq_schema AS OF 'edc-v1.0-certified';

Schema Branching Workflow

SureCentric follows a Git-flow-like branching strategy for all schema changes:

main                          ← certified production schemas only
  │
  ├── schema/edc-v1.1         ← developer branch (human-authored)
  │     └── → PR review → merge to main → dolt_tag('edc-v1.1-certified')
  │
  ├── schema/edc-v1.1-agent-abc  ← agent-initiated branch (named with run UUID)
  │     └── → human review required before merge to main
  │
  └── rollback/edc-v0.9       ← emergency revert snapshot

Branch Naming Conventions

Branch Pattern	Created By	Purpose
schema/<name>	Human developer	Standard schema development
schema/<name>-agent-<uuid>	AI Agent	Agent-initiated schema change
rollback/<version>	System/Manual	Pre-rollback snapshot

Dolt Committer Identity

The committer identity on each Dolt commit provides the actor attribution required by FDA 21 CFR Part 11. Identity is set at the start of every request lifecycle:

• Human users: committer = user's email from JWT claim
• AI Agents: committer = agent:<AgentName>@surecentric, name = <AgentName> (run: <uuid>)
• System jobs: committer = [email protected]

-- Per-request before any dolt_commit call
SET @@dolt_head_committer_email = ?;   -- from ActorContext
SET @@dolt_head_committer_name  = ?;   -- from ActorContext

Disaster Recovery

Dolt supports remote push/pull using DoltHub or any S3-compatible backend:

# Configure remote backup (run once in init.sh)
dolt remote add backup s3://your-bucket/surecentric-dolt-backup

# Scheduled push (cron, daily)
dolt push backup main

This gives a complete, versioned, point-in-time-restorable backup of the entire schema history.