Database Architecture
The system uses four storage tiers arranged by access latency and role:
Database Architecture
Visual reference for the multi-tier storage architecture using Neon PostgreSQL (primary) with Cloudflare Hyperdrive, KV (L1 cache), D1 (L2 edge cache), and R2 (object storage).
Table of Contents
- Storage Tier Overview
- Request Data Flow
- Write Path
- Authentication Flow
- D1 → PostgreSQL Migration Flow
- Local vs Production Connection Routing
- Schema Relationships
Storage Tier Overview
The system uses four storage tiers arranged by access latency and role:
| Tier | Binding | Technology | Role |
|---|---|---|---|
| Primary | HYPERDRIVE | Hyperdrive → Neon PostgreSQL | Primary relational store (source of truth) |
| L1 Cache | COMPILATION_CACHE, METRICS, RATE_LIMIT | Cloudflare KV | Hot-path key-value cache (config, rate limits) |
| L2 Cache | DB, ADMIN_DB | Cloudflare D1 (SQLite) | Edge read replicas, admin DB |
| Object Storage | FILTER_STORAGE | Cloudflare R2 | Large compiled outputs, raw filter content |
Request Data Flow
Current behaviour (Phase 1)
The compile handler today only consults the KV cache (COMPILATION_CACHE). D1, Neon PostgreSQL, and R2 are not in the hot compile path yet:
Target behaviour (Phase 5 — planned)
Once the full Hyperdrive/R2 integration is complete (Phases 2–5), the flow will traverse all storage tiers:
Write Path
Current behaviour (Phase 1)
Today POST /compile writes only to the KV cache:
Target behaviour (Phase 5 — planned)
Once Phase 2–5 are implemented, writes will propagate through all tiers:
Authentication Flow
API key authentication as implemented in worker/middleware/auth.ts (authenticateRequest):
Header routing: Bearer token → Hyperdrive API key auth. No Bearer token (or no Hyperdrive binding) →
X-Admin-Keystatic key fallback.
D1 → PostgreSQL Migration Flow
One-time migration from the legacy D1 SQLite store to Neon PostgreSQL:
Idempotent:
ON CONFLICT DO NOTHINGmeans the migration can be run multiple times safely — only missing rows are inserted.
Local vs Production Connection Routing
How the worker resolves its database connection string depending on the environment:
Set credentials in
.env.local(gitignored). See.env.exampleand local-dev.md.
Schema Relationships
Core PostgreSQL model relationships derived from prisma/schema.prisma.
Field names reflect the underlying database column names (snake_case); Prisma model field names are the camelCase equivalents (e.g., display_name → displayName).
Note:
StorageEntry,FilterCache, andCompilationMetadataare legacy models retained for backward compatibility withHyperdriveStorageAdapterandD1StorageAdapter. They have no foreign-key relations to other models and are migration targets — new code should useCompiledOutput+CompilationEventinstead.
References
- plan.md — Database architecture plan and migration phases
- local-dev.md — Local PostgreSQL setup guide
- postgres-modern.md — PostgreSQL best practices
- quickstart.sh — Automated local Docker bootstrap
- WORKFLOW_DIAGRAMS.md — Compilation and queue workflow diagrams