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 Delivery & Media Storage (Tier 3 - Distributed Edge Caching & Object Storage): Client applications maintain a Zero-Session-KV invariant and deliver static-first content (output: 'static') via Cloudflare Anycast Edge Cache with instant Cache-Tag purging (per ADR-0003 and docs/specs/CONTENT_INVALIDATION_AND_CACHE_FRESHNESS.md). Cloudflare R2 provides zero-egress object storage for photos and documents. Cloudflare KV is strictly restricted to internal platform service state or isolated CMS administrative caches—never exposed on public visitor paths.
• Enterprise External Storage (Tier 4 - Integrated via Hyperdrive): Enterprise clients requiring integration with centralized PostgreSQL clusters (Neon, Supabase, AWS RDS, Cloud SQL) connect through Cloudflare Hyperdrive for global edge connection pooling, TCP/TLS termination, and edge query caching.

flowchart TD
    subgraph ClientFleet["Client Application Tier (apps/) - output: 'static' SSG Edge Delivery"]
        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"]
        ClientD["Enterprise Client (Tier 4)\napps/corporate-portal"]
    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)"]
        ServicePurge["@siteswarm/service-cache-purge\n(Cache-Tag Invalidation Engine)"]
    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 Delivery & Media Storage"]
        Edge_Cache["Cloudflare Edge Cache\n(Cache-Tag Purging & Anycast Delivery)"]
        R2_Media["Cloudflare R2: siteswarm-client-media\n(Zero-Egress Photography & Assets)"]
    end

    subgraph EnterpriseStorage["Tier 4: Enterprise External Storage"]
        HyperdriveMesh["Cloudflare Hyperdrive\n(Edge Pooled Mesh & Query Cache)"]
        PostgresOrigin["Enterprise PostgreSQL\n(Neon, Supabase, AWS RDS)"]
    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 (Admin)| DB_ClientC

    ClientD -->|Hyperdrive Binding| HyperdriveMesh
    HyperdriveMesh -->|Pooled Backbone TLS| PostgresOrigin

    ServiceBAU --> DB_BAU
    ServiceBAU -->|Trigger Invalidation| ServicePurge
    ServicePurge -.->|Purge by Cache-Tag (<150ms)| Edge_Cache
    ServiceQuotes --> DB_Quotes

    ClientC --> R2_Media
    ClientD --> R2_Media
    ClientA -->|Edge Delivery (<20ms TTFB)| Edge_Cache

The Four 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.
4. The Zero-Session-KV & Edge Cache Invariant: In strict accordance with ADR-0003 and docs/specs/CONTENT_INVALIDATION_AND_CACHE_FRESHNESS.md, client applications execute with output: 'static' SSG edge delivery and maintain a strict Zero-Session-KV invariant. Public visitor routes never declare or bind KV namespaces. Edge read-caching relies on Cloudflare Anycast edge caching with instant Cache-Tag purging—never application-level manual KV read caches.

---

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 four distinct architectural storage tiers:

┌────────────────────────────────────────────────────────────────────────┐
│                        SiteSwarm Storage Tiers                         │
├──────────────────────────┬─────────────────────────────────────────────┤
│ Tier 1: Platform-Shared  │ • Basic BAU CMS (Hours, Banners, Alerts)    │
│ (Cloudflare D1 & Service │ • Lead Capture & Contact Form Submissions   │
│  Bindings)               │ • 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 Delivery,   │ • Cloudflare Edge Cache: Cache-Tag Purging  │
│ Media & R2 Storage       │ • Cloudflare R2: High-Resolution Media & PDF│
│ (Edge Cache, R2, Zero KV)│ • Zero-Session-KV Invariant on Public Routes│
├──────────────────────────┼─────────────────────────────────────────────┤
│ Tier 4: Enterprise DB    │ • External PostgreSQL (Neon, Supabase, RDS) │
│ (Cloudflare Hyperdrive)  │ • Pre-existing Corporate ERP, CRM & BI sync │
│                          │ • Specialized Extensions (pgvector, PostGIS)│
└──────────────────────────┴─────────────────────────────────────────────┘

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).
• Edge Caching & Invalidation Layer: Edge read-caching relies on Cloudflare Anycast edge caching with instant Cache-Tag purging (per docs/specs/CONTENT_INVALIDATION_AND_CACHE_FRESHNESS.md and ADR-0003) rather than application-level manual KV read-caching on public routes. Client applications maintain a strict Zero-Session-KV invariant.
• KV Scope Restriction: Cloudflare KV is strictly restricted to optional platform-internal administrative state or service coordination; it is never exposed or bound to public visitor paths.
• Write Mechanism: EmDash CMS (as specified in docs/CLIENT_CMS.md) writes to D1 and dispatches a cache invalidation request to @siteswarm/service-cache-purge targeting tenant:<slug>, evicting stale edge cache globally across 330+ PoPs in under 150ms.
• 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 Delivery, Media Storage & Harmonized KV Policy (Cloudflare R2 & Edge Cache)

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.

The Harmonized KV & Edge Caching Policy

In accordance with ADR-0003 and docs/specs/CONTENT_INVALIDATION_AND_CACHE_FRESHNESS.md, SiteSwarm enforces strict boundaries on edge caching and key-value storage:

1. Zero-Session-KV Invariant on Public Client Applications:

   • Public client applications (apps/<client>) build strictly with output: 'static' SSG edge delivery and declare zero KV namespace bindings (SESSION = 0).
   • This eliminates the Astro SSR session KV auto-provisioning problem where Wrangler would generate ephemeral KV namespaces per PR preview, exhausting account quotas and creating orphaned resources.
   • Public marketing frontends deliver sub-20ms TTFB with 0 KB client JS and zero KV runtime dependencies.

2. Cloudflare Cache-Tag Purging over Manual KV Read Caching:

   • Edge read-caching relies on Cloudflare Anycast edge caching with HTTP surrogate controls (Cache-Tag: tenant:<slug>, entity:<type>) and targeted tag purging via the centralized Platform Purge Service (@siteswarm/service-cache-purge).
   • Public visitor requests never perform manual application-level KV read-caching (env.EDGE_CACHE.get(...)) on public routes. Mutations in CMS trigger atomic Cache-Tag purges that evict stale edge cache globally across 330+ PoPs in under 150ms, delivering sub-2-second phone-to-edge freshness.

3. Strict KV Restriction (Administrative & Platform-Shared Services Only):

   • Cloudflare KV is strictly restricted to optional internal platform administrative services (e.g., auth session tracking in isolated CMS Worker B instances or platform-shared service state).
   • KV is never utilized or bound on public visitor routes.

3.4 Tier 4: Enterprise External Storage (Cloudflare Hyperdrive + PostgreSQL)

For enterprise accounts with non-negotiable external database requirements or pre-existing PostgreSQL clusters, SiteSwarm supports graduated external storage accelerated by Cloudflare Hyperdrive. See Section 4 for the complete architectural deep-dive, egress analysis, and decision matrix.

---

4. Enterprise External Database Integration: Cloudflare Hyperdrive vs. D1

While Cloudflare D1 (serverless edge SQLite) serves as SiteSwarm’s canonical, zero-maintenance baseline, agency operations frequently encounter enterprise clients with non-negotiable external database requirements. To prevent ad-hoc architectural drift while capturing high-value client contracts, SiteSwarm codifies Cloudflare Hyperdrive as the approved enterprise integration layer for PostgreSQL.

4.1 The Enterprise Reality: Centralized PostgreSQL vs. Edge SQLite

Enterprise and mid-market organizations often bring entrenched data ecosystems. Standard local business storage (D1) conflicts with corporate infrastructure when clients mandate:

1. Centralized Relational Clusters: Pre-existing relational data residing in managed PostgreSQL clusters, including:
   • Serverless PostgreSQL: Neon, Supabase, Xata.
   • Hyperscaler Managed Relational DBs: AWS RDS / Aurora PostgreSQL, Google Cloud SQL for PostgreSQL, Microsoft Azure Database for PostgreSQL.
2. Shared Legacy Systems & Business Intelligence (BI): Centralized ERPs (SAP, NetSuite), CRM backends (Salesforce), or analytics warehouses (Snowflake, Databricks, BigQuery) ingesting real-time operational records via PostgreSQL Change Data Capture (CDC / Debezium).
3. Data Sovereignty & Corporate VPC Compliance: SOC2 Type II, HIPAA, or PCI-DSS policies requiring database instances to remain strictly inside customer-owned AWS/GCP VPCs with Customer Managed Encryption Keys (CMEK) and strict audit logging.
4. PostgreSQL-Specific Capabilities: Specialized relational capabilities not native to SQLite, such as pgvector for semantic embeddings/RAG, PostGIS for geospatial indexing, or fine-grained PostgreSQL Row-Level Security (RLS).

For these scenarios, forcing a migration to D1 is a commercial deal-breaker. However, connecting distributed edge runtimes directly to remote relational databases introduces severe performance and availability failure modes.

4.2 The Serverless Edge-to-Postgres Bottleneck

Connecting hundreds of distributed Cloudflare Worker isolates directly to a centralized PostgreSQL origin introduces two catastrophic failure modes:

1. The TCP/TLS Handshake Latency Tax:
   • Cloudflare Workers run across 330+ edge PoPs globally. In a cold-start or unpooled scenario, each isolate must negotiate a new connection:
     • 1 RTT for TCP 3-way handshake.
     • 1–2 RTTs for TLS 1.3 cryptographic handshake.
     • 1–2 RTTs for PostgreSQL protocol authentication and startup negotiation.
   • If an edge Worker in London or Tokyo connects directly to an AWS RDS instance in us-east-1 (North Virginia, round-trip ~75–180ms), connection establishment alone incurs 250ms to 550ms of network latency before the first SQL statement can be dispatched.
2. Connection Exhaustion at the Origin:
   • PostgreSQL utilizes a process-based connection architecture where each connection consumes dedicated server memory (typically 5–10 MB per connection) and OS processes. PostgreSQL instances typically cap max_connections between 100 and 500.
   • A traffic spike on a client website can instantly spin up thousands of concurrent Worker isolates worldwide. When thousands of isolates attempt direct connections, the PostgreSQL database exhausts its connection pool, crashing the database or rejecting transactions with FATAL: remaining connection slots are reserved for non-replication superuser connections (SQLSTATE 53300).
   • Traditional poolers (e.g. PgBouncer, AWS RDS Proxy) mitigate server process exhaustion, but still force the edge isolate to pay the full cross-region TCP/TLS handshake latency penalty on every connection setup.

4.3 Cloudflare Hyperdrive Architecture: Global Edge Pooling & Query Acceleration

Cloudflare Hyperdrive is an edge-native database acceleration proxy designed specifically to make centralized, regional databases feel local to distributed Cloudflare Workers.

sequenceDiagram
    autonumber
    participant Browser as Global Customer (London)
    participant Worker as SiteSwarm Edge Worker (LHR PoP)
    participant Hyperdrive as Cloudflare Hyperdrive (Global Edge Mesh)
    participant Postgres as Origin PostgreSQL (AWS RDS us-east-1)

    Browser->>Worker: GET /api/enterprise/catalog
    Worker->>Hyperdrive: Query via env.HYPERDRIVE (Local Socket / 0ms Handshake)

    alt Edge Query Cache Hit
        Hyperdrive-->>Worker: Return Cached SQL Result (Sub-15ms)
        Worker-->>Browser: 200 OK (Rendered in <25ms total)
    else Edge Query Cache Miss
        Note over Hyperdrive,Postgres: Connection PRE-WARMED in persistent connection pool
        Hyperdrive->>Postgres: Stream SQL query across Cloudflare backbone (~75ms RTT)
        Postgres-->>Hyperdrive: Stream Result Set
        opt Query is Deterministic SELECT
            Hyperdrive->>Hyperdrive: Cache Result at Edge (Configured TTL)
        end
        Hyperdrive-->>Worker: Forward Result Set
        Worker-->>Browser: 200 OK
    end

Core Architectural Mechanics:

1. Global Edge Connection Pooling:
   • Hyperdrive maintains a globally distributed mesh of persistent, pre-warmed connection pools to the target PostgreSQL cluster.
   • When a SiteSwarm Worker initiates a database query, it connects instantaneously to the local Hyperdrive edge proxy over a local socket. Hyperdrive multiplexes the query across its pre-existing, authenticated connection pool, slashing connection setup overhead from hundreds of milliseconds to under 1 millisecond.
2. TCP/TLS Handshake Termination & Cloudflare Backbone Routing:
   • TLS and cryptographic handshakes terminate immediately at the Cloudflare edge PoP nearest the worker.
   • Traffic between Hyperdrive and the origin database traverses Cloudflare’s private, congestion-free global fiber backbone, eliminating public internet routing jitter and packet loss.
3. Edge Query Caching:
   • Hyperdrive inspects inbound SQL traffic. Unmutated read queries (SELECT) can be cached at the Cloudflare edge nearest the end user.
   • Read queries matching active cache entries return in 10–20ms, completely bypassing the cross-region network traversal to the origin database.
   • Hyperdrive automatically bypasses caching for write operations (INSERT, UPDATE, DELETE) and transactions (BEGIN ... COMMIT), routing mutations directly to origin PostgreSQL and invalidating stale query caches.
4. Transparent Driver & ORM Interoperability:
   • Hyperdrive does not require proprietary client SDKs. It exposes standard PostgreSQL protocol compatibility via env.<BINDING_NAME>.connectionString.
   • SiteSwarm utilizes Drizzle ORM with postgres (postgres.js) or @neondatabase/serverless, allowing identical ORM querying patterns whether targeting D1 or Postgres:

apps/enterprise-client/src/lib/db.ts

import { drizzle } from "drizzle-orm/postgres-js";
import postgres from "postgres";

export interface Env {
  HYPERDRIVE: Hyperdrive;
}

export function createEnterpriseDb(env: Env) {
  // Hyperdrive provides a dynamically rewritten connection string
  // pointing to the local edge connection pool with embedded auth tokens
  const sql = postgres(env.HYPERDRIVE.connectionString, {
    max: 5, // Local isolate connection limit
    fetch_types: false, // Performance optimization for edge V8
  });
  return drizzle(sql);
}

apps/enterprise-client/wrangler.toml

name = "apps-enterprise-portal"
main = "src/index.ts"
compatibility_date = "2026-10-01"

[[hyperdrive]]
binding = "HYPERDRIVE"
id = "a0b1c2d3e4f5a6b7c8d9e0f1a2b3c4d5"

4.4 Latency & Egress Analysis: D1 vs. Hyperdrive

To prevent unexpected infrastructure billing or performance regressions, SiteSwarm evaluates D1 and Hyperdrive across empirical latency, egress bandwidth, base hosting costs, and operational overhead.

Evaluation Dimension	Cloudflare D1 (Native Serverless SQLite)	Cloudflare Hyperdrive + External PostgreSQL
Edge Read Latency (Global)	🟢 Sub-5ms to 10ms Read replicas run directly in edge PoP; zero cross-cloud hops.	🟡 10–20ms (Cache Hit) / 30–150ms (Cache Miss) Miss latency is governed by physical distance to origin Postgres.
Write Latency	🟢 20–60ms Routed to Cloudflare primary coordinator.	🟡 30–150ms Direct TCP round-trip to external PostgreSQL host region.
Connection Setup Overhead	🟢 0ms Direct in-memory V8 binding.	🟢 <1ms Terminated locally at edge Hyperdrive proxy.
Egress & Bandwidth Costs	🟢 $0.00 (Zero Egress) 100% internal to Cloudflare global network.	🔴 Variable Origin Egress Hyperdrive-to-Worker is $0, but AWS/GCP/Supabase charges outbound data transfer ($0.09–$0.12/GB).
Base Infrastructure Cost	🟢 $0 to $5/mo Included in Workers Free/Paid plan (up to 50k DBs).	🔴 $20 to $500+/mo Managed PostgreSQL instance (RDS, Cloud SQL, Supabase Pro) + $5/mo Workers Paid.
Daytime Maintenance	🟢 Zero No capacity provisioning, no disk scaling, no OS patching.	🔴 Medium to High VACUUM tuning, connection tuning, VPC peering, migration downtime management.
Disaster Recovery & PITR	🟢 Built-in 30-Day PITR Instant time-travel restore + 1-click SQLite dump.	🟡 Provider-Dependent AWS RDS snapshots, Neon point-in-time branching, manual WAL archiving.
Client Portability (Zero-Lockout)	🟢 Instant (Sub-5 min) 1-click standard .sqlite file running anywhere.	🟢 High Customer already owns the Postgres database; revoking Hyperdrive completes offboarding.

The Egress Cost Gotcha with External PostgreSQL:

While Cloudflare does not charge egress fees on Workers or Hyperdrive, hyperscalers (AWS, Google Cloud, Azure) aggressively monetize outbound data transfer:

• Querying large result sets (e.g. exporting 50,000 product catalog rows or unoptimized SELECT * queries) from an AWS RDS instance in us-east-1 to Cloudflare edge locations incurs standard AWS Data Transfer Out ($0.09 per GB).
• For high-traffic applications processing millions of edge requests per month, uncontrolled egress from external databases can generate surprise monthly cloud bills reaching hundreds or thousands of dollars.
• Invariant: Applications using Hyperdrive must enforce strict query projection (SELECT id, title rather than SELECT *), strict pagination (LIMIT 50), and leverage Hyperdrive query caching for all cache-tolerant reads.

4.5 The Codified Decision Matrix: When to Recommend D1 vs. Hyperdrive

SiteSwarm engineering enforces a deterministic decision rubric. D1 is the canonical default for all standard and agency-managed client builds; Hyperdrive is an approved enterprise upsell for specific technical or compliance constraints.

flowchart TD
    Start["Client Project Intake / Architecture Review"] --> Q1{"Does client bring existing Postgres\nor mandate corporate VPC/BI integration?"}

    Q1 -- Yes --> Enterprise["APPROVED ENTERPRISE UPSELL:\nCloudflare Hyperdrive + PostgreSQL\n(Neon, Supabase, AWS RDS, Cloud SQL)"]
    Q1 -- No --> Q2{"Does application require Postgres-specific\nextensions (pgvector, PostGIS, ltree)?"}

    Q2 -- Yes --> Enterprise
    Q2 -- No --> Q3{"Does client require full headless CMS,\nhigh write rate, or physical tenant isolation?"}

    Q3 -- Yes --> Tier2["SITESWARM CANONICAL TIER 2:\nDedicated Cloudflare D1 Database\n(client-<slug>-d1)"]
    Q3 -- No --> Tier1["SITESWARM CANONICAL TIER 1:\nPlatform-Shared Cloudflare D1\n(@siteswarm/service-cms-bau / quotes)"]

    style Tier1 fill:#d4edda,stroke:#28a745,stroke-width:2px;
    style Tier2 fill:#cce5ff,stroke:#004085,stroke-width:2px;
    style Enterprise fill:#fff3cd,stroke:#856404,stroke-width:2px;

Selection Rubric:

Client Profile & Technical Drivers	Recommended Architecture	Commercial & Operational Model
Standard Local Business Bakery, cafe, salon, dental clinic, plumber, contractor needing BAU hours, menus, contact forms, catering quotes.	Tier 1: Platform-Shared D1 (or Tier 2 Dedicated D1 if full EmDash CMS is licensed)	Standard Agency Retainer ($100–$500/mo) Zero infrastructure cost, zero maintenance, instant 1-click SQLite client export.
Boutique E-Commerce / High-Traffic Hospitality High-volume reservations, table booking, inventory catalog, custom schemas, dedicated compliance boundary.	Tier 2: Dedicated D1 (Cloudflare D1 client-<slug>-d1)	Growth Retainer ($500–$1,500/mo) Physical SQLite boundary, sub-10ms global reads, continuous 30-day Time Travel PITR.
Enterprise with Existing Database Client has active PostgreSQL database powering mobile apps, web portal, or internal ERP.	Enterprise: Cloudflare Hyperdrive (Connecting to client’s AWS RDS / Supabase / Neon)	Enterprise Retainer ($1,500–$5,000+/mo) Client pays their own database hosting + egress; SiteSwarm bills for edge integration & acceleration.
Data Warehouse / CDC / Analytics Mandate Client requires real-time streaming to Snowflake, BigQuery, or Databricks via Debezium / Fivetran.	Enterprise: Cloudflare Hyperdrive (PostgreSQL with WAL logical replication enabled)	Enterprise Retainer PostgreSQL acts as single source of truth; Hyperdrive accelerates edge delivery without fracturing data pipelines.
AI / Vector Search Workloads Client requires semantic catalog search or document RAG powered by pgvector or complex PostGIS queries.	Enterprise: Cloudflare Hyperdrive (or Cloudflare Vectorize + D1 as alternative)	Enterprise Retainer Allows deep Postgres extension utilization without leaving Cloudflare Worker edge execution.

4.6 Security & Network Ingress for Enterprise PostgreSQL

When deploying Hyperdrive against enterprise PostgreSQL origins, engineering must enforce the following security topology:

1. Network Boundary Protection:
   • Never expose raw PostgreSQL port 5432 directly to the open internet without IP restriction.
   • If PostgreSQL is hosted on AWS RDS or GCP Cloud SQL, configure security groups to allow inbound traffic only from Cloudflare’s published IP ranges or route traffic through Cloudflare Tunnel (cloudflared) for zero-public-ingress connectivity.
2. Encrypted Transit:
   • Hyperdrive requires SSL/TLS encryption (sslmode=require or sslmode=verify-full). Unencrypted plain-text database connections are strictly rejected.
3. Least-Privilege Database Roles:
   • Create a dedicated PostgreSQL role for SiteSwarm (e.g. siteswarm_edge_app) restricted to specific schemas and tables. The edge worker must never connect as postgres or superuser.
4. Credential Rotation & Management:
   • Database credentials are bound to Hyperdrive configurations via Cloudflare API / Wrangler, keeping database passwords entirely out of application source code and edge bundles.

---

5. 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 (output: 'static' SSG)
    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)

Public visitor routes are pre-rendered and served via output: 'static' SSG edge delivery. Dynamic form submission endpoints (such as /api/catering-quote) run on-demand edge handlers or Workers fetch dispatches that securely invoke shared platform microservices via zero-latency Cloudflare Service Bindings without bundling SSR database connections or session state into the public frontend.

5.1 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.

---

6. 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.

6.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 via service binding (with edge Cache-Tag invalidation)
  | "dedicated"             // Uses a dedicated client D1 database binding
  | "enterprise-hyperdrive" // Uses Cloudflare Hyperdrive connection to external Postgres
  | "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 D1 storage */
  dedicatedBindingName?: string;
  /** Binding name in wrangler.toml if using enterprise Hyperdrive storage */
  hyperdriveBindingName?: 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;
}

6.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
      }
    }
  }
});

Enterprise Tier Client (Apex Logistics / Corporate Portal)

Uses platform-shared storage for contact capture, but routes operational warehouse inventory queries to an external AWS RDS PostgreSQL database via Cloudflare Hyperdrive:

apps/apex-logistics/swarm.config.ts

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

export default defineAppConfig({
  clientSlug: "apex-logistics",
  capabilities: {
    "lead-capture": {
      type: "horizontal",
      version: "1.2.0",
      options: { provider: "turnstile", notifyChannel: "webhook", storeSubmissions: true },
      targets: ["src/pages/enterprise-quote.astro"],
      storage: {
        mode: "platform-shared",
        serviceBindingName: "QUOTES_SERVICE"
      }
    },
    "inventory-sync": {
      type: "horizontal",
      version: "1.0.0",
      options: { refreshIntervalSec: 300 },
      targets: ["src/pages/catalog/[sku].astro"],
      storage: {
        mode: "enterprise-hyperdrive",
        hyperdriveBindingName: "APEX_HYPERDRIVE" // Cloudflare Hyperdrive binding to AWS RDS Postgres
      }
    }
  }
});

---

7. 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:

7.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.

7.2 The Static Fallback Invariant

If Cloudflare D1, platform service bindings, or edge network partitions occur, 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 output: 'static' SSG edge delivery template compiles with deterministic build-time seed fallbacks, and any dynamic edge revalidation wraps service calls in resilient try-catch fallbacks:

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

import { fallbackHours, type BusinessHours } from "../config/fallback";

export async function getLiveBusinessHours(env: Env, tenantId: string): Promise<BusinessHours> {
  try {
    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;
}

---

8. 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

8.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.

8.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.3 Enterprise External Database Recovery

For enterprise clients on Tier 4 (Hyperdrive + PostgreSQL), disaster recovery and point-in-time restoration are handled natively by the upstream database provider (e.g. AWS RDS automated snapshots, Neon instant branch restores, Supabase WAL archiving). SiteSwarm assumes zero operational backup overhead for external database instances.

---

9. 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.

Case C: Departing Client on Enterprise Hyperdrive (Tier 4)

Because enterprise clients host their PostgreSQL database externally (in their own AWS RDS, Supabase, Neon, or Google Cloud SQL account), offboarding involves zero data extraction friction:

# 1. Export client R2 media assets if applicable
npx wrangler r2 object get siteswarm-fleet-media --directory="media/enterprise-client/" --output="./enterprise-media/"

# 2. De-provision Cloudflare Hyperdrive binding
npx wrangler hyperdrive delete enterprise-db-hyperdrive

• The client already owns and retains 100% of their operational data within their corporate PostgreSQL cluster. Offboarding requires zero data transformation or schema ETL.

---

10. Schema Migration & Fleet Orchestration

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

10.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).

10.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)"]
    CheckChanges -->|"apps/<enterprise-client>/** (Hyperdrive)"| Mig_Enterprise["Run Migrations:\nPostgreSQL Origin (Targeted DB execution)"]

    Mig_BAU --> Verify["Run Post-Migration Synthetic Probes"]
    Mig_Quotes --> Verify
    Mig_Dedicated --> Verify
    Mig_Enterprise --> 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.
4. Enterprise External PostgreSQL Migrations: For enterprise clients utilizing Hyperdrive, Drizzle ORM migrations target PostgreSQL (drizzle-orm/postgres-js). Migrations execute in CI using isolated enterprise pipeline secrets or are delegated directly to the client’s internal database migration workflow, ensuring zero interference with corporate DBA procedures.

---

11. Data Privacy, Encryption & Security Standards

1. Encryption at Rest: All Cloudflare D1 databases, internal KV namespaces (where restricted to platform/admin services), 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.
5. Enterprise Perimeter & Ingress Security: Hyperdrive connections to external PostgreSQL origins enforce TLS encryption in transit (sslmode=require / verify-full), restricted database role permissions (no superuser), and strict network firewalls (allowing only Cloudflare edge IP prefixes or tunneling via Cloudflare Tunnel).

---

12. 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)"]
    T3 --> T5["Sub-Ticket 5: Enterprise Hyperdrive\n(PostgreSQL Connection Blueprint)"]

• Sub-Ticket 1: Extend @siteswarm/governance with StorageMode (platform-shared, dedicated, enterprise-hyperdrive), 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.
• Sub-Ticket 5: Author the enterprise Hyperdrive integration blueprint, pooling configuration, and automated latency/egress guardrails for enterprise PostgreSQL clients.