Subscription — Data & read/write tables
This page gives the database view and explains the external concepts that Subscription references (owned by other domains). Table convention: 📖 READ, ✏️ WRITE (insert/update).
Related external concepts
Subscription does not own the items below — it links to them. Understand them to know the boundaries.
| Concept | What it is | Role in Subscription | Owning domain |
|---|---|---|---|
| Term | A "term" (semester) — a time span with schedule/products/slots. | Subscription links to a Term (via SubscriptionTerm); booking & billing schedules attach by Term. One subscription runs across many Terms. | Master Data |
| TermProduct | A concrete product/slot within a Term. | Used to check compatibility when linking a term, and to resolve the calendar preview (ProductIds × TermIds). | Master Data |
| Program / Product / TermProgramSet | Program structure & product grouping. | SubscriptionProduct links to a Program; the BookingAndBilling type requires a product. | Master Data |
| TermBooking / TermBookingOrder | "Normal" booking (not via subscription). | On subscription submit, the system compares schedules against normal bookings in Submitted/Approved. | Booking |
| Invoice / InvoiceLine / CreditNote | Invoices & credit notes. | Subscription billing generates Invoices by schedule; Skip → regenerate invoice or issue a credit note. | Finance |
| Billing | The financial "ledger" of a booking/enrollment. | Submitting a booking creates a Billing; it's where invoices attach. | Finance |
| InvoiceScheduler | The record scheduling invoice processing. | The Schedule/Approve action creates an InvoiceScheduler. | Finance |
| Account / Attendee / Contact | Parent / child / contact. | Enrollment carries AccountId (parent) + AttendeeId (child). | Master Data |
| Discount | Discount rule. | EnrollmentDiscount applies a discount per EnrollmentBillingSchedule. | Discount |
| BusinessUnit / Org (Site) | Business unit / site. | Data scope & filtering (Manager, booking). | Master Data |
📌 The related domains have their own pages: Booking, Finance, Payment and Master Data.
Data tables — grouped by aggregate
| Aggregate | Main tables | Purpose |
|---|---|---|
| Subscription (template) | Subscription | Defines the package: name, BU/Org, RecurringTypeId, BillingTypeId, publish status, CutOffMinute, TypeId. |
SubscriptionPrice, SubscriptionPriceLine | Price & price lines (Xero item/account code). | |
SubscriptionProduct | Links to a Program (BookingAndBilling only). | |
SubscriptionExtra, SubscriptionDiscount | Add-on/discount configured at the template level. | |
SubscriptionTerm | Links subscription ↔ Term (fields Linked, IsActive, StatusId: 500 Approved / 600 Archived). | |
SubscriptionTermBillingSchedule | The term's available billing schedule (all bookable periods). | |
| Enrollment (runtime) | Enrollment | One enrollment: AccountId, AttendeeId, SubscriptionId, start/end, status. |
EnrollmentPattern | Schedule pattern (used for the overlap guard). | |
EnrollmentPeriod | Periods of the enrollment. | |
EnrollmentBillingSchedule | The booked billing schedule for the enrollment (source for add-on/discount). | |
EnrollmentExtra | Add-on applied per period. | |
EnrollmentDiscount | Discount applied per EnrollmentBillingSchedule. | |
EnrollmentInvoiceLog | Internal billing log (hidden from the Billing Details UI). |
Data tables within each flow
Flow 1 — Admin sets up a subscription
| Action | Tables | Purpose |
|---|---|---|
| Create/edit package | Subscription ✏️ WRITE | Store the template. |
| Configure price | SubscriptionPrice, SubscriptionPriceLine ✏️ WRITE | Price + price lines. |
| Configure program | SubscriptionProduct ✏️ WRITE; Program/Product 📖 READ | Link products. |
| Link term | SubscriptionTerm, SubscriptionTermBillingSchedule ✏️ WRITE; Term/TermProduct 📖 READ | Create the available schedule by term. |
| Unlink booked term | SubscriptionTerm ✏️ UPDATE (StatusId → 600, keep IsActive=true) | Archive, keep schedules for Manager/Billing. |
| Re-link archived term | SubscriptionTerm ✏️ UPDATE (StatusId → 500) | Restore, keep ProductIds/schedules, no duplicates. |
Flow 2 — Customer submits a booking/enrollment
| Action | Tables | Purpose |
|---|---|---|
| Load booking schedule | SubscriptionTermBillingSchedule 📖 READ | Period/start choices (canonical). |
| Overlap guard | Enrollment, EnrollmentPattern 📖 READ; TermBookingOrder, TermBooking 📖 READ | Block schedule clashes (atomic fail on clash). |
| Create runtime | Enrollment, EnrollmentPeriod, EnrollmentPattern, EnrollmentBillingSchedule ✏️ WRITE | Actual enrollment. |
| Generate finance | Billing ✏️ WRITE; Invoice, InvoiceLine ✏️ WRITE | Invoices by schedule (via the Finance service). |
Flow 3 — Billing Details (Schedule / Approve / Skip)
| Action | Tables | Purpose |
|---|---|---|
| Schedule | Invoice 📖 READ; InvoiceScheduler ✏️ WRITE | Create a scheduler ScheduledOn = Invoice.Date (UTC). |
| Approve | Invoice ✏️ UPDATE (→ Approved); InvoiceScheduler ✏️ WRITE | Scheduler immediately (UtcNow). |
| Skip | InvoiceScheduler ✏️ UPDATE (deactivate); Invoice ✏️ UPDATE (→ Initialised 0) | Reset the period; may regenerate/credit note. |
Flow 4 — Manage Add-ons / Discounts
| Action | Tables | Purpose |
|---|---|---|
| Load periods | EnrollmentBillingSchedule 📖 READ | Only booked periods. |
| Save add-on | EnrollmentExtra ✏️ WRITE | Apply/remove add-on per (add-on × period). |
| Save discount | EnrollmentDiscount ✏️ WRITE | Apply/remove discount per period. |
Billing recalculation (if needed) goes through the Finance backend service, not the popup.
Related
- Architecture & data — relationships between the "objects".
- Booking — the normal booking flow (different from subscription booking).
- Related: Architecture & data, Concepts.