Databases and recovery
Database per app environment
Logical model:
Application
├── production SQLite DB
├── preview chg_123 SQLite DB
└── preview chg_456 SQLite DB
Never:
one giant shared SQLite database for all apps
The DB binding service maps:
organization + application + environment
→ authorized SQLite instance
The application never learns the filesystem location.
Physical database service
Each active SQLite database has:
- Encrypted persistent storage
- WAL mode where appropriate
- Controlled connection pool
- Serialized/managed write behavior
- Resource limits
- Migration lock
- Backup/snapshot integration
- Metrics and corruption checks
Avoid running SQLite over a generic shared network filesystem.
Runtime replicas call the DB binding service over authenticated internal RPC.
Migration files
Repository convention:
database/
├── migrations/
│ ├── 0001_create_requests.sql
│ ├── 0002_add_department.sql
│ └── 0003_add_approval.sql
└── seed.ts
The platform stores:
migration name
checksum
source revision
classification
applied environment
applied time
deployment
result
An immutable migration that has run in production cannot be edited in place. A changed checksum fails the build/deployment.
Migration classifications
Additive/low risk
CREATE TABLE
CREATE INDEX
ADD COLUMN with safe defaults/nullable semantics
Potentially destructive
DROP TABLE
DROP COLUMN
rename/rebuild
constraint tightening
type narrowing
large data rewrite
Unclassifiable
Anything the migration parser cannot confidently categorize.
Unclassifiable production migrations are treated as potentially destructive.
For the pilot organization:
- All production migrations require deployment approval during the pilot.
- Destructive migrations always require an authorized human.
- Regulated migrations eventually require stronger separation of duties.
Deployment migration flow
1. Build succeeds
2. Run migrations against preview DB
3. Run tests/health checks
4. Analyze/classify migrations
5. Obtain required approval
6. Acquire production migration lock
7. Create pre-migration snapshot
8. Apply unapplied migrations
9. Verify checksums and schema
10. Start new runtime revision
11. Run health check
12. Switch production route
13. Retain old revision and snapshot
If a migration fails, the existing production runtime remains active wherever schema compatibility permits.
Backward-compatible migrations
Templates and AI guidance should favor expand-and-contract:
Deployment 1:
add new column, support old and new
Deployment 2:
backfill and switch behavior
Deployment 3:
remove old column after rollback window
This preserves the ability to roll code back without immediately restoring data.
Code rollback versus data restore
These are separate operations.
Code rollback
active artifact B
→ active artifact A
Fast, normally nondestructive.
Database restore
current DB
→ selected snapshot
Potentially destroys legitimate records created after the snapshot.
The UI must never present these as a single ambiguous “Rollback” action.
Backup design
Pilot defaults, configurable by contract:
Online recovery points: frequent, target 15-minute RPO
Daily backups: 30 days
Monthly backups: 12 months
Pre-migration snapshots: retained at least 30 days
Deleted-app recovery: 30 days
Use SQLite’s Online Backup API or a validated equivalent to obtain live snapshots. SQLite documents that the backup can run incrementally while other users continue and produces a destination representing a snapshot of the source. SQLite Online Backup API
Backups must be:
- Encrypted
- Stored off the primary runtime volume
- Integrity-checked
- Associated with app/environment
- Periodically restored in automated and manual drills
- Subject to organization retention/purge policy
Safe restore workflow
Do not overwrite the live DB immediately.
1. Select recovery point
2. Restore to a new database instance
3. Run integrity check
4. Start isolated validation environment
5. Review/approve
6. Quiesce writes if required
7. Atomically switch DB binding
8. Retain old DB for a defined window
9. Audit operation
Preview data
Default preview data:
- Empty schema plus migrations
- Synthetic seed records
- Test identities
- No production copy
Optional future modes:
schema only
approved synthetic fixture set
approved de-identified snapshot
Production data cloning is never the default.
Export and portability
Organization admins can export:
- Native SQLite database
- SQL dump where practical
- CSV by table
- Migration history
- Schema manifest
Source and data remain portable if an organization leaves intrnl.
KV
KV is intended for:
- Cache
- Session-like app state
- Feature flags
- Ephemeral coordination
- Temporary counters
- Short-lived data
Authoritative workflow records belong in DB.
Each app environment gets a logical KV namespace through the binding proxy. The app never receives raw Valkey credentials.
KV backup/recovery guarantees are weaker than database guarantees unless a future durable KV tier explicitly defines otherwise.
Migration failure boundary
Keeping the old runtime active does not undo schema changes. The migration runner must prevent incompatible or partially applied schema changes from silently leaving that runtime broken. Phase 5 acceptance must exercise migration failures and schema compatibility explicitly. If compatibility cannot be maintained, stop activation and follow approved recovery procedures; route stability alone does not guarantee uninterrupted service.