Billing Schema

Purpose

Lumie has two billing data families with different ownership rules:

• platform billing records Lumie charging academies for the SaaS subscription
• tuition billing records an academy charging guardians or students

This page is a reference document for those tables, their JSON columns, and the PG audit trail that surrounds payment flows.

Source Paths

Path	Role
lumie-backend/app/src/main/resources/db/migration/public/V28__billing_platform_tables.sql	Platform billing tables and initial PG audit table
lumie-backend/app/src/main/resources/db/migration/public/V29__tuition_tenant_tables.sql	Tenant-scoped tuition tables and JSON column comments
lumie-backend/app/src/main/resources/db/migration/public/V31__billing_add_missing_columns.sql	Adds billing columns and updates payment_transactions.type comments
lumie-backend/app/src/main/resources/db/migration/public/V33__billing_keys_add_customer_key.sql	Toss customer key on billing keys
lumie-backend/app/src/main/resources/db/migration/public/V34__seed_plans.sql	Plan seed data
lumie-backend/app/src/main/resources/db/migration/public/V35__backfill_free_subscriptions.sql	Backfills default tenant subscriptions
lumie-backend/app/src/main/resources/db/migration/public/V37__subscription_scheduled_changes.sql	Scheduled plan-change columns
lumie-backend/app/src/main/resources/db/migration/public/V38__create_billing_operation_locks.sql	Billing operation locking
lumie-backend/modules/billing/src/main/java/com/lumie/billing/domain/entity/PaymentTransaction.java	JPA mapping for PG audit rows
lumie-backend/modules/billing/src/main/java/com/lumie/billing/application/service/PaymentTransactionLogger.java	Request/response audit persistence in REQUIRES_NEW transactions
lumie-backend/modules/billing/src/main/java/com/lumie/billing/application/service/PaymentService.java	One-time payment confirmation audit payloads
lumie-backend/modules/billing/src/main/java/com/lumie/billing/application/service/SubscriptionChargeExecutor.java	Recurring subscription charge audit payloads
lumie-backend/modules/tuition/src/main/java/com/lumie/tuition/domain/entity/TuitionInvoice.java	JPA mapping for tuition_invoices
lumie-backend/modules/tuition/src/main/java/com/lumie/tuition/domain/vo/LineItem.java	Persisted line-item shape inside tuition_invoices.items
lumie-backend/modules/tuition/src/main/java/com/lumie/tuition/application/service/TuitionInvoiceCommandService.java	Total and VAT calculation, invoice issuance success path

Platform Billing

Table	Scope	Purpose
plans	Platform	Plan catalogue and quota/capability metadata
billing_keys	Platform	Toss auto-billing key metadata per tenant
subscriptions	Platform	Tenant subscription state and scheduled plan changes
invoices	Platform	Lumie-to-tenant invoices
alimtalk_credits	Platform	Tenant message credit balance
payment_transactions	Platform	Append-only PG request/response/webhook audit log
tax_invoices	Platform	Tax invoice records tied to subscription invoices
billing_operation_locks	Platform	Concurrency guard for billing workflows

Platform billing tables reference tenants(id) but are not RLS-protected. Application authorization decides which user can view or mutate billing state.

Tuition Billing

Table	Scope	Purpose
guardians	Tenant	Parent or legal guardian contact data
student_guardians	Tenant	Student-to-guardian association
merchant_profiles	Tenant	Toss sub-merchant KYC profile
tuition_invoices	Tenant	Academy-to-guardian/student invoice
tuition_payments	Tenant	Payment attempts and capture/refund state
cash_receipts	Tenant	Cash receipt issuance data

Tuition tables include tenant_id, RLS policies, and tenant-scoped indexes. Some references to user or class concepts are intentionally soft references so the tuition module does not create hard database coupling to every module it coordinates with.

JSON Column Examples

tuition_invoices.items stores a JSON array of LineItem records. A minimal persisted shape is inferred from the LineItem record plus the nested Money value object used in TuitionInvoice:

[
  {
    "name": "June Math tuition",
    "quantity": 1,
    "unitPrice": {
      "amount": 300000
    },
    "amount": {
      "amount": 300000
    }
  },
  {
    "name": "Workbook fee",
    "quantity": 2,
    "unitPrice": {
      "amount": 15000
    },
    "amount": {
      "amount": 30000
    }
  }
]

The enclosing tuition_invoices row should then carry:

• total_amount = 330000
• status = 'ISSUED' or the later lifecycle states defined in the table check
• idempotency_key when the caller wants duplicate-issue protection

payment_transactions.pg_payload stores the raw request or response map that the billing service passes to PaymentTransactionLogger. Minimal examples from the current code paths look like this:

{
  "orderId": "SUB-20260614-001",
  "amount": 99000,
  "planId": "starter"
}

{
  "success": true,
  "paymentKey": "pay_1234567890",
  "error": ""
}

Payment Audit Model

payment_transactions is append-only. It records PG requests, responses, and webhooks with owner metadata. PaymentTransactionLogger persists those rows in their own REQUIRES_NEW transaction so the audit trail can survive an outer business-transaction rollback.

Column group	Meaning
transaction_id, idempotency_key	Correlate retries and external PG calls
owner_entity, owner_id	Link to invoice, tuition invoice, or billing key owner
type, direction	Classify request/response/webhook direction
pg_payload, http_status	Preserve the raw PG payload and status for audit

Do not update or delete audit rows during normal application workflows.

Success Signals

Workflow	Success signal
Tuition invoice issuance	TuitionInvoiceCommandService saves the row and logs that the invoice was issued with an id, student id, and amount
One-time invoice payment confirmation	PaymentService logs a request audit row, a response audit row, then marks the invoice paid when the PG result is successful
Recurring subscription charge	SubscriptionChargeExecutor writes request and response audit rows, then creates the paid invoice when the charge succeeds

Contract Drift

payment_transactions.type has one live mismatch to watch:

• V31__billing_add_missing_columns.sql documents TAX_INVOICE_ISSUE in the column comment.
• The checked-in TransactionType enum currently defines SUBSCRIPTION_CHARGE, SUBSCRIPTION_REFUND, ALIMTALK_RECHARGE, BILLING_KEY_ISSUE, and BILLING_KEY_REVOKE, but not TAX_INVOICE_ISSUE.

Treat that as schema-to-code drift until the billing module or the migration comment is updated.

Verification

cd /path/to/Lumie/lumie-backend
rg -n "create table if not exists (plans|billing_keys|subscriptions|tuition_invoices|tuition_payments|cash_receipts|payment_transactions)" \
  app/src/main/resources/db/migration/public

Expected success signal: the core platform and tuition billing tables are all declared in the migration set.

cd /path/to/Lumie/lumie-backend
rg -n "LineItem|PaymentTransactionLogger|pg_payload|idempotencyKey|issueInvoice|chargeWithBillingKey|confirmPayment" \
  modules/billing modules/tuition

Expected success signal: hits in the billing and tuition modules show the JSON column shapes, audit logger, and payment success paths described on this page.

cd /path/to/Lumie/lumie-backend
rg -n "TAX_INVOICE_ISSUE|SUBSCRIPTION_CHARGE|BILLING_KEY_REVOKE" \
  app/src/main/resources/db/migration/public/V31__billing_add_missing_columns.sql \
  modules/billing/src/main/java/com/lumie/billing/domain/vo/TransactionType.java

Expected success signal: the search surfaces the current comment-to-enum drift so it can be reviewed intentionally instead of hidden.