Billing & Entitlement

Part of: Pickatale Master Build Wiki | Version: v1.7 | Last Updated: 2026-04-18

29. Billing and Entitlement — Full Contracts

Source: (D) Sig direct instruction | Status: Confirmed as spec. All billing tables are MUST BUILD.

29.1 Subscription Types — Complete Definition

29.2 Feature Access Rules by Tier

29.3 checkEntitlement() — The Entitlement Middleware

Every protected endpoint must call this before serving content.

Function signature:

checkEntitlement(context: { user_id?: number, school_id?: number, student_id?: number }): 
  Promise<{ allowed: boolean, tier: 'free' | 'full', expires_at: Date | null }>

Resolution order (first match wins):

1. Check entitlement_grants WHERE (user_id OR school_id) AND expires_at > now(). If found → {tier: 'full'}.
2. Check subscriptions WHERE user_id OR school_id. Match on state:
   • trialing AND trial_ends_at > now() → full
   • active AND current_period_end > now() → full
   • cancelled AND current_period_end > now() → full
   • past_due AND grace_ends_at > now() → full
   • anything else → free
3. Default → {tier: 'free'}

For child accounts: resolve via students.school_id first, then students.parent_id (whichever returns full wins).

This function must be called server-side. Client-side entitlement hints are cosmetic only.

29.4 Entitlement API Endpoint

Endpoint: GET /api/billing/entitlement Auth: X-Internal-Key (service-to-service) OR uc_session (self-check) Query: ?user_id=INT OR ?school_id=INT OR ?student_id=INT

Output:

{
  "tier": "full",
  "state": "trialing",
  "trial_ends_at": "2026-05-02T00:00:00Z",
  "current_period_end": null,
  "grace_ends_at": null,
  "features": ["full_library", "learner_bot", "reports", "interventions", "fluency"]
}

29.5 Subscription Lifecycle — State Transitions

none → trialing (registration)
trialing → active (Stripe payment confirmed, or trial auto-convert)
trialing → expired (trial_ends_at passed, no payment)
active → past_due (Stripe payment_failed webhook)
active → cancelled (user cancels via portal)
past_due → active (payment succeeds within grace period)
past_due → expired (grace_ends_at passed)
cancelled → expired (current_period_end passed)
expired → active (renews via Stripe)
any → active (admin grants entitlement_grant)

29.6 Stripe Webhook Handler

Endpoint: POST /api/billing/webhook Auth: Stripe-Signature header (Stripe secret verification — NOT uc_session)

Events handled:

Audit Log: INSERT audit_log (action='stripe_webhook', metadata={event_type, subscription_id})

Response: Always 200 (Stripe requires this even for unhandled events)

29.7 What Happens When Subscription Expires

Trigger: Cron job runs every hour. Query: subscriptions WHERE state='past_due' AND grace_ends_at < now()

Actions:

1. UPDATE subscriptions.state='expired'.
2. For all students in teacher's classes: UPDATE students.entitlement_tier='free'.
3. Learner Bot: mark learners inactive (skip_nightly=true). Stop nightly cycle.
4. Teacher Portal: add banner "Subscription expired. Renew to restore access."
5. Last cached reports remain visible (read-only).
6. Send SUBSCRIPTION_EXPIRED email to teacher (action: teacher.email, metadata: {teacher_id, tier}).

DB Writes: subscriptions, students, email_log Audit Log: INSERT audit_log (action='subscription_expired', metadata={teacher_id, tier})

29.8 Billing Tables — Full Schema

Table: subscriptions

CREATE TABLE subscriptions (
  id INT PRIMARY KEY AUTO_INCREMENT,
  teacher_id INT NOT NULL,                 -- teacher who holds this license (FK users)
  school_id INT NOT NULL,                  -- school the teacher belongs to (FK schools)
  state ENUM('none','trialing','active','past_due','cancelled','expired') NOT NULL DEFAULT 'none',
  tier ENUM('free','trial','teacher_paid','enterprise','gifted') NOT NULL DEFAULT 'free',  -- canonical v1 tiers
  trial_ends_at DATETIME NULL,
  current_period_start DATETIME NULL,
  current_period_end DATETIME NULL,
  grace_ends_at DATETIME NULL,
  cancelled_at DATETIME NULL,
  stripe_subscription_id VARCHAR(255) NULL,
  stripe_customer_id VARCHAR(255) NULL,
  created_at DATETIME NOT NULL DEFAULT NOW(),
  updated_at DATETIME NOT NULL DEFAULT NOW() ON UPDATE NOW(),
  INDEX(user_id), INDEX(school_id), INDEX(state)
);

Table: entitlement_grants

CREATE TABLE entitlement_grants (
  id INT PRIMARY KEY AUTO_INCREMENT,
  user_id INT NULL,
  school_id INT NULL,
  granted_by INT NOT NULL,                 -- platform admin user_id
  tier ENUM('teacher_paid','enterprise','gifted') NOT NULL,  -- external entitlements only; never free/trial
  expires_at DATETIME NULL,                -- NULL = permanent
  reason VARCHAR(500),
  created_at DATETIME NOT NULL DEFAULT NOW()
);

Table: payments

CREATE TABLE payments (
  id INT PRIMARY KEY AUTO_INCREMENT,
  subscription_id INT NOT NULL,
  amount_cents INT NOT NULL,
  currency CHAR(3) NOT NULL DEFAULT 'USD',
  status ENUM('succeeded','failed','refunded') NOT NULL,
  stripe_payment_intent_id VARCHAR(255),
  created_at DATETIME NOT NULL DEFAULT NOW()
);

40. Entitlement Execution Model

Source: (D) | Status: Confirmed as spec

40.2 Caching Strategy

• In-process cache only (no Redis required for v1). Use a simple Map with TTL.
• TTL: 60 seconds per key. Acceptable staleness — no billing decision is instant.
• Cache invalidation: On Stripe webhook processed (state change) → Account Center emits to all services via internal endpoint POST /api/internal/invalidate-entitlement (X-Internal-Key, fire-and-forget).
• Cache key format: ent:user_{user_id} OR ent:school_{school_id} OR ent:student_{student_id}

40.3 Failure Fallback

Never return 500 to user because entitlement check failed. Always fall back to free tier and log the failure:

INSERT audit_log (action='entitlement_check_failed', metadata={error, fallback: 'free', endpoint})