Inside UPG

How UPG is built: the processes, the pipeline from a provider webhook to a tenant callback, the data model, and the database rules that keep money correct. Read it before you change UPG itself.

How it works

One image, three roles, chosen by SERVICE_ROLE: api (Fastify), worker (apply loop and delivery outbox), sweeper (scheduled jobs). Migrations and bootstrap run as one-off tasks from the same image.

The queue is Postgres, not SQS. Deliveries are rows claimed with FOR UPDATE SKIP LOCKED. That gives a transactional outbox, an audit trail and replay for free. Revisit above about 5 million deliveries a day.

The ingest rules

These four are written at the top of the ingest route, because each was learned the hard way:

1. Always answer 200, even for a bad signature or an unknown connection. A 4xx tells an attacker what is valid and makes the provider retry in a storm.
2. Answer fast. Store first, apply in the worker.
3. Never IP-allowlist provider webhooks. Their ranges change.
4. No rate limit on webhooks. WAF body rules are in count mode on /webhooks/*, because Apple and Google JWS payloads and Stripe bodies trip them.

Data model

Area	Tables
Tenancy	tenants, apps (environment lives here), api_keys (argon2id hash, shown once), connections, rail_credentials (encrypted), endpoints (callback URL, current and previous secret)
Catalog	plans (stable plan_code, entitlement strings, period, kind, seats), plan_rail_mappings (product id, base plan, offer per connection), plan_prices (versioned, per country), plan_groups, plan_addons
State	subscriptions, subscription_periods (append-only ledger), entitlement_states (materialised), subscription_state_transitions, subscription_members, family_invites
Events	provider_events (partitioned monthly, plus a small unpartitioned dedupe registry), events (immutable; payload is the exact delivered bytes), deliveries
Ops	financial_records (gross, fee, tax, net, FX), manual_actions, credential_access_log, reconciliation_runs, idempotency_keys, auth_audit

The subscription key per rail, rail_subscription_id:

Rail	Key
Apple	originalTransactionId
Google	The first purchase token of the chain
Stripe	sub_…, or the charge for one-time purchases
Gateways	Our own checkout id

subscription_periods is the table that cannot be backfilled. If a period is not recorded when it happens, the money history is gone. Treat it as append-only.

Database rules

Rule

Money is bigint minor units plus a char(3) currency. Financial rows are append-only. Ordering and idempotency are database constraints, not application checks.

Rule

Row-level security with FORCE ROW LEVEL SECURITY, and SET LOCAL app.tenant_id inside each transaction. Without FORCE the table owner bypasses RLS. SET instead of SET LOCAL leaks the tenant to the next user of a pooled connection.

Rule

Two database roles: the app role is subject to RLS (NOBYPASSRLS); the migrator owns the schema and bypasses RLS. Tests that run as the migrator do not test tenant isolation.

Rule

Provider credentials use envelope encryption: a KMS-wrapped data key per secret, a small time-limited cache of decrypted keys, every decrypt logged. Only the api role's task can decrypt. The KMS key policy lets the account administer the key, not decrypt with it.

Rule

A subscription's connection is set at creation and never re-chosen. Re-routing an existing subscription means two provider accounts can bill one person.

Plans, prices and changes

• A plan's type, duration and entitlements are locked once sold. Name, seats and on/off stay editable.
• Prices are versioned. A new price applies to new purchases.
• Lifetime is its own duration, not "99 years".
• Seeding is create-only. A deploy must never overwrite what staff changed in the panel.
• Plan groups are ordered tiers. One plan per group per user. Upgrades are allowed, downgrades refused. Upgrade modes: difference (pay new minus unused), full, next_renewal, disabled. An upgrade that would leave old billing running is refused.

Sweeps

Sweep	Why
Expire checkouts	Abandoned checkouts close after 30 minutes
Expire subscriptions	Ends passes, period-end cancels and grace on UPG's clock; renewing subscriptions after 72 hours with no renewal
Reconcile	Asks each rail for the real state near period boundaries. "The rail has no record" never revokes
Google acknowledgements	Retries unacknowledged purchases before the 3-day refund
Google voided purchases	Daily, 30-day lookback
Expired callback secrets	Ends rotation overlaps

Audit

A review on 2026-10-15 counted 94 write paths: 44 fully audited, 33 partly, 17 not. Fixed the next day: every upgrade outcome, grant and revoke with before and after values, replay and redeliver, erasure as its own action. Still open: a system actor, making history rows immutable, provisioning audit. When you add a write path, add its audit row in the same transaction.

Mistakes we made

• Webhooks were stored and never applied. The apply function existed and was tested, but only tests called it. First payments worked because verify asks the rail directly, so nobody noticed renewals, refunds and cancellations piling up. The worker now drains provider_events with a lease and backoff.
• Every webhook in the first deployed environment was dropped. The connection lookup ran before any tenant context, so RLS showed it no rows. And the framework answered form-encoded gateway posts with 415. Tests missed both: they ran as the migrator role and never over HTTP. Fixed with a SECURITY DEFINER lookup function and a raw-bytes body parser on the webhook scope.
• The production image had no Apple root certificates. Every Apple notification would have failed.
• Idempotency keys were tenant-wide. Two apps, or a test and a live key, sending the same key got each other's response. Keys are now scoped to environment and app.
• Reads by id checked only the tenant. Another app's key, or a test key, could read a live subscription. Ownership checks now cover reads too.
• Checkout billed every plan monthly. A quarterly plan on a gateway agreement would have charged every month.
• Every deploy reverted panel edits. The seed overwrote staff changes: a paused gateway came back active. Seeding is now create-only.
• Catalog drift, found by hand in one morning on one live app, with no errors: a product invisible in the store, two active base plans at different prices, 90 promised trial days on a base plan with no offer, a monthly plan mapped to a yearly base plan, a trial offer only for existing subscribers. UPG now runs a drift check against each store.

Checklist for a change to UPG

• Webhook routes still answer 200 fast and store before applying.
• New tables with tenant data have RLS with FORCE.
• Tests run as the app role, over HTTP, against real Postgres.
• Money fields are minor units with a currency; financial rows append-only.
• New write paths write an audit row in the same transaction.
• No rail call inside a database transaction without a recovery path.
• The rename test, second-tenant test and config test still pass.