teesql-example-python is the connection ceremony between a customer's app running inside Intel TDX and a teesql cluster also inside Intel TDX, plus the read/write split + WebSocket fan-out shape that makes multi-browser real-time apps feasible on top of the cluster's primary/secondary topology.

If you just want to use the example, read README.md. This file is for people forking the example and adapting it.

   customer TEE              teesql cluster TEE              chain (Base)
   ─────────────             ──────────────────              ─────────────
   FastAPI app               leader (primary, writes)        TeeSqlClusterApp
                             secondaries (read-only)         dns-controller
   psycopg-ra-tls            sidecar (mutual RA-TLS)         (signs TXT)
     │
     ├── primary forwarder   postgres (replicated WAL)
     └── secondary forwarder
   broadcast.Hub
   WebSocket /ws/events

The customer's TEE and the cluster's TEE are different CVMs, possibly on different Phala nodes, possibly in different regions. The mutual-RA-TLS proxy on :5433 of every cluster member is the only ingress to postgres.

The customer doesn't know the leader's IP, hostname, or instance_id. It knows two things only:

• cluster_uuid — a 10-hex identifier baked into the cluster contract on Base mainnet (TeeSqlClusterApp.clusterId()).
• manifest_signer_address — the 20-byte ECDSA pubkey of the dns-controller CVM that watches the cluster on-chain.

The customer queries:

_teesql-leader.<cluster_uuid>.teesql.com  TXT

…and gets back something like:

"cluster=0x0679c94b…;
 epoch=1;
 leader_instance=aa85038a…;
 leader_url=https://65b61972…-5433s.dstack-base-prod2.phala.network;
 v=1;
 valid_until=…;
 sig=0x1a7303…"

The forwarder verifies sig is a valid ECDSA signature over everything before ;sig=, recovered to manifest_signer_address. A man-in-the-middle DNS server can't forge a leader_url because it doesn't have the manifest_signer's TEE-derived private key.

Once the forwarder has the leader URL, it opens a TLS connection. The server presents a cert chain rooted in the dstack KMS; the cert's quote extension contains the cluster sidecar's TDX quote.

• DcapVerifier (production default once the Python wheel is published): validates the TDX quote against Intel SGX Root CA via dcap-qvl. Reads mr_td, compose_hash, tcb_status. Refuses connection on revoked TCB or invalid quote.
• NoopVerifier (current Python default): trusts the cert chain blindly. Trust anchor is leg (1) — the manifest signer is trusted to only sign URLs of valid cluster members. Fine for operator scripts, NOT for production-facing customer apps. The Rust SDK (sqlx-ra-tls) and JS SDK (prisma-ra-tls) already ship DcapVerifier; Python is following.

The customer's forwarder presents its OWN TLS cert during the handshake. The cert is derived from the dstack guest agent (get_tls_key()), which embeds the customer CVM's TDX quote in an X.509 extension.

The cluster sidecar's proxy.rs does:

1. WebPkiClientVerifier against the dstack KMS root — validates the chain.
2. Local DCAP verification of the embedded quote — proves the customer is really running in TDX.
3. (Optional) app_id allowlist via OID 1.3.6.1.4.1.62397.1.3 — only if the cluster operator has restricted access to specific customer apps.

If any check fails, the sidecar drops the TCP connection before the postgres protocol starts.

The mutual-RA-TLS handshake is the most expensive thing in the critical path of any teesql query. It involves:

• A dstack-sdk.get_tls_key() call to mint a TDX-attested client cert (~50-200ms).
• The cluster's DCAP verification of that cert (~30-100ms server-side).
• The cluster's server cert chain validation by the customer.
• A DNS TXT lookup + signature verify (~30-200ms depending on resolver cache).

Doing that per query — or worse, per poll — would burn the dstack guest agent and double-digit-percent CPU at the cluster sidecar.

This example holds two long-lived psycopg.Connection objects for the entire lifetime of the FastAPI process:

# In server.py's lifespan:
app.state.primary_conn = await loop.run_in_executor(None, open_primary, settings)
poller = Poller(settings, hub)   # owns its own secondary_conn internally
await poller.start()             # opens it ONCE

Both connections are stored on app.state (or inside the Poller instance) and reused for every subsequent request / poll. The TLS session multiplexes an arbitrary number of postgres queries.

Only on psycopg.OperationalError:

• Primary (server.py::post_event): the request handler catches the error, calls _reopen_primary (close + open_primary again), retries the INSERT exactly once. If the second attempt also fails, the request returns 503 and the client retries.
• Secondary (poller.py::Poller._reconnect): the poll loop catches the error, closes the dead connection, calls open_secondary again, resumes. The last_seen cursor is preserved across reconnect — no events are replayed or lost.

This is the only path that pays a fresh handshake in steady state. In the failover case it's a single handshake per replaced connection, which is the cost of leader rotation.

The cluster enforces role grants on the postgres side. The primary role (teesql_readwrite) has INSERT, UPDATE, DELETE. The read-only role (teesql_read) has SELECT only. By using two connections with different roles, an accidental write through the polling task is rejected by postgres rather than getting through.

It's also a clean place to demonstrate the read/write split shape forks will want for any real app — analytics dashboards, change-data capture, cache fills, etc., all read from the secondary while the primary stays focused on writes.

src/teesql_example/poller.py owns the secondary connection and a single async task:

while True:
    rows = SELECT id, topic, payload, recorded_at
             FROM events WHERE id > :last_seen
             ORDER BY id ASC LIMIT 1000
    if rows:
        last_seen = rows[-1]['id']
        for row in rows: hub.publish(row)
    sleep(poll_interval_ms / 1000)

Default poll_interval_ms = 50. End-to-end latency from a POST /events to the WebSocket delivery is dominated by replication lag (typically 50-200ms on our cluster), not poll interval. Bump the interval to 100-250ms if you want lower DB load.

We deliberately did NOT use LISTEN/NOTIFY. Postgres NOTIFY events fired on the primary do not reach a LISTEN on a streaming replica — that's a vanilla-postgres limitation, not something teesql can fix. So either you LISTEN on the primary (and don't actually exercise the read path) or you poll. We chose to poll because it demonstrates the cluster's actual primary→secondary→client flow.

The migration creates an events_notify AFTER INSERT trigger anyway, so a fork that wants to LISTEN against the primary can drop the poller and replace it with an async LISTENer without a schema change.

src/teesql_example/broadcast.py is a small in-process fan-out:

• One publisher (the poller).
• N subscribers (each connected WebSocket gets its own bounded asyncio.Queue).
• Slow subscribers drop their oldest queued event rather than blocking the publisher. This matters: you do NOT want a slow browser to back up the read pipeline for everyone else.

For multi-process / multi-replica deployments (running this example behind a load balancer with N FastAPI replicas), you'd swap the in-process Hub for postgres LISTEN/NOTIFY against the primary, or a proper pub/sub (Redis, NATS). The current shape is the smallest useful thing that exercises the cluster's primary/secondary contract; N=1 process is the assumption.

A teesql cluster has one primary at a time. If the primary's CVM dies, a secondary takes over via the witness flow (a different secondary signs an "old leader is offline" attestation, the new primary submits claimLeader(witnesses) on chain, the dns-controller sees the new LeaderClaimed event, the TXT manifest updates).

The customer's forwarder doesn't poll the TXT record on every connection — that'd be a 3x latency hit per query. Instead:

1. Forwarder caches the leader URL after the first successful resolve.
2. When a connection fails (TLS handshake refused, TCP RST, etc.) the forwarder invalidates its cache and re-fetches the TXT record.
3. The customer's app sees a psycopg.OperationalError; the FastAPI app's retry-once contract kicks in, the poller's reconnect path kicks in, and both reconnect to the new leader on the next attempt.

The TTL on the TXT record is short (default 60s) precisely so a customer that didn't see a connection error still picks up the new leader within a minute.

• It does not implement connection pooling. Two long-lived connections is enough for one FastAPI process; horizontal scaling with M workers needs M*2 forwarders, which is fine for a few workers but not for an autoscaling fleet. A real fork would put a psycopg_pool.ConnectionPool behind each forwarder.
• It does not implement multiple readers. The poller is one task on one secondary connection. If you want N concurrent reads (e.g. per-tenant queries), open N secondary connections (each with its own forwarder) keyed by tenant.
• It does not handle key rotation. If the operator rotates the manifest signer (intentionally or via a TEE compromise recovery), every customer must update their manifest_signer_address config. This is a one-time operational task, not something the SDK can do silently.
• It does not implement DCAP itself. NoopVerifier is the current default. Switch to DcapVerifier when the wheel ships in ra-tls-verify.

• https://github.com/TeeSQL/dstackgres — cluster operator's repo. Source for the sidecar, the dns-controller, and the cluster bring-up procedure.
• https://github.com/TeeSQL/psycopg-ra-tls — the SDK this example depends on. v0.3.0+ ships the localhost forwarder.
• https://github.com/TeeSQL/ra-tls-verify — verifier primitives. NoopVerifier and (eventually) DcapVerifier.
• https://github.com/Phala-Network/dcap-qvl — the Rust crate that becomes the Python DcapVerifier wheel.
• https://github.com/Dstack-TEE/dstack — the TEE platform underneath everything.