SteadyPG: Building a Self-Service Managed PostgreSQL Platform

SteadyPG is a self-service control plane for creating, operating, monitoring, backing up, and scaling managed PostgreSQL databases. I built it to explore what sits behind the deceptively simple promise of “create a database”: infrastructure orchestration, secure credential handling, regional placement, backups, observability, usage-based capacity, subscription billing, customer communication, and the operational tooling needed to run the platform itself.

The result is a working full-stack MVP that can provision isolated PostgreSQL topologies on local Docker infrastructure or remote Linode hosts. Customers can choose a plan, create a database, connect through stable read and write endpoints, add shards or replicas, place read replicas in other regions, configure network access, schedule backups, restore data, inspect metrics, rotate credentials, and review an audit trail from one account dashboard.

Just as importantly, the project documents the line between a convincing prototype and a genuinely production-ready database service. Features such as automated primary failover, tenant database TLS, point-in-time recovery, and provider-level resource enforcement are treated as explicit future engineering projects rather than being hidden behind optimistic marketing language.

Project overview

Area	Implementation
Product	Self-service managed PostgreSQL control plane
Front end	React, TypeScript, Vite, nginx
API	Node.js, Express, TypeScript
Control database	PostgreSQL
Data plane	Docker, HAProxy, PostgreSQL 16/17
Regional infrastructure	Linode hosts, SSH, WireGuard
Backups	PostgreSQL custom-format dumps, MinIO/S3
Billing	Stripe subscriptions and prorated add-ons
Email	SMTP transactional email through AhaSend
Analytics	Privacy-conscious OpenPanel event tracking
Deployment	Docker Compose and Dokploy

The problem I wanted to solve

Provisioning PostgreSQL manually is straightforward when there is one database and one operator. The difficulty increases quickly when the experience needs to be safe, repeatable, billable, observable, and understandable to customers.

A managed database product has to answer a much larger set of questions:

• How does a customer request infrastructure without receiving access to the underlying hosts?
• How are failed or interrupted operations retried without creating duplicate containers, ports, or volumes?
• How are credentials protected inside the control plane?
• How can reads be distributed across replicas while writes always reach the primary?
• How does a customer add capacity, and when should billing happen relative to the infrastructure change?
• How are backups scheduled, retained, stored away from the database host, and restored safely?
• How can a customer restrict database access to specific IPv4 or IPv6 networks?
• What does the customer need to see when storage, transfer, replication, or host capacity approaches a limit?
• What does the platform operator need to see across every account, database, node, and background job?

SteadyPG was designed as an answer to that complete workflow rather than as a thin interface over docker run.

Product goals

I established several principles early in the build:

1. The customer experience should be self-service. Common database operations should not require a support ticket or direct server access.
2. Infrastructure work should be asynchronous and durable. A browser request should create an operation, not remain open while containers and replicas are built.
3. Billing and infrastructure state should agree. Paid capacity must not be applied until payment succeeds, and a failed payment must leave the existing topology unchanged.
4. Regional placement should preserve operator control. Customers choose a region, while the scheduler chooses a healthy physical host with available capacity.
5. Operational state should be visible. Customers and administrators need useful status, progress, metrics, and history rather than an unexplained spinner.
6. The product boundary should be honest. Replication is not the same as automated high availability, and measured allowances are not hard quotas until the data plane enforces them.

Designing the architecture

The most important architectural decision was to separate the public control plane from privileged data-plane work.

The control plane contains the React application, an Express API, and a PostgreSQL metadata database. It owns user accounts, sessions, subscriptions, database specifications, encrypted credentials, billing records, audit events, backup metadata, infrastructure inventory, and the durable job queue.

The provisioner is a separate worker. It is the only application component allowed to communicate with Docker engines or use the deployment SSH key. This reduces the privileged surface of the public API and creates a clear boundary between recording a requested state and changing real infrastructure.

The high-level request flow is:

1. The customer submits an action in the dashboard.
2. The API validates account ownership, plan limits, billing state, and the requested configuration.
3. The API stores the target state and creates a durable job.
4. The provisioner claims the job from PostgreSQL.
5. The worker reconciles Docker resources on the appropriate local or remote hosts.
6. Progress and errors are written back to the control database.
7. The interface polls the API and turns that state into a visible provisioning or maintenance experience.

Jobs are claimed using FOR UPDATE SKIP LOCKED, allowing multiple workers to process independent operations without claiming the same row. Provisioning jobs retry with backoff, while containers and volumes carry ownership labels and networks use deterministic names. That metadata makes resource discovery and cleanup predictable and makes retries far safer than relying only on container names.

Provisioning an isolated PostgreSQL topology

Each logical database can contain multiple application-managed shards. For every shard, SteadyPG provisions:

• a dedicated Docker network;
• a PostgreSQL primary;
• the selected number of streaming read replicas;
• a HAProxy router with a write path fixed to the primary;
• a read path balanced across healthy replicas;
• labelled persistent volumes for controlled discovery and deletion;
• distinct application and replication credentials.

Customers select PostgreSQL 16 or 17 when they create a database. The major version is stored as immutable topology metadata and is resolved through a configurable image template, ensuring that a primary and its replicas cannot accidentally use different major releases. An in-place major upgrade is deliberately not presented as a simple setting because a real upgrade requires a tested pg_upgrade or dump-and-restore workflow.

Docker assigns ports locally. Remote hosts allocate ports transactionally from an administrator-defined range, preventing two databases from receiving the same public port. The connection details returned to the customer use the host’s configured public address rather than exposing control-plane or private infrastructure details.

The current sharding model is intentionally explicit. SteadyPG returns read and write URLs for each shard and records the chosen shard key, but the application remains responsible for hashing that key and selecting an endpoint. The platform does not pretend to transparently parse queries or move rows between shards. Removing a shard is restricted to the highest-numbered shard and requires password confirmation, giving the customer a clear opportunity to evacuate its data first.

Multi-region replicas without exposing infrastructure choices

One of the more substantial additions was regional read-replica placement. A customer may keep a primary in EU West and request a read replica in US East, but they select the region rather than an individual server.

This distinction is important. Customers get control over data locality, while the platform retains the ability to schedule around capacity and unhealthy hosts. SteadyPG maintains a host inventory containing region, health, public address, port range, database capacity, bootstrap state, and current telemetry. The scheduler chooses a healthy host with available capacity and favours the least-used eligible machine.

Remote Docker engines are accessed over pinned, key-only SSH using docker system dial-stdio. The platform does not expose Docker’s TCP API. Administrators obtain the server’s ED25519 fingerprint through a trusted console and store that fingerprint with the host record, preventing the provisioner from silently trusting an unexpected machine.

Cross-region PostgreSQL replication travels through a SteadyPG-managed WireGuard mesh. Each host generates and retains its private key locally; the control plane stores public keys and overlay addresses. A remote region becomes selectable only when recent telemetry shows that the mesh is healthy. Regional replicas receive their own read-only, allowlist-protected endpoint.

This provides read locality and an off-host standby copy, but it is still asynchronous replication. WAN latency and lag remain possible, the write endpoint does not move automatically, and the replica is not promoted if the primary fails. SteadyPG communicates that boundary clearly because automated failover requires consensus, fencing, leader election, and tested endpoint reconfiguration—not merely another PostgreSQL container.

Building backups as an operational workflow

Backups evolved from a manual database action into a complete per-database workflow.

A customer can create an on-demand logical backup or enable an automated rolling schedule. Available frequencies include hourly, every two hours, every six hours, every twelve hours, daily, and weekly schedules. The customer also chooses how many recent archives to retain.

The worker runs a version-matched pg_dump in custom format for every shard primary. Dumps are streamed into an archive and uploaded to a private MinIO or S3-compatible bucket using the AWS SDK. Each archive contains a manifest describing the shards and the corresponding restore commands. This keeps durable backup data outside the database volume and avoids treating a Docker volume copy as the only recovery method.

Backup storage is measured per database. Completed archives count against the purchased allowance, while global retention days, maximum archive count, and individual archive size provide additional operational safeguards. Old archives are removed as the rolling policy advances.

Restoring is handled as another asynchronous job. The account interface reports overall progress and per-shard progress rather than leaving the customer to wonder whether a long-running restore has stalled. Backup creation, scheduling, retention, download, deletion, and restore all share the same ownership and status model.

Logical backups provide a practical recovery path for the MVP, but they are not represented as point-in-time recovery. Continuous WAL archiving and restore-to-a-specific-timestamp remain separate roadmap work.

Designing billing around infrastructure changes

Billing is closely tied to topology, so I treated it as part of the domain model rather than adding it after the infrastructure was complete.

The public pricing experience offers two clear plans—Starter and Scale—alongside a table showing the unit cost of additional capacity. Prices are read from the API at runtime and are configured through environment variables, allowing plan and add-on rates to change without editing the front end.

Each database records its current allowance for:

• shards;
• read replicas per shard;
• database storage;
• backup storage;
• monthly transfer.

The account area shows an estimated current monthly total, the base subscription, per-database add-on costs, and a unit-by-unit breakdown. This made it possible to explain numbers that would otherwise appear as an unexplained account-level total.

When a customer increases capacity, the API first calculates the difference from the plan’s included resources and asks Stripe for an exact proration preview. The confirmation screen separates the amount due now, the recurring monthly increase, and the account’s new total add-on cost.

If confirmed, Stripe immediately invoices the prorated difference. The API uses payment behaviour that fails the request when payment cannot be completed. Only after successful payment does the database transaction update its target capacity and queue infrastructure work. If the card is declined, the database remains unchanged.

Recurring Stripe products and prices for add-ons are created from the configured rates when first needed, then cached in the control database and reused. Quantities are applied as absolute account totals, which avoids drift when several databases contribute to the same add-on type.

The customer interface currently permits safe capacity increases. Destructive reductions remain explicit operations, such as evacuating and removing the final shard, rather than being hidden behind a lower number in a generic configurator.

Network access controls

Every database includes a Networking tab for managing IPv4 and IPv6 CIDR allowlists. Rules are stored as database-owned records and applied to the customer-facing routers. Customers can add a single address with a /32 or /128 mask or authorise an intentional network range.

The “Use my IP” experience needed special attention because an application behind Docker and multiple reverse proxies often sees a bridge or proxy address rather than the browser’s actual internet address. The interface resolves the browser’s public IPv4 address directly, while the API uses an explicitly configured trusted-proxy hop count for server-side address handling. The site’s Content Security Policy permits only the required lookup and analytics endpoints.

For production, these router rules should be paired with provider firewall policy or a dedicated database proxy. Defence in depth matters because Docker-published ports and host firewall behaviour can be surprisingly easy to misconfigure.

Metrics for customers and platform operators

The metrics system has two audiences with different needs.

For customers, the provisioner samples PostgreSQL and container statistics and stores time-series data in the control database. The dashboard exposes selectable time ranges and reports:

• total and active connections;
• transaction rate;
• cache hit ratio;
• database storage used against the purchased limit;
• monthly transfer used against the purchased allowance;
• CPU and memory utilisation;
• healthy streaming replica count;
• replication lag in bytes.

Database storage is derived from PostgreSQL rather than guessed from the container filesystem. Monthly transfer is estimated from restart-aware network counters on customer-facing HAProxy routers. Counters are persisted, converted into deltas, and accumulated into UTC calendar-month totals. Starting with a baseline prevents a worker restart or container replacement from turning a lifetime byte counter into a sudden usage spike. Public read and write traffic is counted, while internal replication and backup traffic is excluded.

Both storage and transfer use progress indicators with remaining capacity and clear warning states. Transactional emails are deduplicated and sent as a database approaches the configured warning percentage and again when it reaches its allowance. The Capacity tab is linked directly from the metric so the customer can act on the warning.

For platform administrators, the dashboard provides a fleet-wide overview of accounts, subscriptions, databases, shards, PostgreSQL nodes, jobs, backups, and pending capacity operations. Each data-plane host reports CPU, load averages, memory, swap, root disk, inode use, Docker disk consumption, image and container counts, network counters, uptime, kernel and architecture details, database-slot utilisation, PostgreSQL node count, and WireGuard mesh state. Administrators can disable scheduling, rerun a bootstrap job, inspect its streamed output, or remove an unused host.

This distinction turned observability into a product feature rather than a collection of server logs. A customer needs to understand their database; a platform operator needs to understand the fleet and the automation acting upon it.

Routine operations and auditability

SteadyPG includes guarded workflows for the maintenance tasks that recur after provisioning.

A rolling restart processes replicas before the primary and waits for PostgreSQL readiness between steps. Credential rotation changes the customer application role while leaving the internal replication credential separate. Secrets stored in the control database are encrypted with AES-256-GCM using a dedicated control-plane key, while account passwords are hashed with bcrypt.

Customer-visible actions are appended to an operational audit trail. Database creation, capacity changes, networking changes, backup activity, restore requests, restarts, credential rotation, shard changes, and deletion can be reviewed from the database or workspace activity views. This gives the workspace a consistent account of what changed and when.

Transactional communication and product analytics

An infrastructure product should not require the customer to keep a browser tab open to know that something important happened.

SteadyPG sends transactional email through an authenticated SMTP relay. The AhaSend integration covers account verification, plan activation, database creation, paid capacity changes, and storage or transfer warnings. SMTP settings remain environment-driven, and local development can run without email verification when the relay is intentionally unconfigured.

OpenPanel provides product analytics for registrations, searches, page views, important buttons, and key database, backup, networking, billing, and maintenance actions. The tracking design intentionally excludes passwords, connection URLs, database names, email addresses, and allowlisted IP ranges. Browser ingestion uses only the public project identifier; no analytics secret is shipped in the client bundle.

This made analytics useful for understanding the product journey without turning operationally sensitive database data into tracking payloads.

Product and interface design

The interface had to make a technically dense system approachable without removing the details customers need to make safe decisions.

The public site explains the service, features, metrics, pricing, and capacity extras. The pricing page deliberately keeps the initial choice to Starter or Scale, then presents add-on rates in a comparison table instead of forcing prospective customers through a complex calculator.

Once signed in, the workspace moves from account overview to database-specific tabs: Overview, Metrics, Capacity, Networking, Backups, Operations, and Activity. Long-running actions show states and progress. Destructive actions use explicit confirmation. Billing previews explain immediate and recurring effects before the customer commits. Empty, provisioning, error, and ready states are all treated as part of the product rather than edge cases.

The application is responsive, built with reusable React components, and served through nginx. Public plan data is runtime-driven, while Vite build-time settings are reserved for genuinely browser-side configuration such as the OpenPanel client ID.

Key engineering challenges

Keeping payment and topology changes consistent

The hardest part of adding paid capacity was sequencing two systems that cannot share a database transaction. Charging too late could create unpaid infrastructure; charging too early without careful failure handling could leave the customer billed for capacity that was never requested.

The solution was to preview first, require successful immediate payment, commit the target capacity second, and let a durable job reconcile the infrastructure third. This separates payment failure from provisioning failure and gives each one a visible recovery path.

Making asynchronous operations understandable

Provisioning, restoring, scaling, and host bootstrapping can take far longer than an HTTP request. Moving them to a job queue solved the server-side timeout problem but created a product-design problem: users still need to know what is happening.

Persisted statuses, progress fields, per-shard restore updates, bootstrap logs, retry states, polling, and audit events became the shared language between the worker and the interface.

Supporting remote infrastructure safely

Remote provisioning can easily become an unauthenticated Docker socket or a collection of shell scripts with weak trust assumptions. SteadyPG instead uses dedicated SSH credentials, pinned server fingerprints, Docker’s SSH transport, bounded port allocation, and a worker-only private key. The Dokploy control-plane deployment does not mount the local Docker socket at all.

Metering traffic without double-counting internal data

Container network counters include more than a simple “customer bytes” number. Traffic through a router has an external and backend leg, counters reset when containers are replaced, and replication should not consume the customer’s application-transfer allowance.

The implemented meter observes only customer-facing routers, persists the last container identity and counters, handles resets, and compensates for the proxy’s two network legs before accumulating a monthly total. The dashboard labels the result as an estimate because provider-grade accounting ultimately belongs at the network edge.

Staying honest about high availability

It is tempting to describe a primary with replicas as highly available. In reality, availability depends on automatic failure detection, consensus, fencing, promotion, and endpoint movement. SteadyPG exposes replica health and lag but describes the current system as replicated rather than automatically failed over. This is both a technical and product decision: accurate expectations are a feature.

Security decisions

Security was considered across the control plane and data plane:

• account passwords are hashed with bcrypt;
• sessions use signed, HTTP-only cookies;
• managed database credentials are encrypted with AES-256-GCM;
• application and replication credentials are separate;
• remote host identities are pinned before SSH authentication;
• the Docker TCP API is not exposed;
• host WireGuard private keys remain on their hosts;
• backup credentials can be restricted to a private bucket;
• API operations are checked against database ownership;
• administrator endpoints require an administrative account;
• analytics avoids customer secrets and identifying database data;
• important changes create an audit record.

The remaining security work is also documented: tenant PostgreSQL TLS, MFA, password reset, distributed rate limiting, provider-level firewall enforcement, a narrower alternative to Docker socket access, independent alert export, and compliance-grade audit retention are required before a public production launch.

Development and validation approach

The product was built in vertical increments so that each milestone delivered a complete customer workflow rather than an isolated backend capability.

The early work established registration, plans, database provisioning, and the marketing and account experience. The next iterations added durable operations, metrics, PostgreSQL version selection, sharding controls, and backups. Billing then expanded from base subscriptions into environment-configured unit pricing and immediate proration. Later work added OpenPanel analytics, SMTP communication, fleet administration, regional replicas, WireGuard health, storage reporting, and monthly transfer metering.

The repository is a TypeScript workspace with independent API and web builds. Validation includes TypeScript checking, Vitest suites for core API and interface behaviour, Docker image builds, migration checks, and live Compose smoke tests. Operational documentation covers local development, production boundaries, Dokploy deployment, Stripe configuration, MinIO/S3 backups, Linode host setup, and the path from the current MVP to a more mature service.

The result

SteadyPG demonstrates an end-to-end managed database experience rather than a static prototype. A user can move from registration and plan selection to a running replicated PostgreSQL database, then operate that database through the same product:

• provision PostgreSQL 16 or 17;
• connect through read and write endpoints;
• add paid shards, replicas, storage, backup capacity, or transfer allowance;
• place new read replicas in another configured region;
• restrict access by IPv4 or IPv6 CIDR;
• create and schedule S3-backed logical backups;
• monitor database health, storage, and monthly traffic;
• restore an archive with visible progress;
• rotate credentials or run a rolling restart;
• inspect billing and operational history;
• receive transactional confirmations and capacity warnings.

Administrators get a corresponding fleet view covering infrastructure health, host capacity, Docker usage, automation, backups, billing operations, and WireGuard connectivity.

The project also produced something less visible but equally valuable: a much clearer understanding of where a database control plane ends and where serious distributed-systems engineering begins.

What I would build next

The next phase would focus on making the existing workflows production-safe before adding more surface area:

1. Add TLS for every tenant PostgreSQL connection, including certificate issuance and rotation.
2. Enforce storage and transfer allowances at the provider or data-plane layer rather than reporting them only as measured commercial allocations.
3. Add continuous WAL archiving and point-in-time recovery, including restore-to-new-database workflows.
4. Move network policy into a dedicated proxy or provider firewall for defence in depth.
5. Export alerts to an independent monitoring system so the SteadyPG dashboard is not the only outage signal.
6. Introduce consensus-backed leader election, automated failover, fencing, controlled switchover, and host evacuation.
7. Validate a pinned, multi-architecture PgBouncer build against prepared statements, credential rotation, restores, rolling restarts, and remote upgrades before releasing transaction pooling.
8. Add MFA, password reset, teams, roles, API tokens, a CLI, Terraform support, and compliance exports.
9. Explore schema and data branches only after backup, recovery, and failure handling have been proven.

Closing reflection

SteadyPG began as an idea for a simpler PostgreSQL hosting experience, but building it revealed that the visible database is only one part of the product. The real work lies in coordinating identity, billing, durable automation, networking, backup storage, regional infrastructure, telemetry, communication, security, and recovery—then presenting those systems in a way customers can understand and trust.

That combination of product design, full-stack engineering, infrastructure automation, and honest operational boundaries is what made SteadyPG such a valuable project to build.