Skip to main content

ADR-042: Litestream for Standalone SQLite Backup

Status: Superseded (Standalone Mode Only) Date: 2025-12-29 Superseded By: ADR-043 (Bidirectional Context Memory Sync) Author: Hal Casteel

Status Change Notice​

⚠️ IMPORTANT: This ADR has been superseded by ADR-043.

Primary Architecture (ADR-043):

  • PostgreSQL (GKE) is the source of truth
  • PowerSync syncs PostgreSQL → SQLite clients
  • REST API pushes SQLite → PostgreSQL
  • Litestream is NOT needed for the primary architecture

Litestream is now OPTIONAL for:

  • Standalone mode (users who opt out of cloud sync)
  • Air-gapped environments (no internet connectivity)
  • Local-only development (no CODITECT account)

Context​

CODITECT originally evaluated Litestream for multi-machine SQLite synchronization. The analysis scored Litestream + Git at 78/100 as the best option for SQLite-only sync.

However, with the adoption of PostgreSQL as the primary record of truth (ADR-043), Litestream becomes redundant:

With PostgreSQL + PowerSyncLitestream Needed?
Local SQLite lost❌ Restore from PostgreSQL
Multi-machine sync❌ PowerSync handles this
Disaster recovery❌ PostgreSQL has backups
Point-in-time recovery❌ PostgreSQL WAL provides this

Decision​

Litestream is relegated to "Standalone Mode" - an optional feature for users who choose not to use cloud synchronization.

Architecture Comparison​

PRIMARY ARCHITECTURE (ADR-043):
┌────────────────────────────────────────────────────────────┐
│                                                            │
│   SQLite ───push───▶ PostgreSQL ───PowerSync───▶ SQLite   │
│   (local)    API      (GKE master)    sync       (all)    │
│                                                            │
│   ✅ Bidirectional    ✅ Multi-tenant    ✅ Team sharing   │
│                                                            │
└────────────────────────────────────────────────────────────┘

STANDALONE MODE (This ADR - Optional):
┌────────────────────────────────────────────────────────────┐
│                                                            │
│   SQLite ───Litestream───▶ GCS ◀───Litestream─── SQLite   │
│   (primary)    WAL stream      restore only     (replica) │
│                                                            │
│   ⚠️ One-way only    ⚠️ No multi-tenant    ⚠️ No merge    │
│                                                            │
└────────────────────────────────────────────────────────────┘

When to Use Litestream (Standalone Mode)​

Use CaseUse Litestream?Why
CODITECT SaaS user❌ NoUse PostgreSQL + PowerSync
Team collaboration❌ NoUse PostgreSQL + PowerSync
Multi-tenant customer❌ NoUse PostgreSQL + PowerSync
Air-gapped environment✅ YesNo cloud connectivity
Opt-out of cloud sync✅ YesPrivacy preference
Local dev only✅ YesNo CODITECT account needed

Standalone Mode Configuration​

For users who opt into Litestream standalone mode:

# Enable standalone mode (no cloud sync)
export CODITECT_SYNC_MODE=standalone

# Setup Litestream backup to GCS
./scripts/litestream-sidecar.sh setup
./scripts/litestream-sidecar.sh start

# On another machine - restore
./scripts/litestream-sidecar.sh restore

Standalone Bucket Structure​

gs://coditect-cloud-infra-context-backups/
└── standalone/
    └── {user_id}/
        └── {machine_id}/
            ├── *.wal           # WAL segments
            └── snapshots/      # Periodic snapshots

Components (Retained for Standalone Mode)​

ComponentLocationPurpose
Litestream binary~/.local/bin/litestreamReplication engine
Configconfig/litestream.ymlGCS replica settings
Sidecar scriptscripts/litestream-sidecar.shManagement CLI
Submodulesubmodules/integrations/litestreamSource reference

Migration Path​

Users currently using Litestream should migrate to PostgreSQL + PowerSync:

# 1. Push existing SQLite data to PostgreSQL
./scripts/context-sync.py push --initial

# 2. Verify data in PostgreSQL
./scripts/context-sync.py status

# 3. Stop Litestream
./scripts/litestream-sidecar.sh stop

# 4. Enable PowerSync for downstream sync
# (automatic once authenticated with CODITECT cloud)

Consequences​

Positive​

  • Simplified primary architecture - No sidecar process needed
  • True bidirectional sync - PostgreSQL + PowerSync handles all cases
  • Unified data model - Single source of truth
  • Better multi-tenant support - PostgreSQL row-level security

Negative (for Standalone Users)​

  • One-way only - Litestream doesn't merge, just replaces
  • No conflict resolution - Primary machine wins
  • Manual coordination - Must manually manage which machine is primary
  • ADR-043: Bidirectional Context Memory Sync (PRIMARY - supersedes this)
  • ADR-014: Multi-Product Commerce Architecture
  • ADR-118: Four-Tier Database Architecture

References​

Original Author: Claude Code + Hal Casteel Status Changed: 2025-12-29 Reason: PostgreSQL + PowerSync provides superior bidirectional sync