Facility access lock on expired or downgraded plans (#242)

Implementation notes for locking facility overflow to read-only instead of tolerating it. Supersedes the design sketch in the issue.

Decisions

• Subscription status does not decide the lock; the plan does. A facility is read-only when its organization holds more facilities than its current plan allows. Expiry only matters because it reverts the plan to STARTER. This keeps one rule for expiry, self-serve downgrades and super-admin changes.
• ENTERPRISE is never swept automatically. StartTermEndGrace and ExpireEndedSubscriptions go from "only max_facilities = 1" to "everything except ENTERPRISE". Enterprise terms end when a super admin lets them, not on a scheduler tick.
• Reads never break. Locked facilities stay listed, switchable, readable and exportable. Only non-idempotent verbs are refused.
• Exactly one facility is covered while over the limit. It survives downgrades via organizations.primary_facility_id, so a customer's working facility does not silently move.
• Feature gates are unchanged. GOVERNANCE, UTILIZATION, ORG_REPORTING and CROSS_FACILITY_TRANSFERS still 403 on a plan that excludes them, reads included. #242 does not split gates per verb.
• SubscriptionAccessMiddleware is untouched. It already allows EXPIRED and only gates /tenant, so the org dashboard and billing stay reachable.
• Transfers out of a locked facility are allowed. The destination must be writable; the source may be locked. Without this, a downgraded customer cannot consolidate devices into their covered facility. This is the one deliberate write exception.

Non-goals

• No overage billing (there is no payment method after non-payment).
• No data deletion, hiding, or truncation.
• No change to create-time quotas for users, facilities or device records.
• No per-verb feature gating (governance reads stay closed on Starter).

---

Slice 0 — make renewal reachable again

Prerequisite. Today an EXPIRED organization cannot pay to come back:

• ExpireEndedSubscriptions sets plan_id to STARTER and clears subscription_code.
• ReactivateSubscription accepts EXPIRED but then checks out org.PlanID — now STARTER, price 0 — and fails with "this plan does not require payment".
• ChangePlan rejects EXPIRED with "subscription is not eligible for plan change".
• AppSidebar.vue hides the whole Finance group (including Billing) when the plan is STARTER, so the entry point is gone as well.

Changes:

1. billing/service/billing.go — ChangePlan accepts plans.StatusExpired in addition to ACTIVE and PENDING_ACTIVATION. changeToFreePlan already handles the free-target case, so no other guard moves.
2. billing/service/billing.go — ReactivateSubscription keeps OVERDUE on the checkout path; for EXPIRED return ErrInvalidInput with a stable code (PLAN_SELECTION_REQUIRED) and let the UI open the plan cards. Checking out the reverted STARTER row is never correct.
3. frontend/src/app/layouts/default/components/AppSidebar.vue — show Finance → Billing for ORG_ADMIN on every plan. The !isStarterPlan guard stays only where it means something (facility admins, already expressed by canFacilityAdminManageBilling).
4. OrgBillingView.vue — EXPIRED renders "Choose a plan to renew" and the plan lineup, not the single "Renew subscription" button (keep that for OVERDUE).

Slice 1 — schema and reconciliation

Migration 075

ALTER TABLE core_public.facilities
    ADD COLUMN IF NOT EXISTS access_state TEXT NOT NULL DEFAULT 'ACTIVE',
    ADD COLUMN IF NOT EXISTS locked_at    TIMESTAMPTZ;

ALTER TABLE core_public.facilities
    ADD CONSTRAINT facilities_access_state_check
    CHECK (access_state IN ('ACTIVE', 'READ_ONLY'));

-- "read-only" and "locked since" can never disagree.
ALTER TABLE core_public.facilities
    ADD CONSTRAINT facilities_locked_at_check
    CHECK ((access_state = 'READ_ONLY') = (locked_at IS NOT NULL));

ALTER TABLE core_public.organizations
    ADD COLUMN IF NOT EXISTS primary_facility_id UUID
        REFERENCES core_public.facilities (id) ON DELETE SET NULL;

The pin lives on the organization, not on the facility. A partial unique index on facilities.is_primary would have to tolerate a transient duplicate while an UPDATE processes the old and new covered rows in one statement; a single column on the organization makes "one covered facility per organization" structural instead.

The migration deliberately does not lock anything. Existing organizations that are already over their plan's allowance (a beta STARTER org holding three facilities) stay writable until their plan next changes. Retro-locking is a deployment decision, made explicitly with cmd/facility-access -apply (see Rollout).

Down: drop the index, the constraints and the three columns. Behaviour returns to today's; the primary pin is lost.

Reconciliation

Owner: the facilities module (internal/modules/facilities/repository/access_state.go), because it owns core_public.facilities. Exported through the module facade as ReconcileOrganizationFacilityAccess(ctx, orgID).

Two statements, both keyed by the same ordering — the organization's chosen facility first, then age, then id:

1. Access state. Rank the surviving facilities, then set access_state = 'ACTIVE' for every rank within max_facilities (< 0 covers everything) and 'READ_ONLY' with locked_at = COALESCE(locked_at, NOW()) beyond it.
2. Covered facility. Point organizations.primary_facility_id at the current pin if it is still valid, otherwise at the oldest survivor.

They are not wrapped in a transaction on purpose: the operation is convergent, and a momentarily missing pin is repaired by the next run rather than being an invalid state. Billing declares the consumer-side interface and receives the facade through Dependencies; core_bootstrap.go wires it.

Also exposed as ListOrganizationsOverFacilityAllowance for the operator command.

Call sites

Reconcile after anything that can change the allowance or the facility set:

Site	Why
applyTransactionPlanToOrganization	paid upgrades and downgrades
changeToFreePlan	direct downgrade to STARTER
AdminProvisionOrg	super-admin plan changes (ENTERPRISE -1 = unlock all)
ExpireEndedSubscriptions	the expiry path (SQL sets STARTER, then reconcile)
ReactivateSubscription / handleInvoicePaid / markOrgActive	unlock on return (no-op when the allowance did not change)
facilitiesService.Delete	promote the next survivor when the covered facility is removed
AdminUpdatePlan (changing max_facilities)	reconciles every organization on that plan
SubscriptionLifecycleScheduler	ExpireEndedSubscriptions returns the expired organizations, and the scheduler reconciles each one

Failure to reconcile must not roll back a payment. Log and continue; the CLI and the next plan write are the repair path.

Lifecycle SQL

Both queries replace AND sp.max_facilities = 1 with AND sp.code <> 'ENTERPRISE', and the surrounding comments change from "multi-facility plans are deliberately left alone" to the new rule.

ExpireEndedSubscriptions keeps the STARTER revert. ExpireGracePeriods then only sees the payment-failure case (term still running) and ENTERPRISE.

Slice 2 — enforcement

Ambient facility (everything under /tenant)

New middleware.FacilityAccessMiddleware(coreQueries) mounted in internal/app/api/router.go immediately after FacilityContextMiddleware and before TenantSchemaMiddleware:

• GET/HEAD/OPTIONS → pass (no query at all).
• other verbs → GetFacilityAccessState(ctx, facilityID); if READ_ONLY and the role is not SUPER_ADMIN, abort with 423 {"code": "FACILITY_READ_ONLY", "message": "...", "facility_id": "..."}.

middleware.CoreQueries gains GetFacilityAccessState; the adapter in internal/app/api/queries_adapter.go implements it from the existing facilities repository.

Rationale for a separate middleware rather than folding it into TenantSchemaMiddleware: one place to test, and the tenant tx stays untouched on GETs.

Explicit-target writes

These bypass tenantGroup because the target facility comes from the payload, not the ambient context. Each calls the facilities module's EnsureFacilitiesWritable before writing:

• data interchange import (adminGroup, target facility from the picker)
• POST /tenant/device-records/transfers and accept: destination only — the source may be locked (transfer-out exception)

Org-level device-record routes are read-only (export/list/get), and acquisition and bulk creation run on the tenant group, so the middleware already covers them.

The guard is implemented once in facilities/reader.FacilityAccess and injected as a narrow consumer-side interface (interchange/service.FacilityAccessGuard, service.CrossFacilityTransferFacilityAccess), so the module boundary holds. It returns a typed 423 error, the same envelope the middleware uses, so a client sees one shape regardless of entry point.

Slice 3 — API and UI

Backend

• facilities/dto.FacilityResponse gains access_state, locked_at, is_primary (derived from the organization's primary_facility_id); mapper and both list queries (ListFacilities, ListFacilitiesByOrganization) select the columns. make sqlc regenerates.
• PATCH /core/facilities/:id/primary — ORG_ADMIN (own org) and SUPER_ADMIN. Sets the organization's primary_facility_id to the target and re-reconciles in the same transaction. Returns the updated facility. Idempotent.
• BillingOverviewResponse gains facility_allowance and locked_facility_count so the billing page renders without a second fetch.

Frontend

• domains/core/types/facilityTypes.ts + facilityService.ts: new fields.
• AppSwitcher.vue: lock icon and "Read-only" label on locked facilities. Switching into one is allowed.
• New ReadOnlyFacilityBanner.vue in the default layout when the active facility is READ_ONLY: why, what still works, and one action ("Make this my covered facility" / "Renew").
• core/api/interceptors.ts: map 423 FACILITY_READ_ONLY to that banner plus a short toast instead of a generic error. Do not retry.
• OrgBillingView.vue: "Facilities covered by your plan" card — each facility with its state, a radio to choose the covered one, and "Renew to unlock all N".
• Offline queue: on 423, stop retrying and mark the queued item blocked with the reason rather than replaying it for ever.

Tests

• backend/tests/e2e/subscription_lifecycle_e2e_test.go — the case at :201-234 currently asserts "multi-facility plans are not swept until that case is decided". Replace with: ADVANCED + 3 facilities, term ends → GRACE → EXPIRED, plan STARTER, oldest facility ACTIVE, other two READ_ONLY, all three readable.
• Extension of that test: renewal via ChangePlan unlocks all three.
• internal/middleware/facility_access_test.go (mirroring subscription_access_test.go): GET passes, POST 423, SUPER_ADMIN bypass, ACTIVE passes.
• Facilities service: delete the covered facility promotes the next oldest; PATCH /primary swaps atomically and is idempotent.
• Billing: ChangePlan accepts EXPIRED; ReactivateSubscription on EXPIRED returns PLAN_SELECTION_REQUIRED and creates no transaction row.
• Frontend: pnpm type-check, pnpm lint, pnpm lint:tokens, pnpm test.

Rollout

1. Migration 075 is additive and safe; it does not lock anyone.
2. Deploy the backend and frontend together — the middleware is the enforcement point, and the UI banner ships with it.
3. Decide on retro-locking existing over-limit organizations: go run ./cmd/facility-access lists them, go run ./cmd/facility-access --apply reconciles them. The command mirrors cmd/status-drift (report/apply split) so the change is a deliberate, logged operation rather than a surprise on deploy.
4. New Coolify env: none.

Rollback

Drop the columns (migration 075 down). Enforcement disappears, data is unaffected, and the organization's covered-facility choice is lost.

Acceptance criteria mapping

Criterion	Covered by
Expired ADVANCED keeps read access, cannot write beyond the allowance	slice 1 + 2
No data deleted or hidden; exports available	GET/OPTIONS pass-through; no DELETE in reconcile
Billing page states which facilities are locked and offers a way to change it	slice 3
Renewing restores full access without a support ticket	slice 0 + reconcile on activation
STARTED/EXPIRED handling no longer special-cases max_facilities = 1	lifecycle SQL

Open risks

• Every non-GET tenant request now costs one small indexed lookup. Acceptable; revisit only if write latency moves.
• A future explicit-target write that forgets the guard is invisible to the middleware. Mitigation: facilities/reader.FacilityAccess is the documented seam, the interchange import path has an e2e assertion, and the transfer destination check sits next to the existing destination validation.
• plan_code in the access token refreshes every 15 minutes, so feature gates lag a downgrade by up to one token lifetime. The facility lock is DB-backed and immediate, so the two can disagree briefly. Pre-existing behaviour.