javohir.dev
KOENUZ
All projectsLive

PG Insight

An open multi-tenant monitoring service: anyone signs up, adds their own PostgreSQL and sees only the databases they added

Open live service: pginsight.javohir.devGitHub code

What this project proves

  • Tenant isolation: a global guard checks the owner on every :targetId route and returns 404, not 403, for someone else's target; adding an unguarded mutating route fails a test
  • SSRF protection: every IP in the DNS answer is checked and the connection is pinned to the verified IP (keeping TLS SNI); production rejected platform-db, 169.254.169.254 and 127.0.0.1
  • Limits on arbitrary SQL: EXPLAIN goes through six layers of checks and runs READ ONLY with a 5 s statement_timeout; pg_sleep was cut off with 400 in 0.05 s and generate_series(1, 10000000000) in 5.4 s
  • I measured the load on monitored databases: 0.06% of one core over 10 minutes, +0.3% container CPU
  • Operations: SHA-tagged deploys, recorded only after a health check, and a two-way rollback drill
Role
Solo: design, backend, frontend, security, tests, deployment and operations (81 commits)
Period
2026.08 – 2026.10
Stack
  • NestJS
  • TypeScript
  • Prisma
  • PostgreSQL
  • TimescaleDB
  • Socket.IO
  • React
  • Vite
  • Tailwind CSS
  • Recharts
  • Jest
  • Vitest
  • Docker
  • Caddy

Key numbers

ItemValueTypeEvidence
Queries sent to the target in 10 minutes2,430MeasuredView evidence
Load on the target (share of one CPU core)0.06 %MeasuredView evidence
Average query execution time0.15 msMeasuredView evidence
Collected data lag1.4 sMeasuredView evidence
Time to block pg_sleep inside EXPLAIN0.05 sMeasuredView evidence
Automated tests (188 backend + 49 frontend)237MeasuredView evidence

The problem

Slow queries, lock waits, bloat and replication lag in a production PostgreSQL have to be checked separately across tools and hand-written SQL. I brought them into one service, and because it is a public service that connects to other people's databases, tenant isolation, SSRF protection and limits on arbitrary SQL had to be there from the start.

Architecture

Collectors gather metrics from each database every 3 seconds to 5 minutes and write them to TimescaleDB (10 hypertables), while live pages query the target directly. Users, targets, alerts and audit logs live in a platform DB (PostgreSQL 16) managed with Prisma 7. Global NestJS 11 guards (Throttler → JWT → TargetAccess) check ownership on every :targetId route, and target queries run as raw SQL on a per-target pool (max 5). There are two roles, admin (the first account) and user (up to 3 own targets, public addresses only), and the UI is in Korean, English and Uzbek.

Data model

Key constraints and indexes

  • users.role DEFAULT 'user', bcrypt 12
  • refresh_tokens.tokenHash UNIQUE: SHA-256 해시만 저장, 사용할 때마다 교체
  • targets.passwordEncrypted: AES-256-GCM, createdByUserId = 테넌트 소유자, sslMode DEFAULT 'require'
  • hypertable 10개, 1일 chunk; connection_metrics, query_metrics는 7일 뒤 압축, retention 7–30일
  • audit_logs: 로그인, 가입, 대상 변경, 모든 EXPLAIN 기록

Key design decisions

An open multi-tenant service, not a demo

Context
Anyone should be able to monitor their own database.
Decision
Open sign-up, with each user seeing only the targets they registered.
Trade-off
Tenant isolation, SSRF protection, quotas and rate limits became mandatory.

Ownership checks in a global guard, not in controllers

Context
A new route must not be able to skip the check.
Decision
Global guards in the order Throttler → JWT → TargetAccess check every :targetId route.
Trade-off
Routes keyed by other IDs need the @TargetAccess decorator.

404, not 403, for someone else's target

Context
Even the existence of a target should not be revealed.
Decision
Without permission the response is the same 404 as for a target that does not exist.
Trade-off
Existence has to be checked separately for admins too.

Resolve the host and pin to the IP

Context
If DNS answers differently at check time and connect time (DNS rebinding), an attacker can reach internal hosts.
Decision
Every IP in the DNS answer is checked with net.BlockList, and the connection goes straight to the verified IP.
Trade-off
TLS SNI has to be passed by hand, and self-hosting needs the TARGET_ALLOW_PRIVATE_HOSTS option.

Keep EXPLAIN, but READ ONLY + 5 s + a function denylist

Context
Owners should see plans for their own databases, but EXPLAIN ANALYZE actually executes the query.
Decision
After six layers of checks it runs in a READ ONLY transaction with statement_timeout and lock_timeout.
Trade-off
Time and function limits can reject some legitimate queries.

New targets default to sslmode=require

Context
The pg driver has no libpq-style prefer (TLS, falling back to plain), so the default prefer was in practice an unencrypted connection.
Decision
Switched new targets and the database default to require (47287b4).
Trade-off
For a database without TLS the user has to choose disable explicitly.

Migrations are never rolled back

Context
A rollback that only swaps the image is fast and safe.
Decision
Every migration is additive (add first, clean up later).
Trade-off
Every migration has to stay compatible with the previous code.

Before and after

Beforerestart → 0 active targets, no data 14:23–17:25 UTC
Afterrestart → 2 active targets, 2,430 queries / 10 minView evidence
What changed
The collection orchestrator started in onModuleInit before the pools connected; it now starts in onApplicationBootstrap, with a lifecycle test that checks the restart order (6246ce2). In the same round I fixed the lock query RENAME error, metric timestamps, paused state, the TLS default and /health returning 500 instead of 503.

Incident write-up

Symptom
After an API restart no target was monitored; the overhead measurement showed 0 queries from PG Insight.
Root cause
The log showed Starting collection for 0 active target(s): the orchestrator started in onModuleInit before the pools had connected, and data from 14:23 to 17:25 UTC was lost.
Fix
Moved startup to onApplicationBootstrap (6246ce2); both targets are collected right after a restart.
Prevention
Added a lifecycle test that reproduces the bug and also checks that paused targets are skipped; the new tests were confirmed to fail on the old code.

Tests and quality

Tests
237
Coverage
—
CI
GitHub Actions (워크플로 준비, billing 문제로 현재 중단)
Test tools
Jest, Supertest, Vitest, 실제 PostgreSQL 16

Deployment and operations

Deployment
iwinv VPS on Docker Compose: api (NestJS, non-root), web (Caddy with the React static files, proxying /api/* and /socket.io/*), platform-db (PostgreSQL 16) and metrics-db (TimescaleDB). Database ports are not exposed. deploy.sh: conflict-marker check → git merge --ff-only → build api and web with SHA tags → wait for health 45 × 2 s → record APP_TAG and .deploy-history only on success. The API container runs prisma migrate deploy on startup.
Backups
The server-wide cron backup (daily at 03:00, kept 7 days) includes platform-db and metrics-db. pg_dump backups of user databases can only be created by an admin.
Rollback
rollback.sh recreates api and web from existing images without a build, checking first that both images exist. Tested in production in both directions (71dceaf ↔ d587ce6), with health 200 both times.
Monitoring
In real use: the LedgerCore and FlashDrop production databases are registered and monitored (internal Docker network on the same server). /api/v1/health (503 when the database is down), audit logs (login, sign-up, target changes, every EXPLAIN), sign-up limited to 5 per hour per IP and login to 10 per minute. GitHub private vulnerability reporting (SECURITY.md)

Screenshots

Dashboard monitoring the LedgerCore production database: connections, pool usage, slow queries, lock waits and cache hit ratio
Connections view: active sessions, wait events, durations and connections per application (LedgerCore DB)

Limits and retrospective

Current limits

  • No account management yet: email verification, password reset and account deletion are missing
  • Diagnostics, security check results, recommendations and error messages come from the backend in English only (the UI itself is in three languages)
  • The connection_by_app hypertable has no retention and grows without bound, and the metrics DB has no migration mechanism (init-platform.sql only runs when the volume is first created)
  • The real-time socket is protected by JWT and ownership, but the UI does not call subscribeToTarget yet and refreshes by REST polling
  • Monitoring queries are not optimized yet: pg_settings is re-read every 10 s and each target has a pool of 5; caching and a 2–3 connection pool are next
  • query_metrics storage (about 4 MB per target per hour) is an estimate from a 22-minute window; the 24-hour measurement is still pending
  • The two existing targets (LedgerCore, FlashDrop) use prefer, meaning unencrypted connections, on the internal Docker network (the traffic never leaves the server)
  • The CI workflow is ready but stopped by a GitHub Actions billing issue, so tests run locally. The key rotation script (rotate-encryption-key.ts) predates the Prisma 7 adapter and is untested in production, and there are no Playwright E2E tests

Key code

Current status and next steps

Collection load on a monitored database was 2,430 queries and 353.4 ms of total execution in 10 minutes, about 0.06% of one CPU core. I found 5 bugs on real production databases (LedgerCore, FlashDrop) and 3 more in code review and tests, and reproduced each one with a test before fixing it. Other cases: a real-time broadcast error that stopped the collectors (b9825e4), a RENAME syntax error in the lock query (b9825e4), connection metrics stamped with the session start time (d587ce6, 891 wrong rows deleted), paused targets turning themselves back on after a restart (41569c7), SSL settings lost on a password change and prefer not actually encrypting (41569c7, 47287b4), and a first rollback drill where the script stopped safely because the old image was missing.