Skip to main content

Keystone — Database Design

Status: Scoping only, for the spike defined in keystone-spike-scope.md. Deliberately light — enough to catch real integration surprises before they happen (the same discipline that caught Waypoint's tid-claim issue and the EntraObjectId FK question), not an exhaustive design meant to document a permanent system. If the spike succeeds and this becomes a real build, this deserves a fuller pass at that point — proportional now, more later.

Read first: keystone-external-login-proposal.md for the original linking-not-coupling pattern this schema is built from, and keystone-framing.md Section 9 for why entitlement is explicitly not part of this schema.

1. What Keystone's own database owns — and what it deliberately doesn't​

Per the framing doc's own resolution: Keystone owns credentials and a stable person ID only. Entitlement — which products a person can actually reach — is delegated to Command Center, not stored here. Worth being as deliberate about this boundary in the schema as the framing doc was in prose: no Subscriptions, ProductAccess, or similar table belongs in this database at all. A query for "can this person use Waypoint" is an API call to Command Center, never a local join.

2. Core schema​

On User.IsActive — a deliberate, precise distinction, not an oversight. This governs whether the credential itself can authenticate at all (e.g., disabled for a security reason) — it has nothing to do with whether the person has access to any particular product. That's Command Center's entitlement question entirely. Worth being explicit about this given RoundTrip's own hard-earned lesson on this exact class of mistake: TRA-434 found two independent, unsynced IsActive flags (TenantUsers and Technicians) that quietly meant different things depending on which one you asked. This schema avoids that by design — IsActive here answers exactly one question, and product access is never asked of this table at all.

On PasswordHash being nullable — a real, intentional consequence of the linking-not-coupling model. Someone who registers via "Sign in with Microsoft" and never sets a local password has a genuine User row with no password, authenticated entirely through their linked ExternalLogin. Both paths are first-class, not one primary and one bolted-on.

3. OpenIddict's own required tables — framework-provided, not designed here​

Any OpenIddict-backed service using its EF Core store needs its own standard tables — OpenIddictApplications, OpenIddictAuthorizations, OpenIddictScopes, OpenIddictTokens — created and managed by options.UseOpenIddict() on the DbContext. These aren't custom-designed as part of this document; they're inherited framework schema, the same way EF Core's own migrations history table isn't something RoundTrip designs by hand either. Worth knowing they exist and will show up in the real schema, not worth designing here.

4. A real evolution from the original proposal doc, made explicit​

The original keystone-external-login-proposal.md had ExternalLogins living on RoundTrip's own TenantUser/Technician aggregates — correct for the context it was written in, before centralization was decided. Now that Keystone is the single central credential owner, that pattern needs to move: linking happens once, centrally, on Keystone's own User/ExternalLogin tables above — not duplicated separately at RoundTrip's and Waypoint's product-level tables.

What this means concretely for the proposal doc's own guardrails, carried forward rather than dropped:

  • Verified-email-required linking, no silent account merging — still fully applies, just enforced once, centrally, at ExternalLogin creation time, instead of separately in each product.
  • "Never let two different kinds of user cross-resolve" — the original concern was framed around RoundTrip's own TenantUser/Technician split specifically. That guardrail still matters, but it now belongs entirely to RoundTrip's own side of the foreign key (Section 5) — Keystone has just one User concept centrally, so this isn't a concern inside Keystone's own schema, only in how RoundTrip resolves which of its own local tables a given KeystoneUserId maps to.

5. The cross-service reference pattern — application-level, not a real database constraint​

Worth being precise about something a diagram could otherwise mislead on: Keystone, RoundTrip, and Waypoint are separate databases, in separate services. A KeystoneUserId column on RoundTrip's TenantUsers table is a logical reference to Keystone's User.Id — enforced by application logic (or optionally validated via a lookup call), never a real SQL FOREIGN KEY constraint, since cross-database foreign keys don't exist in this architecture at all.

RoundTrip's own side of this — TenantUsers.EntraObjectId and Technician.EntraObjectId both get replaced by KeystoneUserId, same shape, no restructuring of anything else in either table.

Waypoint's own side — per keystone-framing.md Section 9, already settled as small and mechanical: WaypointUsers.EntraObjectId → KeystoneUserId, plus a new WaypointUserExternalLogins table — wait, correction worth catching here rather than carrying forward: per Section 4 above, Waypoint doesn't need its own ExternalLogins table at all anymore either, for the same centralization reason RoundTrip doesn't. That detail in the framing doc predates this document's own Section 4 clarification — worth fixing there too, not just noting the inconsistency here silently.

6. What's explicitly deferred, matching the spike's own scope​

Not designed here, on purpose — real work belonging to later phases, not this spike:

  • Command Center's own entitlement/subscription schema — a separate service, a separate document, not Keystone's concern at all.
  • Any admin-app-specific schema — the admin app (per the spike-scope doc) talks to Keystone and Command Center's existing APIs, it doesn't need its own persistence layer for this.
  • Production-tier concerns — audit log tables, session/token revocation lists, rate-limiting state. Real, needed before the "production tier" bar (keystone-framing.md Section 8), not needed to prove the spike's core mechanics.

7. The RoundTrip FK compatibility check — a real caution worth repeating here​

Per the discussion that produced this document: proving KeystoneUserId can replace EntraObjectId needs to run against a genuine, current copy of RoundTrip's real schema — not a simplified stand-in. TenantUsers/Technician have changed substantially in just the last two weeks (the Tier 1/2/3 arc, TRA-434's dual-IsActive finding, TRA-435/436's additions). A compatibility check against a stale mental model of that schema would produce false confidence, which is worse than not checking at all.