Data Isolation, Storage Architecture & Multi-Tenant Strategy True Specification

Data Isolation, Storage Architecture & Multi-Tenant Strategy True Specification

Document Status: 🟢 Active True Specification
Target Audience: Core Engineering, Architecture Contributors, AI Agents
Primary Maintainer: SiteSwarm Architecture Council
Related Documents: HIGH_LEVEL_DESIGN.md, docs/PRD.md, docs/CAPABILITY_MANAGEMENT.md, docs/CLIENT_CMS.md, docs/specs/MULTI_WORKER_PLATFORM_SERVICES.md
Related Epics: #101 (Capability Governance & Platform Services), #34, #35 Implementation Tracking: Issue #29

---

1. Executive Summary & Core Architectural Invariants

SiteSwarm is engineered to power dozens of independent local business web applications from a single monorepo while being operated sustainably by full-time software engineers. For this business model to scale without crushing the engineering team with maintenance overhead or ballooning infrastructure bills, the storage layer must decouple data isolation from operational friction.

Historically, multi-tenant architectures force an agonizing binary choice:

1. Dedicated Database Per Client: Provides complete physical isolation and effortless offboarding, but introduces the “$N$-Database Migration Nightmare” and makes fleet-wide analytics (e.g. monthly ROI digests) excruciating to orchestrate.
2. Monolithic Shared Multi-Tenancy: Consolidates all data into a single shared database with tenant_id columns, simplifying migrations and reporting, but introducing severe risks of cross-tenant query leaks, noisy-neighbor contention, and messy client offboarding.

SiteSwarm resolves this dilemma through a Capability-Oriented Multi-Tier Storage Architecture (Modular Monolith / Monorepo Microservices).

Rather than treating storage as a blanket property of a client, storage isolation is governed at the capability level:

• Shared Platform Capabilities (Tier 1 - Shared by Default): Core operational business-as-usual (BAU) CMS data (hours, banners) and shared cross-cutting horizontal services (Lead Capture, Catering & Quotes Engine) run on pooled, tenant-partitioned platform storage. This eliminates per-client database provisioning and makes fleet analytics trivial.
• Dedicated Graduated Storage (Tier 2 - Isolated on Demand): High-touch clients requiring full headless CMS editing (menus, blogs, rich inventory), high transactional write volume, or strict regulatory isolation are promoted to dedicated Cloudflare D1 databases. The premium monthly retainers paid by these clients comfortably absorb any incremental cloud resources.
• Edge KV & Object Storage (Tier 3 - Distributed Read Caching & Media): Edge KV caches read-heavy configuration for instant sub-5ms global delivery, while Cloudflare R2 provides zero-egress object storage for photos and documents.

flowchart TD
    subgraph ClientFleet["Client Application Tier (apps/)"]
        ClientA["Bakery (Standard Tier)\napps/sweet-valley-bakery"]
        ClientB["Boutique Cafe (Standard Tier)\napps/cafe-luna"]
        ClientC["Fine Dining Restaurant (Premium CMS)\napps/bistro-deluxe"]
    end

    subgraph ServiceMesh["Monorepo Service Mesh (Cloudflare Service Bindings)"]
        ServiceBAU["@siteswarm/service-cms-bau\n(0ms In-Memory V8 Binding)"]
        ServiceQuotes["@siteswarm/service-quotes\n(0ms In-Memory V8 Binding)"]
    end

    subgraph PlatformStorage["Tier 1: Platform-Shared Storage"]
        DB_BAU["Cloudflare D1: platform-bau-db\n(Partitioned by tenant_id)"]
        DB_Quotes["Cloudflare D1: platform-quotes-db\n(Catering & Lead inquiries)"]
    end

    subgraph DedicatedStorage["Tier 2: Graduated Dedicated Storage"]
        DB_ClientC["Cloudflare D1: client-bistro-deluxe-d1\n(EmDash Full CMS, Wine List, Menus)"]
    end

    subgraph EdgeAssetStorage["Tier 3: Edge KV & Object Storage"]
        KV_Cache["Cloudflare KV: edge-runtime-cache\n(Hours, Banners, Feature Flags)"]
        R2_Media["Cloudflare R2: siteswarm-client-media\n(Zero-Egress Photography & Assets)"]
    end

    ClientA -->|Service Binding| ServiceBAU
    ClientA -->|Service Binding| ServiceQuotes
    ClientB -->|Service Binding| ServiceBAU
    ClientB -->|Service Binding| ServiceQuotes

    ClientC -->|Service Binding| ServiceQuotes
    ClientC -->|Direct D1 Binding| DB_ClientC

    ServiceBAU --> DB_BAU
    ServiceBAU --> KV_Cache
    ServiceQuotes --> DB_Quotes

    ClientC --> R2_Media
    ClientA -.->|Fast Read| KV_Cache

The Three Foundational Storage Invariants:

1. The Blast-Radius & Leak Prevention Invariant: Multi-tenant queries in platform-shared services must enforce compile-time or middleware-level tenant scoping. A single bug or unindexed query must never leak Customer Inquiries or PII across client boundaries.
2. The Zero-Lockout & Instant Portability Invariant: Clients retain complete ownership of their data. In accordance with the Zero-Lockout Mandate (docs/CLIENT_CMS.md), departing clients can be cleanly extracted into an open, portable format (standard SQLite file + asset bundle) in under 5 minutes without manual database refactoring.
3. The Sub-Linear Operational Invariant: Adding the 50th client to SiteSwarm must require zero manual database provisioning or infrastructure scripts for standard capabilities. Platform-wide schema updates must run in a single transaction.

---

2. Multi-Tenancy Contender Evaluation Matrix

To establish the storage architecture, we evaluated three primary multi-tenancy models against SiteSwarm’s operating constraints: near-$0 baseline costs, zero daytime maintenance distractions, automated monthly ROI reporting, and clean client portability.

Evaluation Dimension	Contender 1: Strict Physical Isolation (1 D1 per Client)	Contender 2: Monolithic Logical Multi-Tenancy (1 Shared D1)	Contender 3: Capability-Oriented Multi-Tier (SiteSwarm Canonical)
Cloudflare Free Tier Ceiling	🔴 Fails at 11 clients (Hard cap of 10 D1 DBs on Free plan)	🟢 Passes (Single DB uses 1 of 10 slots)	🟢 Passes (Platform services share 2–3 DBs; fits Free plan)
Fleet Schema Migrations	🔴 High friction: Must run migrations $N$ times; risk of schema drift	🟢 Zero friction: Single migration applies to all clients	🟢 Near-Zero: 1 migration for platform services; isolated runs for premium
Monthly Agency ROI Reporting	🔴 Severe bottleneck: Must query $N$ distinct DBs via subrequest loops	🟢 Instant: Single GROUP BY tenant_id query across all leads	🟢 Instant: Quotes and leads live in shared platform capability service
Blast Radius & Leak Risk	🟢 Zero: Physical boundary prevents cross-tenant data leaks	🔴 High: Developer error omitting WHERE tenant_id = ? leaks PII	🟢 High Protection: Strict runtime assertion on shared; physical on premium
Client Offboarding / Export	🟢 Trivial: 1-click wrangler d1 export <db>	🔴 Complex: Custom ETL dump filtering rows by tenant_id	🟢 Built-In: 1-click for dedicated; automated CLI export tool for shared
Noisy Neighbor Contention	🟢 Isolated: A viral client won’t exhaust another client’s D1 locks	🔴 Vulnerable: Traffic spikes or heavy CMS writes lock shared DB	🟢 Protected: Heavy CMS promoted to dedicated D1; BAU isolated from writes
Amortized Infrastructure Cost	🟡 ~$5/mo flat on Workers Paid up to 50k DBs	🟢 $0/mo Free or $5/mo Workers Paid	🟢 Near-$0 Free / $5/mo Paid, financed by premium retainers
Architectural Verdict	❌ REJECTED AS FLEET-WIDE BLANKET	❌ REJECTED AS MONOLITHIC DEFAULT	🟢 ADOPTED AS CANONICAL STRATEGY

---

3. Storage Tiers & Capability Taxonomy

SiteSwarm categorizes all application state into three distinct architectural storage tiers:

┌────────────────────────────────────────────────────────────────────────┐
│                        SiteSwarm Storage Tiers                         │
├──────────────────────────┬─────────────────────────────────────────────┤
│ Tier 1: Platform-Shared  │ • Basic BAU CMS (Hours, Banners, Alerts)    │
│ (Cloudflare D1 & KV)     │ • Lead Capture & Contact Form Submissions   │
│                          │ • Catering & Quote Estimation Engine        │
│                          │ • Fleet Telemetry & Synthetic Alarm Logs    │
├──────────────────────────┼─────────────────────────────────────────────┤
│ Tier 2: Dedicated Tenant │ • Full Headless CMS (EmDash / Astro Blog)   │
│ (Dedicated Cloudflare D1)│ • Dynamic Menus, Wine Lists & Daily Specials│
│                          │ • E-Commerce, Reservations & High-Rate Data │
│                          │ • Strict Regulatory / Data Sovereignty PII  │
├──────────────────────────┼─────────────────────────────────────────────┤
│ Tier 3: Edge Object/KV   │ • Edge KV: 60s TTL Fast-Read Public Cache   │
│ (Cloudflare KV & R2)     │ • Cloudflare R2: High-Resolution Media & PDF│
└──────────────────────────┴─────────────────────────────────────────────┘

3.1 Tier 1: Platform-Shared Storage (Default / Low-Overhead)

Tier 1 serves the vast majority of standard local business client needs. By grouping shared capabilities into dedicated internal microservices, we eliminate the overhead of managing dozens of individual databases.

Domain A: Basic BAU CMS Service (@siteswarm/service-cms-bau)

Stores simple operational overrides (hours, holiday schedules, emergency announcement banners).

• Storage Engine: Central Cloudflare D1 database (siteswarm-platform-bau-db).
• Caching Layer: Cloudflare KV (siteswarm-edge-cache) with a 60-second TTL.
• Write Mechanism: EmDash CMS (as specified in docs/CLIENT_CMS.md) writes to D1, immediately updates KV, and purges the edge cache.
• Schema:

CREATE TABLE client_bau_overrides (
    tenant_id TEXT NOT NULL,
    setting_key TEXT NOT NULL,
    setting_value JSON NOT NULL,
    updated_by TEXT NOT NULL,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (tenant_id, setting_key)
);

CREATE INDEX idx_bau_tenant ON client_bau_overrides(tenant_id);

Domain B: Shared Lead & Catering / Quote Engine (@siteswarm/service-quotes)

Local businesses (bakeries, florists, cafes, auto mechanics) typically receive 5–50 inbound leads or catering/service quote inquiries per week. Deploying dedicated databases for this volume is wasteful.

• Storage Engine: Central Cloudflare D1 database (siteswarm-platform-quotes-db).
• Data Flow: Public inquiries pass through Cloudflare Turnstile bot protection, are validated by Zod, and insert atomically into the shared quotes table.
• Fleet Telemetry Integration: Enables the agency to run instant aggregation queries to populate the automated monthly ROI digest (docs/PRD.md#L304):

CREATE TABLE platform_lead_quotes (
    id TEXT PRIMARY KEY,
    tenant_id TEXT NOT NULL,
    form_type TEXT NOT NULL, -- e.g. 'catering_quote', 'contact_inquiry', 'table_booking'
    customer_name TEXT NOT NULL,
    customer_email TEXT NOT NULL,
    customer_phone TEXT,
    event_date TEXT,
    guest_count INTEGER,
    estimated_value_cents INTEGER,
    message TEXT,
    status TEXT DEFAULT 'pending', -- 'pending', 'contacted', 'booked', 'archived'
    ip_hash TEXT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_quotes_tenant_date ON platform_lead_quotes(tenant_id, created_at DESC);

3.2 Tier 2: Graduated Dedicated Storage (Isolated on Demand)

When a client’s business model expands beyond basic operational settings, the client application graduates to Tier 2.

Graduation Criteria

A client is promoted to a dedicated Cloudflare D1 database when any of the following triggers are met:

1. Full Content Management System: The client requires complete in-browser editing of pages, blogs, multi-category restaurant menus, wine lists, or team profiles (e.g. via EmDash CMS).
2. High Write Throughput: The application processes frequent real-time webhooks, point-of-sale inventory synchronizations, or active customer transactions.
3. Data Isolation & Compliance Mandates: Enterprise or medical/legal clients with strict contractual requirements regarding multi-tenant PII separation.
4. Custom Schema Variations: The client requires bespoke tables that deviate significantly from standard platform capability schemas.

Financial Economics of Graduation

• Running a dedicated D1 database incurs $0 in additional fixed infrastructure fees under the Cloudflare Workers Paid plan ($5/mo for up to 50,000 databases).
• High-touch clients requiring dedicated storage are billed on agency premium retainers ($200 to $1,000+/month). The marginal cost of any additional storage ($0.75/GB-mo beyond 5 GB) or compute is fully covered by the client contract.

3.3 Tier 3: Edge Object & Media Storage (Cloudflare R2)

All binary assets (high-resolution food photography, restaurant menus in PDF format, logo SVGs, client-uploaded banner images) are strictly separated from relational databases.

• Storage Engine: Cloudflare R2 bucket (siteswarm-fleet-media).
• Path Partitioning: Assets are partitioned under /media/<tenant_id>/<asset_hash>.<ext>.
• Zero Egress: Cloudflare R2 does not charge egress fees, ensuring high-resolution image delivery across client websites never triggers surprise bandwidth bills.
• Image Transformation: Paired with Cloudflare Image Resizing or edge Worker transforms to serve responsive AVIF/WebP formats dynamically.

---

4. In-Monorepo Microservices & Service Bindings

SiteSwarm leverages Cloudflare Service Bindings to connect independent client web applications (apps/<client-slug>) to shared platform capability services without traversing the public internet.

sequenceDiagram
    autonumber
    participant Browser as Client Customer Phone
    participant ClientWorker as apps/sweet-valley-bakery (Astro SSR)
    participant QuotesService as @siteswarm/service-quotes (Worker)
    participant PlatformD1 as Cloudflare D1 (platform-quotes-db)
    participant Twilio as Twilio Webhook (SMS Alert)

    Browser->>ClientWorker: POST /api/catering-quote (FormData)
    Note over ClientWorker: Verifies Turnstile Token
    ClientWorker->>QuotesService: env.QUOTES_SERVICE.submitQuote(payload)
    Note over QuotesService: Zero-Latency In-Memory V8 Call
    QuotesService->>PlatformD1: INSERT INTO platform_lead_quotes (tenant_id, ...)
    QuotesService->>Twilio: Dispatch SMS alert to business owner (<10s)
    QuotesService-->>ClientWorker: { success: true, quoteId: "q_8192" }
    ClientWorker-->>Browser: 200 OK (Render Confirmation Step)

Advantages of Cloudflare Service Bindings:

1. Zero-Latency In-Memory IPC: Calls execute directly between V8 isolates in the same edge data center. There is no DNS lookup, no TLS handshake, and 0ms network latency.
2. Zero Ingress/Egress Cost: Worker-to-worker calls over service bindings do not count as external HTTP bandwidth.
3. Compile-Time Contract Sharing: The shared service exposes typed TypeScript RPC methods using @cloudflare/workers-types or hono/rpc, ensuring compile-time validation between client frontends and platform backend services.

---

5. Type-Safe Governance & Manifest Contracts

To maintain compile-time safety and eliminate runtime binding surprises, the storage mode of every capability is declared inside the client’s swarm.config.ts.

5.1 Extension to @siteswarm/governance

packages/governance/src/types.ts

/**
 * Storage isolation modes supported across platform capabilities.
 */
export type StorageMode =
  | "platform-shared"   // Uses shared multi-tenant platform D1/KV via service binding
  | "dedicated"         // Uses a dedicated client D1 database binding
  | "static-only";      // Relies purely on build-time static configuration with zero DB

/**
 * Configuration contract for capability storage.
 */
export interface CapabilityStorageConfig {
  mode: StorageMode;
  /** Binding name in wrangler.toml if using dedicated storage */
  dedicatedBindingName?: string;
  /** Custom platform service identifier if routing to a specialized service */
  serviceBindingName?: string;
}

/**
 * Enhanced Horizontal Capability Contract supporting Storage Governance.
 */
export interface GovernedCapabilityUsage<K extends KnownCapabilityName = KnownCapabilityName> {
  type: "horizontal";
  version: string;
  options: SwarmCapabilityRegistry[K];
  targets: string[];
  storage: CapabilityStorageConfig;
  notes?: string;
}

5.2 Client Manifest Examples

Standard Tier Client (Sweet Valley Bakery)

Uses platform-shared storage for both BAU CMS and catering quotes:

apps/sweet-valley-bakery/swarm.config.ts

import { defineAppConfig } from "@siteswarm/governance";

export default defineAppConfig({
  clientSlug: "sweet-valley-bakery",
  capabilities: {
    "cms-bau": {
      type: "horizontal",
      version: "1.0.0",
      options: { allowEmergencyAlerts: true },
      targets: ["src/components/HoursBanner.astro"],
      storage: {
        mode: "platform-shared",
        serviceBindingName: "CMS_BAU_SERVICE"
      }
    },
    "lead-capture": {
      type: "horizontal",
      version: "1.2.0",
      options: { provider: "turnstile", notifyChannel: "sms", storeSubmissions: true },
      targets: ["src/pages/catering-quote.astro"],
      storage: {
        mode: "platform-shared",
        serviceBindingName: "QUOTES_SERVICE"
      }
    }
  }
});

Upgraded Tier Client (Bistro Deluxe)

Uses platform-shared storage for lead capture, but features a dedicated D1 database for full EmDash CMS:

apps/bistro-deluxe/swarm.config.ts

import { defineAppConfig } from "@siteswarm/governance";

export default defineAppConfig({
  clientSlug: "bistro-deluxe",
  capabilities: {
    "lead-capture": {
      type: "horizontal",
      version: "1.2.0",
      options: { provider: "turnstile", notifyChannel: "sms", storeSubmissions: true },
      targets: ["src/pages/inquiries.astro"],
      storage: {
        mode: "platform-shared",
        serviceBindingName: "QUOTES_SERVICE"
      }
    },
    "menu-catalog": {
      type: "horizontal",
      version: "2.1.0",
      options: { currency: "USD", enableDietaryBadges: true, supportsOrdering: false },
      targets: ["src/pages/menu/[category].astro"],
      storage: {
        mode: "dedicated",
        dedicatedBindingName: "BISTRO_D1" // Dedicated Cloudflare D1 binding
      }
    }
  }
});

---

6. Data Integrity, SQLite WAL Mode & Edge Resiliency

Cloudflare D1 is built on SQLite running over Cloudflare’s distributed storage infrastructure. To ensure zero data loss and prevent lock contention at the edge, SiteSwarm enforces strict transactional practices:

6.1 WAL Mode & Read Replication Handling

1. Primary Coordinator for Writes: All D1 writes (INSERT, UPDATE, DELETE) route to the designated primary database location to ensure serializable transaction ordering.
2. Read Replication: D1 automatically distributes read replicas globally across North America, Europe, Asia-Pacific, and Oceania. Queries execute against the replica closest to the requesting user.
3. Session Consistency: In multi-step user interactions (e.g. a client updating their holiday hours and immediately checking their live site), the worker utilizes the withSession API to guarantee “read-your-own-writes” consistency across read replicas.

6.2 The Static Fallback Invariant

If Cloudflare D1 or KV encounters a regional outage or temporary network partition, a client’s public website must never return an HTTP 500 error or a blank component.

• Every client app bundles static default seed data in swarm.config.ts.
• The edge SSR template wraps database reads in resilient try-catch fallbacks:

apps/sweet-valley-bakery/src/utils/hours.ts

export async function getLiveBusinessHours(env: Env, tenantId: string): Promise<BusinessHours> {
  try {
    const cached = await env.EDGE_CACHE.get(`hours:${tenantId}`, "json");
    if (cached) return cached as BusinessHours;

    const live = await env.CMS_BAU_SERVICE.getHours(tenantId);
    if (live) return live;
  } catch (error) {
    console.error(`[Resilience Fallback] Failed to fetch live hours for ${tenantId}:`, error);
  }
  // Safe Invariant: Always fall back to verified static configuration
  return fallbackHours;
}

---

7. Backup, Disaster Recovery & Point-in-Time Recovery (PITR)

Data integrity requires both continuous point-in-time rollback capabilities and independent offline snapshot archives.

flowchart LR
    subgraph EdgeD1["Live D1 Operations"]
        LiveDB["Live D1 Database\n(Platform or Dedicated)"]
        TimeTravel["D1 Time Travel\n(Continuous 30-Day PITR)"]
    end

    subgraph AutomatedCron["Daily Backup Cron Worker (02:00 UTC)"]
        DumpJob["wrangler d1 export --output=snapshot.sql"]
        Encrypt["AES-256 GCM Encryption"]
    end

    subgraph ColdArchive["Off-Site Cold Archive"]
        R2_Backup["Encrypted Cloudflare R2 Bucket\nsiteswarm-db-backups\n(Retention: 90 Days)"]
        S3_Glacier["Optional Secondary Glacier Mirror"]
    end

    LiveDB --> TimeTravel
    LiveDB --> DumpJob
    DumpJob --> Encrypt
    Encrypt --> R2_Backup
    R2_Backup -.->|Weekly Sync| S3_Glacier

7.1 Continuous Recovery: D1 Time Travel

• Workers Paid Tier: Automatically maintains 30 days of continuous point-in-time recovery.
• If a client accidentally deletes a table or an engineer introduces a corrupt migration, the database can be restored to any exact minute:

  npx wrangler d1 time-travel restore <database_name> --timestamp="2026-09-29T12:00:00Z"

• Restore rate limits (10 restores per 10 minutes per database) are respected.

7.2 Scheduled Logical Backups

In addition to Time Travel, an automated cron Worker runs daily at 02:00 UTC:

1. Iterates over all platform databases and active dedicated client databases.
2. Generates an atomic SQL dump via the Cloudflare D1 REST API / wrangler d1 export.
3. Encrypts the dump with AES-256-GCM.
4. Streams the archive into the siteswarm-db-backups R2 bucket with automated 90-day lifecycle expiration rules.

---

8. Client Offboarding & Extraction Runbook (Zero-Lockout Protocol)

In accordance with SiteSwarm’s core values, no client is ever held hostage. If a client chooses to migrate away from SiteSwarm or transition to self-hosting, engineering follows this standardized, zero-friction runbook.

Case A: Departing Client on Dedicated Storage (Tier 2)

Because their data is physically isolated, offboarding requires a single terminal command:

# 1. Export entire SQLite database to a standalone SQL file
npx wrangler d1 export client-bistro-deluxe-d1 --output=bistro-deluxe-export.sql

# 2. Export client R2 media assets
npx wrangler r2 object get siteswarm-fleet-media --directory="media/bistro-deluxe/" --output="./bistro-media/"

# 3. Zip and deliver package
zip -r bistro-deluxe-data-handoff.zip bistro-deluxe-export.sql bistro-media/

• The exported SQL file runs natively on standard SQLite, Turso, Cloudflare D1, or Postgres (via pgloader).

Case B: Departing Client on Platform-Shared Storage (Tier 1)

For clients using shared platform capabilities, the swarm storage export CLI utility extracts their data cleanly without exposing any other tenant’s information:

# Execute automated tenant extraction CLI
pnpm swarm storage export --tenant=sweet-valley-bakery --out=./export

• Under the hood, the utility:
  1. Connects to platform-bau-db and queries SELECT * FROM client_bau_overrides WHERE tenant_id = 'sweet-valley-bakery'.
  2. Connects to platform-quotes-db and queries SELECT * FROM platform_lead_quotes WHERE tenant_id = 'sweet-valley-bakery'.
  3. Writes the results into a brand new, portable sweet-valley-bakery.sqlite file.
  4. Generates a standalone JSON and CSV export of all customer leads for easy import into Mailchimp, HubSpot, or Excel.
  5. Packages all R2 media assets associated with the tenant.

---

9. Schema Migration & Fleet Orchestration

To maintain velocity, schema migrations must be automated, predictable, and protected against fleet-wide schema drift.

9.1 Migration Engine: Drizzle ORM

SiteSwarm adopts Drizzle ORM for all relational schema definitions and migrations due to its native edge compatibility, zero-dependency footprint, and automatic SQL migration file generation (drizzle-kit).

9.2 Migration Execution Matrix in CI

flowchart TD
    Commit["Git Push / PR Merge to main"] --> CI["GitHub Actions CI Pipeline"]
    CI --> CheckChanges{"Detect Changed Directories"}

    CheckChanges -->|"packages/service-cms-bau/**"| Mig_BAU["Run Migrations:\nplatform-bau-db (1x execution)"]
    CheckChanges -->|"packages/service-quotes/**"| Mig_Quotes["Run Migrations:\nplatform-quotes-db (1x execution)"]
    CheckChanges -->|"apps/<client>/** (Dedicated DB)"| Mig_Dedicated["Run Migrations:\nclient-<slug>-d1 (Targeted client execution)"]

    Mig_BAU --> Verify["Run Post-Migration Synthetic Probes"]
    Mig_Quotes --> Verify
    Mig_Dedicated --> Verify

1. Platform-Shared Migrations: Executed once against the shared platform database. Because standard clients share these services, 100% of standard clients are upgraded simultaneously with zero drift risk.
2. Dedicated Tenant Migrations: Path-filtered in CI. When an engineer modifies schema in apps/<client-slug>/schema/, migrations execute only against that client’s specific D1 database.
3. Drift Detection: The swarm audit CLI includes a --verify-migrations check that compares D1 migration tables against local migration files in CI.

---

10. Data Privacy, Encryption & Security Standards

1. Encryption at Rest: All Cloudflare D1 databases, KV namespaces, and R2 buckets are encrypted at rest by default using Cloudflare-managed AES-256 keys.
2. Encryption in Transit: All client traffic, worker-to-worker service bindings, and database connections require TLS 1.3.
3. Tenant Query Sanitization: All queries executed against platform-shared databases must use parameterized statements (db.prepare("SELECT ... WHERE tenant_id = ?").bind(tenantId)). Raw SQL string interpolation is strictly prohibited by ESLint rule @siteswarm/no-unsafe-sql.
4. PII Retention & Scrubbing: Inquiries older than 365 days in platform_lead_quotes can be automatically anonymized (IP hashes cleared, email addresses masked) via an automated monthly edge worker task to maintain CCPA/GDPR compliance.

---

11. Implementation Roadmap & Sub-Tickets

The data isolation and storage strategy will be rolled out across the following sequenced sub-tickets:

flowchart LR
    T1["Sub-Ticket 1: Storage Contracts\n(@siteswarm/governance StorageMode)"] --> T2["Sub-Ticket 2: Shared Platform D1\n(BAU CMS & Quotes schemas)"]
    T2 --> T3["Sub-Ticket 3: Service Bindings\n(Cloudflare Worker Microservices)"]
    T3 --> T4["Sub-Ticket 4: Extraction CLI\n(swarm storage export runbook)"]

• Sub-Ticket 1: Extend @siteswarm/governance with StorageMode, CapabilityStorageConfig, and update defineAppConfig validation.
• Sub-Ticket 2: Define Drizzle ORM schemas and create Cloudflare D1 databases for shared platform services (platform-bau-db and platform-quotes-db).
• Sub-Ticket 3: Implement in-monorepo microservices for @siteswarm/service-cms-bau and @siteswarm/service-quotes with Cloudflare Service Bindings.
• Sub-Ticket 4: Author the swarm storage export CLI utility to guarantee automated 1-click tenant extraction for departing clients.