Rampline: System and Database Architecture

1. Technology choices

2. Module boundaries

src/
  app/                 routes only (thin): (marketing), app/, platform/, s/ (published pages), api/
  modules/<module>/    domain logic per module: queries, actions, schemas, services
  lib/                 shared infrastructure: db, auth, permissions, jobs, events, storage, mail, ai, branding, licence
  components/ui        design-system components

Rules: a module may import lib/* and another module's public.ts only. Cross-module side effects travel as events (src/lib/events), not direct calls, so automation, analytics and revenue attribution subscribe without coupling.

3. Tenant isolation

• Database roles: rampline_app (RLS enforced, used for every signed-in request through withOrg) and rampline_service (BYPASSRLS, used only by sign-in, signup, public endpoints, webhooks and the worker; every query there filters by organization explicitly).
• withOrg(ctx, fn) opens a transaction and sets app.user_id and app.org_id. Policies call app.has_perm('<perm>'), which requires an active membership in the current organization whose role grants the permission.
• private.tenant_table(table, read, write, delete) applies the standard four policies, grants, the audit trigger and the updated_at trigger to every tenant table. New tables cannot forget isolation without it being visible in review.
• Secrets (API keys, provider credentials) are AES-256-GCM encrypted with SECRET_KEY; the app role cannot read ciphertext columns of other tenants and never receives plaintext in lists.
• Tests (tests/isolation.test.ts) create two workspaces and prove that reads, writes, publishing and exports never cross.

4. Permissions

Permission keys (module.action) live in table permissions and in src/lib/permissions.ts. System roles per organization kind are seeded by private.seed_roles(org):

Custom roles are allowed (Phase F UI). UI hiding is cosmetic; every server action calls requirePerm, and RLS checks again.

5. Data model (initial, Phases A and B)

Foundation (migration 001): users, platform_admins, organizations (kind, parent_id), permissions, roles, role_permissions, memberships (granted_via), invitations, sessions, auth_tokens, rate_limits, audit_log, branding, notifications, jobs, deployment_settings.

Build (migration 002):

Later phases add: products, prices, orders, subscriptions, payments (C); pipelines, deals, tasks, conversations, workflows, workflow_runs, email campaigns, bookings (D); experiments, personalization rules, health checks, incidents, ai_runs (E); licences, plans, entitlements, usage, operator billing accounts (F/G); courses, communities, affiliates, marketplace listings (H).

6. Page document schema

src/modules/pages/schema.ts defines the document (zod) used by the editor, the server and the renderer:

PageDoc { version: 1, theme: Theme, sections: Section[] }
Section { id, layout: "1" | "1-1" | "2-1" | "1-2" | "1-1-1" | "1-1-1-1", style, columns: Block[][], visibility }
Block   { id, type, props (per type), style: { align, marginTop, marginBottom }, visibility: { desktop, tablet, mobile } }

Text uses a safe inline markup subset (**bold**, *italic*, [label](https://…)) parsed into React elements. No stored HTML is ever injected into a published page. The custom-code block renders inside a sandboxed iframe without same-origin access and requires the pages.custom_code permission.

The same React renderer draws the editor canvas and the published page, so what the editor shows is what visitors get.

7. Events

events (organization_id, kind, occurred_at, visitor_id, contact_id, funnel_id, step_id, page_id, publication_id, variant, source jsonb, props jsonb). Producers: published-page renderer (page_view), form endpoint (form_submitted), checkout webhooks (purchase, refund). Consumers: funnel analytics, automation triggers, revenue intelligence. Measured events are facts; estimates are computed views that carry their assumptions.

8. Branding resolution

brandingForOrg(orgId) and brandingForHost(host) (src/lib/branding.ts): each field comes from the closest level that sets it, workspace, then agency, then operator, then built-in defaults. Product name, logo, favicon, colours, fonts, sender, support and legal links, login texts, extra navigation links and custom CSS all come from this, including login pages on operator app domains and transactional mail (sendSystemEmail). Custom CSS is filtered on save (cssProblem); fonts are local stacks, so no request goes to a font service.

9. Deployment topology

            ┌──────────── reverse proxy (TLS, custom domains) ────────────┐
 visitors → │  app (Next.js)  ── /s/* published pages, /api/v1, /app     │
            └──────────────┬───────────────────────────────┬─────────────┘
                           │                               │
                     PostgreSQL 17  ←── worker (jobs, webhooks, mail, health checks)
                           │
                     S3-compatible storage

Health: /api/health (app + database), worker heartbeat in deployment_settings. Backups: pg_dump script plus storage sync (Phase G).

10. Commerce (Phase C)

• Money is integer minor units with an ISO currency. src/modules/commerce/pricing.ts is the only place that computes totals (discount allocation, inclusive or exclusive tax); the server always recomputes from database prices.
• Payment providers implement one interface (src/lib/payments/types.ts): Stripe over REST (Checkout Sessions with inline price_data, so the catalog is not mirrored in Stripe) and a built-in test provider with its own hosted payment page. Provider secrets are AES-256-GCM encrypted with SECRET_KEY; the app database role cannot read them back.
• Flow: checkout element, POST /api/public/checkout/start (contact and checkout session created first, so abandoned checkouts are leads), provider page, GET /api/public/checkout/return (payment verified server-side, order created idempotently, signed purchase cookie set), one-click offers via POST /api/public/offer with that cookie, thank-you page reads /api/public/order-summary.
• Webhooks: POST /api/webhooks/stripe/{payment account id} verifies the signature, stores the event once (webhook_events, unique by event id) and enqueues a stripe.event job; the worker applies it with retries. Return URL and webhook may both arrive: fulfilment locks the checkout session and is idempotent.
• Orders, refunds and subscriptions are written only on the service connection after explicit permission checks (orders.refund, payments.manage); the app role can only read them through RLS.

11. CRM, automations and email (Phase D)

• Triggers are jobs. Code that causes a trigger (form submission, payment, tag change, deal move, new contact) inserts an automation.trigger job with an idempotency key per occurrence, inside its own transaction. It never waits for workflows and never fails because of them (src/modules/automations/triggers.ts).
• Workflows keep a draft (workflows.steps) and immutable versions (workflow_versions). Activation validates the draft with zod (steps.ts, shared with the builder) and freezes it. A run points at a version, so edits never change runs in flight.
• Runs (engine.ts) hold a cursor (a path into the step tree, plus wait or approval state). Each step executes in one transaction with its log row; a step already logged as done is not repeated after a crash. Failures retry with backoff three times, then the run stops as failed with a readable error and a Retry button. Waits and retries are picked up by the worker sweep every 15 seconds. Runs started by other runs carry a depth; past 3 nothing new starts.
• Email is stored as blocks and rendered to table HTML by one renderer (src/lib/email/render.ts) used for the preview, tests and sending. Messages are written to email_messages and delivered by email.send jobs; marketing mail is checked against consent, unsubscribes, bounces and the suppression list both when queued and right before sending. Tracking links are HMAC-signed per message, so the click redirect cannot be abused as an open redirect.
• Providers (providers.ts): log (records, never delivers), SMTP via nodemailer, Resend and Postmark over HTTPS. Workspace credentials are encrypted with the app key and never selectable by the app role. Platform mail uses environment variables (SYSTEM_EMAIL_FROM, RESEND_API_KEY, POSTMARK_SERVER_TOKEN or SMTP_*).
• Dependency added: nodemailer (SMTP). Resend and Postmark use plain fetch, no SDKs.

12. Intelligence (Phase E)

• Measured, attributed, estimated. Revenue figures carry their kind in the data they come from (src/modules/insights/revenue.ts) and in the UI. Estimates always show their formula; attribution names its rule.
• Lead Rescue (src/modules/rescue/service.ts) is a sweep that inserts one rescue_items row per (kind, record), resolves rows whose condition no longer holds, notifies once and escalates once. A first response is a logged call, meeting or email by a person, or a one-to-one email.
• Experiments. A running test on a step swaps in the variant page's publication for visitors assigned to B (FNV hash of experiment and visitor id, stable). The middleware passes a new visitor id within the first request so assignment works on the first visit. Page-view events carry variant and props.experiment_id; results count each visitor once in the version first seen. The always-valid p-value (mixture SPRT) allows looking at results at any time.
• Personalization runs after the experiment on the published document: the highest-priority rule whose conditions all match replaces texts or hides blocks. Unknown visit data never matches. ?rl_ab= and ?rl_p13n= force a version for previews and are not counted.
• Health (src/modules/health/service.ts) reconciles incidents with a partial unique index on open incidents per (kind, ref).
• AI gateway (src/lib/ai/gateway.ts): Anthropic Messages API over fetch with a forced tool call for structured output, validated by zod; workspace switch and monthly budget checked before every call; usage written to ai_usage. Tests swap the transport.

13. Agency, white label and licensing (Phase F)

• Hierarchy. organizations.kind is operator, agency or workspace with parent_id. An agency or operator works inside a client only through a membership with granted_via 'agency' or 'support' (support memberships expire), so every action inside the client still passes the client's RLS. Operators create customers without becoming members.
• Entitlements (src/modules/billing/entitlements.ts): a workspace's plan is its own subscription, else its parent agency's. No plan means no limits, so self-hosted and development installations work without plans; operators are never limited. checkLimit(org, key, adding) runs inside the service functions that create things, never only in the UI. Form leads are never refused. The catalog of limits and flags is in billing/catalog.ts (client-safe).
• Platform billing (src/modules/billing/plans.ts) is separate from commerce: plans, plan_subscriptions (price snapshot, discount, periods), billing_invoices (lines, unique per subscription and period). billingSweep in the worker issues invoices at period end, rolls periods, ends canceled subscriptions and marks overdue invoices. snapshotUsage stores hourly usage.
• Approvals (client_approvals) are tenant rows of the client; a decision checks that the deciding membership is the owner's and not agency or support access.
• Licensing (src/lib/license.ts, src/modules/licensing/service.ts): tokens RL1.<payload>.<signature> signed with Ed25519; the public key ships in license/public.pem, the private key lives only on the software owner's server. Revocation lists RLC1. are signed the same way and only fetched when LICENSE_REVOCATION_URL is set. assertLicensed('workspace' | 'user') runs where workspaces and users are created and only acts on the independent edition. Details in docs/LICENSING.md.

14. Installation and distribution (Phase G)

• One image, two processes. The Docker image runs the web app (next start) and, with another command, the worker (tsx scripts/worker.ts). The app container applies migrations on start (scripts/db.mjs deploy, advisory lock), so the worker waits for the app to be healthy.
• Database roles. Migration 001 creates rampline_app (RLS) and rampline_service (BYPASSRLS) with development passwords; deploy sets their real passwords from APP_DB_PASSWORD and SERVICE_DB_PASSWORD on every start. Migrations run as the admin connection (DATABASE_ADMIN_URL), which owns the schema.
• First run (src/modules/installation/service.ts): needsSetup() is true while no platform admin exists and setup never completed; it answers false on database errors so an outage never exposes the wizard. The token is compared as a SHA-256 hash in constant time. Setup creates the admin, the house operator with its branding, the edition and optional license in one transaction with a row lock on deployment_settings.
• Checks (checks.ts) are shared by the installation page and the CLI. The smoke test (smoke.ts) uses the same service functions as the app and, given an address, the public HTTP endpoints.
• Workspace transfer (transfer.ts): tenant tables are discovered from the schema (every tenant table has organization_id), exported with json_agg and imported with json_populate_recordset, so column types need no mapping. The import runs with session_replication_role = replica (foreign keys and triggers deferred, no audit noise), remaps user ids by email, skips identity and generated columns, and requires the target to have every migration the source had.
• TLS for customer domains: Caddy's on-demand TLS asks /api/tls/ask, which allows only the main host, white-label app domains and domains registered by a workspace.

15. Member portal, communities, affiliates, marketplace (Phase H)

• Members (src/modules/members) are the end customers of one workspace, separate from app users. The portal under /p/<workspace> runs on public routes with the service connection; every read filters by organization and by the member's grants. Sessions and one-time sign-in tokens are stored as hashes; the session cookie is scoped to the portal path.
• Access grants (member_access) carry kind (course, community, level), source (order, subscription, manual) and their own start and end. Commerce events only end or revoke grants, never delete them. Levels expand to courses and communities at read time, so changing a level changes access for everyone who has it.
• Courses (src/modules/courses) compute lesson state per member: open, preview, drip (with the date it opens) or locked. Completion of the last published lesson fires the course_completed trigger once per member and course (idempotency key).
• Communities (src/modules/communities) check access, bans, locks and rate limits in the service before writing; moderation runs through RLS with community.moderate.
• Affiliates (src/modules/affiliates): the middleware keeps rl_ref_first and rl_ref_last (code and time) on public pages; the checkout start reads them into checkout_sessions.referral; attributeOrder in the order job applies the program's rule and window and records at most one commission per order (partial unique index). Refund handling and the approval sweep keep the ledger consistent.
• Templates (src/modules/templates): package.ts is the pure format (tokens for references between items, media rewriting, the safety scan); library.ts captures and installs through withOrg, so each item still passes the workspace's permissions; marketplace.ts spans organizations and therefore uses the service connection with explicit publisher, buyer and platform-admin checks.

16. Security, data lifecycle and verification (Phase J)

• Proxy (src/proxy.ts, Next.js 16's name for middleware): custom-domain routing, visitor and referral cookies, the sign-in gate, and the Content Security Policy for the platform's own pages on app hosts. Requests without a Host header count as the app host.
• Outbound requests to addresses users control go through src/lib/server/net.ts (assertPublicHost, safeFetch), which resolves the host and refuses private and reserved addresses on every redirect hop.
• Accounts (src/modules/account): password reset and email verification use auth_tokens; two-factor sign-in puts a short mfa_login token between the password and the session (cookie scoped to /login). Email links are built by trustedBase from APP_URL or a configured white-label domain.
• Lifecycle (src/modules/lifecycle): deletion sets pending_deletion and purge_after; everything that reads organizations already ignored that status, so the workspace closes at once. The worker purges due workspaces hourly with their files and runs the retention sweep daily; purge_log keeps a record without personal data.
• Verification: scripts/acceptance.ts (twelve scenarios in a real browser), scripts/perf.ts (production-mode check and load), tests/security.test.ts (static authorization check of all server actions).