--- url: /reference/SUBSCRIPTION_PLANS_USE_CASES.md --- # Subscription Plans Use Cases Platform owns the commercial plan catalog, published plan versions, market offerings, and organization subscriptions. This catalog starts from what a real actor tries to do — a business owner starting a trial, the trial ending, choosing a paid plan, upgrading or downgrading, Ninaku ops defining and changing plans, another module asking "does this organization's plan include X / how many Y can it have?" — and derives the minimal API from that, never from the tables. Every case names the invariant it protects, its main path, and its adversarial/edge cases. The proving-test matrix lives in [`SUBSCRIPTION_PLANS_CASE_MATRIX.md`](SUBSCRIPTION_PLANS_CASE_MATRIX.md); nothing here is implemented yet, so read this catalog as frozen intent, not shipped behavior. **Why now.** `platform.provision_organization` requires an active `plan_offering_id` — an organization cannot be created until a sellable plan exists. Group 0 (below) has shipped SP1's seed mechanism, SP2's real `pionero` Plan and its Colombia/Ecuador Offerings, and SP3's `platform.provision_organization_for_caller`. The owner's decision: there is no separate "trial Plan" — a new organization subscribes to a real, priced Plan Offering the person chose, and the first 30 days are free via `plan_offerings.trial_days`. **This revision adds plans *administration*.** SP1-SP12 keep their ids and their content unchanged, except where an owner decision revises them in place (SP5's trial-expiry recommendation, superseded by the grace-period decision below; SP6, cross-referenced against its staff-driven counterpart). SP13-SP19, added after SP12 rather than interleaved, cover the platform super admin as an actor no schema object models today, the public catalog read a signup form needs before any session exists, plan/price/market administration, staff-driven subscription overrides, organization suspension, and the renewal/payment lifecycle the owner decided: **trial end or period end → 3 days of grace → automatic block; a payment → automatic reactivation.** *WHAT* each plan includes — the capabilities/limits catalog — stays out of scope; these cases only note where that catalog plugs into the version lifecycle SP15 already manages. Ground truth for every case below comes from `database/canonical/0060_platform.sql`, `0400_cross_domain_contracts.sql`, `0040_identity.sql` (`assert_membership_seat_available`), `0050_security_rls.sql` (context helpers), `database/access/runtime_grants.sql`, `database/tests/sql/core_lifecycle_fixture.sql`, `database/tests/prepare/prepare-database-contracts.ts` (the executable contract proving `ninaku_runtime` is barred from `platform.provision_organization`), `docs/reference/ARCHITECTURE.md`, `DOMAIN_MAP.md`, `DECISIONS.md` (D02, D18, D21), `docs/contracts/MODULE_CONTRACTS.md`, `API_AND_SCHEMA_DISCIPLINE.md`, `BUSINESS_DESIGN.md`, `APP_SHELL_AND_ONBOARDING.md`, and `docs/reference/ORGANIZATION_USE_CASES.md` (O1, O34), which this catalog cross-references rather than duplicates. **Decision-row legend.** Same as `ORGANIZATION_USE_CASES.md`: `HTTP?` — public route or internal-only. `Command/Read?` — state-changing command, read model, or both. `Module` — the NestJS module that owns the write (`platform`, per `ARCHITECTURE.md`'s `src/modules/` list). `DB objects` — canonical schema objects touched. `Missing grants` — whether `ninaku_runtime` can reach those objects today per `runtime_grants.sql`; "none" means the grants already exist. *** ## 1. Plan catalog as data ### SP1 — Define and publish a plan **Actor:** Ninaku ops (today: whoever authors and reviews a migration — no in-app admin persona or module exists in `DOMAIN_MAP.md`/`ARCHITECTURE.md`). **Intent:** create a commercial `Plan`, attach capabilities/limits to a draft `plan_version`, publish it, and sell it in one or more markets. **Preconditions:** none — this is how the catalog comes to exist at all. **Invariants protected:** * One owning schema file for the whole model (D02) — `plans`, `plan_versions`, `plan_version_capabilities`, `plan_version_limits`, `plan_offerings` all live in `0060_platform.sql`. * `plan_versions_one_active_per_plan_uq` — at most one `active` version per plan at a time. * Published/active version fields (`effective_from`, `published_at`) are immutable once set (`protect_plan_version_identity`); a `retired` version is immutable outright. * Plan children are editable only while their version is `draft` (`require_draft_plan_version`); only `active` capabilities/limit definitions may be attached (`validate_plan_version_capability`, `validate_plan_version_limit`). * A limit value fits the `value_kind` of its Limit Definition (migration `0074`, baseline revision 84). An `integer` limit counts people or invitations, so it takes a whole number from 0 to 2147483647, the range its consumers read as `integer`; both seeded limits, `identity.active_memberships` and `identity.pending_invitations`, are `integer`, and `integer` is the default of a new definition. A `decimal` limit takes any `numeric(20,4)` value. Unlimited carries no value and always fits. `platform.set_plan_version_limit_as_staff` refuses a value outside its kind with `platform_limit_value_kind` (23514), answered as `422 platform.capability_command_refused` with the detail "That limit counts whole items: send a whole number from 0 to 2147483647."; the trigger `validate_limit_value_kind` applies the same rule to every write on `plan_version_limits` and `organization_limit_overrides`. Nothing is rounded: a fraction is refused, never cut. The request body keeps its shape rule (up to 16 digits and 4 decimals, so `1e3` or `-1` is a `400`); only the database knows the definition, so the kind check lives there and the repository maps its refusal. * A published offering's commercial terms are immutable (`protect_plan_offering_terms`) — a price, interval, or market change creates a new offering row, never mutates an existing one, so an existing subscriber's contract terms never move under them. **Main path:** 1. Ops inserts a `platform.plans` row (code, name). 2. Ops inserts a `draft` `platform.plan_versions` row for it, then attaches `plan_version_capabilities`/`plan_version_limits` rows. 3. Ops flips the version to `active` (sets `published_at`, `effective_from`). 4. Ops inserts one `platform.plan_offerings` row per market/currency/billing interval it will sell, `draft` first, then `active` (sets `available_from`). **Adversarial/edge cases:** * Two versions of the same plan both attempt `active` — blocked by the partial unique index. * Editing the capability/limit list of a version already `active` — blocked by `require_draft_plan_version`. * Publishing an offering whose plan or plan version is not `active`, or whose market is not launched (`beta`/`active`/`paused`) — blocked by `validate_plan_offering`. * **Gap found in the schema, not a design choice**: retiring a plan version (`active` → `retired`) does not cascade to its offerings. `plan_offerings_validate_trg` fires `BEFORE INSERT OR UPDATE OF status, plan_version_id, market_id` — only when the offering row itself changes — so an offering can stay `active` and sellable after its plan version retires, unless ops explicitly retires or pauses every offering of that version in the same operation. Document this as an operational runbook step (retire the version's offerings first, or in the same transaction), not something the trigger already enforces. **Decision:** | HTTP? | Command/Read? | Merged with | Module | DB objects | Missing grants | |---|---|---|---|---|---| | No route proposed — no admin persona exists yet to authorize it | Command, delivered as reviewed migration/seed data, not an API call | — | Platform (owns the schema; no application code yet) | `platform.plans`, `plan_versions`, `plan_version_capabilities`, `plan_version_limits`, `plan_offerings` | N/A — ops writes through `ninaku_migrator`, never `ninaku_runtime` | **How plans reach each environment.** Two real options: (a) seed the catalog through a numbered `database/migrations/*.sql` file — the same mechanism `0017_runtime_reaches_organization_platform_and_identity_rbac.sql` already uses for grants, deterministic and reviewed like every other schema change, reproducible across local/staging/production; or (b) build an admin-only HTTP surface first. **(a) is recommended.** No admin persona or module exists yet, and building one is itself a real project, not a precondition for selling a trial. `API_AND_SCHEMA_DISCIPLINE.md` §4/§6 forbid speculative endpoints without a real caller; a future admin catalog API is legitimate later work, triggered by Ninaku ops actually needing to edit plans without a deploy — not by this catalog. ### SP2 — Seed the first real Plan and its Market Offerings **Shipped in Group 0** (migration `0018_the_caller_subscribes_to_a_real_plan_offering.sql`, baseline revision 28). The owner's decision: there is no separate "trial Plan" — a new organization subscribes to a real, priced Plan Offering chosen by the person provisioning it, and the first 30 days are free via `plan_offerings.trial_days`, exactly like every later billing period. **Actor:** Ninaku ops, as a one-time migration (SP1's concrete first instance). **Intent:** make a real, sellable Plan exist, with a priced Offering per supported Market, so `platform.provision_organization_for_caller` (SP3) has something real to point at. **Preconditions:** SP1's mechanism (a migration) is the delivery path. **Invariants protected:** same as SP1, plus `markets_operational_defaults_chk` (an `active` Market needs a default locale and currency) and `plan_offerings_trial_days_chk` (`0`–`365`). **Main path:** the migration seeds, per supported country, a `platform.markets` row (`status = 'active'`); one `platform.plans` row, `pionero`; its version 1, published and `active`, carrying every row `platform.capabilities`/`platform.limit_definitions` hold at seed time (today: none, so the version attaches nothing yet — the insert is a `SELECT` over those tables, not a fixed list, so it is automatically "every capability/limit" whenever a future module seeds real ones before this migration runs); and one `active` `platform.plan_offerings` row per Market, each with `trial_days = 30`. **Markets seeded today.** `reference.countries` carries no "supported" status column — every row it holds is definitionally supported — and today it holds exactly two: Colombia (`CO`) and Ecuador (`EC`), the owner's own example. Both markets get `default_locale_code = 'es'` (the closest existing `reference.locales` code; neither `es-CO` nor `es-EC` exists yet) and `default_currency_code` equal to their own Offering's currency. Adding a third country, or a second Plan, is a later, purely data-carrying migration — never a code change to `platform.provision_organization_for_caller` or to these tables. **Offerings seeded today.** Colombia: `59000 COP`/month. Ecuador: `19.99 USD`/month. Both `billing_interval_unit = 'month'`, `billing_interval_count = 1`, `trial_days = 30`, `status = 'active'`. **Adversarial/edge cases:** * A second Offering for the same Market/Plan is added later without retiring the first — no schema-level protection stops two simultaneously-active Offerings; this is an ops discipline question, not enforced today. * An organization tries to start a second trial after its first ends — nothing in `0060_platform.sql` tracks "has this identity/organization used a trial before"; `subscriptions_one_current_per_organization_uq` only guarantees one *current* subscription per organization, and a brand-new organization is a brand-new row, so trial re-use is bounded only by how hard it is to create a second organization, not by a dedicated check. **Decision:** same row shape as SP1; DB objects add `platform.markets`. *** ## 2. Subscription lifecycle at provisioning ### SP3 — Start a subscription when provisioning an organization **Actor:** any registered, signed-in identity provisioning their own new organization — the same actor as Organization's O1; this case is Platform's half of that same transaction. **Intent:** the new organization starts entitled, on the chosen offering, in one atomic step — never in a state where an organization exists without a subscription. **Shipped in Group 0** alongside SP2 (same migration, same baseline revision 28). **Preconditions:** O1's preconditions; the caller supplies the country, currency and locale they chose for their own organization, plus the `plan_offering_id` they chose from SP1/SP2's read surface. **Invariants protected:** * `subscriptions_one_current_per_organization_uq` — exactly one open (`ended_at IS NULL`) subscription per organization from the first insert onward. * `platform.provision_organization` already computes the correct initial state from the offering's `trial_days`: `trialing` with `trial_ends_at = now() + trial_days` when `trial_days > 0`; `active` with a computed `current_period_end` (using `billing_interval_count`/`billing_interval_unit`) when `trial_days = 0`. This case does not reinvent that arithmetic — it changes who is allowed to trigger it. * **The executable contract already forbids the obvious implementation.** `database/tests/prepare/prepare-database-contracts.ts` (`organizationRuntimeCannotProvisionOrganizations`) proves, against real PostgreSQL, that `ninaku_runtime` executing `platform.provision_organization` gets `permission denied for function provision_organization`. Per `AGENTS.md`'s authority order, an executable `database/tests/` contract outranks this document; the design below is written to keep that test passing unchanged, not to loosen it. **Main path (as designed today, cross-referencing Organization O1):** 1. Caller submits code, name, business unit, verticals, plan offering, locale, currency, idempotency key — exactly O1's inputs. 2. `platform.reserve_organization_provisioning_request` reserves the key. 3. `platform.provision_organization` — `SECURITY DEFINER`, `REVOKE ALL ... FROM PUBLIC`, `SET row_security = off` — validates the owner is an active human identity and the chosen role is a global, active role granting `organization.account.manage`, then inserts organization, business unit, vertical activations, **subscription**, membership, role assignment, and access scope in one transaction. 4. `platform.complete_organization_provisioning_request` marks the request completed. **Why `ninaku_runtime` still cannot call step 3 directly, and what changes.** `provision_organization`'s signature accepts an arbitrary `p_owner_identity_id` and an arbitrary `p_owner_role_id` — nothing in the function ties either to the calling session. Granting `EXECUTE` on this exact function to `ninaku_runtime` would let any authenticated request provision an organization *for a different identity*, using *any* role UUID it can guess or enumerate, entirely bypassing the "provisions only for the caller" property AGENTS.md §10 needs (the API runs only as `ninaku_runtime` — there is no separate narrow control-plane pool to isolate this call behind). **The fix is a new, narrower function, not a new grant on the existing one, and shipped exactly as this signature:** ```sql platform.provision_organization_for_caller( p_idempotency_key varchar, p_code varchar, p_name varchar, p_business_unit_code varchar, p_business_unit_name varchar, p_vertical_codes varchar[], p_country_code varchar, p_locale varchar, p_currency varchar, p_plan_offering_id uuid ) RETURNS uuid ``` It drops `p_owner_identity_id`/`p_owner_role_id` entirely — the only `uuid` parameter is the Plan Offering the caller chose, never an identity or role. Internally it resolves the owner from `identity.current_actor_identity_id()` (already `GRANT EXECUTE`d to `ninaku_runtime`, and already populated pre-tenant — `0050_security_rls.sql` sets `app.actor_identity_id`/`app.identity_id` from the authenticated session, independent of `app.organization_id`, exactly the state O1 runs in) and resolves the owner role from one fixed, seeded code, `organization_owner`, instead of an arbitrary UUID parameter. With no caller-suppliable identity or role, the privilege-escalation vector the existing test guards against does not exist in the new function, so it is safe to `GRANT EXECUTE ... TO ninaku_runtime` on it specifically, while `platform.provision_organization` itself stays exactly as barred as the existing contract test proves today. **Validation the new function adds, before touching any table:** * `p_country_code` must resolve a `reference.countries` row — otherwise refused (`"Country is not a known Reference country"`). * `p_currency` must resolve a `reference.currencies` row — otherwise refused. This is the **organization's own** bookkeeping currency, chosen by the caller, independent of the Plan Offering's own currency (an organization in Colombia may keep its books in USD while paying its Offering in COP). * `p_locale` must resolve a `reference.locales` row (case-insensitively) — otherwise refused. * The country's `platform.markets` row must exist and be `active` — otherwise refused (`"Country has no active Market"`). * The chosen `p_plan_offering_id` must belong to that same country's Market — an Offering from another country is refused, never silently substituted (`"Plan Offering does not belong to the requested country Market"`). * The chosen Offering must itself be `status = 'active'` and within its `available_from`/`available_until` window right now — otherwise refused (`"Plan Offering is not active and available now"`). **Adversarial/edge cases (all already named in `ORGANIZATION_USE_CASES.md` O1; repeated here only where Platform owns the invariant):** * Chosen plan offering is not `active` — refused before any insert, both by the new function's own check and by `validate_subscription_creation`. * Offering's `available_from`/`available_until` window excludes `starts_at` — refused (same trigger, and the new function's own availability check). * Organization's own status is not `active`/`suspended` at subscription creation — refused (same trigger; this only matters for the O1 path in the impossible case of a non-`active` organization existing pre-subscription, since provisioning creates both rows together). * Blocked/disabled owner identity — refused by `platform.provision_organization`'s own check, with no organization, membership, subscription or provisioning-request row left behind (the whole call is one transaction). **Decision:** | HTTP? | Command/Read? | Merged with | Module | DB objects | Missing grants | |---|---|---|---|---|---| | No dedicated route — folded into Organization's `POST /organizations` (O1) | Command | O1 | Organization owns the route; Platform owns `provision_organization_for_caller` and the subscription row it inserts | `platform.subscriptions`, `plan_offerings`, `organization_provisioning_requests` (existing); new function `platform.provision_organization_for_caller` | **Shipped in Group 0.** `ninaku_runtime` now has `EXECUTE` on `provision_organization_for_caller` and `SELECT` on `platform.plans`/`plan_versions`/`markets`; `platform.provision_organization` itself stays barred. | *** ## 3. Reading a subscription ### SP4 — Read my organization's subscription and plan **Actor:** any member with an active membership in the organization (read-only; no `organization.account.manage` required — knowing your own plan is not an administrative act). **Intent:** see the current plan name, version, status, trial/period dates, and the capabilities/limits it grants, in one call. **Preconditions:** tenant context established (Organization O3). **Invariants protected:** tenant isolation — `subscriptions_tenant_select_policy` restricts `SELECT` to `organization_id = (SELECT identity.current_organization_id())`; global catalog tables (`plans`, `plan_versions`, `capabilities`, `limit_definitions`) have no RLS by design (they are not tenant data) but are also read-only to `ninaku_runtime`. **Main path:** 1. Caller requests `GET /subscription` with `X-Organization-Id`. 2. Query joins `platform.subscriptions` (current, `ended_at IS NULL`) → `plan_offerings` → `plan_versions` → `plans`, plus `plan_version_capabilities`/`plan_version_limits` for the entitlement summary. **Adversarial/edge cases:** * Organization with no current subscription — should not exist post-O1 (a subscription is inserted in the same transaction as the organization); if found, treat as a data-integrity signal, not a normal `404`. * Caller belongs to a different organization than the one named by `X-Organization-Id` — refused by the same tenant-context guard as every other tenant-scoped route (Organization O3), never reaches this query. * Ended/cancelled subscription still shows the historical row, not an error — `ended_at IS NOT NULL` is a fact, not a refusal. **Decision:** | HTTP? | Command/Read? | Merged with | Module | DB objects | Missing grants | |---|---|---|---|---|---| | Yes — `GET /subscription` | Read model | — | Platform | `platform.subscriptions`, `plan_offerings`, `plan_version_capabilities`, `plan_version_limits`, `plans`, `plan_versions` | None — every `SELECT` and the `effective_limit`/`effective_capability` `EXECUTE` are already granted to `ninaku_runtime` (`runtime_grants.sql`); the entitlement summary uses those two functions so organization overrides and lapse are reflected | **Grants for SP1/SP2's read surface** (`GET /plans`) are likewise already in place: `SELECT` on `plans`, `plan_versions` and `markets` shipped in Group 0. *** ## 4. Trial and lapse ### SP5 — Trial expiry: what the product does when it ends **Actor:** the system (no human action triggers this; it is a consequence of time passing) and, secondarily, the organization's members who experience it. **Intent:** decide, precisely, what "trial ended" means for every capability-gated operation, without silently extending or silently destroying access. **Preconditions:** an organization on a `trialing` subscription reaches `trial_ends_at`. **Invariants protected — mostly already decided by the schema, not by this document:** * `platform.subscription_is_entitling(org, at)` computes entitlement from **timestamps**, not from `status`: for `trialing`, entitling only holds while `at < trial_ends_at`. This means the moment `trial_ends_at` passes, every `platform.effective_capability`/`assert_operation_allowed` call starts answering `disabled`/refused **automatically**, with zero application code and zero background job — the half-open window is already the enforcement mechanism, per the function's own comment ("stale billing status must never silently extend a trial or a paid period"). * **The schema already forbids a trial grace period.** `subscriptions_grace_chk` requires `current_period_end IS NOT NULL` whenever `grace_until` is set, and `provision_organization` leaves `current_period_start`/`current_period_end` `NULL` for a trialing subscription (`trial_days > 0`). A trial subscription therefore cannot hold a `grace_until` value at all — the constraint would reject it. Grace is a paid-period (`past_due`) concept only. * What is genuinely undecided: whether a **background job** should flip `status` from `trialing` to `ended`/`suspended` once `trial_ends_at` passes and no plan change happened, versus leaving the row `trialing` forever (entitlement is already correctly `disabled` either way, purely from the timestamp). **Main path (decided by the owner; full mechanics in SP18):** the scheduled job transitions `status` from `trialing` to `past_due` — not directly to `ended` — at `trial_ends_at`, for rows with no successor subscription, backfilling `current_period_start`/`current_period_end`/`grace_until` in the same `UPDATE` so the existing `past_due` grace window applies uniformly; a later pass of the same job transitions `past_due → suspended` once `grace_until` passes with no payment recorded. **Adversarial/edge cases:** * Trial subscriber changes plan (SP6) before `trial_ends_at` — the trial subscription's `ended_at` is set as part of that transition; no lapse logic ever runs. * Trial subscriber does nothing — once the scheduled job (SP18) has run and recorded `past_due`, capability-gated writes keep succeeding for the 3-day grace window (`subscription_is_entitling`'s `past_due` branch), then fail closed once `grace_until` passes with still no payment; permission-only operations (no capability check) keep working throughout, so the owner is never locked out of viewing/managing the account or picking a paid plan. * A read that does not call `assert_operation_allowed` at all (e.g., viewing organization settings, which per Organization O5 gates on `identity.effective_permission` alone) is unaffected by trial expiry — this is a real distinction the calling module must get right, same as Organization O34 already flags for permission-vs-entitlement refusals. **Decision:** | HTTP? | Command/Read? | Merged with | Module | DB objects | Missing grants | |---|---|---|---|---|---| | No route — a consequence of `effective_capability`/`assert_operation_allowed`, already reachable | Read (the block); optionally a scheduled Command for the status transition | Shares its mechanism with SP8/SP10 | Platform | `platform.subscriptions` (read); a future scheduled job would need `UPDATE` on `status`/`ended_at` | None for the block itself (already enforced); the scheduled job's `UPDATE` grant is the same gap SP6 needs — see Group 3 | **Superseded by an owner decision — see SP18.** This case's original question (hard block vs. read-only vs. grace) assumed grace would need a schema change it could not justify on its own. The owner's decision applies one policy uniformly to every lapse, trial included: **trial end or period end → 3 days of grace (the organization keeps working) → automatic block.** SP18 shows this needs no `ALTER TABLE`: the job transitions the lapsed `trialing` row straight to `past_due` — an edge `validate_subscription_transition` already allows — in the same `UPDATE` that sets `current_period_start = starts_at`, `current_period_end = trial_ends_at`, and `grace_until = trial_ends_at + interval '3 days'`; every `CHECK` above is satisfied by construction against the *new* row, and `subscription_is_entitling`'s `past_due` branch already grants exactly those 3 days once those columns exist. The original, narrower claim stands unchanged: a `trialing` row itself still cannot hold `grace_until`. It simply is never the row that needs to — it stops being `trialing` the instant grace begins. The remaining smaller decision — whether a background job cleans up `status` for tidiness — is now the whole mechanism, not an optional add-on; SP18 names it. *** ## 5. Changing plan ### SP6 — Change plan (upgrade or downgrade) **Actor:** a member holding org-wide `organization.account.manage`. **Intent:** move the organization onto a different, currently-active offering, ending the old commercial relationship and starting a new one in the same operation. **Preconditions:** tenant context established; an existing current subscription; a target `plan_offering_id` that is `active`. **Invariants protected:** * `subscriptions_one_current_per_organization_uq` — exactly one open subscription per organization at any instant; the transition must end the old row (`ended_at`) and insert the new one, never both open at once. * `protect_subscription_identity` — `organization_id`/`plan_offering_id`/ `starts_at` are immutable per row; a plan change is always a new row, a new commercial identity, never a mutation of the old one. This is also how existing subscribers keep their exact historical terms (SP11). **Main path:** 1. Caller submits a new `plan_offering_id`. 2. A single SQL command ends the current subscription (`UPDATE ... SET ended_at = now(), status = 'ended' WHERE organization_id = $1 AND ended_at IS NULL AND id = $2` — conditional on `id` and `ended_at IS NULL` together) and inserts the new one, in one transaction. 3. If the conditional `UPDATE` affects zero rows, someone already changed the plan concurrently — refuse with a conflict, never insert a second "current" row (the partial unique index is the last line of defense, the conditional `UPDATE` is the actual concurrency control). **Adversarial/edge cases:** * **Concurrent plan changes** — two commands racing to change the same organization's plan: the conditional `UPDATE` above lets exactly one win; the loser's zero-row `UPDATE` is the signal to refuse, not to blindly retry into a duplicate. * **Downgrade below current usage** — e.g., an organization with 8 active memberships downgrading to a plan whose `identity.active_memberships` limit is 5. `platform.effective_limit`'s own comment already answers this: "Contract ceilings survive commercial suspension/expiry so recovery does not grant unlimited usage" — i.e., **usage truth is not Platform's to check**; each owning domain (Identity, for seats) enforces its own ceiling on its own admission path (`identity.assert_membership_seat_available` already does exactly this, comparing `count(*) FROM identity.memberships WHERE status='active'` against `platform.effective_limit(..., 'identity.active_memberships')`). **Recommendation: allow the downgrade unconditionally.** The organization keeps its 8 existing members (nothing deletes rows), but the next attempt to add a 9th member fails closed at Identity's own admission check, using the *new*, lower limit. Building a cross-module "will this downgrade fit" pre-flight for every limit code is real, larger work with no owner today (Platform does not know each domain's usage semantics by design) — flagged as future work, not blocked on it. * Target offering belongs to a plan version already `retired` — the FK and `validate_subscription_creation`'s `v_offering_status <> 'active'` check refuse it, since a `retired` plan version's offerings should also have been retired per SP1's noted gap; if ops failed to retire the offering alongside the version, the offering-status check alone still admits it — another instance of the same SP1 gap. * Plan changes to the exact same offering — a same-offering "change" ends a valid subscription and immediately opens an identical new one; recommend the application layer short-circuits this as a no-op `200`, not a wasted write. **Decision:** | HTTP? | Command/Read? | Merged with | Module | DB objects | Missing grants | |---|---|---|---|---|---| | Yes — `POST /subscription/plan-changes` | Command | — | Platform | `platform.subscriptions` | `ninaku_runtime` has **only `SELECT`** on `platform.subscriptions` today — no `INSERT`/`UPDATE` grant exists, and no write RLS policy exists either (only `subscriptions_tenant_select_policy`, `FOR SELECT`). Recommend a new `SECURITY DEFINER` function `platform.change_subscription_plan(p_new_plan_offering_id uuid)` reading `identity.current_organization_id()` internally, matching the schema's own stated pattern ("control-plane writes use a narrow privileged service role") — this needs one new `GRANT EXECUTE`, not a raw table grant or a new write policy | **A staff override is a distinct case, not this one.** A platform super admin changing an organization's plan without that organization's own `organization.account.manage` permission, and without any tenant context to read `identity.current_organization_id()` from, is SP16 — `platform.change_subscription_plan_as_staff(p_organization_id, ...)`, a different function behind a different route. This case's member-authored path is unaffected by SP16 existing alongside it. ### SP7 — Request cancellation of a subscription **Actor:** a member holding org-wide `organization.account.manage`. **Intent:** stop the subscription, either immediately or at the end of the current billing period — the schema already models both (`cancel_requested_at`, `cancel_at_period_end`). **Preconditions:** an active or trialing current subscription. **Invariants protected:** * `subscriptions_cancel_at_period_end_chk` — `cancel_at_period_end = true` requires `cancel_requested_at` to already be set; you cannot schedule an end-of-period cancellation without having requested one. * `subscriptions_ended_chk` — `ended_at` is set only when `status = 'ended'` and vice versa; a cancellation request alone does not end the row, it only marks intent until the actual end transition runs. **Main path:** 1. Caller requests cancellation, choosing immediate or at-period-end. 2. Immediate: the same conditional end-and-optionally-replace pattern as SP6, except there is no replacement row — the organization ends with no current subscription (a state O1 never produces, but SP7 legitimately can). 3. At-period-end: sets `cancel_requested_at`/`cancel_at_period_end = true` now; a scheduled job later performs the actual `ended`/`status` transition when `current_period_end` passes, exactly like SP5's scheduled-job question. **Adversarial/edge cases:** * Cancelling an already-cancelled (pending) request — idempotent, not an error. * Cancelling a `trialing` subscription immediately leaves the organization with **no current subscription at all** — every capability check already fails closed the same way SP5 describes for a lapsed trial; document this as the same mechanism, not a new one. * An organization with no current subscription attempting any capability-gated operation afterward — `assert_operation_allowed` already answers this correctly today (`effective_capability` returns `disabled` when no entitling subscription row is found), so cancellation needs no new refusal logic, only the write itself. * **Open product decision, to settle before building SP7:** a payment recorded while `cancel_at_period_end = true` (a renewal or an early payment, see `record_subscription_payment_as_staff`) today would leave the cancellation scheduled, because nothing writes `cancel_requested_at` yet and the payment function never reads it. Either the payment withdraws the pending cancellation (the customer changed their mind by paying) or the cancellation still ends the subscription at the new period end. The owner decides; the choice must then be a test of the payment function, not an assumption. **Decision:** | HTTP? | Command/Read? | Merged with | Module | DB objects | Missing grants | |---|---|---|---|---|---| | Yes — `POST /subscription/cancellation` | Command | — | Platform | `platform.subscriptions` | Same gap and same recommended fix as SP6 — a narrow `platform.request_subscription_cancellation(...)` function, one new `GRANT EXECUTE` | *** ## 6. Entitlement contract — how other modules ask ### SP8 — Ask whether a capability is granted (`effective_capability`) **Actor:** another module (Sales, Inventory, a vertical) — never an HTTP caller directly; this is Platform's half of the same composition Organization's O34 names for `assert_operation_allowed`. **Intent:** "may this organization use capability X right now?" without knowing anything about plans, offerings, or subscriptions. **Preconditions:** none beyond an organization id and a capability code. **Invariants protected:** `platform.effective_capability`'s own documented precedence, verbatim from its `COMMENT ON FUNCTION`: **explicit deny, then market support for market-scoped capabilities, then explicit grant, then inclusion in the current entitling subscription's plan version. Anything else is disabled.** Entitlement is never inferred from permission, and vice versa (D-level distinction already made in `0060_platform.sql`'s header: "Entitlement != Permission"). **Main path:** calling module invokes `EntitlementContract.hasCapability(organizationId, capabilityCode)`, implemented as one call to `platform.effective_capability`. **Adversarial/edge cases:** * Organization itself is not `active` — `disabled`, checked first, before even looking at the capability. * Capability code does not exist — `disabled`, not an error; a typo in a calling module never crashes, it silently (from the caller's perspective) refuses. * Market-scoped capability with no market on the current offering, or the market support row is `paused`/`retired`/missing — `disabled`, independent of plan inclusion. * An explicit `deny` override always wins, even over an `active` entitling subscription that includes the capability. **Decision:** | HTTP? | Command/Read? | Merged with | Module | DB objects | Missing grants | |---|---|---|---|---|---| | No — internal contract only | Read (a check) | Composed with SP10 inside `assert_operation_allowed` | Platform exposes `EntitlementContract` | `platform.effective_capability()` | None — already `GRANT EXECUTE`d to `ninaku_runtime` per `runtime_grants.sql` | ### SP9 — Ask a plan limit (`effective_limit`) **Actor:** another module — the concrete, already-shipped example is Identity's `assert_membership_seat_available`, which is the pattern every future limit consumer should copy. **Intent:** "what is this organization's ceiling for quota X, and where did it come from?" — override, plan, or nothing. **Preconditions:** none beyond an organization id and a limit code. **Invariants protected:** `platform.effective_limit`'s documented precedence: **a valid, currently-effective override wins outright; failing that, the current (or most recently ended) subscription's plan limit applies — a lapsed/suspended subscription keeps its ceiling instead of silently becoming unlimited.** Usage truth is never stored here — the function returns a ceiling, never a count; the owning domain always supplies its own count (as `assert_membership_seat_available` does with `identity.memberships`). **Main path:** calling module invokes `LimitContract.effectiveLimit(organizationId, limitCode)`, gets back `{ limitValue, isUnlimited, limitSource }`, and compares against its own domain-computed usage before admitting a new unit. **Adversarial/edge cases:** * No row at all (limit code never defined) — the function returns no row; the caller must treat "undefined" as either "unlimited" or "not applicable," a decision left to each caller, never silently coerced by Platform. * Ended subscription, no override — still returns the last subscription's plan limit, per the function's own comment, exactly the downgrade-below-usage scenario SP6 describes: the ceiling survives, only future admission is gated by it. * A seat limit the member list cannot read — before revision 84 a staff member could store `2.5` seats: admission refused the third member (`3 > 2.5`) while the member list cast the value to `integer` and showed `3`, and `2147483648` made that read fail. An `integer` limit now holds only a whole number from 0 to 2147483647, so what the list shows is what admission enforces (`test/e2e/plan-limit-value-kind.e2e-spec.ts`). The migration brought any stored fraction down to its whole part and any value above 2147483647 to 2147483647, which is what admission already enforced. **Decision:** | HTTP? | Command/Read? | Merged with | Module | DB objects | Missing grants | |---|---|---|---|---|---| | No — internal contract only | Read (a check) | — | Platform exposes `LimitContract` | `platform.effective_limit()` | None — already `GRANT EXECUTE`d | ### SP10 — Authorize an operation combining permission and capability (`assert_operation_allowed`) **Actor:** another module, exactly Organization's O34 — this case is Platform's half of that same split-ownership design; it is not repeated here to be duplicated, only to record Platform's side of the contract in its own catalog. **Intent:** one call that answers "may this membership do X, and does the organization's plan even allow X," instead of two separate calls the caller might forget to compose correctly. **Preconditions:** tenant context established. **Invariants protected:** the function's own header comment, quoted in `ORGANIZATION_USE_CASES.md` O34: **organization-wide RLS alone is not action-level authorization.** `platform.assert_operation_allowed` composes `identity.has_active_tenant_context()`, `identity.effective_permission()`, `platform.effective_capability()`, `organization.assert_outlet_operational()`, and vertical-active checks in one SQL call. **Main path:** as described in O34 — calling module invokes `OperationAuthorizationContract.assertAllowed(membershipId, permissionCode, scope, capabilityCode?)`. **Adversarial/edge cases:** identical to O34's — permission present but entitlement absent (or vice versa) must be distinguishable by the calling module, since it may need to answer its own caller with a different error for each. **Decision:** | HTTP? | Command/Read? | Merged with | Module | DB objects | Missing grants | |---|---|---|---|---|---| | No — internal contract only | Read (a check) | Same case as Organization's O34; recorded here only for Platform's owned SQL objects | Platform exposes `OperationAuthorizationContract` (Organization's O34 write-up owns the split-ownership rationale) | `platform.assert_operation_allowed()`, `effective_capability()`, `identity.effective_permission()` | None — all three already `GRANT EXECUTE`d per the current `runtime_grants.sql` (this closes the gap `ORGANIZATION_USE_CASES.md` O34 flagged when it was written; that document is stale on this exact point after the recent runtime-grants change) | *** ## 7. Versioning and grandfathering ### SP11 — Plan version changes after an organization has subscribed **Actor:** none directly — this is an invariant, not an action; it is what must remain true while SP1 (ops publishing a new version) and SP6 (a different organization changing plan) both happen around an existing subscriber. **Intent:** an existing subscriber's commercial terms never move because Ninaku published a new plan version or a new offering. **Preconditions:** an organization already on `plan_offering_id = A`, pointing at `plan_version_id = V1`; Ninaku publishes `V2` for the same plan. **Invariants protected:** * `plan_versions_one_active_per_plan_uq` forces `V1` to retire before `V2` activates — but retiring `V1` never touches `A` (`plan_offerings.plan_version_id` is immutable once the offering leaves `draft`, per `protect_plan_offering_terms`). * `protect_subscription_identity` — the existing subscription's `plan_offering_id` never changes on its own; only an explicit SP6 command creates a new subscription row, and only that new row can point at a new offering (which, in turn, would point at `V2`). **Main path:** nothing happens to the existing subscriber. `platform.effective_capability`/`effective_limit` keep resolving through the *unchanged* `plan_offering_id` → `plan_version_id` chain on the existing subscription row. Only a fresh `plan_offerings` row under `V2`, and a fresh subscription pointing at it (SP6), moves anyone. **Adversarial/edge cases:** * Ninaku ops assumes retiring `V1` "moves" existing subscribers — it does not, and nothing in the schema would do so; this must be stated explicitly for ops, since it is easy to assume otherwise from the word "retire." * An organization subscribed to `A` (under `V1`) attempts to read entitlements after `V1` retires — unaffected; `effective_capability` joins through `plan_offering_id` → `plan_version_id`, and a `retired` `plan_versions.status` is not checked by `effective_capability` at read time (only by the write-side triggers that govern editing/creating versions and offerings). A subscriber never loses entitlement merely because Ninaku stopped selling their version to new signups — this is the intended grandfathering, not a gap. **Decision:** | HTTP? | Command/Read? | Merged with | Module | DB objects | Missing grants | |---|---|---|---|---|---| | No route — a property proven by tests against SP1 and SP6's write paths | Read (the guarantee itself is a read-time fact) | — | Platform | `plan_versions`, `plan_offerings`, `subscriptions` | None beyond what SP4/SP8/SP9 already need | #### Catalog capability rollout (migration `0075`, Pionero version 4) SP11 cuts both ways: grandfathering also means an existing subscriber does not gain a capability Ninaku adds later. Migration `0075` adds the capability `catalog.items` (`CATALOG_USE_CASES.md` §7.3) by publishing Pionero version 4 (every capability and limit of version 3, plus `catalog.items`) with its Colombia and Ecuador offerings at the same prices, and retiring version 3 and its offerings. No subscription moves. | Organization | `catalog.items` after the migration | |---|---| | Provisioned after the migration (version 4 offering) | `enabled` | | Subscribed before it (version 3 or older offering) | `disabled` until staff act; catalog reads and the ungated repair commands of §7.3 still work, the gated commands answer `403 · platform.capability_not_entitled` | **Rollout path for existing organizations**, one of two staff actions per organization, both audited: * **Change plan** (`POST /platform-admin/organizations/{organizationId}/subscription/plan-changes`, `platform.change_subscription_plan_as_staff`) to the version 4 offering of the organization's market. It ends the current subscription and starts a new `active` one with a new period from now, so it is the right action for a paying organization, not for one still in trial (it would end the trial). * **Grant an override** (`POST /platform-admin/organizations/{organizationId}/capability-overrides`, `platform.record_capability_override_as_staff`, effect `grant`, with a reason) of `catalog.items`. It keeps the subscription and its trial untouched and is the right action for a trialing organization; it stays until revoked or until it expires, so revoke it (`POST /platform-admin/capability-overrides/{overrideId}/revocation`) once the organization moves to version 4. Until one of them runs, the catalog is unavailable to that organization; nothing in the schema moves it automatically. #### Assets capability rollout (migration `0086`, next Pionero version) Migration `0086` (`ASSETS_STAGE_SPECS.md` stage A1b-1) adds the capability `assets.media` and the limits `assets.active_assets = 5000` and `assets.stored_megabytes = 10240` by publishing the next Pionero version (the highest version number of the plan plus one: version 5 on an installation that only has the seeded versions) with every capability and limit of the active version, and copies of its active offerings at the same terms, then retiring that version and its offerings. No subscription moves. | Organization | `assets.media` after the migration | |---|---| | Provisioned after the migration (new version offering) | `enabled`, with both limits | | Subscribed before it (older version offering) | `disabled` until staff act; it keeps its grandfathered terms, and the two limits have no row on its version, so they impose no ceiling | The rollout path is the one above: change plan to the new version's offering, or grant an override of `assets.media` and revoke it once the organization moves. Reading, renaming, archiving and restoring assets never need the capability (`ASSETS_STAGE_SPECS.md` §5.1). Restoring an archived asset re-checks `assets.active_assets` as an upload does (migration `0094`, stage A1b-3); an organization on an older version has no row for that limit, so a restoration there is never refused by it. *** ## 8. Out of scope ### SP12 — Billing and payment collection **Actor:** none — explicitly deferred. **Intent statement:** Platform models the *commercial contract* (plan, version, offering, subscription, amount, currency, billing interval) but never touches money movement. The schema's own header is explicit: "SaaS payment-provider integrations -> integrations" is listed under "This schema does NOT own." `plan_offerings.amount`/`currency_code` describe what should be charged; nothing in `0060_platform.sql` charges a card, calls a payment processor, or reconciles a payment result — that is `payments`'/`integrations`' job, neither of which is built. **Why this is stated as a case, not silently skipped:** the owner's use cases explicitly ask for it to be named as out of scope rather than assumed; `plan_offerings.amount = 0` for the free trial is what makes SP2 buildable today without any billing integration at all — a paid plan (SP2's third open question) is the first point where this boundary becomes load-bearing, since `subscriptions.status = 'past_due'` and `grace_until` exist in the schema specifically for a future billing integration to drive, and nothing today drives them. **Decision:** | HTTP? | Command/Read? | Merged with | Module | DB objects | Missing grants | |---|---|---|---|---|---| | None proposed | Neither — explicitly out of scope | — | Not owned by Platform; future `payments`/`integrations` work | `platform.subscriptions.status IN ('past_due', ...)`, `grace_until` exist but are unused by any write path today | N/A | *** ## 9. Platform administration and the super admin actor ### SP13 — Platform super admin as an actor **Actor:** a Ninaku owner/employee acting as platform staff — a principal that manages the commercial catalog and organization lifecycle across every tenant, never scoped to one organization's own RBAC. **Intent:** let a small, explicitly granted set of identities administer plans, prices, markets, subscriptions and organization status for any organization, while every existing tenant-scoped invariant (D21, RLS, `assert_operation_allowed`) stays exactly as strict as it is today for ordinary members. **Preconditions:** the caller holds a live session (the same `identity.sessions` mechanism as any human identity); a `platform.staff_members` row for that identity is `active` (new table, proposed below). **Invariants protected — why the schema has nothing like this today:** * `identity.identities.identity_kind` only allows `'human'`/`'service'` (`identities_kind_chk`, `0040_identity.sql:39-52`) — there is no third kind for platform staff, and adding one would be the wrong lever anyway: `identity_kind` describes *what kind of principal this is*, not *what it is allowed to do*, and every other authorization fact in the schema (roles, permissions, scopes) already lives in its own table rather than folded into a `CHECK` on `identities`. * `identity.role_assignments` requires `(membership_id, organization_id)` via `role_assignments_membership_organization_fk` — every role, even a "global" one (`roles.organization_id IS NULL`), is only ever *assigned* through a tenant membership. There is no tenant-independent role-assignment row anywhere in the schema. A platform admin is not a member of the organization they are about to suspend or re-price a plan for, so this table cannot express platform staff without weakening the FK that makes D21's own reasoning sound in the first place — reusing it is the wrong fix, not a missing grant. * `identity.effective_permission`, `identity.has_active_tenant_context()`, and `platform.assert_operation_allowed` all resolve through `identity.current_organization_id()`/`current_membership_id()` — they answer "can this membership do X inside this organization," a question meaningless for an actor who, by design, is not inside any one organization when re-pricing a plan or suspending an arbitrary tenant. * `identity.impersonation_sessions` requires `target_membership_id` + `organization_id` (`impersonation_sessions_target_membership_fk`) and is validated by `identity.validate_impersonation_session()` against that membership's own status — impersonation lets an actor act as one specific member of one specific organization, with that member's own permissions, never more. This is exactly why impersonation "grants no plan administration": there is no scope in which an impersonated session could ever reach a platform-admin-only function, because its own validation trigger ties it to a membership, and a membership is tenant-scoped by construction. * `platform.provision_organization`/`provision_organization_for_caller` already establish the pattern this case reuses: a `SECURITY DEFINER` function with `REVOKE ALL ... FROM PUBLIC`, `SET row_security = off`, resolving the caller from `identity.current_actor_identity_id()` (already populated pre-tenant, per `0050_security_rls.sql`) and checking a narrow, purpose-built condition internally, then `GRANT EXECUTE`d to `ninaku_runtime` alone. No new Postgres role is proposed, for the same reason SP3 gives: "the API runs only as `ninaku_runtime` — there is no separate narrow control-plane pool to isolate this call behind." **Why an organization owner can never obtain this.** `organization_owner`'s authority is `organization.account.manage`, granted through a `role_assignments` row `FOREIGN KEY (membership_id, organization_id)`-bound to exactly one tenant; nothing about that row, or about `assert_operation_allowed`'s composition, ever reads `platform.staff_members`. The two tables share no column, no trigger, no function. Even the org owner's own provisioning call narrows its own privilege on purpose — `provision_organization_for_caller` accepts no identity/role parameter precisely so a caller cannot name themselves into a wider role than the one seeded, scoped to the organization they just created. There is no code path, existing or proposed here, that lets an org-scoped grant widen into a platform-wide one; the only way into `platform.staff_members` is a row Ninaku itself inserts. **Proposed minimal model:** ```sql CREATE TABLE platform.staff_members ( id uuid PRIMARY KEY, identity_id uuid NOT NULL REFERENCES identity.identities(id), status varchar(20) NOT NULL DEFAULT 'active' CHECK (status IN ('active', 'revoked')), granted_at timestamptz NOT NULL DEFAULT now(), granted_by uuid REFERENCES identity.identities(id), revoked_at timestamptz, CHECK ( (status = 'revoked' AND revoked_at IS NOT NULL) OR (status = 'active' AND revoked_at IS NULL) ) ); CREATE UNIQUE INDEX staff_members_one_active_per_identity_uq ON platform.staff_members (identity_id) WHERE status = 'active'; ``` One tier only (`active`/`revoked`) — the owner's decision names a single persona, "Ninaku's owners," not a graded admin hierarchy; a role/permission table per staff action would be speculative schema against `API_AND_SCHEMA_DISCIPLINE.md` §6.4 until a second tier is a real requirement. Every new admin function (SP15-SP19) opens with the same guard, inside its own `SECURITY DEFINER`, `SET row_security = off` body — the same double-check discipline `provision_organization_for_caller` uses: ```sql IF NOT EXISTS ( SELECT 1 FROM platform.staff_members sm JOIN identity.identities i ON i.id = sm.identity_id AND i.status = 'active' WHERE sm.identity_id = identity.current_actor_identity_id() AND sm.status = 'active' ) THEN RAISE EXCEPTION 'Operation requires active platform staff' USING ERRCODE = '42501'; END IF; ``` **Authentication.** Reuses 100% of the existing sign-in surface (`identity.authentication_methods`, password/passkey/provider, `identity.sessions`) — a staff identity signs in exactly like any human identity, because it *is* one that additionally holds a `platform.staff_members` row. No new login route, no new credential type. **Authorization at the application layer.** A new `PlatformStaffGuard` (parallel to the existing tenant-context guard) checks the same fact before an admin use case runs; admin use cases open their transaction through `SystemTransactionRunnerService`, never `TenantTransactionRunnerService` — there is no single tenant to scope to. This is a concrete implication for `MODULE_CONTRACTS.md` R9: `SYSTEM_TRANSACTION_RUNNER_ALLOWED_DIRECTORIES` today whitelists only `src/modules/identity/application` (pre-tenant sign-in/registration); shipping SP13 means adding the platform module's admin application directory to that list, with the same rationale R9 already gives identity. **Bootstrapping the first staff member.** Exactly SP1's own precedent: no admin persona or route exists yet to create the first row, so it is a reviewed migration insert by `ninaku_migrator` naming a known identity, never a code path reachable by `ninaku_runtime`. A `POST /platform-admin/staff` route letting an *existing* staff member grant a new one is legitimate future work once more than one admin exists — not required to ship the single-tier model the owner describes today (open question, below). **Auditing.** Every admin write calls `audit.create_platform_record`/ `add_platform_target`/`add_platform_attribute`/`seal_platform_record` (all already `GRANT EXECUTE`d to `ninaku_runtime`, `runtime_grants.sql:84-89`) tagging the staff identity as actor — the exact mechanism `identity` already uses for its own platform audit trail via `PlatformAuditContract` (`src/modules/audit/contracts/platform-audit.contract.ts`, per `DOMAIN_MAP.md`'s `0380_audit` row), reused rather than reinvented. Platform staff writes are audit records exactly like any other platform-level write, distinguished only by `actor_identity_id` carrying a staff identity and the action/target codes naming an admin operation. **Adversarial/edge cases:** * A revoked staff member's session is still live (token not yet expired) — every admin function re-checks `platform.staff_members.status = 'active'` at call time, not at session-issue time, so revocation is effective immediately, not at next sign-in. * An organization owner is *also* granted `platform.staff_members` — nothing stops one identity from holding both an org-scoped role and a staff grant; the two authorities never compose (a staff function ignores `role_assignments` entirely, an org-scoped function ignores `staff_members` entirely), so this is two unrelated grants on the same person, not a privilege-escalation path — an operational caution, not a schema guard. * A staff function is called with an organization id that does not exist — refused by the function's own `FOUND` check, same pattern as `provision_organization_for_caller`'s offering/market lookups. * `ninaku_runtime` calls a staff-only function without an active `platform.staff_members` row — refused, `42501`, before any table is touched. **Decision:** | HTTP? | Command/Read? | Merged with | Module | DB objects | Missing grants | |---|---|---|---|---|---| | No route of its own — the authorization fact every route in SP15-SP19 depends on | Read (the check) | — | Platform (owns the platform-wide control plane exactly as it already owns markets/plans/subscriptions) | New `platform.staff_members` | New table; `ninaku_runtime` gets no direct grant on it — only `EXECUTE` on the staff-gated functions that read it internally, same pattern as `provision_organization_for_caller` | ### SP14 — List plans available in a country before signup **Actor:** anyone deciding whether to sign up — pre-tenant, and in practice pre-authentication too (a pricing page read before any account exists). This is the one actor in the whole catalog that need not hold a session. **Intent:** show plan name, price, currency, billing interval and trial days for a chosen country, so the signup form (O1/SP3) collects a real `plan_offering_id` before it ever calls `provision_organization_for_caller`. **Preconditions:** none — a country code is the only input, and it need not resolve to anything yet. **Invariants protected:** * Global catalog tables (`plans`, `plan_versions`, `markets`, `plan_offerings`) carry no RLS by design (SP4) — this route reads the same rows SP4 joins through, filtered to one market and `active` status, never a tenant-scoped table. * Never leaks a `draft`/`paused`/`retired` offering, plan, or version — the query filters `status = 'active'` at every join, the same filter `validate_plan_offering` enforces on the write side. **Main path:** 1. Caller requests `GET /plans?country=CO`, no session required. 2. Query resolves `platform.markets` by `country_code` (`status = 'active'`, else empty result, not `404`), then joins `plan_offerings` (`status = 'active'`, within `available_from`/`available_until`) → `plan_versions` (`status = 'active'`) → `plans` (`status = 'active'`). 3. Response returns plan name/description, offering amount/currency/billing interval, `trial_days`, per plan. **Adversarial/edge cases:** * Unknown or unsupported country code — empty list, not an error. * Market exists but is `planned`/`paused`/`retired` — empty list; a `beta` market's offerings are included only if the product decision is to show beta pricing publicly (open question, below). * A plan version is `active` with zero `active` offerings anywhere yet — the plan does not appear; a plan with no sellable price is not a real choice at signup. * Caller already holds a session and an organization — this route answers the same way regardless; it is not gated on "has no organization yet." **Decision:** | HTTP? | Command/Read? | Merged with | Module | DB objects | Missing grants | |---|---|---|---|---|---| | Yes — `GET /plans` (public, no session required) | Read model | SP1/SP4's own noted gap — this is that route, fully specified | Platform | `platform.plans`, `plan_versions`, `plan_offerings`, `markets` | **None** — `SELECT` on all four already shipped in Group 0 (`runtime_grants.sql:67,72`); only the NestJS route and its guard configured to skip authentication are new | ### SP15 — Create and manage plans, versions, prices, and markets (super admin) **Actor:** platform staff (SP13). **Intent:** replace SP1's "no admin persona exists, ship it as a migration" default with a real, staff-authorized surface for the owner's four named actions — create a plan; draft, publish and retire a version; add/change/ withdraw a per-country price; open a new market — while keeping every immutability/versioning guarantee SP1/SP11 already proved. **Preconditions:** SP13's staff check passes for every action below. **Invariants protected, and which trigger already makes each action safe:** * **Create a plan** — `plans_code_ci_uq` is the only guard needed; nothing else fires until a version exists. A duplicate code answers `422 platform.plan_version_command_refused` (`A plan with that code already exists.`), never a `500` (migration `0044`). * **Draft a version** — `plan_versions_state_chk` forces a fresh row to `draft` with no `published_at`/`retired_at`. `create_draft_plan_version_as_staff` locks the Plan row before it numbers the draft, so two concurrent drafts of one plan get two distinct numbers. * **Publish a version** — `publish_plan_version_as_staff` locks the Plan row and refuses, with `23514` named `plan_versions_another_version_active`, a publish while another version of the plan is active (`422 platform.plan_version_command_refused`); `plan_versions_one_active_per_plan_uq` stays the backstop and the repository maps its violation to the same refusal. `protect_plan_version_identity` freezes `published_at`/ `effective_from` the instant they are set. * **Retire a version** — **not already safe**, per SP1's own documented gap: retiring a version does not cascade to its offerings, since `plan_offerings_validate_trg` only fires when an offering row itself changes. **This function is where that gap finally closes**: `platform.retire_plan_version_as_staff` retires the version *and*, in the same transaction, transitions every `active`/`paused` offering under it to `retired` — turning SP1's "document this as an ops runbook step" into code that cannot be skipped, because retiring the version and retiring its offerings are now one atomic call. * **Add a per-country price** — always an `INSERT`, never an `UPDATE` of an existing offering: `protect_plan_offering_terms` already forbids changing `amount`/`currency_code`/`billing_interval_*`/`market_id`/`plan_version_id` once an offering leaves `draft`. "Change a price" *is* "add a new offering, then withdraw the old one" — the schema accepts no third option, and the function does not pretend otherwise. * **Withdraw a price** — `protect_plan_offering_terms`'s state machine (`active → paused/retired`, `paused → active/retired`) already refuses withdrawing a `draft` offering the wrong way, or resurrecting a `retired` one; the function only picks `paused` or `retired` and lets the trigger enforce the rest. * **Open a market** — `markets_operational_defaults_chk`/ `markets_launched_at_chk`/`markets_retirement_chk` already refuse an `active`/`beta` market with no default locale/currency or no `launched_at`; the function sets both together in one `INSERT`, so no intermediate invalid state is reachable. **Main path (staff-only `SECURITY DEFINER` functions, each `SET row_security = off`, each opening with SP13's staff check):** 1. `platform.create_plan_as_staff(p_code, p_name, p_description)` → `INSERT platform.plans`. 2. `platform.create_draft_plan_version_as_staff(p_plan_id)` → `INSERT platform.plan_versions (status='draft', ...)`. Attaching capabilities/ limits to the draft is next round's work (the owner's "WHAT each plan includes" catalog) — this function only creates the empty draft those attachments will plug into. 3. `platform.publish_plan_version_as_staff(p_plan_version_id, p_effective_from)` → `UPDATE ... SET status='active', published_at=now(), effective_from=coalesce(p_effective_from, now())`. 4. `platform.retire_plan_version_as_staff(p_plan_version_id)` → retires the version, then cascades to its offerings as described above. 5. `platform.create_plan_offering_as_staff(p_plan_version_id, p_market_id, p_currency, p_interval_unit, p_interval_count, p_amount, p_trial_days)` → `INSERT ... status='draft'`, then `platform.publish_plan_offering_as_staff(p_offering_id, p_available_from)` → `UPDATE ... SET status='active', available_from=...` — mirrors the plan-version draft/publish split. 6. `platform.withdraw_plan_offering_as_staff(p_offering_id, p_final boolean)` → `UPDATE ... SET status = CASE WHEN p_final THEN 'retired' ELSE 'paused' END`. 7. `platform.open_market_as_staff(p_country_code, p_default_locale_code, p_default_currency_code, p_status)` → `INSERT platform.markets (status = p_status, launched_at = CASE WHEN p_status <> 'planned' THEN now() END, ...)`. **Step-up:** the five commands that change what is sold, at what price and where (publish a version, retire a version, publish an offering, withdraw an offering, open a market) need a live elevation (`X-Elevation-Token`) on top of the staff session; without it they answer `403 platform.elevation_required` and change nothing. Creating a plan, drafting a version and drafting an offering do not need it. Proven in `test/e2e/platform-admin-catalog-elevation.e2e-spec.ts`. **Adversarial/edge cases:** * Two staff calls race to publish two different versions of the same plan `active` at once — both serialize on the Plan row, exactly one wins and the loser is refused (`422 platform.plan_version_command_refused`) with no unique violation, never a duplicate active version. * A staff member retires a version with zero offerings — the cascading `UPDATE` affects zero rows; not an error. * A staff member publishes an offering whose plan version retired between the draft call and the publish call — refused by `validate_plan_offering`'s existing "requires active Plan and Plan Version" check, exactly as it already refuses this for any caller today. * A staff member opens a market for a country `reference.countries` does not know — refused by `markets_country_fk`. * A staff member creates an offering for a market, a currency or a target organization that does not exist — each failure names its constraint (`plan_offerings_market_fk`, `plan_offerings_currency_fk`, `plan_offerings_target_organization_fk`) and the API answers `422 platform.plan_offering_command_refused` with a detail naming only the missing reference; an unknown plan version stays a `404`. See the case matrix for the proving tests. * A staff member opens a second `active` market for a country that already has one — refused by `markets_country_uq`; there is no "two markets, same country" concept. **Decision:** | HTTP? | Command/Read? | Merged with | Module | DB objects | Missing grants | |---|---|---|---|---|---| | Yes — `POST /platform-admin/plans`, `.../plans/{id}/versions`, `.../plan-versions/{id}/publish`, `.../plan-versions/{id}/retire`, `.../plan-versions/{id}/offerings`, `.../offerings/{id}/publish`, `.../offerings/{id}/withdraw`, `.../markets` | Command | — | Platform | `platform.plans`, `plan_versions`, `plan_offerings`, `markets` | `ninaku_runtime` has **no write grant at all** on any of these four tables today (Group 0 only granted `SELECT`) — seven new `GRANT EXECUTE`s, one per function above, no raw table grant and no new RLS policy (none of these tables carry RLS; they are global catalog, not tenant data) | ### SP16 — Assign a plan, extend a trial, or change an organization's plan (staff override) **Actor:** platform staff (SP13) — the same commercial transition SP6 protects, executed by someone with no membership in the target organization at all. **Intent:** let staff move any organization onto a different offering, or push out its trial deadline, without requiring the organization to hold `organization.account.manage` or ask for it — a support-negotiated extension, a manually agreed upgrade, a sales-assisted plan change. **Preconditions:** SP13's staff check; the target organization exists; for a plan change, the target offering is `active` (identical to SP6). **Invariants protected:** exactly SP6's own — `subscriptions_one_current_per_organization_uq`, `protect_subscription_identity` — plus one structural difference: **the function takes `p_organization_id` as an explicit parameter, never `identity.current_organization_id()`**, because staff has no tenant context to read one from. **Main path:** 1. `platform.change_subscription_plan_as_staff(p_organization_id, p_new_plan_offering_id)` — the same conditional end-and-insert pair SP6 uses, scoped by the explicit parameter instead of a GUC. 2. `platform.extend_subscription_trial_as_staff(p_organization_id, p_new_trial_ends_at)` — `UPDATE platform.subscriptions SET trial_ends_at = p_new_trial_ends_at WHERE organization_id = p_organization_id AND ended_at IS NULL AND status = 'trialing'`. Nothing protects `trial_ends_at` today (`protect_subscription_identity` only guards `organization_id`/ `plan_offering_id`/`starts_at`), so this is direct and already safe once the caller is authorized — `subscriptions_trial_chk` is the only constraint in play, and extending forward never violates it. 3. `platform.assign_plan_as_staff(p_organization_id, p_plan_offering_id)` — for an organization with no current subscription (SP7's immediate cancellation, or a lapsed-and-blocked one from SP18) — a plain `INSERT`, guarded by `validate_subscription_creation` exactly as SP3's insert already is. **Adversarial/edge cases:** * Staff extends a trial that is not currently `trialing` — the function's own `WHERE status = 'trialing'` matches zero rows; refused as a no-op, not silently applied to the wrong lifecycle stage. Extending an `active` period is a different operation (a manual renewal, SP19). * Staff assigns an offering from a different country's market than the organization's own — **not refused by `validate_subscription_creation`**, which never checks market/country agreement (only SP3's self-service function does). A real gap for the staff path specifically — flagged as an open question below rather than assumed either way. * Two staff members race to change the same organization's plan — identical to SP6's conditional-`UPDATE` concurrency answer. * Two staff calls with different Idempotency-Keys race to assign a plan to one organization with no current Subscription — `assign_plan_as_staff` locks the Organization row first, so exactly one creates the Subscription and the other is refused (`422 platform.subscription_command_refused`), never a `500`. * Staff assigns a plan to an `archived` organization — refused by `validate_subscription_creation`'s existing organization-status check, unchanged from SP3. **Decision:** | HTTP? | Command/Read? | Merged with | Module | DB objects | Missing grants | |---|---|---|---|---|---| | Yes — `POST /platform-admin/organizations/{id}/subscription/plan-changes`, `.../trial-extensions`, `.../assignments` | Command | Shares its write surface with SP6/SP7, never their route | Platform | `platform.subscriptions` | Same underlying gap as SP6/SP7 (only `SELECT` exists) plus three new `GRANT EXECUTE`s, each internally staff-gated instead of `current_organization_id()`-gated | ### SP17 — Suspend and reactivate an organization (super admin) **Actor:** platform staff (SP13) — this is Organization's O6 case, revisited under a real actor: O6 itself leaves the caller as "a product decision"; this case closes that question with SP13's model. **Intent:** move the organization between `active` and `suspended` without deleting it, from an actor outside the tenant entirely. **Preconditions:** SP13's staff check; for reactivation, D21's guarantee must already be satisfiable. **Invariants protected — already fully enforced, no new trigger needed:** * `organizations_status_chk` and D21's `assert_administration_preserved` (`0400_cross_domain_contracts.sql`, nine deferred triggers, `DECISIONS.md#d21`) fire on the `UPDATE` regardless of who runs it — a staff-authored `UPDATE organization.organizations SET status = 'active'` is exactly as guarded against reactivating an administrator-less organization as O6's own member-authored path. D21 is column-level, not caller-level, by design. * Precedent for a staff function writing into another module's owning schema already exists: `platform.provision_organization` itself inserts into `organization.organizations`, `business_units`, `identity.memberships`, etc. from within `platform`'s own `SECURITY DEFINER` function, because it is a control-plane composition, not a domain write claimed by `platform`. `platform.suspend_organization_as_staff`/`reactivate_organization_as_staff` follow the identical precedent — they live in `platform`, not `organization`, even though the column they touch belongs to `organization`. **What "blocked" means for the organization's users while suspended.** `platform.effective_capability`'s first check — `organization.status <> 'active'` returns `disabled` immediately, before the capability lookup runs — already fails every capability-gated write closed the moment `status = 'suspended'`, with zero new code. What is *not* already answered: `identity.has_active_tenant_context()` checks `o.status = 'active'` as part of its own `EXISTS` (O3), so a suspended organization's members cannot even establish tenant context today — not even for a read. **Recommendation (open question, below):** keep that full refusal as the default; a "billing read-only" mode is new application-layer work layered on top, exactly like SP5's own read-only-mode question, not required to ship this case. **Main path:** 1. `platform.suspend_organization_as_staff(p_organization_id, p_reason)` → locks the current Subscription row (`FOR UPDATE`), then `UPDATE organization.organizations SET status = 'suspended', suspension_source = 'staff' WHERE id = p_organization_id AND status = 'active'`; records `p_reason` via `audit.create_platform_record`, not a new column. 2. `platform.reactivate_organization_as_staff(p_organization_id)` → locks the current Subscription row, refuses while that Subscription is `suspended`, then `UPDATE ... SET status = 'active', suspension_source = NULL WHERE id = p_organization_id AND status = 'suspended'`; D21's deferred triggers evaluate at commit as they always do. 3. `platform.archive_organization_as_staff` takes the same Subscription lock first and clears `suspension_source`. **Why the column and the lock (migration `0045`).** `organization.organizations.suspension_source` (`staff` or `subscription`, `NULL` unless the organization is suspended) records who suspended it, so a payment can tell a lapse from a staff decision. Every writer of the organization's status takes the Subscription row first and the Organization row second (the order the payment and the grace-expiry sweep already used), so no two of them can wait on each other. The migration backfilled existing suspended rows as `subscription` when the current Subscription was `suspended` and as `staff` otherwise, the conservative choice: staff must reactivate those. **Adversarial/edge cases:** * Reactivating a suspended organization whose current Subscription is `suspended` — refused (`23514`, `organization_reactivation_subscription_suspended`, HTTP `422` with the audited reason `subscription_suspended`): a payment restores the entitlement first, and an organization suspended by a lapse is reactivated by that payment. A staff-suspended organization whose Subscription is not suspended reactivates as before. * Reactivating a suspended organization that lost its last administrator while suspended — refused at commit by D21, identical to O6's own documented case. * Suspending an organization mid-provisioning (no completed `organization_provisioning_requests` row) — the conditional `WHERE status = 'active'` still matches (provisioning already sets `status = 'active'` before completing); O6 already flags this as a state that should not exist yet — a staff-side pre-check is recommended (open question, below), but nothing in `0400_cross_domain_contracts.sql` ties provisioning completion to suspension eligibility today. * Staff suspends an already-`suspended` organization — the conditional `UPDATE` matches zero rows; recommend an idempotent no-op `200`. * Staff suspends an `archived` organization — matches zero rows; archived stays archived, unchanged from O6. **Decision:** | HTTP? | Command/Read? | Merged with | Module | DB objects | Missing grants | |---|---|---|---|---|---| | Yes — `POST /platform-admin/organizations/{id}/suspension`, `.../reactivation` | Command | Same underlying column as O6; a distinct actor and route | Platform (control-plane composition; column owned by Organization) | `organization.organizations`, transitively D21's triggers | `ninaku_runtime` already has `UPDATE` on the profile and status columns of `organization.organizations` (`runtime_grants.sql`, a column-level grant that leaves out `suspension_source`) — **this corrects a stale note in `ORGANIZATION_USE_CASES.md` O6**, written before that grant shipped; only two new `GRANT EXECUTE`s are missing | *** ## 10. Renewal lifecycle and payments ### SP18 — Renewal lifecycle: trial to active, grace, automatic block, automatic reactivation **Actor:** the system (a scheduled job, running as a `service`-kind identity — `identity.identities.identity_kind = 'service'` already exists for exactly this) for every lapse transition; platform staff (SP13) only for recording the payment that ends a lapse (SP19). **Intent:** apply the owner's renewal policy uniformly: **trialing → (paid) active → period renewal; non-payment (trial end or period end) → 3 days of grace (still works) → automatic block; a payment → automatic reactivation.** This revises SP5's original recommendation (hard block, no grace), reconciled in SP5's own text above. **Preconditions:** a subscription reaches `trial_ends_at` (trialing) or `current_period_end` (active) with no successor action; or a `past_due` subscription's `grace_until` passes with no payment recorded. **Invariants protected, and the schema gap this case resolves without an `ALTER TABLE`:** * `validate_subscription_transition` already allows every edge this design needs: `trialing → past_due`, `active → past_due`, `past_due → suspended`, `past_due → active`, `suspended → active` — none are new; the design only decides which already-legal edge the job takes, and when. * **Case A — a paid period lapses (already fully supported today).** `active → past_due`, `SET grace_until = current_period_end + interval '3 days'`. `subscriptions_grace_chk` is already satisfied, because an `active` row already has `current_period_end` set (`subscriptions_entitling_period_chk`). **No schema change needed for this branch** — exactly what SP5's own text already says: "grace is a paid-period concept only." * **Case B — a trial lapses (the case the brief names as needing a schema change — it does not).** A `trialing` row has `current_period_start`/ `current_period_end` both `NULL` (`provision_organization` never sets them for a trial), so it genuinely cannot hold `grace_until` while it stays `trialing` — that much of SP5's original text is exactly right. But grace does not need to live on the `trialing` row. The job's single `UPDATE` transitions `trialing → past_due` **and**, in the same statement, backfills `current_period_start = starts_at`, `current_period_end = trial_ends_at`, `grace_until = trial_ends_at + interval '3 days'`. Every `CHECK` is evaluated against the *new* row, where `status = 'past_due'` and `current_period_end IS NOT NULL` both already hold — `subscriptions_grace_chk` and `subscriptions_entitling_period_chk` pass by construction. `protect_subscription_identity` never protects `current_period_start`/ `current_period_end` (only `organization_id`/`plan_offering_id`/ `starts_at`), so this `UPDATE` needs no trigger change either. **Verified against every trigger in `0060_platform.sql` that touches these columns — no `ALTER TABLE` is required.** An explicit `is_trial_period` marker column was considered and rejected: it would duplicate information the row's own history (`starts_at`/`trial_ends_at`, still present and immutable) already carries, against `API_AND_SCHEMA_DISCIPLINE.md` §6.4's ban on speculative columns. * `platform.subscription_is_entitling`'s `past_due` branch — `current_period_start <= at AND at < grace_until` — is what actually grants the 3 grace days in both cases, unchanged, because both cases now supply the columns it already reads. * **Automatic block** is `past_due → suspended`, already a legal edge; `subscription_is_entitling` has no branch at all for `suspended` (only `trialing`/`active`/`past_due` are matched), so the moment `status = 'suspended'`, entitlement is `false` with zero new code — the same half-open-window property SP5 already names for trial expiry, now extended uniformly to every lapse. * **Automatic reactivation** on payment is `past_due → active` or `suspended → active` (SP19 performs the `UPDATE`) — both already legal edges. The Organization follows only when the lapse suspended it (`suspension_source = 'subscription'`, which `apply_grace_expiry` writes). **Idempotency and concurrency of the scheduled job.** This is not key-based retry like `organization_provisioning_requests` (that guards a *caller's* retry of one request); here, idempotency comes from the job's own `WHERE` clause re-stating every precondition on each run — e.g. `UPDATE ... SET status='past_due', ... WHERE status='trialing' AND trial_ends_at <= now()`. A row already transitioned no longer matches that `WHERE` clause, so a second or overlapping run is a no-op for it — the same conditional-`UPDATE` concurrency pattern SP6 already established, reused for a background actor instead of an HTTP caller. **Interaction with `subscription_is_entitling` — the job is reporting hygiene, not a security boundary.** Because every branch above is timestamp-driven, a *late* job run never over-grants access: if `current_period_end` has passed but the job has not yet flipped `active → past_due`, `subscription_is_entitling`'s `active` branch (`p_at < current_period_end`) already evaluates `false` on its own — the row is correctly non-entitling *before* `status` catches up, exactly as SP5's original text already argues for trial expiry. The job's real job is to make `status` (and every admin-panel/reporting view, SP4's read model, SP17's edge cases) agree with what entitlement math has already been computing all along. **Main path:** 1. A scheduled job (the same `@nestjs/schedule` + advisory-lock template `SubscriptionLifecycleSweepJob` already ships — periodic PostgreSQL housekeeping, not outbox dispatch; the outbox itself is a PostgreSQL queue drained by a separate worker service, per `AGENTS.md` §5.4 and [`OUTBOX_USE_CASES.md`](OUTBOX_USE_CASES.md), never BullMQ) runs periodically as a `service`-kind identity and calls one new function per lapse kind: `platform.apply_trial_lapse()`, `platform.apply_period_lapse()`, `platform.apply_grace_expiry()` — each a plain `SET`-based `UPDATE` with a `WHERE` clause re-stating every precondition, no per-row loop needed in SQL. 2. Each function requires no staff check — a `service`-kind caller is not staff and does not need to be; this is system housekeeping, not an administrative decision. 3. `SystemTransactionRunnerService` opens the job's transaction (no tenant context exists for a cross-organization sweep) — the same R9 whitelist addition SP13 already needs. **Adversarial/edge cases:** * A subscriber changes plan (SP6) or gets a staff-assisted extension (SP16) between two job runs — the job's `WHERE` clause no longer matches that row; no lapse is ever misapplied to a row that moved on. * Payment recorded exactly at the `grace_until` boundary — SP19's `UPDATE` and the job's `apply_grace_expiry()` race the same row exactly like SP6's plan-change race; whichever commits first wins, the other's conditional `WHERE` matches zero rows. The job suspends the Organization in the same statement and only for the Subscriptions that statement suspended, so a payment that wins the row never leaves the Organization suspended under an `active` Subscription. A payment that loses this race is not lost — it is still recorded (SP19's payment row is append-only, independent of the subscription's `status`): it waits for the Subscription row, then renews the Subscription and reactivates the Organization the sweep suspended. * A subscription has no current row at all (SP7's immediate cancellation) — no job function's `WHERE` clause ever matches a row that does not exist; nothing to lapse. **Decision:** | HTTP? | Command/Read? | Merged with | Module | DB objects | Missing grants | |---|---|---|---|---|---| | No route — a scheduled job, three new functions | Command (three housekeeping transitions) | Shares its mechanism with SP5 (supersedes SP5's own placeholder note) | Platform | `platform.subscriptions` | Three new `GRANT EXECUTE`s to `ninaku_runtime`, callable only by the `service`-kind job identity in practice — no staff check, but nothing stops another caller from invoking them, since they carry no caller-specific data; an acceptable exposure, unlike SP15-SP17's staff-only functions | ### SP19 — Record a payment **Actor:** platform staff (SP13) today; a payment gateway webhook handler tomorrow (same underlying step, different caller). **Intent:** record that money was collected against a specific subscription's lapsed period, and drive the same automatic reactivation SP18 names, without building any billing integration — exactly the boundary SP12 already draws. **Preconditions:** a subscription in `past_due` or `suspended` (or, for an ordinary renewal, `active` approaching `current_period_end`) exists for the target organization. **Invariants protected:** * SP12's boundary stays exactly where it is — this case never calls a payment processor, never touches a card, never reconciles a provider result. It only records the fact "an amount was collected, staff attests to it" and applies its consequence to `platform.subscriptions`. * Payment records are audit-grade: insert-only, never edited, never deleted, `created_at` immutable — the discipline `API_AND_SCHEMA_DISCIPLINE.md` §6.2 already requires. **Proposed new object** (real schema work for the owner's next round, named here only so the lifecycle above has somewhere to end): ```sql CREATE TABLE platform.subscription_payments ( id uuid PRIMARY KEY, subscription_id uuid NOT NULL, organization_id uuid NOT NULL REFERENCES organization.organizations(id), amount numeric(19,6) NOT NULL CHECK (amount > 0), currency_code varchar(3) NOT NULL REFERENCES reference.currencies(code), recorded_by uuid NOT NULL REFERENCES identity.identities(id), external_reference varchar(200), note varchar(500), created_at timestamptz NOT NULL DEFAULT now(), CONSTRAINT subscription_payments_subscription_fk FOREIGN KEY (subscription_id, organization_id) REFERENCES platform.subscriptions (id, organization_id) ); ``` The composite foreign key (migration `0048`) keeps a payment from naming the Subscription of another Organization. `external_reference` is the one column that anticipates the gateway without building it — nullable and never required. The manual path may populate it (shipped by migration `0047`): staff attach the id they read on the bank transfer or the processor receipt, and the same reference is never recorded twice. **Main path:** 1. `platform.record_subscription_payment_as_staff(p_organization_id, p_amount, p_currency_code, p_note, p_external_reference)` — SP13's staff check, then `INSERT platform.subscription_payments`. 2. In the same transaction, applies the renewal, with three rules that all follow from one principle: a payment never makes a Subscription less entitled than it was, and never leaves a paid Subscription without access. `grace_until = NULL`, `status = 'active'` and the new `current_period_end` is always `anchor + billing_interval`, where the anchor is the old `current_period_end`, except after a long lapse (rule c). a. **Early payment, period still live** (`current_period_end` is in the future, the usual manual payment of an `active` Subscription): `current_period_start` does not move and one interval is added at the end. The Subscription stays entitled every instant, the customer gets the time they paid for after the current period, and two early payments in a row add two intervals without ever opening a gap. Starting the new period at the old end, which is in the future, would make `current_period_start <= now()` false and cut the access the customer already had until that end. b. **Payment inside grace** (`past_due`, old end already in the past but the renewed period still reaches `now()`): the new period starts at the old `current_period_end`, never at `now()`, so the contract's own clock is kept and no time is lost or gifted. c. **Payment after a long lapse** (old end plus one interval is not after the payment): the renewed period would still be entirely in the past and the customer would pay and still have no access. The new period is anchored at the payment instant, so the customer gets one full paid interval from the payment and the arrears before it are forgiven. The alternative, requiring the whole arrears, needs a commercial decision and is not taken here. A payment that would still leave the Subscription without access is refused (`23514`, `subscription_payment_not_entitling`) before the Organization is reactivated and before anything is kept. 3. This is the one call that turns `past_due`/`suspended` back to `active` — automatic reactivation, from the row's own point of view. It reactivates the Organization only when it is `suspended` with `suspension_source = 'subscription'`; an Organization suspended by staff stays suspended after the payment and staff reactivate it explicitly. **How a future gateway plugs in without changing the lifecycle.** A gateway webhook lands as a durable outbox/inbox event (the same async pattern `AGENTS.md` §5.4 already names for external integrations) and its handler calls the *same* underlying "apply a successful payment" step above — only the caller changes, from a staff HTTP request to an automated webhook handler in a future `payments`/`integrations` module (D18's contract composition, not a new lifecycle). `recorded_by` would then carry a `service`-kind identity representing the gateway integration instead of a staff identity — the column's meaning does not change, only who is allowed to be it. **Adversarial/edge cases:** * Staff records a payment for an organization with no `past_due`/ `suspended`/`active`-nearing-renewal subscription at all — refused; there is no lapse to resolve and no period to extend. * Staff records an amount or currency that does not match the offering the Subscription is on (a partial payment, a discount, another currency) — refused before anything is written: a manual staff payment is a full-period payment, so the amount must equal the offering's price and the currency the offering's currency (`numeric` comparison; shipped by migration `0041`). The refusal is `422 · platform.organization_command_refused` and the refused attempt leaves a `denied` platform audit record with the reason `payment_amount_mismatch` or `payment_currency_mismatch`. * Two staff members record two payments for the same lapsed period, with no external reference — both rows persist; the second reactivation `UPDATE` is a no-op once the first already moved `status` to `active` — not an error, a redundant but honestly recorded payment row. A payment without a reference is never deduplicated. * The same real-world payment is registered twice with the same external reference, even under two different `Idempotency-Key`s (idempotency only protects one HTTP operation, not one real-world fact) — the second is refused before anything is written: no payment row, no renewal, no reactivation. The comparison is `lower(btrim(reference))` across every payment, backed by the partial unique index `subscription_payments_external_reference_ci_uq`; the function checks first, so the normal case is a clean refusal, and the index settles a race between concurrent payments, whose unique violation the repository maps by constraint name. Over HTTP it is `422 · platform.organization_command_refused` with a `denied` platform audit record carrying the reason `payment_already_recorded`, and the `Idempotency-Key` stays free. The reference is global: the model has no payment source, so two sources that issue the same reference would collide (a known simplification), and it is optional, never required. * A payment is recorded exactly as SP18's `apply_grace_expiry()` job runs against the same row — ordinary conditional-`UPDATE` concurrency, identical to SP6/SP18's own race answers. **Decision:** | HTTP? | Command/Read? | Merged with | Module | DB objects | Missing grants | |---|---|---|---|---|---| | Yes — `POST /platform-admin/organizations/{id}/subscription/payments` | Command | Drives the same transition SP18's job would otherwise leave to time alone | Platform | New `platform.subscription_payments`; `platform.subscriptions` | New table (needs its own migration, out of this docs-only round); reachable only through the one new `GRANT EXECUTE` on `record_subscription_payment_as_staff` | *** ## Derived API (proposal) Grouped by family, with the cases each route serves. Every route follows `API_AND_SCHEMA_DISCIPLINE.md`'s task-oriented rule. Three route groups never share a guard: **public** (no session), **tenant** (a member's own organization, gated by tenant context), and **platform-admin** (staff-only, no tenant context at all). ### Public and tenant routes | Group | Routes | Cases served | |---|---|---| | Plan catalog (public read) | `GET /plans` (active plans + their active offerings, optionally filtered by market; no session required) | SP1, SP2, SP14 | | Provisioning | `POST /organizations` (Organization's existing route; now calls `platform.provision_organization_for_caller`) | SP3 | | Subscription (read) | `GET /subscription` | SP4, SP5 | | Subscription (write) | `POST /subscription/plan-changes`, `POST /subscription/cancellation` | SP6, SP7 | | Entitlement (internal) | no route — `EntitlementContract`, `LimitContract` | SP8, SP9 | | Authorization (internal) | no route — `OperationAuthorizationContract` (shared with Organization O34) | SP10 | | Versioning | none — an invariant proven by tests, not a route | SP11 | | Billing | none proposed | SP12 | ### Platform-admin routes (staff-only) | Group | Routes | Cases served | |---|---|---| | Staff actor (internal) | no route — the authorization fact every row below depends on | SP13 | | Plan/price/market administration | `POST /platform-admin/plans`, `.../plans/{id}/versions`, `.../plan-versions/{id}/publish`, `.../plan-versions/{id}/retire`, `.../plan-versions/{id}/offerings`, `.../offerings/{id}/publish`, `.../offerings/{id}/withdraw`, `.../markets` | SP15 | | Subscription overrides | `POST /platform-admin/organizations/{id}/subscription/plan-changes`, `.../trial-extensions`, `.../assignments` | SP16 | | Organization lifecycle | `POST /platform-admin/organizations/{id}/suspension`, `.../reactivation` | SP17 | | Organization archival (one-way, elevation guarded) | `POST /platform-admin/organizations/{id}/archival` | O6 in `ORGANIZATION_USE_CASES.md`, same staff-command shape as SP17 | | Renewal lifecycle (system) | no route — a scheduled job | SP18 | | Payments (manual today, gateway-ready tomorrow) | `POST /platform-admin/organizations/{id}/subscription/payments` | SP19 | *** ## Implementation plan (staging-sized groups) Each group closes a self-contained slice and is small enough to land on `staging` on its own (≤ ~400 changed lines per `AGENTS.md`). | Group | Cases | Why this boundary | |---|---|---| | **0 — grants and seed data (blocking Organization O1) — SHIPPED, revision 28** | SP1 (seed mechanism), SP2, SP3 | **This is the group that unblocked Organization O1, and nothing else did.** Migration `0018_the_caller_subscribes_to_a_real_plan_offering.sql` shipped: `platform.markets` rows for Colombia and Ecuador (the owner's own example; more countries are a later data-only migration), the real `pionero` `plans`/`plan_versions`/`plan_offerings` rows (SP2 — a 30-day free trial via `trial_days` on a real, priced Offering, never a separate trial Plan), the missing `identity.permissions('organization.account.manage')` + one global `identity.roles('organization_owner')` row + `identity.role_permissions` link (previously only test fixtures seeded these), the new `platform.provision_organization_for_caller` function narrowed to the caller (SP3), its `GRANT EXECUTE ... TO ninaku_runtime`, and `GRANT SELECT ON platform.plans, platform.plan_versions, platform.markets TO ninaku_runtime`. `database/tests/prepare/prepare-database-contracts.ts` proves `ninaku_runtime` can call the new function for itself per country/currency, that an offering from another country or an inactive offering is refused, that no partial garbage survives a refused attempt, and that `organizationRuntimeCannotProvisionOrganizations` keeps passing unchanged against `platform.provision_organization`. | | **1 — read the catalog and my subscription** | SP1 (read half), SP4 | Depends only on Group 0's grants; `GET /plans` and `GET /subscription` are pure reads over already-existing (post-Group-0) tables. | | **2 — trial expiry semantics** | SP5 | Mostly already true by construction (`subscription_is_entitling`'s half-open window); this group is the scheduled-job decision and the tests proving the SQL-level block, not new schema. | | **3 — change plan and cancellation** | SP6, SP7 | New `SECURITY DEFINER` functions `platform.change_subscription_plan`, `platform.request_subscription_cancellation`, each reading `identity.current_organization_id()` internally — avoids granting raw `INSERT`/`UPDATE` on `platform.subscriptions` to `ninaku_runtime` (today only `SELECT` exists) and avoids a new tenant-write RLS policy, consistent with the schema's own "control-plane writes use a narrow privileged service role" comment. Depends on Group 1. | | **4 — entitlement and authorization contracts** | SP8, SP9, SP10 | Application-layer `EntitlementContract`/`LimitContract`/`OperationAuthorizationContract` wrapping functions already `GRANT EXECUTE`d today; ships once a first real consuming module needs it — same timing note `ORGANIZATION_USE_CASES.md` O34 already makes. | | **5 — versioning/grandfathering tests** | SP11 | Proves the existing immutability triggers behave as documented; no new grants, no new code beyond tests. | | **6 — billing** | SP12 | Explicitly deferred; no group scheduled. | | **7 — platform staff actor and audit wiring** | SP13 | New `platform.staff_members`, the staff-check helper every later group reuses, the `PlatformStaffGuard`, and the `SYSTEM_TRANSACTION_RUNNER_ALLOWED_DIRECTORIES` addition for the platform module's admin application directory. Blocks Groups 9-11 and 13; does not block Group 8 or 12. | | **8 — public plan catalog read** | SP14 | Depends only on Group 0's grants (already shipped); independent of Group 7 — a pure public read with no staff involved. | | **9 — plan/version/price/market administration** | SP15 | Seven new staff-gated functions and their `GRANT EXECUTE`s; closes SP1's version-retirement-cascade gap as part of the same work. Depends on Group 7. | | **10 — staff subscription overrides** | SP16 | Three new staff-gated functions mirroring SP6/SP7's own write shape with an explicit `p_organization_id` instead of tenant-context resolution. Depends on Group 7. | | **11 — organization suspension/reactivation by staff** | SP17 | Two new staff-gated functions over an already-granted column (`organization.organizations.status`); D21 already protects reactivation with no new trigger. Depends on Group 7. | | **12 — renewal-lifecycle scheduled job** | SP18 | Three new functions, no staff check (system/service caller only), the `@nestjs/schedule` repeatable job, and the same R9 allowlist addition Group 7 needs for its own module directory — can ship independently of Groups 9-11, but shares Group 7's `SystemTransactionRunnerService` wiring. | | **13 — payment recording and automatic reactivation** | SP19 | New `platform.subscription_payments` table (this revision's only new table besides SP13's `staff_members`) and the one function that both records a payment and applies the renewal SP18's job would otherwise leave to a lapse. Depends on Groups 7 and 12 (reads the same lapse state the job produces). | *** ## Open product questions Each needs a real decision from the owner; a recommendation is named so none of Groups 7-13 above is blocked waiting for it. | Question | Recommendation | |---|---| | Does `GET /plans` (SP14) require any session, or is it fully public/pre-authentication? | Fully public — a pricing page a visitor reads before deciding to sign up needs no session, and the route already needs none technically. | | Does a `beta`-status market's pricing appear on the public catalog (SP14)? | No — only `active` markets, until a product decision explicitly wants a public beta-pricing page. | | Is a single staff tier (`active`/`revoked`, SP13) enough, or does the owner want graded admin permissions from day one? | Single tier — matches "Ninaku's owners" exactly; extend only when a second tier is a real, named need. | | Should a `POST /platform-admin/staff` route exist now, or does bootstrapping stay migration-only (SP13)? | Migration-only for the first row (SP1's own precedent); a self-service grant route is legitimate once more than one admin exists. | | Can staff assign an organization a plan offering from a market outside its own country (SP16)? | Allow it, but require an explicit confirmation flag on the command — staff overrides exist precisely for negotiated exceptions the self-service path correctly refuses. | | Does a suspended organization's members get a "billing read-only" mode, or does suspension keep refusing tenant context outright, as it does today (SP17)? | Keep today's full refusal as the default for both suspension reasons (policy and billing); build read-only mode only if support demand names a concrete need. | | Should suspending an organization mid-provisioning be blocked by an explicit pre-check, or left as a reachable-but-meaningless edge case (SP17)? | Add the pre-check — it is a cheap `EXISTS` on a completed provisioning request and closes a corner O6 already flags as "should not exist yet." | | Does a staff-recorded payment that does not match the offering's price still fully reactivate the subscription, or only pro-rate the period (SP19)? | Neither: a staff-recorded payment must equal the offering's price and currency, otherwise it is refused and nothing is written (migration `0041`); pro-rating and partial payments are deferred to when a gateway can compute them precisely. |