Entitlement Assistant · Data

This part is the information viewpoint: the data, a subsystem at a time, every table with every column, then the lifecycles the data moves through. It starts with the model at a glance and the rules every table keeps.

1. Model at a glance — 50 tables, each with its keys
2. Customer identity — the people and organizations served
3. Internal identity — employees and system identities
4. Account — every account, its version, its holds
5. Product — features, plans and add-ons
6. Access — roles and permissions
7. Entitlement — subscriptions, add-ons, grants
8. Policy — the policy set, in versions
9. Support — cases, change requests, approvals
10. Attribution — the audit chain
11. State machines — five lifecycles, the change request’s first

• References

1. Model at a glance

The model has 50 tables: 49 across nine subsystems — authorization has none, since it decides from the others’ data — and one the application holds, the outbox. Figure 8 shows every table with its keys alone; each subsystem’s section below draws its tables again with every column.

Every table keeps the same rules. Ids are UUIDv7, stored as PostgreSQL’s own uuid and shown with a prefix, so they sort by time; they are not secrets, since every access is authorized. Nothing is deleted: the application’s role has no DELETE, so an identity ends by its status and a grant or a case by a timestamp and a reason, and its history stays.

The database holds the structure, and the code the rules. Every status and kind is a text column whose check lists its values, so a misspelt state cannot be stored; some reasons are free text — a change request’s status reason, an assignment’s reason, a grant’s reason. Reference data is keyed by readable keys: kebab-case for products, features, plans and policies, such as grants-up-to-14-days, and dotted for permissions, such as account.read.

What an account is entitled to is never stored. It is computed when asked, by one building block, from the plan of the subscription’s current term, its add-ons and its active grants, so it cannot drift from them. Foreign keys point down the layer order; the audit chain alone refers back, by soft reference, so it depends on nothing it records.

Two constraints carry the support rules into the database. A change request is bound to its case’s subject by a composite foreign key — the case and the account together — so a request can never name an account its case is not about. An approval is one to one with its change request: its key is unique, though the shape would allow many.

The schema allows a little more than the code does, and says so here rather than leave it to be found. A case assignment may end as reassigned, though no code reassigns a case; a grant may end because its subscription ended, though no code ends it so; and nothing in the schema stops two activations of the policy set, or two grants of one permission to an employee, being current at once.

The application’s one table is the outbox: what a use case asks of the approval workflow, written in the use case’s own transaction and delivered after it commits.

2. Customer identity

Customer identity holds the people and organizations a provider serves. A customer is an organization or an individual, never both, and an individual belongs to at most one organization at a time, for as long as its membership lasts. Figure 9 draws its 4 tables, every column.

A customer’s kind is part of the key an organization or an individual refers to it by, so a row of one cannot stand for a customer of the other kind; a person’s email is stored normalized, once, and a membership ends with a reason rather than disappearing.

3. Internal identity

Internal identity holds the provider’s own identities: its employees, with their department, job function and level, and its system identities, which act where no person clicks. An employee’s password hash is replaced, never overwritten. Figure 10 draws its 6 tables, every column.

An employee’s kind and a system identity’s are held the same way, in the key, so an internal account knows which it belongs to. The department, job function and level are what give an employee their birthright roles in access, without anyone assigning them.

4. Account

Account holds every account, customer or internal, with its status and a version that each applied change advances. A hold suspends an account until it is released: a held account is refused every action but reading it, auditing it, cancelling a request and handling its case. Figure 11 draws its 4 tables, every column.

The version is what keeps a change honest: a change request records the version it was previewed at, and applying it checks the account still has it. Every hold records who placed it and who released it, so a suspension’s history stays readable.

5. Product

Product holds the catalog: products and their features, the operations each feature allows, plans on ladders, add-ons, and which features and quotas each plan and add-on includes. Figure 12 draws its 9 tables, every column.

A plan’s rank on its ladder is what would make a change an upgrade or a downgrade — changing a plan is not built — and an add-on joins only the plans it is eligible for. A bundle’s quota is what a plan or an add-on adds to a feature that counts, such as seats.

6. Access

Access holds roles and permissions. A permission is an operation on an internal resource or on a feature; roles give permissions to employees and to customers; a department, function and level give birthright roles at once. Figure 13 draws its 8 tables, every column.

A permission and the role that gives it name the same kind of principal, employee or customer, checked by keys, so an employee role cannot carry a customer’s permission. An assignment to an employee keeps its reason, its giver, its expiry and its end.

7. Entitlement

Entitlement holds what an account has bought or been given: subscriptions, their terms on a plan, their add-ons, and temporary grants. What an account is entitled to is computed from these, never stored. Figure 14 draws its 4 tables, every column.

A grant adds its quota to the feature for its time, and the grant it extends is a key of its own, unique, so a grant is extended at most once and the chain of extensions can be followed back to the first.

8. Policy

Policy holds the policy set in versions: each policy, when it applies, what it requires in CEL, its outcome when unmet, the actions it governs and its prerequisites, and which version is active. Figure 15 draws its 5 tables, every column.

A version is registered with the source revision it was built from, and an activation names the version in force from its moment. A prerequisite names the permission that fulfils it — for the approval a long grant needs, the permission to decide approvals.

9. Support

Support holds the cases: who works each, its subject and contact, the names it recorded, its change requests and their approvals. Figure 16 draws its 5 tables, every column.

A case records the names of the accounts it touched as they were when it saw them, so a later rename does not rewrite its history; it records too how its contact was authenticated, and by whom.

10. Attribution

Attribution holds the audit chain: its records, the accounts and reasons each names, and the chain’s head. Figure 17 draws its 4 tables, every column.

A record names its participants with their roles and carries reason codes, not words, so it holds no personal data; the head is one row, locked while a record joins, holding the last sequence number and hash.

11. State machines

Five things in the model have a lifecycle, and the change request’s is the one every change passes through. It is stored awaiting confirmation whatever its preview says; a confirmation the decision point answers Deny is refused with a 403 and leaves it awaiting confirmation; a confirmation applies it at once, or sends it to wait for an approval; it ends applied, refused, expired or canceled — canceled too when it has gone stale, because the account moved since its preview.

The project records lifecycles as statuses and facts as timestamps: a change request’s status says where it stands, and its timestamps say when it was requested, confirmed and applied, each with the account that did it. A change request’s status never goes back: from awaiting confirmation or approval it moves once to a later state, and its four end states are final. A case once closed is not reopened; a question that comes back opens a new case (Figure 18).

References

• The project’s data model: tech-docs/data-model/README.md and a document a subsystem.
• The migrations, at typescript/system/subsystem/*/src/infra/migrations/.