Billing Schema — Compatibility & Risk Analysis · Billing

Cross-reference of packages/core-database/src/schema.ts vs billing architecture docs.

Docs: architecture.md · implementation_plan.md · razorpay_setup_guide.md

Existing Schema — What's Already There

Table	Status	Notes
`plans`	⚠️ Needs columns added	Missing: `priceYearly`, `yearlyDiscountPct`, `trialDays`, Razorpay plan IDs, seat config
`planFeatures`	🔴 Replace entirely	Uses `jsonb limits` blob — billing requires a normalized `plan_service_limits` table with one row per limit key
`subscriptions`	⚠️ Needs columns added	Missing: `billingCycle`, `hasUsedTrial`, `trialEnd`, `cancelAtPeriodEnd`, `pendingPlanId`, `provider`, `razorpayCustomerId`, `razorpaySubscriptionId`, `stripeCustomerId` (nullable)
`services`	✅ Compatible	Already has `code PK` + `name`. No changes needed.
`tenantServices`	⚠️ Needs 1 column	Already has `limits jsonb` + `enabled` + `source`. Missing: `updatedAt` timestamp.
`tenants`	✅ Compatible	Already has `planId` FK. No changes to existing columns.
`memberships`	✅ Compatible	`isOwner` boolean already present — billing RBAC can use it directly.

Tables to ADD (New — Zero Breaking Risk)

Table	Risk	Notes
`service_limits`	🟢 None	New table. Normalizes limit definitions per service.
`plan_service_limits`	🟢 None	New table. Replaces `planFeatures.limits` jsonb blob logic.
`tenant_coins`	🟢 None	New table. One row per tenant.
`coin_transactions`	🟢 None	New table. Append-only ledger.
`tenant_addons`	🟢 None	New table. Active coin-purchased extras.
`processed_payment_events`	🟢 None	New table. Idempotency guard for webhooks.
`coin_packs`	🟢 None	New config table. Purchasable coin pack definitions.
`addon_catalog`	🟢 None	New config table. Add-on definitions.

Detailed Change Analysis

1. `plans` — ADD columns

Current:

export const plans = pgTable("plans", {
    id: text("id").primaryKey(),
    name: text("name").notNull(),
    priceMonthly: integer("price_monthly").notNull(),  // USD cents
    isPublic: boolean("is_public").default(false).notNull(),
})

Needs added:

priceMonthlyInr: integer("price_monthly_inr"),             // INR paise (Razorpay)
priceYearlyInr: integer("price_yearly_inr"),               // INR paise, nullable (free = 0)
yearlyDiscountPct: integer("yearly_discount_pct").default(0).notNull(),
trialDays: integer("trial_days").default(0).notNull(),
// Razorpay Plan IDs (subscription plans)
razorpayPlanIdMonthly: text("razorpay_plan_id_monthly"),
razorpayPlanIdYearly: text("razorpay_plan_id_yearly"),
// Stripe Price IDs (Phase 8, nullable now)
stripePriceIdMonthly: text("stripe_price_id_monthly"),
stripePriceIdYearly: text("stripe_price_id_yearly"),
// Seat config for hybrid model
includedSeats: integer("included_seats").default(0).notNull(),
extraSeatCostInr: integer("extra_seat_cost_inr").default(0),  // paise/seat/month
isPerSeat: boolean("is_per_seat").default(false).notNull(),

Risk: 🟡 Low

All new columns are nullable or have default → no migration data loss
Existing code reading plans still works (additive)
priceMonthly (USD cents) kept intact for Stripe Phase 8 compatibility

2. `planFeatures` — DEPRECATE / REPLACE

Current:

export const planFeatures = pgTable("plan_features", {
    planId: ...,
    serviceCode: ...,
    limits: jsonb("limits").$type<Record<string, number>>(),  // ← blob
})

Problem: Architecture requires normalized plan_service_limits with one row per (plan_id, service_code, limit_key, value). The existing planFeatures.limits is a JSON blob — you can't query "all plans where blog.posts > 10" efficiently.

Required action: Create plan_service_limits as a new parallel table. Old planFeatures table:

Gets deprecated (stop writing to it, keep for backwards compat)
Gets data migrated → then dropped after all reads move over

Risk: 🔴 High

planFeatures may already be queried in the codebase
Before touching: run grep -r "planFeatures" apps/ packages/
Recommendation: Keep planFeatures alive, create plan_service_limits as parallel. Migrate service reads one by one. Drop only when all reads are moved.

3. `subscriptions` — ADD columns

Current:

export const subscriptions = pgTable("subscriptions", {
    id: text("id").primaryKey(),
    tenantId: ...,
    planId: ...,
    status: text("status").notNull(),           // active, past_due, canceled
    currentPeriodEnd: timestamp(...),
    paymentMethodId: text("payment_method_id"),  // ← Stripe-specific
    createdAt: ...,
})

Needs added:

billingCycle: text("billing_cycle").default("monthly").notNull(),      // monthly | yearly
hasUsedTrial: boolean("has_used_trial").default(false).notNull(),
trialEnd: timestamp("trial_end"),
cancelAtPeriodEnd: boolean("cancel_at_period_end").default(false).notNull(),
pendingPlanId: text("pending_plan_id").references(() => plans.id),    // scheduled downgrade
provider: text("provider").default("razorpay").notNull(),              // razorpay | stripe
razorpayCustomerId: text("razorpay_customer_id"),
razorpaySubscriptionId: text("razorpay_subscription_id"),
stripeCustomerId: text("stripe_customer_id"),                          // Phase 8, nullable
stripeSubscriptionId: text("stripe_subscription_id"),                  // Phase 8, nullable
updatedAt: timestamp("updated_at").defaultNow().notNull(),

About paymentMethodId: Stripe-specific — Razorpay doesn't use it. Keep column, leave nullable. Will be used when Stripe is added (Phase 8).

Status values: Add trialing to the allowed set — enforce in code (not DB constraint).

Risk: 🟡 Low — all new columns are defaulted/nullable. Existing reads unaffected.

4. `tenantServices` — ADD column

Current has: tenantId, serviceCode, enabled, limits jsonb, source

Needs: updatedAt: timestamp("updated_at").defaultNow().notNull()

Risk: 🟢 None — additive, with default.

Note on limits jsonb: Architecture's tenant_services stores limits as an effective blob (plan limits + addon boosts merged). This matches tenantServices.limits exactly — ✅ no design conflict.

New Tables — Full Drizzle Schema

`service_limits`

export const serviceLimits = pgTable("service_limits",
  {
    serviceCode: text("service_code").references(() => services.code).notNull(),
    limitKey: text("limit_key").notNull(),        // "posts", "storage_mb", "seats"
    displayName: text("display_name").notNull(),
    defaultValue: integer("default_value").default(0).notNull(),
    unit: text("unit"),                           // "MB", "count", "boolean"
  },
  (t) => ({ pk: primaryKey(t.serviceCode, t.limitKey) })
)

`plan_service_limits`

export const planServiceLimits = pgTable("plan_service_limits",
  {
    planId: text("plan_id").references(() => plans.id).notNull(),
    serviceCode: text("service_code").references(() => services.code).notNull(),
    limitKey: text("limit_key").notNull(),
    value: integer("value").notNull(),            // -1 = unlimited, 0 = disabled
  },
  (t) => ({ pk: primaryKey(t.planId, t.serviceCode, t.limitKey) })
)

`tenant_coins`

export const tenantCoins = pgTable("tenant_coins", {
    tenantId: text("tenant_id").primaryKey().references(() => tenants.id),
    balance: integer("balance").default(0).notNull(),
    updatedAt: timestamp("updated_at").defaultNow().notNull(),
})

`coin_transactions`

export const coinTransactions = pgTable("coin_transactions", {
    id: text("id").primaryKey(),
    tenantId: text("tenant_id").references(() => tenants.id).notNull(),
    amount: integer("amount").notNull(),          // positive = credit, negative = debit
    balanceAfter: integer("balance_after").notNull(),
    reason: text("reason").notNull(),             // plan_purchase | addon_renewal | manual_top_up
    description: text("description"),
    referenceId: text("reference_id"),            // Razorpay payment_id or order_id
    createdAt: timestamp("created_at").defaultNow().notNull(),
})

`tenant_addons`

export const tenantAddons = pgTable("tenant_addons", {
    id: text("id").primaryKey(),
    tenantId: text("tenant_id").references(() => tenants.id).notNull(),
    addonType: text("addon_type").references(() => addonCatalog.id).notNull(),
    quantity: integer("quantity").default(1).notNull(),
    status: text("status").default("active").notNull(),   // active | paused
    nextRenewal: timestamp("next_renewal"),
    createdAt: timestamp("created_at").defaultNow().notNull(),
})

`processed_payment_events`

export const processedPaymentEvents = pgTable("processed_payment_events", {
    providerEventId: text("provider_event_id").primaryKey(),
    provider: text("provider").notNull(),          // razorpay | stripe
    eventType: text("event_type").notNull(),
    processedAt: timestamp("processed_at").defaultNow().notNull(),
})

`coin_packs`

export const coinPacks = pgTable("coin_packs", {
    id: text("id").primaryKey(),                  // small, medium, large
    name: text("name").notNull(),
    pricePaise: integer("price_paise").notNull(), // INR paise (41500 = ₹415)
    coins: integer("coins").notNull(),
    bonusPct: integer("bonus_pct").default(0).notNull(),
    razorpayPriceId: text("razorpay_price_id"),
    isActive: boolean("is_active").default(true).notNull(),
    sortOrder: integer("sort_order").default(0).notNull(),
})

`addon_catalog`

export const addonCatalog = pgTable("addon_catalog", {
    id: text("id").primaryKey(),
    displayName: text("display_name").notNull(),
    serviceCode: text("service_code").references(() => services.code).notNull(),
    limitKey: text("limit_key").notNull(),
    coinCostPerUnit: integer("coin_cost_per_unit").notNull(),
    unitLabel: text("unit_label").notNull(),       // "GB", "seat"
    isRecurring: boolean("is_recurring").default(false).notNull(),
    isActive: boolean("is_active").default(true).notNull(),
})

Risk Summary

#	Risk	Severity	Mitigation
R1	`planFeatures` queried in existing app code	🔴 High	`grep -r "planFeatures" apps/ packages/` before touching. Deprecate, don't drop immediately.
R2	`subscriptions.paymentMethodId` — Stripe-specific, null for Razorpay	🟡 Medium	Keep column, nullable. Phase 8 Stripe will use it.
R3	`plans.priceMonthly` is USD cents — Razorpay needs INR paise	🟡 Medium	Add `price_monthly_inr` separately. Keep `priceMonthly` for Stripe compatibility.
R4	`tenantServices.limits` jsonb — effective limits blob	🟢 None	Already compatible. No change needed.
R5	~10 new Drizzle migrations in one go	🟡 Medium	Run on staging first. Back up DB before migrating prod.
R6	`subscriptions` PK stores Razorpay `sub_...` ID	🟢 None	Compatible — `id` field can store any string ID.
R7	`tenant_coins.balance` could go negative on concurrency bug	🔴 High	Use `SELECT FOR UPDATE` on every coin debit. Never update balance without row lock.
R8	`memberships.isOwner` for billing RBAC	🟢 None	Already a boolean in schema — works out of the box.

Compatibility With Existing Code

Area	Compatible?	Notes
Auth / JWT	✅	`memberships.isOwner` already exists → billing RBAC works out of the box
Invitations (seat limit enforcement)	✅	`invitations` → `memberships` flow unchanged. Billing adds a pre-check only.
Team RBAC	✅	`roles` + `memberships` + `permissions` not touched
Media service (`mediaFiles`)	✅	Storage limit check reads `tenantServices.limits.storage_mb` — structure unchanged
Communication service (`messageLogs`)	✅	Email send limits use `tenantServices.limits.email_sends` — structure unchanged
API Keys limit	✅	`apiKeys` table unchanged — limit check is additive
`tenants.planId` FK	✅	Already references `plans.id` — works seamlessly with billing
`verifications` / `userAuthProviders`	✅	Not touched by billing at all

Recommended Migration Order

Migration 1:  Add columns to `plans` (all nullable/defaulted — zero risk)
Migration 2:  Add columns to `subscriptions` (all nullable/defaulted — zero risk)
Migration 3:  Add `updatedAt` to `tenant_services`
Migration 4:  Create `service_limits` + `plan_service_limits`
Migration 5:  Seed `service_limits` + `plan_service_limits` data
Migration 6:  Create `tenant_coins` + `coin_transactions`
Migration 7:  Create `tenant_addons` + `processed_payment_events`
Migration 8:  Create `coin_packs` + `addon_catalog`
Migration 9:  Seed `coin_packs` + `addon_catalog`
Migration 10: (After billing verified in prod) — migrate reads off `planFeatures`, then drop

Rule: Never run all migrations at once in production. Migrations 1–3 are zero-risk and can deploy anytime. Migrations 4+ require the billing service code to be ready and tested on staging.