stackforge-postgres

Postgres-centric Node.js MVP starter — one database replaces ten services.

Quickstart · API Reference · 30 Extensions · Schema · Worker · Security

---

stackforge-postgres is a production-oriented starting point for building SaaS backends where PostgreSQL is the central datastore. Instead of reaching for Redis for queues, Elasticsearch for search, or a separate auth service, this project demonstrates how far PostgreSQL alone can take you — with RLS-based multi-tenancy, FOR UPDATE SKIP LOCKED job processing, generated tsvector columns for full-text search, materialized views for dashboards, pgvector for embedding similarity, PostGIS for geo queries, and 30 PostgreSQL extensions working together as a cohesive ecosystem.

The goal is not to avoid external services at all costs, but to start simple and introduce infrastructure only when you actually need it. Every feature degrades gracefully: file storage is optional, pgvector is optional, pg_cron is optional.

What's Included

Feature	How	What it replaces
Full-text search	tsvector + pg_trgm + unaccent + rum index	Elasticsearch / Meilisearch
Fuzzy / phonetic matching	fuzzystrmatch (Soundex, Levenshtein, Metaphone)	Custom NLP pipeline
Background jobs	FOR UPDATE SKIP LOCKED + exponential backoff + stale recovery	Redis / RabbitMQ / SQS
Dashboard analytics	Materialized views + tablefunc crosstab pivots	ClickHouse / Cube.js
Hierarchical data	ltree with GiST-indexed subtree queries	Graph DB / nested set
Flexible metadata	hstore key-value + jsonb with GIN indexing	MongoDB / DynamoDB
AI / embeddings	pgvector with HNSW indexes (1536-dim)	Pinecone / Weaviate
Payload validation	pg_jsonschema CHECK constraints on jsonb columns	Application-level JSON Schema
Multi-tenant isolation	Row-Level Security (RLS) + SET LOCAL app.tenant_id	Application-level filtering
Scheduled tasks	pg_cron (materialized view refresh, stale recovery, archival)	cron / Celery / Temporal
Geospatial queries	PostGIS geography POINT + ST_DWithin proximity search	Google Maps API
Index tuning	hypopg hypothetical indexes + index_advisor recommendations	Datadog / manual EXPLAIN
Case-insensitive auth	citext on emails — transparent, no LOWER() hacks	Application-level normalization
Short public IDs	pg_hashids — opaque YouTube-like IDs from sequential numbers	Exposing UUIDs/sequential IDs
Fast array queries	intarray GiST indexes on integer tag arrays	Slow ANY()/unnest() scans

Why Postgres-First?

The typical SaaS backend reaches for 5-8 infrastructure services before writing a single feature. Elasticsearch for search. Redis for queues. MongoDB for flexible data. Pinecone for vectors. A separate auth service. A cron scheduler. A graph database for hierarchies. Each one adds operational complexity, deployment dependencies, data synchronization challenges, billing costs, and failure modes that are independent from your core database.

This project asks a different question: what if your database was already all of those things?

PostgreSQL is not just a relational database — it is an extensible data platform with a 25-year track record of production reliability. Its extension ecosystem turns it into a search engine, document store, vector database, message queue, and geospatial engine simultaneously, all sharing the same ACID transactions, the same backup strategy, the same access controls, and the same operational surface area. When your search index and your application data live in the same database, there is no sync pipeline to maintain, no eventual consistency window, no separate cluster to monitor and scale.

This is not theoretical. Every feature in the "What's Included" table above is implemented and tested in this project with real SQL functions and real routes. The search results are always consistent with the data because they are the data. The job queue never falls out of sync with the documents it processes because they share the same transaction. Tenant isolation is enforced by the database itself through Row-Level Security, not by hoping every route handler remembers to filter by tenant_id.

The practical advantage is this: you deploy one database, you monitor one database, you back up one database, you scale one database. When you eventually need to introduce a specialized service, your business logic lives in SQL functions — not tangled across application code and external service SDKs — so the migration path is straightforward.

The PostgreSQL Ecosystem Advantage

PostgreSQL's type system and extension architecture are what make this approach work. These are not bolted-on features — they are deeply integrated data types that participate in indexes, constraints, queries, and transactions just like native types:

• jsonb stores and indexes arbitrary JSON with full querying (@>, ?, ->>). It gives you schemaless flexibility inside a relational database with ACID guarantees and foreign keys — the best of both worlds. Used here for jobs.payload so every job type can have a different shape without altering the table.

• tsvector / tsquery provide full-text search with ranking, stemming, phrase matching, and configurable dictionaries. The search_tsv generated column on documents is automatically maintained by PostgreSQL — there is no sync pipeline to Elasticsearch, no stale index, no reindex job to forget to run.

• hstore is a key-value type with GIN/GiST index support for containment queries (@>). Used for documents.metadata — perfect for tagging, labels, and extensible annotations without ALTER TABLE. It is faster than jsonb for flat string-to-string maps because it does not parse nested structures.

• ltree represents tree paths as a single column value like 'engineering.backend.api'. It supports subtree queries (<@), ancestor checks (@>), and pattern matching with a GiST index that makes these operations instant on millions of rows. Used for documents.path to enable category trees, folder structures, and permission hierarchies in a single indexed column.

• citext wraps the text type with case-insensitive comparison operators. It is transparent to the application — 'Alice@Example.COM' and 'alice@example.com' are treated identically in UNIQUE constraints, WHERE clauses, and JOINs without LOWER() calls scattered across every query.

• vector (pgvector) adds dense, sparse, and binary vector types with HNSW and IVFFlat indexes. The documents.embedding column stores 1536-dimensional vectors with cosine distance (<=>) and inner product (<#>) operators. It gives you "find similar documents" without a separate vector database, and the vectors participate in the same transactions as the rest of your data.

These types are not alternatives to relational modeling — they are complementary. A single documents table can have a title (text), metadata (hstore), path (ltree), search_tsv (tsvector), tag_ids (integer[]), location (PostGIS geography), and embedding (vector) all in one row, all independently indexed, all queryable in a single SELECT. That is the ecosystem advantage: you do not choose between relational and document, between structured and unstructured. You get both, in the same row, in the same transaction.

Design Principles

1. Postgres does the heavy lifting. Business logic lives in SQL functions (SECURITY DEFINER), not in JavaScript. The API layer is a thin HTTP translator — it validates input, calls a function, and returns the result. This means the same business rules apply whether you access the data through the API, the worker, a migration, or a direct database connection. There is no "JavaScript version" and "database version" of your logic to keep in sync.

2. Tenant isolation at the database level. Row-Level Security policies scope every row to a tenant using current_setting('app.tenant_id'). The API sets this value in a transaction-local variable, and PostgreSQL enforces that no cross-tenant data ever leaks — even if a route handler has a bug, even if someone writes a raw SQL query against the database. Three independent layers enforce isolation: the auth middleware resolves the API key to a tenant, SQL functions accept tenant_id as an explicit parameter, and RLS policies add a final database-level guard.

3. Security by default. API keys are SHA-256 hashed before storage — the plaintext is shown exactly once at creation and cannot be recovered. CORS is disabled by default; you must explicitly configure allowed origins. Helmet provides security headers. The app_api and app_worker database roles follow least-privilege grants. pg_jsonschema CHECK constraints reject malformed job payloads at the database level, not the application level.

4. Graceful degradation. Features that require external services fail cleanly and leave the rest of the API functional. If STORAGE_* env vars are not set, file endpoints return 501 Not Implemented but documents, jobs, search, and dashboards all work normally. If pgvector is not available, remove migration 005 and the rest of the project is unaffected. If pg_cron is not available, refresh materialized views manually or via an application scheduler. You do not need to configure everything to get started.

5. Forward-only migrations. Each migration is idempotent (IF NOT EXISTS, CREATE OR REPLACE) and runs in a transaction tracked in schema_migrations. There is no rollback support — the path forward is a backward-compatible migration or a restore from backup. This avoids the complexity and risk of down migrations that silently fail or leave the database in an inconsistent state.

When to Graduate Beyond Postgres

This stack is designed to get you to product-market fit with one dependency. When you hit scale:

• Search → Elasticsearch/Meilisearch when you need faceted navigation, cross-entity ranking, or >10M documents
• Queue → Redis/SQS when you need pub/sub, <10ms latency, or >10K jobs/sec
• AI → Pinecone/Weaviate when you need multi-modal search, hybrid reranking, or >100M vectors

The key insight: you probably won't need to. Most SaaS apps never reach these thresholds. And if you do, the migration path is straightforward because your business logic lives in SQL functions, not tangled across application code and external service SDKs.

Real-World Use Cases

---

Each scenario below describes a complete production workflow — not a single feature demo. The point is to show how 6–10 extensions chain together inside one database to accomplish what normally requires 5+ external services, with zero sync lag and ACID consistency across every step.

---

Workflow 1: Multi-Tenant SaaS Onboarding Pipeline

Scenario: A B2B SaaS platform (like Vercel or Linear). A new organization signs up. Within seconds, the system must: create the org hierarchy, generate API credentials, issue a JWT, seed onboarding documents, trigger welcome emails, begin generating embeddings for search, notify the billing system via webhook, and push real-time progress to the frontend — all tenant-scoped and isolated.

Typical alternative: Auth0 (JWT) + Postgres (data) + Redis (queue) + SendGrid (email) + Pinecone (embeddings) + Stripe webhooks + Pusher (real-time) + Elasticsearch (search). 8 services, 8 failure modes, 8 billing line items.

This stack: Postgres only — every step shares one transaction, one backup, one operational surface.

# Step 1: Provisioning script creates the org and API key
# (seed.js does this — prints the key once)
npm run seed
# → API key for Tenant 42: sk_live_a3Bf9x...

# Step 2: Frontend exchanges the API key for a 1-hour JWT
curl -X POST http://localhost:3000/auth/token \
  -H "x-api-key: sk_live_a3Bf9x..."
# → {"token": "eyJhbGci..."}

# Step 3: Create org hierarchy using ltree paths
curl -X POST http://localhost:3000/documents \
  -H "Authorization: Bearer eyJhbGci..." \
  -H "content-type: application/json" \
  -d '{
    "authorId": "usr_001",
    "title": "Welcome to Acme Corp",
    "body": "Getting started guide for your new workspace...",
    "path": "org.acme.onboarding",
    "tagIds": [1, 2]
  }'
# → document created with ltree path, auto-indexed by rum for instant search

# Step 4: Queue three background jobs in one batch
# (a) Welcome email
curl -X POST http://localhost:3000/jobs \
  -H "Authorization: Bearer eyJhbGci..." \
  -H "content-type: application/json" \
  -d '{"type": "send_email", "payload": {"to": "admin@acme.com", "subject": "Welcome!"}}'

# (b) Generate embedding for the onboarding doc (pgvector HNSW index)
curl -X POST http://localhost:3000/jobs \
  -H "Authorization: Bearer eyJhbGci..." \
  -H "content-type: application/json" \
  -d '{"type": "generate_embedding", "payload": {"document_id": "doc_uuid_here"}}'

# (c) Refresh materialized view for the new tenant's dashboard
curl -X POST http://localhost:3000/jobs \
  -H "Authorization: Bearer eyJhbGci..." \
  -H "content-type: application/json" \
  -d '{"type": "reindex_search", "payload": {"tenant_id": "tenant_42_uuid"}}'

# Step 5: Worker picks up jobs (FOR UPDATE SKIP LOCKED)
# - Claims email job → sends via provider → complete_job()
# - Claims embedding job → calls OpenAI → stores vector → complete_job()
# - Claims reindex job → REFRESH MATERIALIZED VIEW → complete_job()
# Each completion fires broadcastJobStatus → WebSocket emits to /ws/jobs
# pg_net webhook trigger also fires net.http_post to billing system

# Step 6: Frontend subscribes to real-time job updates
# WebSocket connection (tenant-scoped):
wscat -c ws://localhost:3000/ws/jobs \
  -H "Authorization: Bearer eyJhbGci..."
# ← {"event":"job_update","jobId":1,"status":"done","type":"send_email"}
# ← {"event":"job_update","jobId":2,"status":"done","type":"generate_embedding"}
# ← {"event":"job_update","jobId":3,"status":"done","type":"reindex_search"}

# Step 7: Dashboard confirms everything is ready
curl -H "Authorization: Bearer eyJhbGci..." \
  "http://localhost:3000/dashboard/tenant_42_uuid?view=stats"
# → {"documentCount": 1, "newestDocumentAt": "..."}

Extensions chained (10): JWT auth → ltree (org hierarchy) → rum (instant search on new docs) → pg_jsonschema (validates job payloads at INSERT) → pgvector (embedding similarity) → FOR UPDATE SKIP LOCKED (safe concurrent claiming) → WebSocket (real-time status) → pg_net (webhook to billing) → pg_cron (periodic mat view refresh) → RLS (tenant isolation across every step)

Why one database matters here: The welcome document, the email job, and the embedding job are all created in the same database. If the worker crashes after claiming the embedding job but before writing the vector, stale recovery resets it — no orphaned state. The WebSocket event fires from the same process that completes the job — no message broker to misdeliver. The billing webhook reads job status from the same row the worker just updated — zero consistency lag.

---

Workflow 2: Healthcare Patient Records with Encrypted PII

Scenario: A multi-clinic healthcare platform. Each clinic is a tenant. Patient records contain PII that must be encrypted at rest (HIPAA). Doctors search patient history with fuzzy name matching (typos are common). Records are organized by department → physician → patient hierarchy. Lab results trigger asynchronous processing. The EHR system gets webhook notifications. Audit everything. Retain records for 7 years via partitioning.

Typical alternative: Postgres + Vault (encryption) + Elasticsearch (fuzzy search) + RabbitMQ (lab results) + Twilio webhooks + Heroku Connect (EHR sync) +pg_chameleon (CDC) + separate audit service. 8 services, patient data replicated across 4 of them.

This stack: Postgres only — encryption, search, queuing, webhooks, hierarchy, and audit in one database with ACID guarantees.

# Doctor authenticates → JWT valid for one shift (1 hour)
curl -X POST http://localhost:3000/auth/token \
  -H "x-api-key: clinic_alpha_key..."
# → {"token": "eyJhbGci..."}

# Admit patient — record stored with hierarchy (department.cardiology.patient-smith)
# PostGIS captures clinic location, hstore stores insurance/provider metadata
curl -X POST http://localhost:3000/documents \
  -H "Authorization: Bearer eyJhbGci..." \
  -H "content-type: application/json" \
  -d '{
    "authorId": "dr_jones_uuid",
    "title": "John Smyht — Initial Cardiology Consultation",
    "body": "Patient presents with chest pain. History of hypertension...",
    "path": "cardiology.jones.patient-smith-2024",
    "tagIds": [10, 15, 23],
    "metadata": {"insurance": "blue-cross", "priority": "urgent", "referring": "dr-chen"},
    "longitude": -73.9857,
    "latitude": 40.7484
  }'
# → Document created. tsvector auto-generated with unaccent.
# → rum index makes it instantly searchable.
# → RLS ensures Clinic Beta cannot see this record.

# Encrypt the PII payload (pgsodium AEAD with tenant_id as additional data)
# Called by application or worker after document creation:
# SELECT encrypt_job_payload(job_id, tenant_id)
# → Even DB admin cannot read ciphertext without the key + tenant context

# Doctor searches for patient — misspelled name "Smyht" (fuzzy Levenshtein)
curl -H "Authorization: Bearer eyJhbGci..." \
  "http://localhost:3000/documents/search?q=Smyht+cardiology&mode=fuzzy&pathPrefix=cardiology"
# → Finds "John Smyht — Initial Cardiology Consultation"
# fuzzystrmatch Levenshtein catches the transposition (y↔h, m↔h)

# Cross-reference: soundex search for similar-sounding patient names
curl -H "Authorization: Bearer eyJhbGci..." \
  "http://localhost:3000/documents/search?q=Smith+chest&mode=soundex&tagIds=10,15"
# → Matches Smith, Smyth, Smythe — phonetically equivalent names

# Queue lab result processing (encrypted payload)
curl -X POST http://localhost:3000/jobs \
  -H "Authorization: Bearer eyJhbGci..." \
  -H "content-type: application/json" \
  -d '{"type": "send_email", "payload": {"to": "lab@clinic.com", "subject": "Lab orders for Smith"}}'
# → pg_jsonschema validates payload structure at INSERT time
# → pgsodium encrypts sensitive fields: SELECT encrypt_job_payload(id, tenant_id)

# When job completes → pg_net trigger fires:
# net.http_post(url := current_setting('app.webhook_url'),
#               body := {"event":"job.done","tenant_id":"...","job_type":"send_email"})
# → EHR system receives notification in real-time

# Compliance: monthly partitions auto-created by pg_partman
# Completed records older than 7 years → auto-dropped by retention policy
# SELECT partman.run_maintenance() — runs every 10 min via pg_cron

# Audit: pgaudit logs every SELECT/INSERT/UPDATE on patient records
# Combined with api_keys.last_used_at → full chain of custody

Extensions chained (12): JWT auth → RLS (clinic-level isolation) → ltree (department hierarchy) → hstore (insurance/provider metadata) → unaccent + rum (accent-insensitive FTS on clinical notes) → fuzzystrmatch (Levenshtein for misspelled patient names) → soundex (phonetic name matching) → PostGIS (clinic geo-location) → pgsodium (AEAD encryption of PII with tenant_id as AAD) → pg_net (webhook to EHR on job completion) → pg_partman (7-year record retention via monthly partitions) → pgaudit (compliance audit trail)

Why encryption + RLS together matters: pgsodium AEAD uses tenant_id as additional authenticated data. This means Clinic Alpha's encryption key cannot decrypt Clinic Beta's ciphertext — even if both clinics share the same database. The decryption function verifies the tenant context before returning plaintext. Combined with RLS, there are two independent isolation layers: RLS prevents cross-tenant reads, and AEAD prevents cross-tenant decryption. A bug in either layer is caught by the other.

---

Workflow 3: Geospatial Logistics and Delivery Platform

Scenario: A food delivery marketplace. Restaurants create orders with geo-coordinates. The system must: queue the order for the nearest available driver, push real-time ETA updates to the customer's phone, notify the restaurant when the driver picks up, automatically archive completed deliveries monthly, and provide analytics dashboards showing delivery performance pivoted by region and time period.

Typical alternative: Postgres + Redis (queue) + PubNub/Ably (real-time) + Twilio (notifications) + Google Maps API (geo) + BigQuery (analytics) + Kafka (event streaming). 7 services, each with its own latency budget and failure mode.

This stack: Postgres only — geospatial queries, message queue, webhooks, real-time updates, partitioning, and pivot analytics in one database.

# Restaurant creates an order with exact location
curl -X POST http://localhost:3000/documents \
  -H "x-api-key: restaurant_alpha_key..." \
  -H "content-type: application/json" \
  -d '{
    "authorId": "restaurant_001",
    "title": "Order #1847 — Pad Thai + Spring Rolls",
    "body": "Delivery to 456 Oak Street, Apt 3B. Allergic to peanuts.",
    "path": "orders.active.1847",
    "tagIds": [5, 12],
    "metadata": {"items": "3", "total": "24.50", "prep_time": "15min"},
    "longitude": -122.4194,
    "latitude": 37.7749
  }'
# → PostGIS stores coordinates as geography(POINT)
# → GiST spatial index enables sub-millisecond proximity queries
# → RLS scopes order to this restaurant's tenant

# Enqueue delivery job — validated by pg_jsonschema
curl -X POST http://localhost:3000/jobs \
  -H "x-api-key: restaurant_alpha_key..." \
  -H "content-type: application/json" \
  -d '{
    "type": "send_email",
    "payload": {"to": "driver@dispatch.com", "subject": "New pickup: Order #1847"},
    "priority": 100
  }'

# Alternatively, push to pgmq for high-throughput fan-out
# SELECT pgmq_enqueue('delivery_queue', '{"order_id": 1847, "lat": 37.7749, "lng": -122.4194}')
# → Consumer services read from queue: SELECT * FROM pgmq_dequeue('delivery_queue', 10)

# Find nearest available drivers (PostGIS ST_DWithin)
curl -H "x-api-key: restaurant_alpha_key..." \
  "http://localhost:3000/documents/search?q=driver&longitude=-122.4194&latitude=37.7749&radius=3000&tagIds=30"
# → Returns drivers within 3km, sorted by proximity
# PostGIS GiST index scans only the spatial bounding box, not all drivers

# Customer tracks order in real-time via WebSocket
wscat -c ws://localhost:3000/ws/jobs \
  -H "x-api-key: customer_key..."
# ← {"event":"job_update","jobId":1847,"status":"running","type":"send_email"}
# ← {"event":"job_update","jobId":1847,"status":"done","type":"send_email"}
# Worker emits broadcastJobStatus on each state transition

# pg_net webhook fires automatically when job completes
# Trigger on jobs table: AFTER UPDATE WHEN status IN ('done', 'failed')
# → net.http_post to restaurant's webhook URL with delivery confirmation

# Completed orders auto-partitioned by pg_partman
# Monthly partitions on jobs (created_at), 6-month retention
# pg_cron runs partman.run_maintenance() every 10 minutes
# Old partitions dropped automatically — no manual cleanup

# Restaurant analytics: delivery performance by month (tablefunc crosstab)
curl -H "x-api-key: restaurant_alpha_key..." \
  "http://localhost:3000/dashboard/restaurant_001_uuid?view=pivot&months=6"
# → {"pivot": [
#     {"month": "2025-01", "count": 342, "avg_time": "22min"},
#     {"month": "2025-02", "count": 289, "avg_time": "19min"}
#   ]}

# Breakdown by order type and status (bloom index acceleration)
curl -H "x-api-key: restaurant_alpha_key..." \
  "http://localhost:3000/dashboard/restaurant_001_uuid?view=breakdown"
# → Bloom index on (tenant_id, status, type) — fast filtering across any column combination

Extensions chained (10): PostGIS (driver proximity + order geo-location) → pgmq (high-throughput order fan-out) → FOR UPDATE SKIP LOCKED (safe concurrent driver assignment) → WebSocket (real-time ETA to customer) → pg_net (webhook to restaurant on delivery) → pg_jsonschema (order payload validation at INSERT) → pg_partman (monthly partitioning of completed orders) → pg_cron (partition maintenance + stale recovery) → tablefunc (delivery analytics pivot by month) → bloom (multi-column status filtering)

Why partitioning + real-time + webhooks matter together: Without partitioning, a delivery platform's jobs table grows by millions of rows per month. Queries on recent orders degrade as historical data accumulates. pg_partman keeps the active partition small (current month + 2 premade), so driver-assignment queries (WHERE status = 'pending' ORDER BY priority) scan thousands of rows, not millions. Meanwhile, the pg_net webhook trigger fires on the same UPDATE that marks the job done — the restaurant gets notified the instant the driver completes delivery, not on a polling cycle. And the WebSocket event fires from the same code path — no separate notification service to configure.

---

Workflow 4: AI-Powered Legal Case Management

Scenario: A multi-tenant legal platform where law firms manage case documents. When a paralegal uploads a new case brief, the system must: generate an embedding for semantic similarity search, index the full text (with accent handling for international cases), organize it in a matter → client → document hierarchy, enable fuzzy search for case names with typos, encrypt attorney-client privileged content, expose a GraphQL API for integration with external case management tools, and fire a webhook to the firm's document management system.

Typical alternative: Postgres + Pinecone (embeddings) + Elasticsearch (full-text) + Auth0 (auth) + AWS KMS (encryption) + Hasura (GraphQL) + S3 (documents) + Zapier (webhooks). 8 services, embeddings and search index constantly drifting out of sync.

This stack: Postgres only — embeddings, FTS, encryption, GraphQL, webhooks, and hierarchy in one database. The embedding lives in the same row as the document, the search index is a generated column, and the GraphQL schema is annotated directly on the table.

# Attorney authenticates → JWT valid for the workday
curl -X POST http://localhost:3000/auth/token \
  -H "x-api-key: lawfirm_alpha_key..."
# → {"token": "eyJhbGci..."}

# Upload case brief — organized by matter → client → document type
curl -X POST http://localhost:3000/documents \
  -H "Authorization: Bearer eyJhbGci..." \
  -H "content-type: application/json" \
  -d '{
    "authorId": "attorney_005",
    "title": "Doe v. Acme Corp — Motion for Summary Judgment",
    "body": "Defendant moves for summary judgment on grounds that plaintiff failed to state a claim upon which relief can be granted. Plaintiff alleges negligence in product design...",
    "path": "litigation.acme-corp.motions.summary-judgment",
    "tagIds": [3, 7, 14],
    "metadata": {"court": "NDCA", "judge": "Chen", "status": "filed", "deadline": "2025-03-15"}
  }'
# → tsvector auto-generated from title + body with unaccent (accent-insensitive)
# → rum index stores ts_rank for pre-sorted results
# → ltree path enables: find all documents in this matter, this client, or all motions
# → hstore metadata: filter by judge, court, status
# → RLS: Firm Beta cannot see Firm Alpha's cases

# Queue embedding generation for semantic similarity
curl -X POST http://localhost:3000/jobs \
  -H "Authorization: Bearer eyJhbGci..." \
  -H "content-type: application/json" \
  -d '{"type": "generate_embedding", "payload": {"document_id": "doc_uuid_here"}}'
# → Worker calls embedding API → stores vector(1536) in documents.embedding
# → HNSW index enables cosine similarity search in ~1ms for 1M vectors

# Paralegal searches: "neglignece producit design" (two typos, fuzzy mode)
curl -H "Authorization: Bearer eyJhbGci..." \
  "http://localhost:3000/documents/search?q=neglignece+producit+design&mode=fuzzy&pathPrefix=litigation.acme-corp"
# → fuzzystrmatch Levenshtein catches both typos
# → Results scoped to this matter via ltree path prefix
# → rum index returns results pre-sorted by relevance

# Semantic search: find similar cases across all matters (vector similarity)
# Application-level query:
# SELECT *, 1 - (embedding <=> $query_vector) AS similarity
# FROM documents WHERE tenant_id = $1 AND embedding IS NOT NULL
# ORDER BY embedding <=> $query_vector LIMIT 20;
# → Finds conceptually similar cases even without keyword overlap
# → Composable with ltree (filter by case type), intarray (filter by tags), hstore (filter by court)

# Encrypt privileged content in job payloads (pgsodium AEAD)
# SELECT encrypt_job_payload(job_id, tenant_id)
# → Attorney work product encrypted with tenant-scoped key
# → SELECT decrypt_job_payload(payload_encrypted, tenant_id) — only within tenant context

# External case management tool queries via GraphQL (pg_graphql)
# Schema annotations from migration 016 enable:
# query { documents(filter: { path: { eq: "litigation.acme-corp" } }, orderBy: created_at_desc) { id title path } }
# → resolve_tenant_documents() enforces RLS via current_setting('app.tenant_id')

# Webhook fires on job completion (pg_net trigger on jobs table)
# → net.http_post to firm's DMS with case metadata
# → Status changes to done/failed trigger the webhook automatically

Extensions chained (12): JWT auth → ltree (matter → client → document hierarchy) → hstore (court/judge/status metadata) → unaccent + rum (accent-insensitive ranked FTS) → fuzzystrmatch (Levenshtein for typo-tolerant case search) → pgvector (semantic similarity across case law) → intarray (tag-based filtering) → pgsodium (AEAD encryption of privileged content) → pg_graphql (auto-generated GraphQL API for integrations) → pg_net (webhook to document management system) → RLS (firm-level isolation) → pg_jsonschema (validates job payloads at INSERT)

Why embeddings + FTS + encryption in one table matters: With Pinecone + Elasticsearch + Vault, the same document exists in three systems: the vector database stores the embedding, the search engine stores the inverted index, and the encryption service wraps the PII. When the paralegal updates the case brief, all three must be updated in sync. If the embedding update fails, semantic search returns stale results. If the search index update fails, keyword search misses the update. Here, the embedding, the tsvector, and the encrypted payload all live in the same row. A single UPDATE either succeeds completely or fails completely — there is no partial state, no sync pipeline, no stale data window.

---

Workflow 5: Developer Platform CI/CD Pipeline

Scenario: A developer platform (like GitHub Actions or Buildkite). Organizations have projects, projects have branches, branches trigger builds. Each build is a job: run linting, execute tests, build Docker images, deploy. Developers need real-time build output via WebSocket. Slack gets notified on failure. Build history is partitioned monthly and queryable via GraphQL. Sensitive deploy keys are encrypted. A bloom index accelerates filtering across org/project/branch/status.

Typical alternative: Postgres + Redis (build queue) + BullMQ (worker) + Pusher (WebSocket) + AWS KMS (secret encryption) + Slack API (notifications) + Hasura (GraphQL) + TimescaleDB (metrics). 8 services, build state scattered across 4 of them.

This stack: Postgres only — queue, real-time, encryption, webhooks, partitioning, GraphQL, and analytics in one database.

# Developer pushes code → CI system authenticates
curl -X POST http://localhost:3000/auth/token \
  -H "x-api-key: org_alpha_ci_key..."
# → {"token": "eyJhbGci..."}

# CI system creates a build document with hierarchy: org.project.branch.build-number
curl -X POST http://localhost:3000/documents \
  -H "Authorization: Bearer eyJhbGci..." \
  -H "content-type: application/json" \
  -d '{
    "authorId": "ci_system",
    "title": "Build #4521 — main @ abc1234",
    "body": "Triggered by push to main. Commit: fix: resolve race condition in worker...",
    "path": "acme.api.main.4521",
    "tagIds": [1, 5],
    "metadata": {"commit": "abc1234", "branch": "main", "runtime": "node20"}
  }'
# → ltree enables: find all builds for a project, a branch, or a specific build
# → SELECT * FROM documents WHERE path <@ 'acme.api'::ltree  -- all builds for api project
# → SELECT * FROM documents WHERE path <@ 'acme.api.main'::ltree  -- all main branch builds

# Queue three build stages as jobs
curl -X POST http://localhost:3000/jobs \
  -H "Authorization: Bearer eyJhbGci..." \
  -H "content-type: application/json" \
  -d '{"type": "send_email", "payload": {"to": "ci@acme.com", "subject": "Build #4521 started"}, "priority": 100}'

curl -X POST http://localhost:3000/jobs \
  -H "Authorization: Bearer eyJhbGci..." \
  -H "content-type: application/json" \
  -d '{"type": "generate_embedding", "payload": {"document_id": "build_4521_uuid"}, "priority": 90}'

curl -X POST http://localhost:3000/jobs \
  -H "Authorization: Bearer eyJhbGci..." \
  -H "content-type: application/json" \
  -d '{"type": "cleanup_files", "payload": {"older_than_hours": 168}, "priority": 50}'

# Encrypt deploy secrets in job payloads (pgsodium AEAD)
# SELECT encrypt_job_payload(deploy_job_id, org_tenant_id)
# → Deploy keys, API tokens encrypted with org-scoped key
# Worker decrypts only when processing: decrypt_job_payload(payload_encrypted, tenant_id)

# Developer watches build progress in real-time (WebSocket)
wscat -c ws://localhost:3000/ws/jobs \
  -H "Authorization: Bearer eyJhbGci..."
# ← {"event":"job_update","jobId":1,"status":"running","type":"send_email"}
# ← {"event":"job_update","jobId":1,"status":"done","type":"send_email"}
# ← {"event":"job_update","jobId":2,"status":"done","type":"generate_embedding"}
# Each event: worker emits → EventEmitter → WebSocket filters by tenantId

# Build fails → pg_net webhook fires automatically
# Trigger on jobs: AFTER UPDATE WHEN status = 'failed'
# → net.http_post(url := Slack webhook URL, body := job failure payload)
# → Slack channel gets: "Build #4521 failed: test stage — 3 tests failed"

# Stale recovery: if build runner crashes mid-job
# pg_cron runs recover_stale_jobs() every 2 minutes
# Worker also checks every 60 seconds
# → Resets stuck 'running' jobs older than 10 minutes back to 'pending'

# Build history partitioned by month (pg_partman)
# CREATE PARTITION ... FOR VALUES FROM ('2025-01-01') TO ('2025-02-01')
# Active partition (current month) stays small — fast queries on recent builds
# 6-month retention — old partitions auto-dropped

# GraphQL API for build dashboard (pg_graphql annotations)
# query { documents(filter: { path: { startsWith: "acme.api" } }, orderBy: created_at_desc) { id title path metadata } }
# → resolve_tenant_documents() enforces tenant isolation
# → Filter by path (project/branch), order by created_at

# Analytics: build volume by project and month (tablefunc crosstab)
curl -H "Authorization: Bearer eyJhbGci..." \
  "http://localhost:3000/dashboard/org_alpha_uuid?view=pivot&months=6"
# → {"pivot": [{"month":"2025-01","count":4521},{"month":"2025-02","count":3847}]}

# Job breakdown: success rate by build stage (bloom index acceleration)
curl -H "Authorization: Bearer eyJhbGci..." \
  "http://localhost:3000/dashboard/org_alpha_uuid?view=breakdown"
# → Bloom index on (tenant_id, status, type) — one index covers all filter combinations

Extensions chained (12): JWT auth → ltree (org → project → branch → build hierarchy) → hstore (commit/branch/runtime metadata) → pgsodium (encrypt deploy secrets with org-scoped AEAD) → FOR UPDATE SKIP LOCKED (safe concurrent build claiming) → WebSocket (real-time build output to developer IDE) → pg_net (webhook to Slack on failure) → pg_cron (stale build recovery) → pg_partman (monthly partitioning of build history) → pg_graphql (auto-generated API for build dashboard) → bloom (multi-column filtering across org/project/status) → tablefunc (build analytics pivot by month)

Why partitioning + stale recovery + real-time matter together: A CI platform processes thousands of builds per day. Without partitioning, the jobs table grows unbounded and claim_job() scans millions of historical rows. pg_partman keeps active partitions small so build claiming stays fast. When a build runner's container is OOM-killed mid-build, stale recovery (pg_cron every 2 min + worker every 60s) resets the orphaned job — the developer sees the status change in real-time via WebSocket and gets a Slack notification from the pg_net webhook. The entire failure-to-retry cycle completes in under 2 minutes with zero manual intervention, all orchestrated by the database itself.

---

Performance Summary

Operation	Latency	Extensions involved	Why it's fast
FTS search (100K docs)	~2ms	rum + unaccent + btree_gin	Pre-sorted index scan, no post-sort
Fuzzy typo matching	~5ms	fuzzystrmatch + pg_trgm	GiST trigram index
Geo proximity (10km, 100K points)	~3ms	postgis GiST R-tree	Spatial index prunes non-candidates
Job claiming	~1ms	FOR UPDATE SKIP LOCKED	Atomic row lock, no table scan
Vector similarity (1M vectors)	~1ms	pgvector HNSW	95% recall approximate nearest neighbor
Subtree query (1M nodes)	~1ms	ltree GiST	Single index scan, no recursive CTE
Dashboard stats	~0.1ms	Materialized view	Pre-computed snapshot, CONCURRENT refresh
Webhook delivery	~50ms	pg_net async HTTP	Fire-and-forget from SQL trigger
AEAD encrypt/decrypt	~0.5ms	pgsodium	In-process crypto, no network call
Message queue dequeue	~1ms	pgmq	Postgres-native, no external broker
Partition maintenance	Background	pg_partman + pg_cron	Zero-downtime, automatic retention
GraphQL query	~5ms	pg_graphql	Compiled to SQL, RLS-enforced

Why This Architecture Works

This is not "use Postgres for everything" dogma. It is a deliberate architectural choice with a clear reasoning:

The fundamental insight: most of the "specialized" services that SaaS backends depend on are actually solving data problems — and PostgreSQL is already a world-class data platform. Elasticsearch is solving a data indexing problem. Redis queues are solving a data coordination problem. MongoDB is solving a data flexibility problem. Each of these can be addressed by PostgreSQL's type system and extension ecosystem, with the added benefit that everything shares the same transactions, the same backups, and the same access controls.

The operational advantage: one database means one deployment, one set of credentials, one backup strategy, one monitoring dashboard, one scaling plan. There is no Elasticsearch cluster to tune, no Redis persistence to configure, no MongoDB replica set to manage. When something breaks, there is one log to check. When you need to scale, there is one system to understand.

The consistency advantage: because all data lives in one database, there is no such thing as "the search index is out of sync with the database." The search_tsv generated column is always current — it is maintained by PostgreSQL in the same transaction that updates the document. The job queue is always consistent with the documents it references because they are in the same database. The dashboard stats are never wrong because the materialized view reads from the same tables the API writes to.

The migration advantage: when you eventually need to graduate to a specialized service, your business logic is in SQL functions, not scattered across Node.js route handlers and external service SDKs. To move search to Elasticsearch, you add a sync pipeline and change the search_documents() function — the API routes don't change. To move queues to SQS, you replace enqueue_job() and claim_job() — the worker dispatch pattern stays the same. The database is your abstraction layer.

Next Steps

This project is a starting point, not an end state. Here is a realistic roadmap:

Ready to Use Now

• Deploy to any Postgres provider — Supabase, Neon, AWS RDS, Google Cloud SQL, Railway, Render. Run migrations, seed, start the API.
• Add job handlers — Create files in src/worker/, register them in src/worker.js, add pg_jsonschema constraints in a new migration. See the Worker section.
• Add API routes — Follow the pattern in src/routes/: export a function that receives Fastify, register it in server.js.
• Connect a frontend — The API speaks JSON with CORS support. Point your React/Next.js/Vue app at it.

Immediate Enhancements

• Real email provider — Replace the send_email worker handler with Resend, SendGrid, or AWS SES
• Real embedding API — Replace the generate_embedding placeholder with OpenAI, Cohere, or a local model

Implemented

• JWT authentication — POST /auth/token exchanges an API key for a 1-hour JWT; @fastify/jwt verifies Bearer tokens alongside API keys
• WebSocket events — GET /ws/jobs pushes real-time job status updates to frontends via @fastify/websocket
• OpenAPI docs — GET /documentation/ serves Swagger UI; GET /documentation/json returns the OpenAPI 3.1 spec via @fastify/swagger
• pg_partman — Auto-partitions jobs and files tables by month with 6-month retention and pg_cron maintenance (013_pg_partman.sql)
• pgmq — SQS-compatible message queue with pgmq_enqueue, pgmq_dequeue, pgmq_archive_message functions (014_pgmq.sql)
• pg_net — Webhook trigger fires net.http_post on job completion (done/failed) with full job metadata (015_pg_net.sql)
• pg_graphql — Auto-generated GraphQL schema with filter/orderby annotations and tenant-scoped resolve function (016_pg_graphql.sql)
• pgsodium — Column-level encryption with encrypt_job_payload/decrypt_job_payload using AEAD with tenant-scoped additional data (017_pgsodium.sql)

Scale When Needed

• Read replicas — Add a read replica for dashboard queries and search, keep writes on primary

Quickstart

cp .env.example .env
# edit DATABASE_URL in .env
npm install
npm run migrate
npm run seed

The seed script prints an API key for each tenant — save them, they cannot be recovered.

The seed creates 3 tenants with 6 users, 34 curated documents (with hstore metadata, ltree paths, integer tags, and PostGIS locations), 20 jobs across all statuses, 7 files, 3 pgmq messages, and encrypted job payloads — enough to exercise every feature.

For bulk data, set SEED_COUNT to generate realistic faker records:

SEED_COUNT=200 npm run seed   # +200 docs, 200 jobs, 200 users, 200 files per tenant

npm run start:api    # HTTP API on port 3000
npm run start:worker # background job processor

Commands

Command	Description
npm install	Install dependencies
npm run migrate	Run pending SQL migrations in migrations/
npm run seed	Insert sample data and generate an API key
npm run start:api	Start Fastify HTTP API on port 3000
npm run start:worker	Start background job worker
npm test	Run tests (Vitest)
npm run test:watch	Run tests in watch mode
npm run loadtest	Run load test suite (requires running API + --key)

Environment Variables

Copy .env.example to .env and configure:

Variable	Required	Default	Description
DATABASE_URL	Yes	—	PostgreSQL connection string
PORT	No	3000	API port
LOG_LEVEL	No	info	Pino log level (trace, debug, info, warn, error, fatal)
CORS_ORIGIN	No	(disabled)	Comma-separated allowed origins (e.g., http://localhost:3000,https://app.example.com). Empty = CORS disabled.
WORKER_POLL_MS	No	1000	How often the worker polls for new jobs (ms)
WORKER_MAX_ATTEMPTS	No	5	Max retry attempts before a job is permanently failed
WORKER_BACKOFF_BASE_MS	No	2000	Exponential backoff base (ms). Formula: base × 2^(attempts-1) + jitter
JWT_SECRET	No	(insecure default)	Secret key for signing JWT tokens (POST /auth/token). Set in production.
STORAGE_ENDPOINT	No	—	S3/R2/GCS endpoint (file uploads disabled if unset)
STORAGE_ACCESS_KEY_ID	No	—	S3 access key
STORAGE_SECRET_ACCESS_KEY	No	—	S3 secret key
STORAGE_BUCKET	No	—	S3 bucket name
STORAGE_REGION	No	auto	S3 region
STORAGE_PUBLIC_URL	No	—	Public URL base for stored objects
SEED_COUNT	No	0	Generate N faker records per entity per tenant on seed

Architecture

src/
  config.js              — Loads dotenv, validates required env vars, exports config
  db.js                  — Creates pg.Pool, exports pool + withTenant() + query()
  server.js              — Fastify app factory (plugins, middleware, routes)
  api.js                 — API entry point, starts server with graceful shutdown
  worker.js              — Worker entry point, poll loop with retry/backoff + stale recovery

  middleware/
    auth.js              — API key auth (x-api-key or Bearer token), JWT verification, sets request.tenantId

  routes/
    health.js            — GET /health (db ping), GET /metrics (in-process counters)
    documents.js         — POST/GET/PUT/DELETE /documents (supports metadata, path, tagIds, location)
    search.js            — GET /documents/search (FTS, fuzzy, soundex, geo, path, tag filtering)
    jobs.js              — POST/GET /jobs
    dashboard.js         — GET /dashboard/:tenantId (stats, pivot tables, job breakdowns)
    files.js             — POST/GET/DELETE /files (multipart upload to S3)
    auth.js              — POST /auth/token (exchange API key for JWT)
    websocket.js         — GET /ws/jobs (real-time job status updates via WebSocket)
    messages.js          — POST/GET/DELETE /messages (pgmq message queue)
    encryption.js        — POST /jobs/:id/encrypt, POST /jobs/:id/decrypt (pgsodium AEAD)
    webhook.js           — PUT/DELETE /webhook (configure pg_net webhook URL)
    graphql.js           — POST /graphql (pg_graphql query proxy)

  lib/
    graceful.js          — SIGTERM/SIGINT handler → close server → close pool
    metrics.js           — In-process counters/gauges/timings (exposed at /metrics)
    pagination.js        — Limit/offset helper with defaults, clamping, and metadata builder
    storage.js           — S3/R2/GCS abstraction (@aws-sdk/client-s3), API key hashing
    job_events.js        — EventEmitter for broadcasting job status to WebSocket clients
    faker.js             — @faker-js/faker data generators (fakeUser, fakeDocument, fakeJob, fakeFile, etc.)

  worker/
    registry.js          — Handler registration (registerHandler, getHandler)
    send_email.js        — Sends email (logs to stdout; plug in a real provider)
    reindex_search.js    — Refreshes tenant_document_stats materialized view
    generate_embedding.js — Stores a placeholder vector (plug in a real embedding API)
    cleanup_files.js     — Removes orphaned file records from the database
    stale_recovery.js    — Resets stuck running jobs back to pending

scripts/
  migrate.js             — Custom forward-only migration runner (reads .sql files)
  seed.js                — Demo user, API key, documents, jobs, files; refreshes materialized view; seeds pgmq/pgsodium. Set SEED_COUNT=N for faker-generated bulk data.

loadtest/
  lib.js                 — Shared helpers: runAutocannon, auth, faker-powered data generators
  worker.js              — Worker thread runner for parallel scenarios
  scenarios/
    crud-flood.js          — Full document lifecycle (create→read→update→delete) with tracked IDs, variable body sizes
    search-mix.js          — Weighted FTS/fuzzy/soundex with typos, SQL injection attempts, compound filters
    job-storm.js           — Burst job enqueue with scheduling, deep pagination, priority distribution
    geo-proximity.js       — 12 real-world city hotspots, variable radius, geo+tag compound queries
    auth-burst.js          — Token refresh storms, invalid key rejection, malformed JWT, dual-auth
    dashboard-analytics.js — Weighted drill-down (stats/pivot/breakdown), variable month ranges
    message-queue.js       — 5 named queues, weighted payload types (notifications/webhooks/audit)
    encryption-cycle.js    — Creates real jobs, encrypts/decrypts sensitive payloads by tracking IDs
    websocket-flood.js     — 4-phase traffic (ramp-up→sustained→spike→cooldown) with connection churn
    mixed-workload.js      — Production traffic ratios (35% search, 15% read, 12% create, etc.)

migrations/
  001_init.sql                        — Extensions, app_users, documents, jobs, materialized view, indexes
  002_refresh_tenant_document_stats.sql — Refreshes materialized view concurrently
  003_api_keys_rls_roles.sql          — API keys table, RLS policies, DB roles (app_api, app_worker)
  004_functions.sql                   — SQL functions for all CRUD/business operations (SECURITY DEFINER)
  005_files_vector.sql                — Files table + RLS, pgvector column + HNSW index
  006_pg_cron.sql                     — pg_cron scheduled refresh of materialized view (every 5 min)
  007_recover_stale_jobs.sql          — Function to reset stuck running jobs
  008_extensions_quick_wins.sql       — citext, unaccent, hstore, ltree, btree_gin, pg_jsonschema
  009_functions_v2.sql                — Updated functions with metadata/path/unaccent support
  010_extensions_v2.sql               — hypopg, plpgsql_check, pgtap, postgres_fdw, index_advisor, rum, bloom, fuzzystrmatch, intarray, pg_hashids, tablefunc, postgis
  011_functions_v3.sql                — Updated functions for tags, location, fuzzy search, hashids, pivots
  012_pg_cron_v2.sql                  — pg_cron: stale recovery (*/2 min), archive old jobs (daily), cleanup failed (daily)
  013_pg_partman.sql                  — pg_partman: auto-partition jobs/files by month with 6-month retention
  014_pgmq.sql                        — pgmq: SQS-compatible message queue (enqueue, dequeue, archive)
  015_pg_net.sql                      — pg_net: webhook trigger on job completion (HTTP POST from SQL)
  016_pg_graphql.sql                  — pg_graphql: auto-generated GraphQL schema with tenant-scoped access
  017_pgsodium.sql                    — pgsodium: column-level encryption for sensitive job payloads

API Reference

All routes except /health, /metrics, /documentation/, and /auth/token require an API key via the x-api-key header or a JWT via Authorization: Bearer <token>.

Method	Path	Auth	Description
GET	/health	No	Health check with database ping
GET	/metrics	No	In-process metrics (counters, gauges, timings)
GET	/documentation/	No	Swagger UI (OpenAPI 3.1 spec)
GET	/documentation/json	No	OpenAPI 3.1 spec as JSON
POST	/auth/token	API Key	Exchange API key for a 1-hour JWT
POST	/documents	Yes	Create document (with optional metadata, path, tagIds, location)
GET	/documents/:id	Yes	Get single document
PUT	/documents/:id	Yes	Update document (title, body, metadata, path, tagIds, location)
DELETE	/documents/:id	Yes	Delete document
GET	/documents/search	Yes	Full-text / fuzzy / soundex / geo search with path and tag filtering
POST	/jobs	Yes	Enqueue a background job
GET	/jobs	Yes	List jobs (filterable by status)
GET	/dashboard/:tenantId	Yes	Get tenant dashboard (stats, pivot tables, job breakdowns)
POST	/files	Yes	Upload file (multipart)
GET	/files/:id	Yes	Get file metadata + signed download URL
DELETE	/files/:id	Yes	Delete file
GET	/ws/jobs	Yes	WebSocket: real-time job status updates
POST	/messages	Yes	Enqueue message to pgmq queue
GET	/messages	Yes	Dequeue messages from pgmq queue
DELETE	/messages	Yes	Archive a pgmq message
POST	/jobs/:id/encrypt	Yes	Encrypt job payload (pgsodium AEAD)
POST	/jobs/:id/decrypt	Yes	Decrypt encrypted job payload
PUT	/webhook	Yes	Set webhook URL for tenant
DELETE	/webhook	Yes	Remove webhook URL
POST	/graphql	Yes	Execute GraphQL query (pg_graphql)

Documents

Create a document

curl -X POST http://localhost:3000/documents \
  -H "x-api-key: $KEY" \
  -H "content-type: application/json" \
  -d '{
    "authorId": "00000000-0000-0000-0000-000000000010",
    "title": "Getting Started with PostgreSQL",
    "body": "PostgreSQL is a powerful, open source object-relational database system.",
    "metadata": {"priority": "high", "tags": "database, postgres"},
    "path": "engineering.backend.database",
    "tagIds": [1, 5, 12],
    "longitude": -122.4194,
    "latitude": 37.7749
  }'

• metadata — Flat key-value object stored as hstore. Useful for tags, labels, feature flags, or any extensible annotations. Keys and values must be strings.
• path — Dot-separated hierarchical path stored as ltree. Use for categories, folder structures, or org hierarchies. Only alphanumeric characters and underscores, separated by dots.
• tagIds — Array of integer tag IDs stored as integer[]. Indexed with intarray GiST for fast set operations (intersection, containment).
• longitude / latitude — Geographic coordinates stored as PostGIS geography(POINT). Must be provided as a pair. Enables proximity search.

Response:

{
  "id": "a1b2c3d4-...",
  "tenantId": "00000000-0000-0000-0000-000000000001",
  "authorId": "00000000-0000-0000-0000-000000000010",
  "title": "Getting Started with PostgreSQL",
  "body": "PostgreSQL is a powerful, open source object-relational database system.",
  "metadata": "\"priority\"=>\"high\", \"tags\"=>\"database, postgres\"",
  "path": "engineering.backend.database",
  "createdAt": "2025-01-15T10:30:00.000Z",
  "updatedAt": "2025-01-15T10:30:00.000Z"
}

Search documents

Supports three search modes with accent-insensitive matching (unaccent dictionary):

Mode	Parameter	Method	Best for
fts (default)	q=postgres	websearch_to_tsquery + rum index	Natural language queries
fuzzy	q=postgres&mode=fuzzy	Levenshtein distance via fuzzystrmatch	Typo-tolerant matching
soundex	q=robert&mode=soundex	Soundex phonetic matching via fuzzystrmatch	"Sounds like" queries

Additional filters:

Filter	Parameter	Example
Path subtree	pathPrefix=engineering.backend	Only documents under this path
Tags	tagIds=1,5,12	Documents with any of these tags
Geographic radius	longitude=-122.4&latitude=37.8&radius=5000	Documents within 5km (meters)

# basic full-text search
curl -H "x-api-key: $KEY" \
  "http://localhost:3000/documents/search?q=postgres&limit=10"

# fuzzy search (typo-tolerant)
curl -H "x-api-key: $KEY" \
  "http://localhost:3000/documents/search?q=postgras&mode=fuzzy"

# soundex search (phonetic matching: "robert" finds "Rupert")
curl -H "x-api-key: $KEY" \
  "http://localhost:3000/documents/search?q=robert&mode=soundex"

# geo search: within 5km of San Francisco
curl -H "x-api-key: $KEY" \
  "http://localhost:3000/documents/search?q=coffee&longitude=-122.4194&latitude=37.7749&radius=5000"

# search within a path subtree with tag filter
curl -H "x-api-key: $KEY" \
  "http://localhost:3000/documents/search?q=api&pathPrefix=engineering.backend&tagIds=1,5"

# accent-insensitive: matches "café" when searching "cafe"
curl -H "x-api-key: $KEY" \
  "http://localhost:3000/documents/search?q=cafe"

Response:

{
  "items": [
    {
      "id": "a1b2c3d4-...",
      "title": "Getting Started with PostgreSQL",
      "fts_rank": 0.075,
      "trigram_score": 0.5,
      "createdAt": "2025-01-15T10:30:00.000Z",
      "path": "engineering.backend.database"
    }
  ]
}

Update a document

curl -X PUT http://localhost:3000/documents/a1b2c3d4-... \
  -H "x-api-key: $KEY" \
  -H "content-type: application/json" \
  -d '{"title": "Updated Title", "metadata": {"status": "archived"}, "path": "archive.old"}'

Delete a document

curl -X DELETE http://localhost:3000/documents/a1b2c3d4-... \
  -H "x-api-key: $KEY"

Jobs

Enqueue a job

The type field determines which worker handler processes the job. The payload is validated against a JSON Schema via pg_jsonschema — malformed payloads are rejected at the database level.

curl -X POST http://localhost:3000/jobs \
  -H "x-api-key: $KEY" \
  -H "content-type: application/json" \
  -d '{
    "type": "send_email",
    "payload": {"to": "user@example.com", "subject": "Welcome"},
    "priority": 10
  }'

Response:

{
  "id": 42,
  "type": "send_email",
  "payload": {"to": "user@example.com", "subject": "Welcome"},
  "status": "pending",
  "priority": 10,
  "attempts": 0,
  "createdAt": "2025-01-15T10:30:00.000Z"
}

Payload validation — each job type has a JSON Schema enforced by pg_jsonschema:

Job type	Required fields	Optional fields
send_email	to (email)	subject, body
generate_embedding	document_id (uuid)	—
reindex_search	—	tenant_id (uuid)
cleanup_files	—	older_than_hours (integer, min 1)

Invalid payloads are rejected at INSERT time — you get a database error instead of a silently malformed job.

List jobs

# all jobs
curl -H "x-api-key: $KEY" "http://localhost:3000/jobs"

# filter by status
curl -H "x-api-key: $KEY" "http://localhost:3000/jobs?status=pending&limit=20&offset=0"

Dashboard

Three dashboard views available via the view query parameter:

Stats (default) — Pre-computed tenant stats from materialized view:

curl -H "x-api-key: $KEY" \
  "http://localhost:3000/dashboard/00000000-0000-0000-0000-000000000001"

Response:

{
  "tenantId": "00000000-0000-0000-0000-000000000001",
  "documentCount": 5,
  "newestDocumentAt": "2025-01-15T10:30:00.000Z",
  "updatedAt": "2025-01-15T10:35:00.000Z"
}

Pivot — Document creation counts by month (via tablefunc crosstab):

curl -H "x-api-key: $KEY" \
  "http://localhost:3000/dashboard/00000000-0000-0000-0000-000000000001?view=pivot&months=6"

Response:

{
  "tenantId": "00000000-0000-0000-0000-000000000001",
  "pivot": [
    {"month": "2025-01", "count": 12},
    {"month": "2025-02", "count": 8}
  ]
}

Breakdown — Job status counts by type:

curl -H "x-api-key: $KEY" \
  "http://localhost:3000/dashboard/00000000-0000-0000-0000-000000000001?view=breakdown"

Response:

{
  "tenantId": "00000000-0000-0000-0000-000000000001",
  "breakdown": [
    {"type": "send_email", "pending": 3, "running": 1, "done": 45, "failed": 2},
    {"type": "generate_embedding", "pending": 0, "running": 0, "done": 30, "failed": 0}
  ]
}

Files

Upload a file (requires STORAGE_* env vars)

curl -X POST http://localhost:3000/files \
  -H "x-api-key: $KEY" \
  -F "file=@report.pdf" \
  -F "uploaderId=00000000-0000-0000-0000-000000000010"

Response:

{
  "id": "f1e2d3c4-...",
  "filename": "report.pdf",
  "contentType": "application/pdf",
  "sizeBytes": 1048576,
  "createdAt": "2025-01-15T10:30:00.000Z"
}

Get file metadata + signed download URL

curl -H "x-api-key: $KEY" "http://localhost:3000/files/f1e2d3c4-..."

If STORAGE_* env vars are not configured, file endpoints return 501 Not Implemented.

Message Queue (pgmq)

Send, receive, and archive messages on named Postgres queues. Powered by the pgmq extension.

Enqueue a message

curl -X POST http://localhost:3000/messages \
  -H "x-api-key: $KEY" \
  -H "content-type: application/json" \
  -d '{"queue": "notifications", "payload": {"action": "welcome", "email": "user@example.com"}}'

Response:

{"msgId": 42}

Dequeue messages

curl -H "x-api-key: $KEY" "http://localhost:3000/messages?queue=notifications&limit=10"

Messages have a 5-minute visibility timeout — dequeued messages are hidden from other consumers until the timeout expires. If not archived within that window, they become visible again.

Archive a message (mark as processed)

curl -X DELETE http://localhost:3000/messages \
  -H "x-api-key: $KEY" \
  -H "content-type: application/json" \
  -d '{"queue": "notifications", "msgId": 42}'

Response: {"archived": true}

Encryption (pgsodium)

Encrypt and decrypt sensitive job payloads using pgsodium AEAD (Authenticated Encryption with Additional Data). The tenant_id is used as additional authenticated data — ciphertext encrypted under one tenant cannot be decrypted under another.

Encrypt a job's payload

curl -X POST http://localhost:3000/jobs/42/encrypt \
  -H "x-api-key: $KEY"

Response: {"encrypted": true} — the payload_encrypted column is set; the original payload jsonb is left intact.

Decrypt a job's payload

curl -X POST http://localhost:3000/jobs/42/decrypt \
  -H "x-api-key: $KEY"

Response:

{"payload": {"to": "user@example.com", "subject": "Welcome"}}

Returns 404 if no encrypted payload exists for that job.

Webhooks (pg_net)

Configure a per-session webhook URL. When a job transitions to done or failed, the notify_job_webhook trigger fires net.http_post to the configured URL with the job's metadata as the request body.

Set webhook URL

curl -X PUT http://localhost:3000/webhook \
  -H "x-api-key: $KEY" \
  -H "content-type: application/json" \
  -d '{"url": "https://hooks.slack.com/services/T00/B00/xxx"}'

Remove webhook URL

curl -X DELETE http://localhost:3000/webhook \
  -H "x-api-key: $KEY"

The webhook URL is set per database session via SET app.webhook_url. The trigger checks this setting before firing — if null, no HTTP request is made. Requires pg_net extension and shared_preload_libraries.

GraphQL (pg_graphql)

Execute GraphQL queries against the database via pg_graphql. Schema annotations on tables and columns (set in migration 016) enable filtering and ordering.

curl -X POST http://localhost:3000/graphql \
  -H "x-api-key: $KEY" \
  -H "content-type: application/json" \
  -d '{"query": "{ documents { id title path createdAt } }"}'

Variables are supported:

curl -X POST http://localhost:3000/graphql \
  -H "x-api-key: $KEY" \
  -H "content-type: application/json" \
  -d '{"query": "query ($limit: Int) { documents(first: $limit) { id title } }", "variables": {"limit": 5}}'

Tenant isolation is enforced via current_setting('app.tenant_id') through the auth middleware. Requires pg_graphql extension.

Health & Metrics

# health check (no auth required)
curl http://localhost:3000/health

# in-process metrics (no auth required)
curl http://localhost:3000/metrics

Database Schema

Tables

app_users — Application users, scoped per tenant.

Column	Type	Notes
id	uuid (PK)	Auto-generated
tenant_id	uuid	Tenant scope
email	citext	Case-insensitive, unique per tenant
created_at	timestamptz

api_keys — API key credentials, scoped per tenant.

Column	Type	Notes
id	uuid (PK)	Auto-generated
tenant_id	uuid	Tenant scope
key_prefix	text	First 12 chars for identification
key_hash	text	SHA-256 hash (plaintext never stored)
name	text	Human-readable label
created_at	timestamptz
expires_at	timestamptz	Nullable — null = never expires
last_used_at	timestamptz	Updated on each request

documents — User documents with full-text search, metadata, hierarchy, tags, and location.

Column	Type	Notes
id	uuid (PK)	Auto-generated
tenant_id	uuid	Tenant scope (RLS)
author_id	uuid (FK → app_users)
title	text	Indexed with pg_trgm for typo-tolerant search
body	text
search_tsv	tsvector	Auto-generated from title + body using unaccent dictionary, indexed with RUM
metadata	hstore	Key-value metadata (tags, labels, annotations) with GiST index
path	ltree	Hierarchical path for categories/folders with GiST index
tag_ids	integer[]	Tag IDs with GiST index via intarray
location	geography(POINT)	PostGIS geographic coordinates for proximity search
embedding	vector(1536)	Optional pgvector embedding
created_at	timestamptz
updated_at	timestamptz

jobs — Background job queue with JSON Schema validation.

Column	Type	Notes
id	bigserial (PK)
tenant_id	uuid	Tenant scope (RLS)
type	text	Determines which handler processes it
payload	jsonb	Arbitrary job data, validated per type by pg_jsonschema CHECK constraints
payload_encrypted	text	pgsodium AEAD-encrypted ciphertext (nullable, set via encrypt_job_payload)
status	text	pending → running → done / failed
priority	int	Higher = runs first
attempts	int	Incremented on each retry
run_at	timestamptz	Scheduled execution time
started_at	timestamptz	When the worker claimed it
finished_at	timestamptz	When it completed or failed
last_error	text	Error message from last attempt
created_at	timestamptz
updated_at	timestamptz

files — Uploaded file metadata.

Column	Type	Notes
id	uuid (PK)	Auto-generated
tenant_id	uuid	Tenant scope (RLS)
uploader_id	uuid (FK → app_users)
filename	text	Original filename
content_type	text	MIME type
size_bytes	bigint
storage_key	text	S3 object key
created_at	timestamptz

tenant_document_stats — Materialized view with pre-computed per-tenant stats (document count, newest document timestamp). Refreshed every 5 minutes via pg_cron.

Row-Level Security (RLS)

Enabled on documents, jobs, and files. Policies use current_setting('app.tenant_id', true) to scope rows. The withTenant(pool, tenantId, fn) helper wraps queries in a transaction with SET LOCAL app.tenant_id, ensuring the API layer never leaks cross-tenant data.

SQL functions use SECURITY DEFINER with explicit tenant_id parameters — they bypass RLS but enforce isolation through function arguments.

Database Roles

Role	Purpose
app_api	Used by the API service — can execute functions, read/write documents, jobs, files
app_worker	Used by the worker — can claim/complete/fail jobs, read tables
Table owner	Used for migrations

SQL Functions

Function	Purpose
create_document(tenant_id, author_id, title, body, metadata, path, tag_ids, longitude, latitude)	Insert a document with optional hstore metadata, ltree path, integer tags, and PostGIS location
update_document(id, tenant_id, title, body, metadata, path, tag_ids, longitude, latitude)	Update document fields (null params = keep existing)
delete_document(id, tenant_id)	Delete a document
search_documents(tenant_id, query, limit, path_prefix, mode, tag_ids, longitude, latitude, radius)	Multi-mode search: FTS (default), fuzzy (Levenshtein), soundex — with path, tag, and geo filtering
enqueue_job(tenant_id, type, payload, run_at, priority)	Create a job (payload validated by pg_jsonschema)
claim_job()	Claim next pending job (FOR UPDATE SKIP LOCKED)
complete_job(id)	Mark job done
fail_job(id, error)	Mark job failed
retry_job(id, run_at, max_attempts)	Re-queue or fail permanently if max attempts exceeded
get_tenant_stats(tenant_id)	Read from materialized view
get_tenant_documents_pivot(tenant_id, months)	Monthly document creation pivot table (tablefunc)
get_tenant_jobs_breakdown(tenant_id)	Job status counts grouped by type
encode_document_id(id)	Convert document UUID to short hashid
encode_job_id(id) / decode_job_id(hashid)	Convert job bigserial to/from short hashid
recover_stale_jobs(stale_minutes)	Reset stuck running jobs back to pending
pgmq_enqueue(p_queue, p_payload)	Send message to pgmq queue
pgmq_dequeue(p_queue, p_limit)	Read messages from pgmq queue (5-min visibility timeout)
pgmq_archive_message(p_queue, p_msg_id)	Archive a processed pgmq message
notify_job_webhook()	Trigger: fires net.http_post on job status change to done/failed
graphql.resolve_tenant_documents()	Tenant-scoped GraphQL resolver for documents
encrypt_job_payload(p_id, p_tenant_id)	Encrypt job payload using pgsodium AEAD
decrypt_job_payload(p_encrypted, p_tenant_id)	Decrypt pgsodium-encrypted job payload

PostgreSQL Extensions & Types

Core Types (built into PostgreSQL)

These are not extensions — they ship with every PostgreSQL installation:

Type	Where used	What it replaces
jsonb	jobs.payload	MongoDB, DynamoDB — schemaless JSON with full indexing (GIN), nested queries (@>, ?, ->>), and ACID transactions
tsvector / tsquery	documents.search_tsv	Elasticsearch — generated column auto-maintains a search index, with ranking, stemming, and phrase matching
uuid	All primary keys	Auto-incrementing integers — globally unique, safe for distributed systems

Extensions (enabled via migrations)

Extension	Purpose	Required	External service replaced
pgcrypto	UUID generation (gen_random_uuid())	Yes	—
pg_trgm	Trigram similarity search on document titles	Yes	Elasticsearch fuzzy matching
citext	Case-insensitive text for emails	Yes	Application-level LOWER() normalization
unaccent	Removes diacritics for FTS (café → cafe)	Yes	Custom NLP pipeline
hstore	Key-value metadata with GIN/GiST indexes	Yes	MongoDB, DynamoDB, Redis hashes
ltree	Hierarchical tree paths with subtree queries	Yes	Graph DB, nested set model
btree_gin	Composite indexes (btree + GIN) for tenant+FTS	Yes	— (performance)
pg_jsonschema	JSON Schema validation on jsonb columns	Yes	Application-level validation library
rum	RUM index for ranked FTS without post-sort	Yes	— (FTS performance)
bloom	Space-efficient multi-column indexes on jobs	Yes	Multiple partial indexes
fuzzystrmatch	Soundex, Levenshtein, Metaphone matching	Yes	Custom phonetic NLP
intarray	Fast integer array operations + GiST indexes	Yes	Slow ANY()/unnest() queries
pg_hashids	Short, opaque public IDs from sequential numbers	Yes	Exposing UUIDs or sequential IDs in URLs
tablefunc	Crosstab/pivot tables for dashboard analytics	Yes	Application-side data pivoting
postgis	Geospatial types and proximity queries	No	Google Maps API, PostGIS external
hypopg	Hypothetical indexes for tuning without creating them	No	Manual EXPLAIN guesswork
plpgsql_check	PL/pgSQL function linter (catches errors at definition)	No	Runtime SQL bugs
pgtap	Database unit testing framework	No	HTTP-only integration tests
postgres_fdw	Query external PostgreSQL servers as local tables	No	ETL scripts, data federation
index_advisor	Automatic index recommendations with cost estimates	No	Manual EXPLAIN ANALYZE
pg_stat_statements	Query performance monitoring	No*	Datadog, New Relic APM
pg_cron	Scheduled jobs: mat view refresh, stale recovery, archival	No*	cron, Celery, Temporal
pgaudit	Audit logging	No*	Custom audit trail middleware
vector (pgvector)	Embedding similarity search with HNSW index	No	Pinecone, Weaviate, Milvus
pg_partman	Auto-partitions jobs/files by month, 6-month retention	No*	Manual partition management
pgmq	Postgres-native message queue (enqueue, dequeue, archive)	No	SQS, RabbitMQ, Redis queues
pg_net	HTTP webhooks from SQL (trigger on job completion)	No*	Zapier, custom webhook services
pg_graphql	Auto-generated GraphQL API with schema annotations	No	Hasura, custom GraphQL server
pgsodium	AEAD column encryption for sensitive job payloads	No	AWS KMS, Vault

*Requires shared_preload_libraries in PostgreSQL server config.

How Each Extension Works

citext — Wraps the text type with case-insensitive comparison operators. app_users.email uses it so 'Alice@Example.COM' and 'alice@example.com' are treated identically in UNIQUE constraints, WHERE clauses, and JOINs. Zero code changes — it's transparent to the application.

unaccent — A text search dictionary that strips diacritical marks. Combined with a custom text search configuration (en_unaccent), the search_tsv generated column on documents automatically indexes accent-normalized text. Searching for "cafe" finds documents containing "café", "naïve" matches "naive", and so on. Critical for any application with international content.

hstore — A key-value type that stores flat string-to-string maps. Indexed with GiST for fast containment queries (@>). Used on documents.metadata for extensible tagging and annotations. Example: {'priority' => 'high', 'tags' => 'api, docs'}. Faster than jsonb for flat data because it doesn't parse nested structures.

ltree — Represents tree paths as a single column value like 'engineering.backend.api'. Supports subtree queries (<@ finds all descendants), ancestor checks (@>), and pattern matching (~ with regex). A GiST index makes these queries instant even with millions of rows. Used on documents.path for categories, folder structures, and org hierarchies.

btree_gin — Enables composite GIN indexes that include standard btree types. The documents_tenant_fts_idx index combines tenant_id (equality) with search_tsv (full-text) in a single index — the database can satisfy "tenant X's documents matching query Y" with one index scan instead of two.

pg_jsonschema — Adds jsonb_matches_schema() function for use in CHECK constraints. Each job type has a JSON Schema enforced at the database level — send_email requires to (email format), generate_embedding requires document_id (uuid format), etc. Invalid payloads are rejected at INSERT time with a constraint violation error, ensuring malformed jobs never enter the queue.

pgvector — Adds a vector data type for dense embeddings with HNSW (approximate nearest neighbor) and IVFFlat indexes. The documents.embedding column stores 1536-dimensional vectors. Supports cosine distance (<=>), inner product (<#>), and L1 distance (<+>). Enables "find similar documents" queries without a separate vector database.

pg_trgm — Trigram matching breaks text into 3-character subsequences and computes similarity scores. Used for typo-tolerant search on document titles — searching "postgras" finds "PostgreSQL". The % operator uses a GiST index for fast fuzzy matching.

pg_cron — Runs SQL statements on a cron schedule inside the database. Used to refresh the tenant_document_stats materialized view every 5 minutes. No external scheduler needed.

pgcrypto — Cryptographic functions including gen_random_uuid() for primary keys. Ships with PostgreSQL but must be enabled via CREATE EXTENSION.

rum — A GIN-like index that stores additional information (lexeme positions, timestamps) in the index itself. Unlike a standard GIN index, RUM can return results pre-sorted by ts_rank, eliminating the expensive post-index sort. The RUM index on documents.search_tsv means ranked FTS queries are a single index scan.

bloom — A space-efficient index based on Bloom filters that supports equality checks on any combination of indexed columns. A single bloom index on jobs(tenant_id, status, type) accelerates queries filtering on any subset of those columns — replacing multiple partial indexes that would each only help one specific WHERE clause.

fuzzystrmatch — Provides Soundex (phonetic matching — "Robert" sounds like "Rupert"), Levenshtein distance (edit distance between strings), and Double Metaphone. The soundex search mode in search_documents() uses it for "sounds like" queries. Levenshtein enables "did you mean?" suggestions.

intarray — Optimized operators and GiST index support for integer arrays. The tag_ids column on documents uses it so queries like WHERE tag_ids && ARRAY[1,5] (has any of these tags) or WHERE tag_ids @> ARRAY[1,5] (has all of these tags) are index-accelerated instead of sequential scans.

pg_hashids — Generates short, YouTube-like IDs from numbers (e.g., job ID 42 → "jR"). The encode_job_id() and decode_job_id() SQL functions use it so API URLs can use /jobs/jR instead of /jobs/42, hiding business info (sequential IDs reveal creation order and volume).

tablefunc — Provides crosstab() for pivot tables directly in SQL. The get_tenant_documents_pivot() function uses it to generate monthly document creation counts — a single SQL call produces data that would otherwise require application-side array manipulation.

postgis — The gold standard for geospatial data in PostgreSQL. Adds geography types, distance calculations (ST_DWithin), bounding box queries, and spatial indexes. The location column on documents enables "find documents within 5km of this point" queries with index support.

hypopg — Creates hypothetical indexes that exist only in memory — not on disk. Feed a query to index_advisor (which uses hypopg) and it tells you exactly which indexes to create, with before/after cost estimates, without the risk of a multi-hour CREATE INDEX on a production table.

plpgsql_check — Static analysis for PL/pgSQL functions. Reports unused variables, type mismatches, missing RETURN paths, and SQL errors at function definition time rather than at runtime. Run it against all functions in migration 011 to catch issues before they hit production.

pgtap — xUnit-style testing framework for PostgreSQL. Write SELECT plan(3); SELECT ok(...); SELECT is(...); to test SQL functions, RLS policies, and constraints directly inside the database — no Node.js or HTTP layer needed.

postgres_fdw — Foreign Data Wrapper that lets you query a remote PostgreSQL server as if it were a local table. Use it to read from a reporting database, a legacy system, or a separate analytics server without data replication.

index_advisor (Supabase) — Given a SQL query, recommends which indexes to create and shows estimated cost improvements. Uses hypopg under the hood. Supports parameterized queries ($1, $2) and works through views.

pg_partman — Automated table partitioning. Migration 013 partitions jobs and files by created_at into monthly native partitions with 3 premade. A 6-month retention policy auto-drops old partitions (configurable in partman.part_config). pg_cron runs partman.run_maintenance() every 10 minutes to create new partitions and drop expired ones. Keeps the active partition small so queries on recent data stay fast.

pgmq — Postgres-native message queue. Provides send, read, and archive operations on named queues. Messages have a visibility timeout — once read, they're hidden from other consumers until the timeout expires (default 5 minutes in the pgmq_dequeue wrapper). If not archived within the window, they become visible again. Replaces SQS/RabbitMQ for most queue use cases. Migration 014 creates a job_queue and wraps pgmq functions with SECURITY DEFINER helpers.

pg_net — Asynchronous HTTP client inside PostgreSQL. The notify_job_webhook() trigger function (migration 015) fires net.http_post whenever a job transitions to done or failed. The target URL is read from current_setting('app.webhook_url'), set per-session via PUT /webhook. The HTTP request is asynchronous and non-blocking — it won't slow down the job update. Replaces external webhook services like Zapier or custom notification infrastructure.

pg_graphql — Auto-generates a GraphQL schema from PostgreSQL tables. Migration 016 adds @graphql comments on tables (documents, jobs, files, app_users, api_keys) and columns to enable filtering and ordering. The POST /graphql route calls graphql.resolve() with the query and variables. The graphql.resolve_tenant_documents() function enforces RLS via current_setting('app.tenant_id'). Replaces Hasura or custom GraphQL servers for basic query use cases.

pgsodium — Modern cryptography extension built on libsodium. Migration 017 creates an AEAD-Det (Authenticated Encryption with Additional Data) key named app_payload_encryption. The encrypt_job_payload() function encrypts a job's jsonb payload using the key and the tenant_id as additional authenticated data — this means ciphertext encrypted under Tenant A cannot be decrypted under Tenant B, even by a DBA. The decrypt_job_payload() function reverses it. The encrypted ciphertext is stored in a separate payload_encrypted text column. Replaces AWS KMS or HashiCorp Vault for column-level encryption.

Worker

The worker (src/worker.js) is a long-running process that:

1. Polls the jobs table using claim_job() (which uses FOR UPDATE SKIP LOCKED for safe concurrent claiming)
2. Dispatches to a handler registered by job type
3. Completes the job on success, or retries with exponential backoff on failure
4. Recovers stale jobs — every 60 seconds, resets running jobs older than 10 minutes back to pending (in case a worker crashed mid-processing)
5. Shuts down gracefully on SIGTERM/SIGINT — finishes the current job, then exits

Built-in Job Handlers

Type	Handler	Description
send_email	src/worker/send_email.js	Logs email payload to stdout. Plug in a real email provider (SES, Mailgun, etc.).
reindex_search	src/worker/reindex_search.js	Refreshes the tenant_document_stats materialized view.
generate_embedding	src/worker/generate_embedding.js	Stores a placeholder vector. Plug in a real embedding API (OpenAI, Cohere, etc.).
cleanup_files	src/worker/cleanup_files.js	Removes orphaned file records from the database.

Adding a New Job Handler

1. Create a file in src/worker/:

// src/worker/my_handler.js
export default async function myHandler(job) {
  const { payload } = job;
  // do work...
  return { result: "done" };
}

2. Register it in src/worker.js:

import myHandler from "./worker/my_handler.js";
registerHandler("my_handler", myHandler);

3. Add JSON Schema validation in a migration:

ALTER TABLE jobs ADD CONSTRAINT my_handler_payload_valid
  CHECK (
    type != 'my_handler' OR
    jsonb_matches_schema('{"type":"object","required":["key"]}'::jsonschema, payload)
  );

4. Enqueue from the API:

curl -X POST http://localhost:3000/jobs \
  -H "x-api-key: $KEY" \
  -H "content-type: application/json" \
  -d '{"type": "my_handler", "payload": {"key": "value"}}'

Backoff Formula

When a job fails, it is retried with: base × 2^(attempts-1) + random jitter

Attempt	Delay
1	~2s
2	~4s
3	~8s
4	~16s
5	permanently failed

Security

API Key Authentication

• Keys are generated by the seed script (or programmatically) and hashed with SHA-256 before storage
• Only the first 12 characters (key_prefix) are stored in cleartext for identification
• The full plaintext key is shown exactly once at creation and cannot be recovered
• Keys support optional expiration (expires_at) and track last usage (last_used_at)

Tenant Isolation

Enforced at three independent layers:

1. API layer — Auth middleware resolves the API key to a tenant_id and attaches it to the request
2. Function layer — SQL functions accept tenant_id as an explicit parameter and filter by it
3. Database layer — RLS policies enforce that queries only return rows matching current_setting('app.tenant_id')

Other Security Measures

• Helmet — Security headers (X-Content-Type-Options, X-Frame-Options, etc.)
• CORS — Disabled by default; configure allowed origins via CORS_ORIGIN env var
• Parameterized queries — All SQL uses $1, $2 placeholders — no string interpolation
• Least-privilege DB roles — app_api and app_worker only have the grants they need
• Payload validation — pg_jsonschema CHECK constraints reject malformed job payloads at the database level

Testing

npm test           # run all tests once
npm run test:watch  # run tests in watch mode

Tests use Vitest and mock database calls (no running Postgres required). 177 tests across 15 files:

tests/
  middleware/
    auth.test.js           — Auth middleware (public paths, API key validation, JWT fallback)
    rate_limit.test.js     — Rate limiting (200 under limit, 429 over limit, retry-after header)
    error_handler.test.js  — Error handling (500 JSON for unhandled errors, 404 JSON for unknown routes)
  worker/
    stale_recovery.test.js — Stale job recovery logic (4 tests)
  routes/
    documents.test.js      — Document CRUD with metadata/path (7 tests)
    documents_v2.test.js   — Tag IDs, location, validation (7 tests)
    search_v2.test.js      — FTS/fuzzy/soundex, geo, tag filtering (7 tests)
    dashboard_v2.test.js   — Stats, pivot, breakdown, validation (6 tests)
    validation.test.js     — Request schema validation (4 tests)
    openapi.test.js        — OpenAPI spec generation (6 tests)
    websocket.test.js      — WebSocket route registration, EventEmitter (4 tests)
    jwt_auth.test.js       — JWT issuance, verification, expiration (6 tests)
    new_routes.test.js     — Messages, encryption, webhook, GraphQL (9 tests)
  lib/
    pagination.test.js     — Pagination helper (9 tests)
  migrations/
    extensions.test.js     — Verifies migration SQL structure for all 17 migrations (84 tests)

The test suite covers: authentication (public/protected paths, API key, JWT), rate limiting, error handling, document CRUD, search (FTS/fuzzy/soundex/geo), dashboard, job creation, pgmq message queue, encryption/decryption, webhooks, GraphQL, pagination, worker stale recovery, OpenAPI spec, WebSocket events, and migration structure verification for all 17 migrations.

Migration System

Custom runner in scripts/migrate.js:

• Files in migrations/ sorted lexicographically and applied in order
• Each migration runs in a transaction
• Tracks applied files in the schema_migrations table
• SQL is idempotent (CREATE TABLE IF NOT EXISTS, CREATE OR REPLACE FUNCTION)
• Forward-only — no rollback support

To add a new migration, create a file like 013_my_feature.sql in migrations/.

Notes & Gotchas

• pg_cron, pgaudit, pg_stat_statements: These extensions require PostgreSQL server-level configuration (shared_preload_libraries). They may fail or be no-ops on managed providers.
• vector extension: If your PostgreSQL provider doesn't support pgvector, remove migration 005. The rest of the API works fine.
• postgis: Available on most managed providers (Supabase, Neon, AWS RDS, GCP Cloud SQL). If unavailable, remove the location column from migration 010 and the geo search logic from migration 011.
• pg_jsonschema: Available on Supabase, Neon, and self-hosted PostgreSQL. If your provider doesn't support it, remove the CHECK constraints — the API will still work, but payload validation will happen only at the application level.
• rum extension: Not available on all managed providers. If unavailable, the GIN index from migration 001 still works — just without the pre-sorted ranking optimization.
• Worker stale recovery: Runs every 60 seconds in the worker process, plus a pg_cron job every 2 minutes (migration 012) as a backup.
• API key shown once: The seed script generates and prints an API key. It's stored as a SHA-256 hash — the plaintext cannot be recovered.
• File storage is optional: If STORAGE_* env vars are not set, file endpoints return 501 Not Implemented. The rest of the API works normally.
• /health and /metrics are public: No authentication required. In production, consider restricting access.
• Materialized view refresh: Requires pg_cron for auto-refresh. If not available, call REFRESH MATERIALIZED VIEW CONCURRENTLY tenant_document_stats manually.
• Job archival: pg_cron automatically archives completed jobs older than 90 days and cleans up failed jobs older than 30 days (migration 012).
• hstore vs jsonb: hstore is for flat string-to-string maps with fast containment queries (tags, labels, metadata). jsonb is for nested structures (job payloads). They coexist — use the right tool for the job.
• pg_partman: Requires shared_preload_libraries. Partition maintenance runs via pg_cron every 10 minutes. If unavailable, tables stay unpartitioned.
• pgmq: If unavailable, message queue functions won't exist. seed.js wraps pgmq seeding in try/catch.
• pg_net: Requires shared_preload_libraries. Webhook trigger checks current_setting('app.webhook_url', true) before firing — set per-session via PUT /webhook.
• pg_graphql: If unavailable, GraphQL annotations and resolve function won't exist. REST API unaffected.
• pgsodium: If unavailable, remove migration 017 and payload_encrypted column. seed.js wraps encryption in try/catch.
• JWT_SECRET default: Falls back to insecure-default-change-me if env var is empty. Set a strong secret in production.
• Load testing: Run npm run loadtest -- --key <api-key> against a running API. Seeds 200 docs + 100 jobs before scenarios. 10 scenarios with production-like traffic patterns.
• Faker data: src/lib/faker.js provides typed generators (fakeDocument, fakeJob, etc.) with faker.seed(42) for reproducibility. Used by seed.js and load tests.

Load Testing

Uses autocannon via scripts/loadtest.js. Requires a running API server and an API key. Pre-seeds 200 documents + 100 jobs with realistic faker data before scenarios run.

npm run loadtest -- --key <api-key> --url http://localhost:3000 --duration 10 --connections 20

Options: --url, --key (required), --duration (default 10s), --connections (default 20), --scenario (default "all"), --parallel (default "true"), --seed (default 200 docs).

Scenarios

Scenario	What it stresses
crud-flood	Full create→read→update→delete lifecycle. Tracks created IDs. Variable body sizes (500B–50KB). 15% delete probability. 404 probes.
search-mix	Weighted query distributions (60% FTS, 30% fuzzy, 10% soundex). Typo queries. SQL injection attempts. Edge-case inputs. Compound geo+path+tag filters.
job-storm	Burst enqueue patterns (1/5/20). Scheduled future jobs. Weighted status polling. Deep pagination. Priority distribution.
geo-proximity	12 real-world city hotspots with jitter. Radius 500m–200km. Midpoint-between-cities queries. Zero-result probes. Geo+tag compounds.
auth-burst	Token refresh storms. Invalid key rejection (SQL injection, empty, oversized, malformed JWT). No-auth tests. Dual-auth. Rotating cache.
dashboard-analytics	Weighted drill-down (stats 50%, pivot 30%, breakdown 20%). Variable month ranges. Connection-capped for expensive queries.
message-queue	5 named queues. Weighted payload types (notifications, webhooks, audit). Batch payloads. Variable dequeue limits.
encryption-cycle	Creates real jobs, encrypts/decrypts by tracking IDs. Sensitive payload patterns (passwords, credit cards, API keys).
websocket-flood	4-phase traffic (ramp-up→sustained→spike→cooldown). 10% connection churn every 2s. Variable batch sizes. Message validation.
mixed-workload	Production traffic ratios via weight (35% search, 15% read, 12% create, 8% update, 10% job list, 5% job create, 5% dashboard, etc.).

Dependencies

Package	Purpose
fastify	HTTP framework
pg	PostgreSQL client
dotenv	Environment variable loading
@fastify/cors	CORS support
@fastify/helmet	Security headers
@fastify/multipart	File upload handling
@fastify/rate-limit	Request rate limiting (100 req/min)
@aws-sdk/client-s3	S3/R2/GCS object storage
@aws-sdk/s3-request-presigner	Signed URL generation
fluent-json-schema	JSON Schema validation for request payloads
@fastify/swagger	OpenAPI 3.1 spec generation
@fastify/swagger-ui	Swagger UI at /documentation/
@fastify/jwt	JWT token signing and verification
@fastify/websocket	WebSocket support for real-time job updates
@faker-js/faker	Realistic fake data for seeding and load testing (dev dependency)
vitest	Test framework (dev dependency)
autocannon	HTTP load testing (dev dependency)
ws	WebSocket client for tests (dev dependency)

License

ISC