Skip to main content

Command Center Database Requirements

Version: 2.1 Date: September 2026 Author: Pete Carroll, Founder — Traxs Group LLC Status: Settled. Core schema is final; both flagged open items are resolved below.

What changed from v1.0, and why​

The v1.0 draft (kept in the document history below) had the right instinct but three real problems, found while checking it against command-center-framing.md and keystone-framing.md:

  1. Tenant ||--|| Subscription was modeled one-to-one. A tenant can subscribe to RoundTrip, Waypoint, and Relay independently — that has to be one-to-many.
  2. The same relationship was represented twice — a direct FK column and a separate relationship line to the same table (Tenant.EntitlementId FK alongside Tenant }o--|{ Entitlement; Products.ModuleId FK alongside Products }o--|{ Modules). Every relationship below now appears exactly once.
  3. Entitlement as its own table duplicated what Subscription already represents. A tenant is "entitled" to a product precisely when an active Subscription row exists for that (Tenant, Product) pair — a separate Entitlement table would just be a second source of truth for the same fact, with its own isSubActive bool to keep in sync. Dropped; GET /v1/entitlements is a query over Subscription, not a table lookup.

The real gap wasn't a missing table — it was PriceCommitment, which is now correctly shaped per keystone-framing.md §4 and the reasoning already worked through in command-center-thoughts.md: a rate lock has to apply to products a tenant hasn't subscribed to yet, so it can't be keyed by (TenantId, ProductId) the way Entitlement was.

This schema also builds directly on the ratified decision in command-center-framing.md §5/§6: Tenant.TenantId here is RoundTrip's existing TenantId — seeded on day one, not a new ID.

Table Descriptions​

TablePurpose
TenantThe canonical account. TenantId is RoundTrip's existing TenantId, reused — not a new ID minted at Command Center. Holds what's genuinely shared across products: company identity, one Stripe customer per tenant, and who owns the account (OwnerId).
ProductReference table — RoundTrip, Waypoint, Relay.
ProductTierEach product's tiers (Starter/Standard/Professional), scoped per product since tiers and pricing are product-specific, not shared across products.
TierPriceThe rate card, versioned. EffectiveTo IS NULL (or a future date) means current; a closed EffectiveTo preserves what a tenant was actually charged historically. This is what makes the 90-day pricing-change-notice policy representable — the old rate keeps its own row until the notice period ends, rather than being overwritten.
SubscriptionOne row per (Tenant, Product) the tenant has ever subscribed to. Status (Active/Trial/PastDue/Cancelled/Suspended) is what "entitled" actually means — no separate table needed. This is also where per-product trial and cancellation dates live, since RoundTrip, Waypoint, and Relay each run their own trial clock. Written by Command Center's own Stripe webhook dispatcher, as an internal command — not by each product's own handler; see command-center-stripe-migration.md (2026-09-16), which reverses the local-processing decision described below.
PriceCommitmentA rate-lock program a tenant holds — the Founders Program, today; the shape supports any future one. Keyed by TenantId alone, deliberately, because the lock has to cover products the tenant hasn't subscribed to yet.
PriceCommitmentLineThe actual locked rate, one row per (Product, Tier) the commitment covers. Checked at the moment a new Subscription gets created for that tenant: if an active, unexpired line exists for the chosen (Product, Tier), that price wins over TierPrice's current rate.

What's deliberately not a table: bundle discounts (10% at two products, 20% at the full suite). They're symmetric regardless of adoption order and key off nothing but a live count of a tenant's Active/Trial Subscription rows — a calculation at billing time, not stored data. Storing it would just create a second place it could go stale.

The two open items — resolved, 2026-09-03​

Both were put to a separate Claude session working directly in RoundTrip's own codebase context, since RoundTrip's actual behaviors (SubscriptionEnforcementBehavior, TenantScopingBehavior, the Stripe webhook handler) live there, not in anything available to this document. Reasoning checked below before accepting it — both hold up.

1. Where StripeSubscriptionId and per-product status live — confirmed, with the exact right parallel drawn. A single tenant-level SubscriptionStatus trying to represent RoundTrip, Waypoint, and Relay independently is the same category of mistake as TRA-434's dual IsActive — one field standing in for two things that vary independently, caught before it becomes a real bug instead of after. StripeSubscriptionId and Status stay on the per-product Subscription row, as designed above; StripeCustomerId alone stays on Tenant.

A real follow-on question this correctly surfaced, and Pete's follow-up resolved it: the schema says where the data lives, not who writes it. RoundTrip's Stripe webhook handling — signature verification, Tenant.ActivateSubscription()/CancelSubscription()/MarkPaymentFailed() — is real, hard-won infrastructure (the TRA-359/417 investigation found a Stripe.NET SDK pinned fifteen months behind the account's API version, masked by a misleading exception message). Originally decided (2026-09-03): RoundTrip keeps processing its own Stripe events locally, unchanged in mechanism — just writing into a Subscription-shaped record instead of directly onto Tenant. Centralizing webhook processing at Command Center was explicitly deferred, not rejected outright — the named trigger for revisiting it was "once Waypoint actually has real billing and there's a real second case to generalize from."

Superseded, 2026-09-16 — see command-center-stripe-migration.md. The new, multi-product signup flow turned out to be exactly that second case: command-center-brd.md §3's "one Stripe customer per tenant" goal and the cross-product bundle-discount logic both need visibility no single product's isolated Stripe integration has. Webhook processing now centralizes into Command Center after all — RoundTrip's existing handler is decommissioned, not retargeted. The "backup" idea below, and the graceful-degradation reasoning that rejected it, remain correct and unaffected by this reversal — the reversal is about which single system owns webhook processing, not about weakening that safeguard.

A "backup" idea was raised and correctly rejected:

A "backup" idea was raised and correctly rejected: having both Command Center and RoundTrip independently listen to and process the same Stripe events isn't redundancy, it's a double-processing risk — two uncoordinated systems acting on the same event need real idempotency coordination between them, which doesn't exist today and shouldn't get introduced just to hedge against Command Center's own reliability. Graceful degradation is already the right tool for that (Section 4 of command-center-framing.md) — each product genuinely able to keep running on its own, not two systems racing to process the same webhook.

2. Plan/TrialEndsAt — not actually a toss-up, corrected. This document originally called it a judgment call between two defensible options. That undersold it: command-center-framing.md §4 already settled graceful degradation as non-negotiable ("Command Center going down must never take a product down with it"), and SubscriptionEnforcementBehavior checks subscription state on effectively every authenticated write — a live dependency on Command Center there would violate a constraint that's already locked in, not a style choice. Decided: RoundTrip keeps a local cache of its own tier/trial state, kept correct by Command Center pushing updates into it — the identical shape to how TenantScopingBehavior already resolves SubscriptionStatus from a local row today, kept correct by Stripe's webhooks arriving asynchronously rather than a live Stripe call on every request. Same proven pattern, Command Center substituted for Stripe as the upstream source. The concrete sync mechanism (Command Center pushes a webhook to RoundTrip on change, versus RoundTrip polling) is real implementation detail worth settling when this gets built, not a re-opening of the question itself.

Tenant ownership — resolved, 2026-09-15​

Tenant.OwnerId (nullable Guid) records who owns the account. It grants exactly one protection, shared by no one else: the owner can never be demoted or removed from TenantAdmin, by anyone — including other admins they themselves appointed. It grants no exclusive power beyond that — any TenantAdmin can still grant TenantAdmin to someone else, same as before this field existed. Surfaced while scoping Keystone's Unlink feature (keystone-build-log.md Entry 5, which framed this as owner-exclusive appointment rights; refined here to the narrower GitHub/Slack/Stripe-style protection — settled 2026-09-15).

  • Holds a KeystoneUserId value, not a real foreign key — same treatment TenantId itself already gets across the platform; Command Center's database has no way to verify a KeystoneUserId is real, it just stores what Keystone returned.
  • No uniqueness constraint. One person can legitimately own more than one tenant.
  • Nullable, and stays null for existing tenants. RoundTrip tenants that predate this platform effort have no KeystoneUserId to record — the protection this field exists to provide simply doesn't apply to them yet, and there's no backfill planned.
  • Set exactly once, at Tenant creation, directly from the KeystoneUserId already sitting in SignupAttempt — no extra call needed, since the first person to complete signup is the owner by construction.

What this field does not yet do: enforce anything. The actual "can't remove the owner" check happens inside RoundTrip's/Waypoint's own product UI, and neither can perform it today — TenantUser/WaypointUser have no persisted KeystoneUserId to compare against this field. That gap, and the endpoint this implies Command Center needs to expose, are tracked in platform-action-items.md, not solved by this schema change alone.

What this replaces from the v1.0 draft's remaining sections​

  • "Subscription Architecture Tables" (tiers, pricing, discounts) — addressed above: ProductTier, TierPrice, and the bundle-discount calculation.
  • "Platform Administration" (tenant counts, revenue, system health) — genuinely a later concern, not schema. This is analytics/ops-dashboard territory, and command-center-framing.md §8 already places "platform analytics" in the long-term, correctly-sequenced-later bucket. Nothing in the "needed now" list depends on it — revisit once the core schema above is built and populated with real data to report on.
  • "Supporting Tables" — no specific requirement has surfaced yet for anything beyond what's modeled above. Left open rather than speculatively designed.

Document History​

VersionDateAuthorChanges
1.0August 2026Pete CarrollInitial rough draft — four tables (Tenant, Subscription, Entitlement, Products/Modules), scoped before the Tenant-ownership question was resolved.
2.0September 2026Pete Carroll (with Claude)Redesigned against the ratified Tenant-ownership decision (command-center-framing.md §5/§6). Fixed the Tenant:Subscription cardinality bug and the duplicated FK/relationship-line pattern. Replaced Entitlement with a query over Subscription (no separate table). Added PriceCommitment/PriceCommitmentLine, keyed by TenantId alone, to represent the Founders Program's forward-looking rate lock. Added TierPrice for versioned pricing (90-day notice policy). Flagged two open items needing Pete's confirmation: where StripeSubscriptionId/SubscriptionStatus live once multiple products have billing, and whether RoundTrip keeps a local cache of its own tier/trial state.
2.1September 2026Pete Carroll (with Claude, and a separate Claude session in RoundTrip's own codebase context)Both open items resolved. Stripe webhook processing stays local to each product (deferred centralization to Phase 5, with a "dual-processing backup" idea explicitly considered and rejected). RoundTrip keeps a local cache of Plan/TrialEndsAt, synced from Command Center the same way TenantScopingBehavior already keeps SubscriptionStatus correct from Stripe today — settled by the already-locked-in graceful-degradation requirement, not a toss-up.
2.22026-09-16Pete Carroll (with Claude)Reverses v2.1's "Stripe stays local" decision — see command-center-stripe-migration.md. The multi-product signup flow is the exact second case v2.1 named as the trigger for revisiting it; webhook processing now centralizes into Command Center. The dual-processing-backup rejection is unaffected and still correct.