Standalone ARM — Data Model Specification¶

1. Overview¶

This document defines the canonical data model for the standalone ARM platform. The model is ERP-agnostic — entities are defined in ARM's own schema and synchronized with external systems via the integration layer.

Design Principles¶

Tenant isolation — every entity belongs to a tenant (institution)
Audit trail — all entities track created_by, created_at, updated_by, updated_at
Soft delete — records are archived, not permanently deleted
Referential integrity — foreign keys enforced at the database level
Extensibility — custom fields supported via a JSON metadata column per entity
ERP independence — ARM maintains its own IDs; external system IDs stored as reference fields

2. Entity Relationship Diagram¶

┌──────────────┐
│   Tenant     │
│ (Institution)│
└──────┬───────┘
       │ 1
       │
       │ *
┌──────▼───────┐     1    ┌──────────────────┐
│   Debtor     │──────────│  Billing Profile  │
│  (Family)    │          │  (per debtor)     │
└──────┬───┬───┘          └────────┬──────────┘
       │ 1 │ 1                     │ 1
       │   │                       │
       │   │ *                     │ *
       │ ┌─▼───────────┐  ┌────────▼──────────┐
       │ │   Parent /   │  │ Billing Instruction│
       │ │  Guardian    │  │ (item+period+rules)│
       │ │ (portal user)│  └────────┬──────────┘
       │ └─────────────┘           │
       │ *                         │
┌──────▼───────┐                   │
│   Student    │                   │
│              │                   │
└──────────────┘          ┌────────▼──────────┐
                                   │ 1
                                   │
                                   │ *
                          ┌────────▼──────────┐
                          │ Billing Instruction│
                          │ Applied To         │
                          │ (student/family)   │
                          └───────────────────┘

┌──────────────┐     *    ┌──────────────────┐     *    ┌──────────────┐
│   Item       │──────────│  Transaction     │──────────│   Payment    │
│ (Fee/Discount)│         │ (Invoice/Order)  │         │  Record      │
└──────────────┘          └────────┬─────────┘          └──────────────┘
                                   │ 1
                                   │
                                   │ *
                          ┌────────▼──────────┐
                          │       RPS         │
                          │ (Recurring Payment│
                          │  Schedule)        │
                          └────────┬──────────┘
                                   │ *
                                   │
                                   │ 1
                          ┌────────▼──────────┐
                          │   Recurrence      │
                          │ (Payment Schedule │
                          │  Template)        │
                          └───────────────────┘

3. Core Entities¶

3.1 Tenant (Institution)¶

The top-level entity representing a school, nursery, university, or organization.

Field	Type	Required	Description
`id`	UUID	Yes	Primary key
`name`	VARCHAR(255)	Yes	Institution name
`code`	VARCHAR(50)	Yes	Unique institution code (e.g., "pymble", "saint-edwards")
`type`	ENUM	Yes	`school`, `nursery`, `university`, `other`
`country`	VARCHAR(3)	Yes	ISO 3166-1 alpha-3 country code
`timezone`	VARCHAR(50)	Yes	IANA timezone (e.g., "Australia/Sydney")
`currency`	VARCHAR(3)	Yes	ISO 4217 currency code (e.g., "AUD", "AED")
`fiscal_year_start`	DATE	Yes	First day of fiscal year
`logo_url`	VARCHAR(500)	No	Institution logo for branding
`branding`	JSONB	No	Custom branding config (colors, fonts, terms)
`settings`	JSONB	No	Institution-level configuration (feature flags, defaults)
`status`	ENUM	Yes	`active`, `suspended`, `archived`
`erp_type`	ENUM	No	`netsuite`, `dynamics365`, `none`
`erp_config`	JSONB	No	ERP connector configuration (encrypted)
`created_at`	TIMESTAMP	Yes	Record creation time
`updated_at`	TIMESTAMP	Yes	Last update time

3.2 Debtor (Family/Account)¶

The billing entity — represents a family or account. Billing is always to the debtor. Contact details (email, phone, address) live on the Parent / Guardian entity — the primary parent's details are used for billing correspondence.

Field	Type	Required	Description
`id`	UUID	Yes	Primary key
`tenant_id`	UUID (FK)	Yes	Foreign key to Tenant
`debtor_code`	VARCHAR(50)	Yes	Unique identifier within the tenant (supports numeric or alphanumeric)
`billing_title`	VARCHAR(255)	Yes	Display name for correspondence (e.g., "The Smith Family")
`status`	ENUM	Yes	`active`, `inactive`, `on_hold`, `archived`
`credit_balance`	DECIMAL(12,2)	No	Credit amount owed to the family — the family's "wallet". Increases on overpayment or unapplied credit notes, decreases on credit consumption, refunds, or voided overpayments. Lives at debtor level (not per-term) so credit is always available regardless of billing cycle state
`external_id`	VARCHAR(100)	No	ID in the connected ERP (e.g., NetSuite internal ID)
`sis_id`	VARCHAR(100)	No	ID in the connected SIS
`metadata`	JSONB	No	Custom fields (institution-configurable)
`created_at`	TIMESTAMP	Yes
`updated_at`	TIMESTAMP	Yes
`created_by`	UUID	Yes	User who created the record
`updated_by`	UUID	Yes	User who last updated

Unique constraint: (tenant_id, debtor_code)

3.3 Parent / Guardian¶

Represents an individual parent or guardian within a family. Parents are the people who log into the portal, receive correspondence, and authorize payments. All contact information (email, phone, address) lives here — the primary parent's details are used for billing correspondence and invoice delivery.

Field	Type	Required	Description
`id`	UUID	Yes	Primary key
`tenant_id`	UUID (FK)	Yes	Foreign key to Tenant
`debtor_id`	UUID (FK)	Yes	Foreign key to Debtor (family)
`first_name`	VARCHAR(100)	Yes	Parent first name
`last_name`	VARCHAR(100)	Yes	Parent last name
`email`	VARCHAR(255)	Yes	Email address (used for portal login and correspondence)
`phone`	VARCHAR(50)	No	Contact phone number
`address`	JSONB	No	Billing address (street, city, state, postcode, country)
`relationship`	ENUM	Yes	`mother`, `father`, `guardian`, `step_parent`, `other`
`is_primary`	BOOLEAN	Yes	Primary contact for billing correspondence (exactly one per debtor). Invoices, reminders, and payment confirmations are sent to the primary parent's email
`portal_access`	BOOLEAN	Yes	Whether this parent can log into the payment portal
`portal_auth_method`	ENUM	No	`otp`, `sso_jwt`, `password` — authentication method for portal
`sso_external_id`	VARCHAR(255)	No	External SSO identifier (e.g., JWT sub claim from My Pymble)
`last_login_at`	TIMESTAMP	No	Last portal login timestamp
`status`	ENUM	Yes	`active`, `inactive`, `archived`
`external_id`	VARCHAR(100)	No	ID in the connected ERP
`sis_id`	VARCHAR(100)	No	ID in the connected SIS
`metadata`	JSONB	No	Custom fields
`created_at`	TIMESTAMP	Yes
`updated_at`	TIMESTAMP	Yes

Unique constraint: (tenant_id, email) — each email unique within an institution

Business rule: Exactly one parent per debtor must have is_primary = true. The primary parent's email and address are used for all billing correspondence. Both parents may have portal_access = true.

3.4 Student¶

Field	Type	Required	Description
`id`	UUID	Yes	Primary key
`tenant_id`	UUID (FK)	Yes	Foreign key to Tenant
`debtor_id`	UUID (FK)	Yes	Foreign key to Debtor (family)
`student_code`	VARCHAR(50)	Yes	Unique student identifier within the tenant
`first_name`	VARCHAR(100)	Yes	Student first name
`last_name`	VARCHAR(100)	Yes	Student last name
`year_level`	VARCHAR(20)	No	Year/grade level (e.g., "7", "K", "Pre-K")
`campus_id`	UUID (FK)	No	Foreign key to Campus (for multi-campus institutions)
`student_type`	VARCHAR(50)	No	Configurable type (e.g., "all", "staff_child", "boarding", "indigenous", "scholarship")
`family_order`	INTEGER	No	Position within the family (1st child, 2nd child, 3rd+) for sibling discounts
`enrollment_date`	DATE	No	Date the student enrolled
`withdrawal_date`	DATE	No	Date the student withdrew (NULL if still enrolled)
`status`	ENUM	Yes	`enrolled`, `withdrawn`, `graduating`, `deferred`
`external_id`	VARCHAR(100)	No	ID in the connected ERP
`sis_id`	VARCHAR(100)	No	ID in the connected SIS
`metadata`	JSONB	No	Custom fields
`created_at`	TIMESTAMP	Yes
`updated_at`	TIMESTAMP	Yes

Unique constraint: (tenant_id, student_code)

3.5 Campus¶

Field	Type	Required	Description
`id`	UUID	Yes	Primary key
`tenant_id`	UUID (FK)	Yes	Foreign key to Tenant
`name`	VARCHAR(255)	Yes	Campus name
`code`	VARCHAR(50)	Yes	Campus code
`address`	JSONB	No	Campus address
`status`	ENUM	Yes	`active`, `inactive`
`created_at`	TIMESTAMP	Yes
`updated_at`	TIMESTAMP	Yes

3.6 Item (Fee / Discount)¶

Field	Type	Required	Description
`id`	UUID	Yes	Primary key
`tenant_id`	UUID (FK)	Yes	Foreign key to Tenant
`item_code`	VARCHAR(50)	Yes	Unique item code within the tenant
`name`	VARCHAR(255)	Yes	Item display name (e.g., "Tuition Fee Year 7", "Sibling Discount")
`description`	TEXT	No	Full description
`category`	ENUM	Yes	`charge`, `discount`
`billing_engine_type`	VARCHAR(100)	No	Classification for segmentation (e.g., "Tuition", "Levy", "Discount - Sibling")
`amount`	DECIMAL(12,2)	No	Default amount (may be overridden per year level or student type)
`amount_type`	ENUM	Yes	`fixed`, `percentage`
`tax_treatment`	ENUM	No	`taxable`, `tax_exempt`, `tax_inclusive`
`tax_rate`	DECIMAL(5,2)	No	Applicable tax rate percent
`recurrence`	ENUM	Yes	`one_time`, `recurring`
`is_optional`	BOOLEAN	No	If true, parent can opt in/out (e.g., building levy)
`status`	ENUM	Yes	`active`, `inactive`, `archived`
`external_id`	VARCHAR(100)	No	ID in the connected ERP
`metadata`	JSONB	No	Custom fields
`created_at`	TIMESTAMP	Yes
`updated_at`	TIMESTAMP	Yes

Unique constraint: (tenant_id, item_code)

3.7 Item Pricing Rule¶

Allows item amounts to vary by year level, campus, or student type.

Field	Type	Required	Description
`id`	UUID	Yes	Primary key
`item_id`	UUID (FK)	Yes	Foreign key to Item
`year_level`	VARCHAR(20)	No	Apply this price to a specific year level
`campus_id`	UUID (FK)	No	Apply this price to a specific campus
`student_type`	VARCHAR(50)	No	Apply this price to a specific student type
`amount`	DECIMAL(12,2)	Yes	Override amount for this combination
`effective_from`	DATE	No	Start date for this pricing
`effective_to`	DATE	No	End date for this pricing
`created_at`	TIMESTAMP	Yes
`updated_at`	TIMESTAMP	Yes

3.8 Billing Profile¶

One per debtor per billing cycle — the container for billing instructions.

Field	Type	Required	Description
`id`	UUID	Yes	Primary key
`tenant_id`	UUID (FK)	Yes
`debtor_id`	UUID (FK)	Yes	Foreign key to Debtor
`billing_cycle_id`	UUID (FK)	Yes	Foreign key to Billing Cycle
`status`	ENUM	Yes	`draft`, `active`, `closed`, `archived`
`total_charges`	DECIMAL(12,2)	No	Calculated total charges
`total_discounts`	DECIMAL(12,2)	No	Calculated total discounts
`net_amount`	DECIMAL(12,2)	No	Calculated net amount (charges - discounts)
`opening_balance`	DECIMAL(12,2)	No	Outstanding balance carried forward from prior periods. Per-term value — set by carry-forward, manual entry, or CSV import
`metadata`	JSONB	No	Custom fields
`created_at`	TIMESTAMP	Yes
`updated_at`	TIMESTAMP	Yes

3.9 Billing Cycle¶

Defines a billing period (e.g., "2026 Annual", "2026 Term 1").

Field	Type	Required	Description
`id`	UUID	Yes	Primary key
`tenant_id`	UUID (FK)	Yes
`name`	VARCHAR(100)	Yes	e.g., "2026 Annual", "2026 Term 1"
`period_start`	DATE	Yes	Billing period start date
`period_end`	DATE	Yes	Billing period end date
`payment_terms_days`	INTEGER	Yes	Payment due in N days from invoice date
`status`	ENUM	Yes	`setup`, `configuring`, `review`, `approved`, `generating`, `active`, `closed`
`frequency`	ENUM	Yes	`annual`, `semi_annual`, `term`, `quarterly`, `monthly`, `custom`
`midyear_start`	DATE	No	Different start date for mid-year enrollees
`midyear_divisible_value`	INTEGER	No	Value used for mid-year pro-ration calculation
`settings`	JSONB	No	Cycle-specific settings (messages for pending balances, exclusion rules)
`created_at`	TIMESTAMP	Yes
`updated_at`	TIMESTAMP	Yes

3.10 Billing Instruction¶

Master billing record — one per item per year level (or per rule combination).

Field	Type	Required	Description
`id`	UUID	Yes	Primary key
`tenant_id`	UUID (FK)	Yes
`billing_cycle_id`	UUID (FK)	Yes	Foreign key to Billing Cycle
`item_id`	UUID (FK)	Yes	Foreign key to Item
`year_level`	VARCHAR(20)	No	Year level this instruction covers
`campus_id`	UUID (FK)	No	Campus this instruction covers
`student_type`	VARCHAR(50)	No	Student type filter
`billing_engine_type`	VARCHAR(100)	No	Classification from segmentation
`amount`	DECIMAL(12,2)	Yes	Amount to bill per student/family
`status`	ENUM	Yes	`draft`, `active`, `voided`
`created_at`	TIMESTAMP	Yes
`updated_at`	TIMESTAMP	Yes

3.11 Billing Instruction Applied To¶

Maps a billing instruction to specific students/families.

Field	Type	Required	Description
`id`	UUID	Yes	Primary key
`billing_instruction_id`	UUID (FK)	Yes	Foreign key to Billing Instruction
`debtor_id`	UUID (FK)	Yes	Foreign key to Debtor
`student_id`	UUID (FK)	No	Foreign key to Student (NULL for family-level items)
`amount`	DECIMAL(12,2)	No	Override amount for this specific assignment (NULL = use instruction amount)
`is_exception`	BOOLEAN	No	True if this is an exception override
`exception_reason`	VARCHAR(255)	No	Reason for exception
`status`	ENUM	Yes	`active`, `excluded`, `voided`
`created_at`	TIMESTAMP	Yes
`updated_at`	TIMESTAMP	Yes

3.12 Transaction (Invoice / Billing Order)¶

Field	Type	Required	Description
`id`	UUID	Yes	Primary key
`tenant_id`	UUID (FK)	Yes
`debtor_id`	UUID (FK)	Yes	Foreign key to Debtor
`billing_profile_id`	UUID (FK)	No	Foreign key to Billing Profile
`transaction_number`	VARCHAR(50)	Yes	Human-readable transaction number
`type`	ENUM	Yes	`invoice`, `billing_order`, `credit_note`
`status`	ENUM	Yes	`draft`, `pending`, `sent`, `partially_paid`, `paid`, `overdue`, `cancelled`, `voided`, `closed`
`issue_date`	DATE	Yes	Date the transaction was generated
`due_date`	DATE	Yes	Payment due date
`subtotal`	DECIMAL(12,2)	Yes	Total before tax
`tax_amount`	DECIMAL(12,2)	No	Total tax
`total_amount`	DECIMAL(12,2)	Yes	Grand total
`amount_paid`	DECIMAL(12,2)	No	Amount paid so far
`amount_outstanding`	DECIMAL(12,2)	No	Remaining balance
`payment_link`	VARCHAR(500)	No	Unique URL for parent payment portal
`paid_from_portal`	BOOLEAN	No	"Paid from Portal" flag — prevents duplicate payment setup
`is_closed`	BOOLEAN	No	Whether the billing order is closed (prevents further invoice generation)
`parent_transaction_id`	UUID (FK)	No	For invoices generated from a billing order — links to the parent billing order
`external_id`	VARCHAR(100)	No	ID in the connected ERP
`pdf_url`	VARCHAR(500)	No	URL to the generated PDF invoice
`metadata`	JSONB	No	Custom fields
`created_at`	TIMESTAMP	Yes
`updated_at`	TIMESTAMP	Yes

Unique constraint: (tenant_id, transaction_number)

3.13 Transaction Line Item¶

Field	Type	Required	Description
`id`	UUID	Yes	Primary key
`transaction_id`	UUID (FK)	Yes	Foreign key to Transaction
`item_id`	UUID (FK)	Yes	Foreign key to Item
`student_id`	UUID (FK)	No	Foreign key to Student (if student-level line item)
`description`	VARCHAR(255)	Yes	Line item description
`quantity`	DECIMAL(8,2)	Yes	Quantity (default 1; used for term-based billing orders)
`unit_price`	DECIMAL(12,2)	Yes	Price per unit
`discount_amount`	DECIMAL(12,2)	No	Discount applied
`tax_amount`	DECIMAL(12,2)	No	Tax for this line item
`total`	DECIMAL(12,2)	Yes	Line total (quantity × unit_price - discount + tax)
`sort_order`	INTEGER	No	Display order on the invoice
`created_at`	TIMESTAMP	Yes

3.14 Recurrence (Payment Schedule Template)¶

Defines the parent's chosen payment schedule — governs RPS record creation.

Field	Type	Required	Description
`id`	UUID	Yes	Primary key
`tenant_id`	UUID (FK)	Yes
`debtor_id`	UUID (FK)	Yes	Foreign key to Debtor
`transaction_id`	UUID (FK)	Yes	Foreign key to Transaction
`payment_method`	ENUM	Yes	`credit_card`, `direct_debit`, `bank_transfer`, `bpay`, `edstart`, `other`
`frequency`	ENUM	Yes	`weekly`, `fortnightly`, `monthly`, `term`, `annual`, `custom`
`num_installments`	INTEGER	Yes	Total number of installments
`installment_amount`	DECIMAL(12,2)	Yes	Amount per installment
`total_amount`	DECIMAL(12,2)	Yes	Total planned payment amount (may include opening balance + building levy)
`start_date`	DATE	Yes	First payment date
`end_date`	DATE	No	Last expected payment date
`encrypted_payment_details`	TEXT	Yes	Encrypted card/bank details (see Security spec)
`status`	ENUM	Yes	`active`, `paused`, `completed`, `cancelled`
`created_at`	TIMESTAMP	Yes
`updated_at`	TIMESTAMP	Yes

3.15 RPS (Recurring Payment Schedule) Record¶

Individual scheduled payment — one per processing date.

Field	Type	Required	Description
`id`	UUID	Yes	Primary key
`tenant_id`	UUID (FK)	Yes
`recurrence_id`	UUID (FK)	Yes	Foreign key to Recurrence
`debtor_id`	UUID (FK)	Yes	Foreign key to Debtor
`transaction_id`	UUID (FK)	Yes	Foreign key to Transaction (invoice/billing order)
`payment_method`	ENUM	Yes	`credit_card`, `direct_debit`, `bank_transfer`, `bpay`, `edstart`, `other`
`amount`	DECIMAL(12,2)	Yes	Payment amount for this installment
`processing_date`	DATE	Yes	Scheduled processing date
`status`	ENUM	Yes	`created`, `pending`, `processing`, `processed`, `failed`, `cancelled`, `refunded`
`gateway_reference`	VARCHAR(255)	No	Payment gateway transaction reference
`authorization_code`	VARCHAR(50)	No	Gateway authorization code (e.g., eway "00")
`failure_code`	VARCHAR(50)	No	Decline/failure code
`failure_reason`	VARCHAR(255)	No	Human-readable failure description
`retry_count`	INTEGER	No	Number of retry attempts
`payment_record_id`	UUID (FK)	No	FK to Payment record (created on success)
`processed_at`	TIMESTAMP	No	When the payment was actually processed
`created_at`	TIMESTAMP	Yes
`updated_at`	TIMESTAMP	Yes

Index: (tenant_id, processing_date, status) — optimized for daily RPS processing query

3.16 Payment Record¶

Represents a successful payment received.

Field	Type	Required	Description
`id`	UUID	Yes	Primary key
`tenant_id`	UUID (FK)	Yes
`debtor_id`	UUID (FK)	Yes
`transaction_id`	UUID (FK)	Yes	The invoice/billing order this payment applies to
`rps_id`	UUID (FK)	No	FK to RPS record (NULL for manual payments)
`payment_number`	VARCHAR(50)	Yes	Human-readable payment reference
`amount`	DECIMAL(12,2)	Yes	Payment amount
`payment_method`	ENUM	Yes	`credit_card`, `direct_debit`, `bank_transfer`, `bpay`, `edstart`, `cash`, `cheque`, `other`
`payment_date`	DATE	Yes	Date payment was received
`gateway_reference`	VARCHAR(255)	No	External payment gateway reference
`status`	ENUM	Yes	`applied`, `pending_deposit`, `deposited`, `refunded`, `voided`
`external_id`	VARCHAR(100)	No	ID in the connected ERP
`notes`	TEXT	No	Internal notes
`created_at`	TIMESTAMP	Yes
`updated_at`	TIMESTAMP	Yes

4. Supporting Entities¶

4.1 Audit Log¶

Field	Type	Required	Description
`id`	UUID	Yes	Primary key
`tenant_id`	UUID (FK)	Yes
`entity_type`	VARCHAR(50)	Yes	Entity being audited (e.g., "debtor", "transaction", "rps")
`entity_id`	UUID	Yes	ID of the entity
`action`	ENUM	Yes	`create`, `update`, `delete`, `status_change`, `payment_submitted`, `payment_received`
`user_id`	UUID	No	User who performed the action
`old_values`	JSONB	No	Previous field values (for updates)
`new_values`	JSONB	No	New field values
`ip_address`	VARCHAR(45)	No	Client IP
`user_agent`	VARCHAR(255)	No	Client user agent
`created_at`	TIMESTAMP	Yes

4.2 Email Log¶

Field	Type	Required	Description
`id`	UUID	Yes	Primary key
`tenant_id`	UUID (FK)	Yes
`debtor_id`	UUID (FK)	No
`transaction_id`	UUID (FK)	No
`template_name`	VARCHAR(100)	Yes	Email template used
`recipient`	VARCHAR(255)	Yes	Email recipient
`subject`	VARCHAR(255)	Yes	Email subject
`status`	ENUM	Yes	`queued`, `sent`, `delivered`, `bounced`, `failed`
`sent_at`	TIMESTAMP	No
`error_message`	TEXT	No
`created_at`	TIMESTAMP	Yes

4.3 File Record¶

Tracks generated files (ABA, PDF, CSV exports).

Field	Type	Required	Description
`id`	UUID	Yes	Primary key
`tenant_id`	UUID (FK)	Yes
`type`	ENUM	Yes	`aba_file`, `invoice_pdf`, `csv_export`, `csv_import`, `report`
`filename`	VARCHAR(255)	Yes	Original filename
`storage_url`	VARCHAR(500)	Yes	Cloud storage URL
`size_bytes`	BIGINT	No	File size
`record_count`	INTEGER	No	Number of records in the file (for ABA/CSV)
`generated_by`	UUID	No	User who generated the file
`created_at`	TIMESTAMP	Yes

5. Configuration Entities¶

5.1 Payment Configuration (per tenant per billing cycle)¶

Field	Type	Required	Description
`id`	UUID	Yes	Primary key
`tenant_id`	UUID (FK)	Yes
`billing_cycle_id`	UUID (FK)	Yes
`credit_card_enabled`	BOOLEAN	Yes
`direct_debit_enabled`	BOOLEAN	Yes
`bpay_enabled`	BOOLEAN	No
`edstart_enabled`	BOOLEAN	No
`weekly_enabled`	BOOLEAN	Yes
`weekly_max_splits`	INTEGER	No	Maximum installments for weekly frequency
`fortnightly_enabled`	BOOLEAN	Yes
`fortnightly_max_splits`	INTEGER	No
`monthly_enabled`	BOOLEAN	Yes
`monthly_max_splits`	INTEGER	No
`term_enabled`	BOOLEAN	No
`annual_enabled`	BOOLEAN	No
`annual_discount_percent`	DECIMAL(5,2)	No	Discount for choosing annual (e.g., 2.5)
`annual_split_enabled`	BOOLEAN	No	Allow splitting annual payment across dates
`flexible_dates`	BOOLEAN	Yes	True = parent chooses dates; False = fixed dates
`fixed_payment_dates`	JSONB	No	Array of fixed dates (e.g., ["2026-02-01", "2026-05-01", "2026-08-01", "2026-11-01"])
`created_at`	TIMESTAMP	Yes
`updated_at`	TIMESTAMP	Yes

5.2 Student Type (configurable per tenant)¶

Field	Type	Required	Description
`id`	UUID	Yes	Primary key
`tenant_id`	UUID (FK)	Yes
`code`	VARCHAR(50)	Yes	e.g., "all", "staff_child", "boarding", "indigenous", "scholarship"
`name`	VARCHAR(100)	Yes	Display name
`description`	TEXT	No
`created_at`	TIMESTAMP	Yes

5.3 Email Template¶

Field	Type	Required	Description
`id`	UUID	Yes	Primary key
`tenant_id`	UUID (FK)	Yes
`template_type`	ENUM	Yes	`invoice`, `reminder`, `payment_confirmation`, `payment_failed`, `welcome`
`subject`	VARCHAR(255)	Yes	Email subject template
`body_html`	TEXT	Yes	HTML email body with template variables
`body_text`	TEXT	No	Plain text fallback
`is_active`	BOOLEAN	Yes
`created_at`	TIMESTAMP	Yes
`updated_at`	TIMESTAMP	Yes

6. Index Strategy¶

Entity	Index	Columns	Purpose
RPS	`idx_rps_daily_processing`	(`tenant_id`, `processing_date`, `status`)	Daily RPS processing query
RPS	`idx_rps_debtor`	(`debtor_id`, `status`)	Debtor payment history lookup
Transaction	`idx_txn_debtor_status`	(`debtor_id`, `status`)	Outstanding payment queries
Transaction	`idx_txn_due_date`	(`tenant_id`, `due_date`, `status`)	Overdue invoice reports
Billing Instruction	`idx_bi_cycle`	(`billing_cycle_id`, `status`)	Billing cycle instruction lookup
Student	`idx_student_year_campus`	(`tenant_id`, `year_level`, `campus_id`)	Configurator item-to-year mapping
Parent	`idx_parent_debtor`	(`debtor_id`, `is_primary`)	Primary contact lookup
Parent	`idx_parent_email`	(`tenant_id`, `email`)	Portal login lookup
Payment	`idx_payment_date`	(`tenant_id`, `payment_date`)	Payment reconciliation
Audit Log	`idx_audit_entity`	(`entity_type`, `entity_id`)	Audit trail per entity

7. Migration Notes¶

From Current NetSuite ARM¶

NetSuite Entity	ARM Entity	Notes
Customer Record (Debtor)	Debtor	Map NetSuite internal ID to `external_id`
Contact fields on Customer	Parent / Guardian	Extract name, email, phone from customer record; map portal users
Service Item / Discount Item	Item	Map NetSuite item internal ID to `external_id`
Billing Profile (Pymble)	Billing Profile	Direct mapping
Billing Instruction	Billing Instruction	Direct mapping
Billing Instruction Applied To	Billing Instruction Applied To	Direct mapping
Invoice / Sales Order	Transaction	Map type based on source record type
Payment Record	Payment Record	Map with gateway reference
RPS Record	RPS	Map with recurrence linkage
Customer Deposit	Payment Record	Map as type `deposit` in metadata