# Pricing Tiers And Billable Events: Current Schema Guide

This document summarizes what has been implemented for pricing and billable usage tracking, with a detailed schema-level breakdown of:

- `payment_tiers`
- `billable_events`

It is intended as a handoff/onboarding reference.

## Deployment Status

- The schema changes documented here have been migrated to the database.
- This document reflects the current, post-migration table definitions.

## Scope Implemented

- Added the `PaymentTier` model/table (`payment_tiers`) to store plan definitions.
- Linked organizations to a single pricing tier via `organizations.payment_tier_id`.
- Added admin UI for creating/listing pricing tiers:
  - `GET /admin/`
  - `GET /admin/pricing-tiers`
  - `POST /admin/pricing-tiers`
- Added the `BillableEvent` model/table (`billable_events`) for per-event billing records.
- Applied migrations in small steps (table creation, then indexes, then org FK wiring).

## Thesis Lab Feature Usage Records

Thesis Lab Wave 1 uses `feature_usage_records` in addition to legacy
`billable_events`. The feature-usage table is the quota and settlement source of
truth for API/MCP golden paths:

- `POST /v1/research-groups:preview`
  - records a zero-unit usage trace
  - no quota family is consumed
- `POST /v1/research-groups:from-description`
  - reserves one `groups_per_cycle` unit
  - later settlement commits or voids the reservation
- `POST /v1/research-groups/{group_id}:ask`
  - commits `questions_per_cycle` units equal to accepted questions
- `POST /v1/thesis:compose-and-recruit`
  - reserves one `theses_per_cycle` unit
  - reserves planned `groups_per_cycle` units based on decomposition options
  - later runtime settlement commits delivered usage and voids undelivered
    reservations

Clients should read the response `usage_recorded[]` array as the immediate audit
trace for quota-backed operations. Operators should use
`feature_operations.operation_id` plus child `feature_usage_records` when
debugging retries, stale reservations, or billing publication.

## Table: `payment_tiers`

### Purpose

Defines a reusable pricing plan object. One tier can be assigned to many organizations; each organization can reference one tier.

### Column Reference

| Column | Type | Nullable | Default | Index / Constraint | Meaning |
|---|---|---|---|---|---|
| `id` | `INTEGER` | No | Auto PK | Primary key | Internal numeric identifier. |
| `uuid` | `VARCHAR(64)` | No | `uuid4().hex` | Unique index (`ix_payment_tiers_uuid`) | Stable external-safe identifier. Stored as string, not Postgres UUID type. |
| `name` | `VARCHAR(128)` | No | None | Unique index (`ix_payment_tiers_name`) | Human-readable plan name (for example: `Free`, `Pro`, `Enterprise`). |
| `description` | `VARCHAR(1024)` | Yes | `NULL` | None | Optional plan description text. |
| `stripe_id` | `VARCHAR(128)` | Yes | `NULL` | Unique index (`ix_payment_tiers_stripe_id`) | Optional Stripe product/price mapping key. |
| `agent_tier` | `INTEGER` | No | `0` | None | Numeric tier level used by business logic to gate access/capability. |
| `monthly_price_cents` | `INTEGER` | No | `0` | None | Monthly plan price in cents. |
| `agent_question_multiplier` | `FLOAT` | No | `1.0` | None | Multiplier used in question/billing logic. |
| `free_credits_cents` | `INTEGER` | No | `0` | None | Included monetary credits (cents). |
| `free_questions` | `INTEGER` | No | `0` | None | Included question count allowance. |
| `inc_saas` | `BOOLEAN` | No | `false` | None | Feature flag: SaaS/app access included. |
| `inc_research_studies` | `BOOLEAN` | No | `false` | None | Feature flag: research studies included. |
| `inc_slack` | `BOOLEAN` | No | `false` | None | Feature flag: Slack integration included. |
| `inc_research_groups` | `BOOLEAN` | No | `false` | None | Feature flag: research groups included. |
| `inc_quick_questions` | `BOOLEAN` | No | `false` | None | Feature flag: quick questions included. |
| `created_at` | `TIMESTAMP WITH TIME ZONE` | No | `now()` | Index (`ix_payment_tiers_created_at`) | Row creation timestamp. |
| `updated_at` | `TIMESTAMP WITH TIME ZONE` | Yes | `now()` | None | Row update timestamp (`onupdate=now()` in model). |

### Relationship Wiring

- `Organization.payment_tier_id` is a nullable FK to `payment_tiers.id` with `ON DELETE SET NULL`.
- ORM direction:
  - `Organization.payment_tier` (many-to-one)
  - `PaymentTier.organizations` (one-to-many)

### Notes

- `name`, `uuid`, and `stripe_id` are unique at DB level.
- Most numeric and boolean plan fields are non-null with explicit server defaults, which simplifies inserts from admin UI and scripts.

## Table: `billable_events`

### Purpose

Stores one row per billable action (for example, a paid question or admin-originated usage event), including who incurred it and what it cost.

### Column Reference

| Column | Type | Nullable | Default | Index / Constraint | Meaning |
|---|---|---|---|---|---|
| `id` | `INTEGER` | No | Auto PK | Primary key | Internal event ID. |
| `organization_id` | `INTEGER` | No | None | Index (`ix_billable_events_organization_id`), FK | Organization that is billed for the event. |
| `user_id` | `INTEGER` | No | None | Index (`ix_billable_events_user_id`), FK | User who initiated the billable action. |
| `type` | `VARCHAR(64)` | No | None | Index (`ix_billable_events_type`) | Event type/category string (for aggregation and billing logic). |
| `charge_cents` | `INTEGER` | No | `0` | None | Charge amount in cents for this event. |
| `is_admin` | `BOOLEAN` | No | `false` | None | Marks whether event originated from an admin path/context. |
| `created_at` | `TIMESTAMP WITH TIME ZONE` | Yes | `now()` | None | Event creation timestamp (set automatically for new rows). |
| `updated_at` | `TIMESTAMP WITH TIME ZONE` | Yes | `now()` | None | Event update timestamp (`onupdate=now()` in ORM). |

### Foreign Keys

- `organization_id` -> `organizations.id` (`ON DELETE CASCADE`)
- `user_id` -> `user.id` (`ON DELETE CASCADE`)

### Notes

- Timestamp columns were added as nullable for low-risk rollout on live traffic while still defaulting new rows to `now()`.
- The model currently defines core fields only; there is no explicit ORM relationship object from `Organization` or `User` back to `BillableEvent` yet.

## Migration History (Relevant, Applied)

- `c9f4a2b7d1e8`: create `payment_tiers`.
- `a1b2c3d4e5f6`: add `payment_tiers` indexes.
- `e5f6a7b8c9d0`: add `organizations.payment_tier_id`.
- `f6a7b8c9d0e1`: add index on `organizations.payment_tier_id`.
- `a7b8c9d0e1f2`: add FK (not valid) from organizations to payment tiers.
- `b8c9d0e1f2a3`: validate organizations payment-tier FK.
- `c3d4e5f6a7b8`: create `billable_events`.
- `d4e5f6a7b8c9`: add `billable_events` indexes.
- `d0e1f2a3b4c5`: add `billable_events.created_at` and `billable_events.updated_at`.

## Current Admin Surface

- Pricing tiers can be viewed/created from:
  - `/admin/`
  - `/admin/pricing-tiers`

The admin screen supports plan creation and displays current tiers with core pricing fields, feature flags, and count of organizations currently assigned to each tier.