Stripe Customer Billing — Data Model

Status: Design v3 — ready for implementation in Queue. Operator override 2026-05-11 UTC reverted the Raptor stopgap proposal — billing stays in Queue per the architectural intent. Sub-cards remain area:queue. Billing ships AFTER Queue is built; v1 launch proceeds without paid-signup-in-product. See ADR-0075.

Epic: #403 — console billing console (operator-facing customer billing) Parent card: #405 Refs: #406 (Stripe service layer), #407 (webhook handler), #408 (customer list view), #409 (customer detail view) Date: 2026-05-10 UTC (v1) / 2026-05-11 UTC (v2, v3, v5) ADRs: ADR-0071 (Queue-as-authority — confirmed), ADR-0073 (Raptor stopgap proposed — superseded by ADR-0075), ADR-0075 (operator override — billing stays in Queue) Supersedes PR: #1604 (v2 baseline)

Changelog

1. Context

This document covers the data model for Raxx's customer-facing billing records: the Stripe Customer, Subscription, Invoice, and PaymentMethod objects that back the operator billing console. It is distinct from the operator-side vendor-spend model in docs/architecture/billing-data-model.md, which tracks Heroku, AWS, Cloudflare, and Postmark infrastructure costs.

Why Queue, not Console (long-term): Queue owns all customer identity, sessions, RBAC, and audit as of the 2026-05-09 operator decision. Billing records co-locate naturally with customer identity — a billing customer IS a Queue customer. Console is the operator admin plane but it is not an identity service; it would have been a secondary home for customer data that already lives in Queue.

Why Raptor for v1: Queue does not exist as code. Building Queue from scratch to host billing before the 2026-05-23 UTC launch deadline requires 11–13 combined dev-days with zero slip budget. ADR-0073 documents this decision. The schema is portable and unchanged; the migration is a post-launch epic.

Cards #406–#409 and #1630–#1635 target Raptor for v1. PM should retarget labels from area:queue to area:raptor now.

2. Invariants

These constraints override any design choice below.

3. Authority Model (v3 intent — see V1 addendum for stopgap)

Rationale (full argument in ADR-0071): Queue already holds the canonical customer row. Billing data is per-customer data. Placing it anywhere else creates a secondary authority that must be kept in sync with Queue's identity state — an unnecessary coupling. Console is an admin plane, not an identity service; it reads customer state, it does not own it.

The v1 stopgap (ADR-0073) temporarily violates this model. Billing PII lives in Raptor-DB. The migration post-launch restores the clean separation.

4. Data Model

For v1: Tables land in Raptor's Alembic chain. Migration revision number confirmed by feature-developer against the live chain head when claiming #406 (must be after #1556 Postgres migration chain completes).

For post-v1 Queue migration: File is queue/migrations/versions/queue_0010_stripe_billing_tables.py — same schema, different chain.

4.1 billing_customer

One row per customer. Created when the Stripe customer.created event arrives or when the service layer provisions a Stripe customer for an existing customer row.

billing_customer
----------------
id                   TEXT (UUID)     PK, Python-generated
queue_customer_id    TEXT (UUID)     NOT NULL, UNIQUE — soft reference in v1 (no hard FK);
                                     hard FK into queue_customers.id post-migration
stripe_customer_id   TEXT            NOT NULL, UNIQUE — Stripe's `cus_...` ID; indexed
billing_email        TEXT            NOT NULL — PII; anonymized on DSR erasure
billing_name         TEXT            NULL — PII; anonymized on DSR erasure
address_line1        TEXT            NULL — PII; anonymized on DSR erasure
address_line2        TEXT            NULL — PII (apt/suite/floor); same retention and erasure class as address_line1
address_city         TEXT            NULL — PII; anonymized on DSR erasure
address_state        TEXT            NULL — PII; anonymized on DSR erasure
address_postal_code  TEXT            NULL — PII; anonymized on DSR erasure
address_country      TEXT(2)         NULL — ISO 3166-1 alpha-2; retained post-erasure for tax
default_pm_last4     TEXT(4)         NULL — last four digits of default card; NOT PII under PCI
default_pm_brand     TEXT            NULL — 'visa', 'mastercard', etc.
default_pm_exp_year  INTEGER         NULL
default_pm_exp_month INTEGER         NULL
customer_segment     TEXT            NOT NULL DEFAULT 'organic'
                                     CHECK customer_segment IN
                                       ('founders','organic','referral','paid_acq','partner_referral','comp')
acquisition_source   TEXT            NULL — UTM-style channel string, max 256 chars
stripe_created_at    TIMESTAMPTZ     NOT NULL — Stripe object creation timestamp (UTC)
created_at           TIMESTAMPTZ     NOT NULL DEFAULT CURRENT_TIMESTAMP
updated_at           TIMESTAMPTZ     NOT NULL DEFAULT CURRENT_TIMESTAMP

UNIQUE INDEX idx_bc_stripe_customer_id ON (stripe_customer_id)
UNIQUE INDEX idx_bc_queue_customer_id ON (queue_customer_id)
INDEX idx_bc_created_at ON (created_at)
INDEX idx_bc_customer_segment ON (customer_segment)

PII fields: billing_email, billing_name, address_line1, address_line2, address_city, address_state, address_postal_code. On DSR erasure these are set to NULL and billing_email is replaced with a tombstone token erased:<sha256-of-original-email[:8]> to preserve uniqueness constraint integrity. address_country is retained for tax compliance.

address_line2: nullable TEXT, added for apt/suite/floor/unit suffixes. Same encryption treatment and 7-year retention floor as address_line1.

customer_segment: NOT NULL, default organic. Enum values: - founders — first-cohort founders pricing (Q1 decision) - organic — direct/unpaid acquisition (default) - referral — customer-referral program - paid_acq — paid marketing channel (Google/Meta/etc.) - partner_referral — broker or platform-partner referral - comp — operator-granted complimentary access

acquisition_source: nullable TEXT, max 256 chars. UTM-style channel string (e.g. utm_source=google&utm_medium=cpc&utm_campaign=q2-launch). Set at signup time from the first-touch UTM parameters on the customer row. Never updated after initial write. Not PII; retained indefinitely.

Payment method columns: raw card data is never stored. default_pm_last4 and default_pm_brand are display-only fields safe to retain post-erasure. Full payment method objects live in Stripe only.

4.2 billing_subscription

One row per Stripe subscription. The schema supports multiple subscriptions per customer (Stripe allows this), but a partial unique index enforces at most one active subscription per customer at any time (see uniqueness constraint below).

billing_subscription
---------------------
id                      TEXT (UUID)     PK, Python-generated
billing_customer_id     TEXT (UUID)     NOT NULL, FK → billing_customer.id ON DELETE CASCADE
stripe_subscription_id  TEXT            NOT NULL, UNIQUE — Stripe's `sub_...` ID; indexed
stripe_customer_id      TEXT            NOT NULL — denormalized for direct Stripe API lookup
plan_tier               TEXT            NOT NULL — CHECK IN ('free','founders','pro','pro_plus')
stripe_price_id         TEXT            NULL — Stripe Price ID; NULL for free tier
status                  TEXT            NOT NULL — CHECK IN
                                           ('active','trialing','past_due','canceled',
                                            'unpaid','incomplete','incomplete_expired')
current_period_start    TIMESTAMPTZ     NOT NULL
current_period_end      TIMESTAMPTZ     NOT NULL
cancel_at_period_end    BOOLEAN         NOT NULL DEFAULT false
canceled_at             TIMESTAMPTZ     NULL — set when Stripe fires subscription.deleted
trial_end               TIMESTAMPTZ     NULL — reserved; NULL for all v1 plans
feature_locked_at       TIMESTAMPTZ     NULL — set at the moment a subscription transitions
                                               to a lower tier (downgrade marker; see §12)
stripe_created_at       TIMESTAMPTZ     NOT NULL
created_at              TIMESTAMPTZ     NOT NULL DEFAULT CURRENT_TIMESTAMP
updated_at              TIMESTAMPTZ     NOT NULL DEFAULT CURRENT_TIMESTAMP

UNIQUE INDEX idx_bsub_stripe_subscription_id ON (stripe_subscription_id)
INDEX idx_bsub_billing_customer_id ON (billing_customer_id)
INDEX idx_bsub_status ON (status) WHERE status NOT IN ('canceled','incomplete_expired')
INDEX idx_bsub_current_period_end ON (current_period_end)

Uniqueness constraint (Q-MULTI-SUBSCRIPTION resolved):

CREATE UNIQUE INDEX uq_billing_subscription_active_per_customer
ON billing_subscription (billing_customer_id)
WHERE status IN ('active', 'trialing');

This partial unique index enforces at most one active or trialing subscription row per billing_customer_id. The status IN ('active', 'trialing') predicate means canceled and incomplete rows do not compete with new subscriptions.

Application-level guard (supplements the DB constraint):

Before creating or reactivating a subscription, the service layer must check for an existing active row and return a user-friendly error response (409 Conflict, code SUBSCRIPTION_ALREADY_ACTIVE) before any INSERT reaches the DB. The DB-level constraint is the final backstop. If the DB unique violation ever fires in production (meaning the app guard was bypassed), Sentry receives a CRIT-level alert (billing.subscription.duplicate_guard_miss) so the race condition is investigated immediately.

feature_locked_at: nullable TIMESTAMPTZ set to NOW() at the instant a subscription row transitions to a tier lower than its predecessor (detected during webhook upsert by comparing plan_tier against the previous row value). This timestamp is the boundary for read-only enforcement in §12. See §12.2 for how the application interprets this field.

trial_end: column reserved for future use. NULL on all v1 rows. The trialing status value is included in the unique index predicate now so the constraint is correct when trials are introduced post-v1.

Status enum: active, trialing, past_due, canceled, unpaid, incomplete, incomplete_expired.

4.3 billing_invoice

One row per Stripe invoice. Invoices can exist without a subscription (one-off charges) so billing_subscription_id is nullable.

billing_invoice
----------------
id                      TEXT (UUID)     PK, Python-generated
billing_customer_id     TEXT (UUID)     NOT NULL, FK → billing_customer.id ON DELETE CASCADE
billing_subscription_id TEXT (UUID)     NULL, FK → billing_subscription.id ON DELETE SET NULL
stripe_invoice_id       TEXT            NOT NULL, UNIQUE — Stripe's `in_...` ID; indexed
stripe_customer_id      TEXT            NOT NULL — denormalized
stripe_subscription_id  TEXT            NULL — denormalized; NULL for non-subscription invoices
amount_due              INTEGER         NOT NULL — cents; CHECK amount_due >= 0
amount_paid             INTEGER         NOT NULL DEFAULT 0 — cents; CHECK amount_paid >= 0
amount_remaining        INTEGER         NOT NULL DEFAULT 0 — cents
currency                TEXT(3)         NOT NULL DEFAULT 'usd' — ISO 4217 lowercase
status                  TEXT            NOT NULL — CHECK IN ('draft','open','paid','void','uncollectible')
invoice_event_type      TEXT            NULL — 'payment_succeeded','payment_failed','voided','uncollectible'
                                               set at the time of the final event on this invoice
due_date                TIMESTAMPTZ     NULL — NULL for auto-charge invoices
paid_at                 TIMESTAMPTZ     NULL — timestamp of payment; NULL until paid
hosted_invoice_url      TEXT            NULL — Stripe-hosted payment page URL
invoice_pdf_url         TEXT            NULL — Stripe PDF download URL
stripe_created_at       TIMESTAMPTZ     NOT NULL
created_at              TIMESTAMPTZ     NOT NULL DEFAULT CURRENT_TIMESTAMP
updated_at              TIMESTAMPTZ     NOT NULL DEFAULT CURRENT_TIMESTAMP

UNIQUE INDEX idx_binv_stripe_invoice_id ON (stripe_invoice_id)
INDEX idx_binv_billing_customer_id ON (billing_customer_id)
INDEX idx_binv_billing_subscription_id ON (billing_subscription_id)
INDEX idx_binv_status ON (status) WHERE status NOT IN ('void','uncollectible')
INDEX idx_binv_due_date ON (due_date) WHERE due_date IS NOT NULL
INDEX idx_binv_event_type ON (invoice_event_type) WHERE invoice_event_type IS NOT NULL

No line-item table for v1. Stripe invoice line items are single-line for subscription charges. A billing_invoice_line_item table is out of scope for v1 (see §13, open question Q-INVOICE-LINE-ITEMS).

4.4 billing_subscription_mirror

PII-free mirror table. Raptor uses this for paywall enforcement. In v1 (all billing in Raptor-DB), this is populated by a local service call rather than a cross-service HTTP fan-out. In the post-migration design, Queue fans out to Raptor via POST /api/internal/billing/mirror-sync.

billing_subscription_mirror
----------------------------
queue_customer_id   TEXT (UUID)     PK — same ID as billing_customer.queue_customer_id
plan_tier           TEXT            NOT NULL — CHECK IN ('free','founders','pro','pro_plus')
status              TEXT            NOT NULL — CHECK IN ('active','trialing','past_due','canceled',
                                                         'unpaid','incomplete','incomplete_expired')
current_period_end  TIMESTAMPTZ     NOT NULL
updated_at          TIMESTAMPTZ     NOT NULL — LWW guard; only write if incoming updated_at > stored

INDEX idx_bsm_status ON (status) WHERE status = 'active'

No PII. No billing email, no name, no address. No DSR action required on this table — erasure of the customer record propagates via the fan-out call setting status = 'canceled' and plan_tier = 'free'.

The Raptor mirror migration is in Raptor's Alembic chain. It is blocked on the Raptor Postgres migration (epic #1556) completing first.

JIT mirror check — fail-closed (v1): If a Raptor API call reaches the paywall gate and the billing_subscription_mirror row is missing for an authenticated customer, Raptor returns 402 Payment Required (fail-closed) rather than granting access. Rationale: a missing mirror row is a data-sync anomaly, not a normal state; granting access (fail-open) would silently bypass the paywall during any mirror-sync gap. The Sentry alert billing.mirror.missing_row fires on every fail-closed hit so the gap is detected immediately. The nightly reconciler closes the gap within 24h. This is an ADR-worthy choice; see ADR-0071 notes section for the fail-closed argument.

4.5 Entity Relationships

erDiagram
    billing_customer ||--o{ billing_subscription : "has"
    billing_customer ||--o{ billing_invoice : "has"
    billing_subscription ||--o{ billing_invoice : "generates"

    billing_customer {
        text id PK
        text queue_customer_id UK
        text stripe_customer_id UK
        text billing_email
        text billing_name
        text address_line2
        text address_country
        text customer_segment
        text acquisition_source
        text default_pm_last4
        timestamptz stripe_created_at
    }

    billing_subscription {
        text id PK
        text billing_customer_id FK
        text stripe_subscription_id UK
        text plan_tier
        text status
        timestamptz current_period_end
        boolean cancel_at_period_end
        timestamptz feature_locked_at
    }

    billing_invoice {
        text id PK
        text billing_customer_id FK
        text billing_subscription_id FK
        text stripe_invoice_id UK
        integer amount_due
        integer amount_paid
        text status
        text invoice_event_type
        timestamptz paid_at
    }

4.6 v_customer_payment_reliability (derived view)

No stored score. A payment-reliability rubric stored as a single column on billing_customer is FCRA-adjacent: if it ever influences pricing, service eligibility, or tier access, it could constitute a consumer report and trigger adverse-action-notice obligations under 15 U.S.C. § 1681. Even if it never influences those decisions, the stored score would require disclosure in the privacy policy under GDPR Article 22 (automated profiling) and CCPA notice obligations.

Design decision: expose a read-only SQL view that aggregates raw event counts from billing_invoice at query time. The operator console renders the raw counts plus a last-N-events strip rather than a single numeric score. No stored column; no aggregate that could be treated as a consumer score.

CREATE VIEW v_customer_payment_reliability AS
SELECT
    bc.id                   AS billing_customer_id,
    bc.queue_customer_id,
    COUNT(*) FILTER (
        WHERE bi.invoice_event_type = 'payment_failed'
        AND bi.status NOT IN ('void')
    )                       AS failed_charge_count,
    COUNT(*) FILTER (
        WHERE bi.invoice_event_type = 'payment_succeeded'
        AND bi.paid_at > bi.due_date
        AND bi.due_date IS NOT NULL
    )                       AS late_payment_count,
    COUNT(*) FILTER (
        WHERE bi.status = 'uncollectible'
    )                       AS chargeback_count,
    COUNT(*) FILTER (
        WHERE bi.status = 'paid'
    )                       AS total_paid_invoices,
    MAX(bi.paid_at)         AS last_payment_at
FROM billing_customer bc
LEFT JOIN billing_invoice bi ON bi.billing_customer_id = bc.id
GROUP BY bc.id, bc.queue_customer_id;

What the console shows (§409 integration): The customer detail page renders failed_charge_count, late_payment_count, chargeback_count, and a last-5-invoice event strip from the raw billing_invoice rows. It does not compute or display a combined score. The operator reads the raw signal and applies judgment. This is documented in the #409 design review.

BLR reference: A BLR card has been filed to confirm: (a) what we can store vs. derive, (b) what we can act on, (c) what disclosure obligations apply. The view definition above is the provisional design pending that sign-off. If BLR determines that even the derived counts trigger disclosure obligations, the view will be replaced with a direct query in the UI layer with no materialized form in the DB.

5. Sync Strategy from Stripe

5.1 Webhook-driven primary (real-time)

Handled in #407. The billing webhook handler exposes POST /api/billing/webhook (in Raptor for v1; in Queue post-migration):

1. Verifies Stripe-Signature header via HMAC (STRIPE_WEBHOOK_SECRET from vault). Returns 400 immediately on failure (no retry; this is a security event — see §8 row W-HMAC).
2. Reads event.id as the natural idempotency key. Checks a processed_stripe_events dedup table (single-column TEXT, TTL 72h) before processing. Returns 200 immediately if already seen.
3. Upserts the affected DB row using ON CONFLICT (stripe_*_id) DO UPDATE SET ... WHERE updated_at < EXCLUDED.updated_at (LWW on updated_at).
4. On subscription upsert: detects plan tier change (downgrade) and sets feature_locked_at if the new tier is lower than the previous one.
5. On success: updates billing_subscription_mirror (intra-DB call in v1; cross-service fan-out in post-migration design).
6. Returns 200 to Stripe within 10 seconds. Stripe retries on 5xx with exponential backoff.

Handled event types (MVP): - customer.created, customer.updated, customer.deleted - customer.subscription.created, customer.subscription.updated, customer.subscription.deleted - invoice.created, invoice.updated, invoice.payment_succeeded, invoice.payment_failed, invoice.voided

Out-of-order events: use Stripe's created timestamp on the event object plus the LWW predicate. An invoice.paid arriving before invoice.created upserts with full data; the invoice.created event that arrives later has an earlier created timestamp and is dropped by the LWW guard.

5.2 Webhook sequence (v1 — all Raptor)

sequenceDiagram
    participant S as Stripe
    participant WH as Raptor /api/billing/webhook
    participant RDB as Raptor-DB
    participant CON as Console (reader)

    S->>WH: POST event (Stripe-Signature header)
    WH->>WH: HMAC verify — 400 on failure (security event, no retry)
    WH->>RDB: SELECT FROM processed_stripe_events WHERE event_id = ?
    alt already processed
        WH-->>S: 200 (idempotent)
    else new event
        WH->>RDB: UPSERT billing_* row (LWW guard)
        WH->>WH: detect tier downgrade → set feature_locked_at
        WH->>RDB: INSERT INTO processed_stripe_events
        WH->>RDB: UPDATE billing_subscription_mirror (intra-DB)
        WH-->>S: 200
    end
    note over CON,RDB: Console reads Raptor billing API in v1
    CON->>WH: GET /api/internal/billing/customer/{id}
    WH->>RDB: SELECT billing_customer + subscription + invoices
    WH-->>CON: JSON response

5.3 Nightly reconciliation (drift detection)

A GitHub Actions cron job at 02:00 UTC calls the billing service's /api/billing/reconcile endpoint (internal, not customer-facing). The reconciler:

1. Pages through GET /v1/subscriptions?status=all&limit=100 (Stripe API).
2. Compares Stripe state to DB rows by stripe_subscription_id.
3. Writes drift rows found to a billing_reconcile_log table (append-only).
4. When drift is detected: Sentry WARN-level alert fires. Operator console surfaces the mismatch count. Stripe is authoritative for subscription state; local derived metadata is secondary. The reconciler logs the delta but does not auto-correct — corrections go through the normal webhook upsert path to preserve the audit trail. The SOP (docs/ops/runbooks/billing/reconciler-mismatch.md) defines resolution steps.

5.4 Idempotency

• Stripe event ID is the natural key for the dedup table.
• All upserts use ON CONFLICT (stripe_*_id) DO UPDATE ... WHERE updated_at < EXCLUDED.updated_at.
• The webhook handler is stateless and safe to call multiple times with the same event.

6. RBAC and Access Control

RBAC permissions use the <app>-<resource>-<level> naming convention. The permission names are forward-compatible with the post-migration Queue RBAC model.

6.1 Permission set

6.2 Read access

Route guards: @require_rbac('queue-billing-read') on all /api/internal/billing/* and /api/billing/* read routes.

Console reads billing data via Raptor's (v1) or Queue's (post-migration) /api/internal/billing/* endpoints with a service-to-service token. Console has no direct billing DB connection.

Customer self-service read: the billing service exposes GET /api/billing/snapshot returning the mirror columns for the authenticated customer's queue_customer_id. This is PII-free (plan tier + status + period end only).

6.3 Write / ops access (cancel, refund, credit)

Write operations require queue-billing-write. This role is not seeded for any group in this design — reserved for break-glass assignment only.

All write operations append a row to billing_action_log. This is the money-state audit trail.

Integration with break-glass flow (#1478): queue-billing-write should be included in the break-glass time-limited grant set.

7. PII and Compliance

7.1 PII inventory

v1 note: All billing PII is in Raptor-DB. This is the explicit debt of the Path B stopgap (ADR-0073). The DSR erasure path applies the same anonymization logic regardless of which service holds the table.

7.2 DSR erasure path

On a customer data subject erasure request: 1. billing_email is replaced with tombstone token erased:<8-char-sha256>. 2. billing_name, address_line1, address_line2, address_city, address_state, address_postal_code are set to NULL. 3. billing_invoice rows are retained — required for 7-year tax compliance. The billing_customer row is NOT deleted; it is anonymized in-place. 4. Stripe account deletion is a separate operator action in the Stripe dashboard. The service does not call Stripe's customer delete API on DSR — that requires explicit operator confirmation to avoid billing errors. This is tracked in Q-STRIPE-DSR (see §13). 5. Mirror table: status = 'canceled', plan_tier = 'free'. No PII to erase there.

7.3 Encryption at rest

Heroku Postgres encrypts storage at rest (AES-256). No additional application-layer KMS encryption is required for billing fields. The operator-approved KMS HMAC budget (project_kms_audit_chain_approved.md) is consumed by the billing_action_log HMAC chain integrity, not by column-level encryption.

7.4 KMS HMAC chain on billing_action_log

The billing_action_log table (all money-state audit entries) participates in the KMS HMAC hash chain approved in project_kms_audit_chain_approved.md. Each inserted row includes an hmac_chain_hash column computed by kms:GenerateMac over (previous_hash || row_payload) using alias/raxx-audit-hmac. SC-A11 (HMAC chain integrity check job) covers this table. A chain-break detection event is a P0 security alert (see §8 row W-KMS).

Post-migration constraint: When billing tables migrate from Raptor to Queue, the backfill script must preserve all hmac_chain_hash values exactly — do not recompute. Chain continuity is the audit guarantee.

7.5 Breach notification

If billing_customer PII were exposed in a breach, affected customers and the DPA are notified within 72 hours per GDPR Article 33. The existing breach-notification automation path is responsible. The billing tables must be added to the breach-scope inventory.

7.6 Secrets

For v1: STRIPE_RESTRICTED_KEY and STRIPE_WEBHOOK_SECRET live in Infisical under /Raxx/Raptor/Billing/Stripe/ (operator action required — confirm or create this path before #406 is claimed). Both are rotatable without a redeploy. Rotation SOP: backend_v2/sops/rotation/stripe-restricted-key.md (filed as part of #406).

Post-migration: secrets move to /Raxx/Queue/Billing/Stripe/ per the v3 design. One-time operator path update.

8. Failure Modes Matrix

Column definitions: - Failure — what broke - Behavior — what the system does (automatically) - Alert (channel + threshold) — where the noise goes and when it fires - SOP doc path — the runbook SRE drafts; architect names it here so the file path is a known TODO - Severity — P0 = customer-visible money impact / security event; P1 = operator cannot act; P2 = drift/sync lag; P3 = logging/observability only

9. Migration Sketch

v1 Raptor migration: - File: next available revision in Raptor's Alembic chain after #1556 chain completes - Feature-developer confirms revision number against the live chain head before claiming #406 - Blocked on #1556 (Raptor Postgres migration)

Tables created in upgrade(): - billing_customer — see §4.1 - billing_subscription — see §4.2 (includes feature_locked_at, unique index uq_billing_subscription_active_per_customer) - billing_invoice — see §4.3 - billing_subscription_mirror — see §4.4 - processed_stripe_events — dedup table: (event_id TEXT PK, created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP) - billing_action_log — append-only audit with HMAC chain: (id UUID PK, actor_id TEXT, action TEXT, entity_type TEXT, entity_id TEXT, payload JSONB, hmac_chain_hash TEXT NOT NULL, created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP) - billing_reconcile_log — append-only drift log: (id UUID PK, stripe_id TEXT, field TEXT, raptor_value TEXT, stripe_value TEXT, detected_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP) - v_customer_payment_reliability — derived view (see §4.6)

downgrade() drops the view first, then all tables in reverse dependency order.

Post-launch Queue migration: - File: queue/migrations/versions/queue_0010_stripe_billing_tables.py — identical schema - Requires Queue Phase 2 completion as prerequisite - Backfill script: queue/ops/backfill_billing_from_raptor.py - Full plan: ADR-0073 §Post-Launch Migration Plan

Rollout gating (v1): All Raptor billing routes are gated behind FLAG_BILLING_RAPTOR_API (seeded in this migration). Setting heroku config:set FLAG_BILLING_RAPTOR_API=false on raxx-api-prod hides all billing routes immediately without a deploy.

Rollback: downgrade() drops all tables cleanly. The flag gate makes the migration safe to roll back during dark-launch without affecting active sessions.

10. Security Checklist (GDPR / design-invariant verification)

11. Rollout Plan

12. Tier Downgrade Architecture

This section addresses the operator's three questions from PR #1604 review (2026-05-11 UTC). These are product-level behavioral decisions, documented as design principles here, with implementation tracked in sub-cards.

12.1 Data stranding — the invariant

Invariant I-10: Customer data is the customer's regardless of tier. Downgrade changes feature access, not data access.

Concretely: backtests, strategies, journal entries, trade history, and all other user-generated records remain readable at all tiers, including after downgrade. Free-tier users retain full CSV/JSON export rights. Nothing is ever deleted or hidden as a consequence of tier change. The feature_locked_at timestamp on billing_subscription marks the moment of downgrade — it is the boundary for write-gate logic only.

Why: GDPR Article 20 (data portability) and CCPA § 1798.100 (right to access) guarantee that a customer can retrieve their data regardless of their commercial relationship state. Beyond legal compliance: the product thesis is that Raxx enforces the structure the customer chose — that structure belongs to them. A downgrade should feel like "I'm stepping back from a paid feature" not "I'm losing my work."

12.2 Read vs. write locking on downgrade

Write: locked from feature_locked_at. Any API route that performs a Pro+ write operation (live trading config, advanced backtest jobs, options-chain writes) checks billing_subscription.feature_locked_at. If set, the route returns 403 Feature write-locked with a body that names the locked feature and points to support.raxx.app for re-subscription. New records under Pro+ feature namespaces are blocked; existing records are never touched.

Read: never locked. No route checks feature_locked_at before serving a read. Existing data remains visible.

Export: always permitted. The CSV/JSON export endpoints bypass tier checks entirely and rely only on session authentication. This is a hardcoded bypass, not a flag — it must not be gated.

12.3 UI behavior on downgrade — the read-only-with-history pattern

Standard feedback_hide_dont_gray_unavailable_features.md rule: surfaces the user hasn't chosen are hidden, never grayed out. Downgrade is the legitimate edge case that requires a narrow carve-out:

Two-tier rule:

The read-only-with-history pattern is NOT a grayed-out paywall pattern. It has no upgrade CTA. It does not show locked padlocks. It communicates: "this is your history, it is still yours." The distinction: hiding preserves the integrity of the current-tier UI; showing history preserves the customer's relationship with their own data.

Example: A downgraded customer's advanced backtest run from last quarter remains visible in the backtest history list with a "Pro+ (read-only)" pill. They can view the full results, download the CSV, and read all charts. The "Run new backtest" button is absent from the UI (not grayed). The Pro+ configuration UI that would let them configure a new advanced backtest is absent.

Operator sign-off required (Q-DOWNGRADE-UI-RO-MODE): This two-tier rule constitutes a new UI principle that extends feedback_hide_dont_gray_unavailable_features.md for the downgrade edge case. It needs operator confirmation before feature-developer implements it. PM is filing the decision card. See §13.

12.4 Downgrade resistance strategy

Downgrade resistance is in scope for the marketing-strategist and PM. The architecture does not impose dark-pattern friction. The design holds space for:

• Pause subscription (1/2/3 months) as a pre-downgrade off-ramp — Stripe supports subscription pause via pause_collection.
• Win-back — ex-Pro+ customers receive a discounted re-subscribe offer at 30/60/90 days post-downgrade (email channel via Postmark, gated on FLAG_BILLING_WINBACK).

The pause and win-back flows are post-v1 implementation scope. The schema supports them (cancel_at_period_end, trial_end); the migration does not need re-running.

13. Open Questions

Questions marked RESOLVED are closed; links point to the card PM filed. Questions marked OPEN are blocking sub-cards.

1. Q-INVOICE-LINE-ITEMS (OPEN — blocks #409): §4.3 defers a billing_invoice_line_item table. Confirm with PM that the detail view (#409) does not need itemized line items at launch. Refer to #409 architect review for the full question context.

2. Q-DOWNGRADE-UI-RO-MODE (OPEN — blocks #408, #409): The read-only-with-history pattern (§12.3) extends the hide_dont_gray principle for downgraded customers who have prior Pro+ data. Operator sign-off required before feature-developer implements the conditional rendering logic. PM filing decision card.

3. Q-STRIPE-KEY-PATH (OPEN — blocks #406): Operator must confirm or create Infisical path /Raxx/Raptor/Billing/Stripe/ and populate STRIPE_RESTRICTED_KEY and STRIPE_WEBHOOK_SECRET before #406 can be claimed. Test keys are in vault at /MooseQuest/stripe/ — operator action: copy to the Raptor path.

4. ~~Q-BILLING-NAV-SHELL (RESOLVED — PM filed nav-shell card)~~ PM filed card-billing-nav-shell. Nav entry point and RBAC guard wiring for Console sidebar is tracked there.

5. ~~Q-STRIPE-DSR (RESOLVED — PM filed DSR card)~~ Full DSR end-to-end flow (including Stripe DELETE /v1/customers/{id} question) tracked in the DSR pre-launch card under compliance epic #1496.

6. ~~Q-RETENTION-JOB (RESOLVED — PM filed retention card)~~ Nightly PII anonymization job tracked as a pre-launch card under compliance epic #1496.

7. ~~Q-FOUNDERS-MIGRATION (RESOLVED — PM filed founders migration card)~~ Post-launch backfill of stripe_price_id for founders tier tracked as a pre-launch ops card.

8. ~~Q-MULTI-SUBSCRIPTION (RESOLVED — see §4.2)~~ Uniqueness constraint confirmed YES. Partial unique index uq_billing_subscription_active_per_customer added. Application-level guard specified. Sentry alert on DB-level violation.

14. Out of Scope — Handoff to Downstream Cards

Cards retargeted from area:queue to area:raptor (v1 implementation):

• Stripe service layer (backend_v2/api/services/stripe_client.py) → #406
• Webhook handler (backend_v2/api/routes/billing_webhook.py) → #407
• Customer list view UI (Console reads Raptor billing API) → #408
• Customer detail view UI (Console reads Raptor billing API, renders v_customer_payment_reliability) → #409
• DSR end-to-end flow → #1630 (compliance epic #1496)
• PII retention automation → #1631 (compliance epic #1496)
• Founders tier price ID backfill → #1632
• Billing dashboard → #1633
• Tier transition / downgrade flow → #1635
• Billing nav shell → PM card-billing-nav-shell
• Post-launch Queue billing migration epic → PM to file, refs ADR-0073
• Revenue summary / aggregation → post-v1
• Dunning state machine → post-v1
• Invoice line-item table → post-v1 (Q-INVOICE-LINE-ITEMS)
• Downgrade resistance + win-back strategy → marketing-strategist + PM
• Pause subscription (Stripe pause_collection) → post-v1