Slot Models & Enforcement Walls

Every entitlement wall lives in the database as a BEFORE trigger or a SECURITY DEFINER RPC, so direct PostgREST writes are governed exactly like app code. App surfaces never count or block on their own; they pre-check for UX and translate the DB raise into an actionable error.

Wall directory

Wall	SQLSTATE	Raised by	Migration
Count caps: podcasts, automation rules, booking links, notification templates, staff seats, storage	23514 (check_violation)	GATE-3 BEFORE triggers (enforce_*_account_cap)	20260611094845, templates in 20260612105120 (+ 20260711130100 starter semantics)
Managed-episode month slots	PT429	consume_managed_episode via episode_slot_reconcile	20260608110521 §8 + 20260612135602
Recording-date window	PT422	enforce_recording_window	20260625130000
Publish output meter	PT430	episode_publish_meter_reconcile	20260625120000
External-hosting election	PT403	enforce_external_hosting_entitlement	20260625140000
Publish requires finalized audio	PT428	enforce_native_publish_requires_audio	20260622120000
episodes.podcast_id immutability	0A000	forbid_episode_podcast_change	20260612135602 §6
System slot columns (origin, drawn_from_grant_id)	42501	forbid_client_slot_column_writes	20260612135602 §6.5

Trigger name order is load-bearing on episodes (BEFORE triggers fire alphabetically): client-column guards, then trg_episode_p… (publish meter), then trg_episode_r… (recording window), then trg_episode_s… (slot reconciler). An out-of-window date raises PT422 before a slot is consumed, and a publish can hit PT430 before PT429; app error handling checks the earlier-firing codes first.

All the GATE-3 count-cap triggers share one shape: resolve the account from podcasts.billing_account_id (unresolvable = deny), take pg_advisory_xact_lock(hashtext('quota:' || account || ':' || key)), deny on a missing account_entitlements row (C8), no-op when effective_cap() is NULL, then compare the account-pooled count against the cap and raise 23514 with an upgrade HINT.

A. Podcast slots (podcasts_per_account)

Occupancy is lifecycle-aware, defined twice in lockstep:

• TypeScript: countOccupiedSlots() in src/lib/billing/podcast-slots.ts (feeds assertLimit pre-flights and the layout's usage display).
• SQL: the same predicate inside enforce_podcasts_account_cap() (trigger_zz_enforce_podcasts_account_cap, migration 20260611094845 §4).

Rules:

• active and paused podcasts always occupy a slot.
• pending_deletion occupies only when the podcast is show.fm-hosted (hosting_type <> 'external') and deletion_scheduled_at is in the future.
• External pending_deletion podcasts free their slot immediately; a NULLdeletion_scheduled_at under pending_deletion does not occupy.

The trigger fires only on a not-occupied → occupied transition and raises 23514 at cap (catalog: Free = 1, paid = unlimited). It also hardens cross-account moves: a browser JWT may only attach a podcast to a billing account it is a member of; service-role and Hyperdrive writers (auth.uid() NULL) skip the membership check but never the cap. It is named trigger_zz_* so it fires after trigger_set_podcast_billing_account stamps the account.

B. Managed-episode slots (managed_episodes_per_month, PT429)

The slot is a live projection over episodes, not a counter: an episode holds one slot while origin = 'native' AND status IN ('draft', 'scheduled', 'published'), bucketed by the calendar month of recording_scheduled_at in the podcast timezone (date_trunc('month', recording_scheduled_at AT TIME ZONE COALESCE(default_timezone, 'UTC'))). Imports never consume slots. Hard on every tier.

The reconciler

episode_slot_reconcile() (migration 20260612135602 §5) runs as three BEFORE triggers on episodes with tight WHEN clauses:

• ENTER (row becomes counted): calls consume_managed_episode(episode, podcast, recording_at) (20260608110521 §8.2), which may RAISE PT429 and abort the write. Its return value (a drawn pack grant id, or NULL for a base episode) is stamped inline onto NEW.drawn_from_grant_id.
• LEAVE (archive, delete, native → import): credits back the stamped grant via credit_back_managed_episode_grant() (clamps at 0, reactivates an exhausted grant, journals in usage_credit_adjustments; no-op on terminal revoked/expired grants).
• MOVE (both states counted, (account, month) bucket changed): credit back the old draw, re-consume under the new bucket. A reschedule into a full month raises PT429 exactly like a fresh confirm.

Inside consume_managed_episode

1. Resolve account + timezone from the podcast; authorization is role-presence (get_podcast_role NULL for a real authenticated user = 42501); service-role and NULL-auth Hyperdrive callers pass.
2. Serialize per (account, month): pg_advisory_xact_lock(hashtext('managed_episode:' || account || ':' || 'YYYY-MM')).
3. Count this month's other counted episodes (+1 for the one being admitted), tallying how many already drew a pack credit.
4. C8: a missing managed_episodes_per_month row raises PT429 (misprovisioned = deny).
5. Within base (catalog default + Σ override deltas): admit, draw nothing.
6. Over base: this write must find one pool credit for itself plus one for every unbacked existing over-base episode (the base-downgrade residue). If entitlement_pool_remaining() < required, RAISE PT429. Otherwise draw 1 FEFO (valid_until ASC NULLS LAST), journal a pack_draw, and return the grant id.

Lifecycle hooks

• Cancel frees the slot: free_managed_episode_slot_on_cancel() (AFTER UPDATE on bookings, 20260612135602 §7) archives the orphaned draft/scheduled episode once a session's confirmed-booking count reaches zero, which fires the reconciler's LEAVE. It serializes on a per-session advisory lock (never a booking_sessions row lock, which would invert the RPC lock order and deadlock).
• Confirm re-enters: confirm_booking_in_session (re-defined in §8) takes the same advisory lock and widens the episode flip to pending_confirmation or archived → draft, so a confirm landing after a cancel-archive re-enters the counted set with a fresh cap check (may RAISE PT429).
• Admin free: admin_free_slot(episode, admin, reason) (20260615140000_gate6_admin_tooling.sql) archives a draft/scheduled native episode and logs to admin_action_log in one transaction; the archive's LEAVE does the credit-back (never inline, or the pool would double-refill). Idempotent: an already archived/published episode returns freed: false.

Capacity-aware public calendar

managed_slots_remaining(account, month_start) (20260612135602 §9) returns the remaining recurring slots for a podcast-tz month (NULL = unlimited). It deliberately excludes the pack pool: offering pool capacity across every future month would let guests book more months than the pool covers. The public booking read path filters candidate slots per slot (not per display month) via filterSlotsByMonthCapacity() in src/lib/entitlements/managed-episodes.ts, so a boundary slot that lands in the podcast's next month is judged against that month. This read surface over-offers on error/unprovisioned accounts by design; consume_managed_episode at confirm is the authoritative wall.

App translation

managed-episodes.ts exports MONTH_FULL_SQLSTATE = 'PT429', isMonthFullError(), and monthFullGate() / monthFullMessage(), whose copy is:

{Month Year} is full; every episode slot for that month is taken. Add an episode pack or upgrade your plan to open more slots, or pick a date in another month.

Guarded system columns

episodes.origin and episodes.drawn_from_grant_id are system-owned (import worker, backfill, reconciler). forbid_client_slot_column_writes raises 42501 for any authenticated-uid writer; otherwise a browser JWT could insert an unmetered origin = 'import' production episode or forge a drawn_from_grant_id stamp and farm credit-backs. forbid_episode_podcast_change (0A000) closes the cross-account re-bucketing vector outright.

C. Recording window (advance_booking_days, PT422)

enforce_recording_window() (20260625130000) bounds episodes.recording_scheduled_at to [start of the current month, now() + advance_booking_days] in the podcast timezone, on INSERT and on any UPDATE that changes the date:

• Floor (always enforced): no cross-month back-dating; otherwise a back-dated episode lands in an old bucket and escapes the current month's PT429 cap. RAISE PT422 with PG HINT = 'recording_window_floor'. Not an upsell; no plan lifts it.
• Ceiling (C8 fail-closed): a missing advance_booking_days row denies; a finite cap enforces now() + cap days; a NULL cap (no tier today) means no upper bound. RAISE PT422 with HINT = 'recording_window_advance'. This one is an upsell; higher tiers book further out (14 / 60 / 90 / 180 / 365 days).
• Exemption: only a genuine Hyperdrive import write (auth.role() IS NULL AND NEW.origin = 'import'); back-catalogue imports carry years-old air dates. service_role is not exempt. A server-only GUC (app.bypass_recording_window = 'on') exists for future admin-correction RPCs and the pgTAP harness; nothing sets it in production.

App translation: src/lib/entitlements/recording-window.ts (RECORDING_WINDOW_SQLSTATE, isRecordingWindowError(), recordingWindowMessage() reads the hint and deliberately never hardcodes the day count).

The same key also acts as a read-time value clamp on booking links: clampAdvanceBookingDays() (src/lib/entitlements/advance-booking.ts) computes min(persisted max_advance_days, effective_cap) on every read with a service-role client, throwing on cap-resolution errors (a silent fall-open to the stored value is the exact bypass it closes) and clamping to 0 on a missing row or unresolvable account.

D. Publish meter (published_episodes_per_month, PT430)

The slot model caps how many episodes exist per recording month, but archiving frees the slot, so publish → archive → republish could churn unlimited publishes on Free. The fix (20260625120000) meters the act of going live:

• episode_publish_meter_reconcile() fires on every native → published transition (INSERT and UPDATE legs; a published → published edit never double-counts) and records one append-only "realize" row in published_episode_meter_events.
• The realized month comes from the DB clock in the podcast tz, never NEW.published_at (client-forgeable; the RSS feed serves on status = 'published' alone).
• Past the cap (catalog: Free = 2/month, every paid tier unlimited) it raises PT430. Unlimited accounts skip the check but still record, so the ledger stays a complete audit trail.
• Never refunded: unpublish emits nothing, republish re-consumes (+1). The allowance resets on the 1st via the month bucket itself.
• Serialization: pg_advisory_xact_lock(hashtext('publish_meter:' || account || ':YYYY-MM')); C8 missing-row deny; excludes origin = 'import' (safe only because Free lacks the podcast_import feature, migration 20260622130000).

App translation: src/lib/entitlements/published-episodes.ts (PUBLISH_LIMIT_SQLSTATE, isPublishLimitError(), publishLimitGate(verb)); the copy states that unpublishing does not free the allowance and that it resets on the 1st, without hardcoding the cap number.

E. External hosting (external_hosting, PT403)

enforce_external_hosting_entitlement() (20260625140000) rejects setting podcasts.hosting_type = 'external' unless the billing account holds the external_hosting feature. Caller-agnostic with no service-role exemption; the /p/new create runs service-role, which is exactly the gap the guard closes. Electing 'podcasterplus' is always allowed (external → internal migration must never block). Trigger names (trigger_z_enforce_external_hosting_*) place it after the billing-account stamp and before the podcast-slot cap. The /p/new action also gates app-side via requireFeature('external_hosting') for a friendly upsell before the insert; src/lib/entitlements/external-hosting.ts maps the raise (EXTERNAL_HOSTING_SQLSTATE = 'PT403', externalHostingGate()).

F. Storage cap (storage_gb_per_account)

Not a monthly meter: a running account-wide SUM(episodes.audio_file_size_bytes).

• Upload pre-check: assertStorageAllows() in src/api/routes/media/audio.ts throws EntitlementError(409) before issuing a presigned upload.
• DB trigger: enforce_storage_account_cap() (20260611094845 §5) on INSERT/UPDATE OF audio_file_size_bytes. The INSERT leg is mandatory (a direct insert with a size set would bypass an UPDATE-only trigger); shrinking or unchanged bytes pass. Cap in GiB: v_cap_gb * 1073741824 (1024³). RAISE 23514.
• Deleting audio frees space immediately (the SUM is live). The dashboard reads GET /api/account/usage (src/api/routes/account/usage.ts), which returns { storage: { usedBytes, capBytes } } with capBytes: null = unlimited.

G. Starter content slot semantics

Migration 20260711130100_starter_content_cap_semantics.sql: new podcasts are seeded with system notification templates and disabled starter automation rules, but Free caps for both keys are 0, so naive counting would brick free-tier podcast creation. The GATE-3 triggers were re-defined with slot-occupying semantics:

• Notification templates: is_system rows never occupy a slot (early RETURN in the trigger; the pooled count filters is_system = FALSE). User templates count exactly as before.
• Automation rules: a row occupies a slot iff (NOT is_system) OR is_enabled. Seeding disabled starters is free; activating a starter consumes a slot, so the trigger also fires on UPDATE OF is_enabled, is_system. Free (cap 0) sees the starters but cannot activate them; the DB backstops the app-layer check.

The click-time pre-count mirrors these semantics exactly: countOccupiedAutomationSlots() and countAccountTemplates() in src/lib/server/entitlement-page-data.ts (user rules + enabled system rules; non-system templates).

Related

• Entitlement Model for effective_cap() / consume_quota()
• Gate Errors & UI Surfaces for how each SQLSTATE reaches the user