CRMS Cloud Control Plane: Case Study

The problem

Application-layer multi-tenancy and check-then-act quota checks break under concurrent requests: a missing WHERE clause leaks tenant rows, and parallel VM provisioning requests bypass vCPU/RAM limits.

Check-then-act race conditions allow concurrent API calls to over-allocate organization vCPU and RAM quotas.

Time-series container telemetry quickly grows past 1,000,000 rows, making unindexed B-tree scans slow and bloated.

Exposing the raw Docker socket to a web backend creates a root-privilege escalation vector.

How it works

01

PostgreSQL 16 Row-Level Security (RLS)

Wrapped every request in a database transaction setting SET LOCAL app.current_org_id, enforcing tenant isolation directly inside PostgreSQL policies alongside btree_gist temporal exclusion constraints.

02

Pessimistic SELECT FOR UPDATE quota locks

Implemented sp_allocate_resource in PL/pgSQL to lock the parent quota row during allocation, serializing concurrent requests and eliminating over-allocation races.

03

1M+ row range partitioning + BRIN & GIN indexes

Partitioned usage_metrics by timestamp and indexed with BRIN (block-range) and GIN (JSONB tags), reducing time-range query latency from 42ms to 0.2ms.

04

Read-only Docker proxy & LISTEN/NOTIFY SSE

Collected live container CPU/memory every 30s through a restricted tecnativa/docker-socket-proxy and pushed threshold alerts to the React console via PostgreSQL pg_notify and Server-Sent Events.

Architecture

All business invariants live inside PostgreSQL 16 triggers, stored procedures, and RLS policies.

01
Isolated Docker telemetry & console
Container stats are read via a restricted socket proxy while tenants interact through the React console.
02
30-second metric ingestion
The collector batches CPU, memory, network, and disk I/O readings into PostgreSQL.
03
Per-request RLS transaction context
Every API call sets app.current_org_id locally so PostgreSQL policies filter rows automatically.
04
Transactional stored procedures
SELECT ... FOR UPDATE row locks prevent concurrent VM quota over-allocation.
05
Partitioned storage with BRIN & GIN
1,000,000+ metric rows are range-partitioned and queried in 0.2ms.
06
Zero-polling LISTEN/NOTIFY stream
Threshold triggers fire pg_notify, streaming live alerts to the browser over SSE.

Database benchmark (1,000,000+ rows)

indexed time-range query latency (down from 42ms): 0.2 ms
partitioned telemetry rows benchmarked: 1,000,000+
3NF/BCNF tables with RLS & exclusion constraints: 14
quota race-condition over-allocations under parallel load: 0

1M-row time-range query execution timePostgreSQL EXPLAIN ANALYZE execution time before and after range partitioning + BRIN/GIN indexing (lower is better).

Partitioned + BRIN / GIN Index0.2 ms
Unindexed Sequential Scan42.0 ms

Technical deep dive

Complete system specification, schemas, and implementation notes.

What happens when PostgreSQL 16 is the application engine—not just a dumb row store? CRMS is a multi-tenant cloud infrastructure, quota, billing, and live Docker telemetry platform where authorization, concurrency control, and alerting live directly inside PostgreSQL.

The Problem

In most web applications, critical invariants—like tenant isolation, VM quota enforcement, temporal pricing windows, and audit logging—are scattered across application-layer if statements and ORM hooks. Under concurrent requests, check-then-act race conditions allow users to over-allocate cloud quotas, bypass permissions, or corrupt billing history.

The Outcome

A zero-ORM, database-first cloud control plane (PostgreSQL 16 + Raw SQL Express API + React + Docker Socket Collector) seeded with 1,000,000+ time-series metric rows, enforcing strict row-level security (RLS), lock-safe provisioning, sub-millisecond indexed analytics, and real-time LISTEN/NOTIFY SSE alerts.

How It Works (The Mechanism)

Database-Enforced Multi-Tenancy (RLS): Every API request opens a transaction with SELECT set_config('crms.user_id', $id, true). PostgreSQL Row-Level Security (RLS) policies and SECURITY DEFINER stored procedures enforce role hierarchy (admin, manager, developer, viewer) directly at the storage engine level.
Race-Condition-Proof Quota Provisioning: Proves and eliminates concurrent check-then-act quota over-allocation using SELECT ... FOR UPDATE row locking and savepoints inside sp_provision_vm, automatically retrying 40001/40P01 serialization failures.
1M+ Row Partitioning & Index Optimization: Uses declarative time-range partitioning, btree_gist temporal exclusion constraints (EXCLUDE USING gist to prevent overlapping billing/allocation windows), and BRIN + GIN indexes—cutting 1M-row analytical queries from 42 ms sequential scans down to 0.2 ms index scans.
Live Trigger → pg_notify → SSE Pipeline: A collector ingests real Docker container stats via a read-only docker-socket-proxy every 30s; threshold breaches fire a PostgreSQL trigger (fn_metric_alert) that emits pg_notify, streaming instant toast alerts to the React UI over Server-Sent Events (SSE).

Architecture & System Design

Key Engineering Decisions

Zero ORM (pg Driver + Named SQL Registry): Bypassed Prisma/TypeORM entirely so every query uses native PostgreSQL 16 features: recursive CTEs for role hierarchies, window functions, FILTER aggregates, materialized views (REFRESH CONCURRENTLY via pg_cron), and btree_gist range exclusion constraints.
Interactive DB Lab with Live EXPLAIN (ANALYZE, FORMAT JSON): Built an in-app query analyzer executing inside a READ ONLY transaction under the least-privilege crms_readonly role, rendering live query execution plans and proving index speedups (42 ms -> 0.2 ms) on 1M+ seeded rows.
Privilege-Separated Docker Telemetry: Never mounts /var/run/docker.sock into the application or collector container; instead routes through a read-only docker-socket-proxy (POST=0) and ingests metrics via a restricted crms_collector database role.

The Value Stack

Database Engine: PostgreSQL 16 (btree_gist, pg_cron, pg_stat_statements, pgcrypto, Declarative Partitioning, Row-Level Security, PL/pgSQL Triggers & Stored Procedures).
Backend & Collector: Node.js 20, Express, TypeScript, node-postgres (pg), Server-Sent Events (SSE), Vitest + Supertest.
Frontend: React, Vite, TypeScript, TanStack Query, Recharts, Tailwind CSS.
Infrastructure: Docker Compose, Nginx, docker-socket-proxy, GNU Make test harness.

The Proof

Seeded and benchmarked against 1,000,000+ time-series metric rows, reducing query execution time by 99.5% (42 ms to 0.2 ms) via BRIN, GIN, and partial B-Tree indexing.
Verified with an automated SQL & concurrency test suite covering RLS isolation, temporal exclusion constraints, deadlock/race prevention, and LISTEN/NOTIFY event streaming.