PostgreSQL
PostgreSQL is the database underneath Connect, and the choice is load-bearing rather than incidental: workspace isolation is enforced in the database itself with row-level security, uniqueness constraints do the work that careful application code would otherwise have to do, and every record in the product is a row that can be constrained, indexed and audited.
What relational buys here#
Three things, and they are the reason the choice was not close. Constraints let the database refuse bad data rather than trusting every code path that writes it. Transactions let several related changes happen or not happen together, which matters when a message, a relationship and an audit entry all describe one event. And declarative access control lets the isolation rule live below the application, where a forgotten filter cannot bypass it.
The third is the decisive one for a product where one deployment holds many businesses' mail. Isolation that is only ever a WHERE clause is isolation that lasts until somebody writes a query without it.
The features this system leans on#
- Row-level security
- Policies on the table itself, forced so that even the table's owner is subject to them. This is the third of three isolation layers and the only one that survives an application bug.
- Partial unique indexes
- A uniqueness rule that applies to a subset of rows. Provider webhook nonces are stored under one, which turns replay protection into something the database refuses rather than something code has to remember to check.
- Transactional schema change
- Migrations that either apply completely or not at all, which is what makes running them at every boot safe rather than reckless.
- Multi-version concurrency
- Readers do not block writers and writers do not block readers, so a long report does not stall the engine that is answering a customer.
- Expressive indexing and constraints
- Foreign keys, check constraints, and indexes matched to the actual query shapes rather than added uniformly.
Does Connect use PostgreSQL?#
Used, as the only production database. Row-level security is forced on every scoped table, and workspace isolation is enforced three separate times: an allowlist in the middleware refuses a customer session calling an operator path, the ORM kernel filters every query by workspace, and the database applies its own policies underneath both.
The schema is rooted at workspaces. Nearly every table carries a workspace_id and is filtered automatically; a short list of control-plane tables — platform identity, sessions, billing, and website enquiries addressed to the operator — is the deliberate exception, and the exception is enumerated rather than assumed.
The costs you inherit with it#
The database is not the part of a system that goes wrong loudly. It goes wrong as a slow page, a growing table, or a query count that rises with the size of the customer rather than the size of the screen.
- Statement count is the metric that matters, not wall-clock on a laptop. Measured here: an operator's customer list went from 5,574 statements to 3; a triage strip and the workspace console went from 83 to 11 and from 89 to 17, flat with page size; the home screen went from 133 to 15, also flat.
- Work pushed into the application scales badly. A conversation list that read 400 rows and then counted, filtered, searched and paged in Python inside that slice reported an empty page five and a zero count on a 430-conversation workspace.
- A sample is not an aggregate. A summary derived from the newest 500 rows reported 500 organisations where there were 530, and none already contacted where there were 40.
- Dead rows accumulate. Multi-version concurrency means updates leave versions behind, and cleanup is held back by long-running transactions.
Operational facts worth knowing#
Schema migrations run at every boot, so a deploy that starts is a deploy whose schema is current. Security policies are applied by a separate operational script rather than by the application, which is a deliberate split: the application should not be able to weaken its own isolation.
The database password is not held in the application's configuration file. The managed database service owns the master credential and rotates it into a secret store; configuration names the secret, and boot reads the password from there. A stale copy of a rotated password in a configuration file once made the *next* restart authenticate as nobody, which read convincingly as a bad deploy — see secret management.
Questions#
Why not a document database for message data?
Because the hard requirements here are relational ones: isolation enforced below the application, referential integrity between people, conversations and commitments, and uniqueness constraints that make duplicate provider events impossible rather than unlikely. Message bodies are the easy part of the problem.
How is one workspace kept out of another's rows?
Three times over: the middleware allowlist, the ORM workspace kernel, and row-level security in the database. The layers are independent, so a mistake in one is caught by the others. Row-level security is the one that holds when a query is written without a filter.
Does the application create its own security policies?
No. Tables are created at boot; policies come from a separate operational hardening step. The separation is intentional — an application that could grant itself broader access would be a weaker boundary than one that cannot.