Phase 1 — Data Model

---

Entity Relationship

---

Tables

billing_legal_entities

Represents Jod as the seller of record in a specific market. Each row is one registered Jod company — the entity whose name, tax registration, and invoice sequence will appear on every invoice issued in that country.

Phase 1 seeds two rows: Jod Pte. Ltd. (Singapore, sg_gst) and PT Jod Indonesia (Indonesia, id_vat).

Column	Type	Notes
id	bigint PK
country_id	bigint FK	geo_countries.id — country of incorporation. Drives the default tax regime.
legal_name	string	Exact company name as registered (e.g. "Jod Pte. Ltd.")
registration_number	string	UEN (SG) or NPWP (ID). Unique across all rows.
registered_address	string	Full registered address. Comma-delimited lines.
tax_regime	enum string	sg_gst or id_vat. Determines which tax codes are valid on billing_product_prices.
default_currency	string	ISO 4217 — SGD or IDR.
xero_organisation_id	string (nullable)	Populated in Phase 5 when Xero sync is wired up. Null is fine in Phase 1.
invoice_number_prefix	string	Unique prefix for invoice numbers, e.g. SG-INV- or ID-INV-.
invoice_number_sequence	integer	Sequence counter, incremented atomically (row-level lock) on each invoice issue. Format on invoice: {prefix}{zero-padded 6 digits} e.g. SG-INV-000042.
status	enum string	active or inactive. New invoices and prices cannot reference an inactive entity. Deactivation is irreversible.
admin_created_by_id	bigint FK (nullable)	identities_admins.id — audit trail for creation.
admin_updated_by_id	bigint FK (nullable)	identities_admins.id — audit trail for last change.
created_at	datetime
updated_at	datetime

Key constraints:

• registration_number is globally unique — UENs and NPWPs do not overlap across countries in practice.
• invoice_number_prefix is globally unique — SG-INV- and ID-INV- must not be shared across entities.
• tax_regime drives the allowed tax code list on billing_product_prices. A validator must reject mismatched combinations (e.g. sg_gst entity with PPN_STD tax code).
• Immutable after creation: legal_name, registration_number, country_id, tax_regime, default_currency, invoice_number_prefix. Changing any of these would make already-issued invoices inaccurate. To reflect a real-world change (e.g. company rebranding), create a new entity and deactivate the old one.
• Mutable fields: registered_address, xero_organisation_id only.

---

billing_products

Represents a global sellable SKU — a product that Jod sells regardless of which market the customer is in. Prices and tax are defined separately on billing_product_prices.

A product is linked to a billing_entitlement, which determines what type of credits are granted when this product is purchased (placement credits or gig credits).

Column	Type	Notes
id	bigint PK
billing_entitlement_id	bigint FK	billing_entitlements.id — the entitlement instrument this product grants.
sku	string	Human-meaningful, globally unique code (e.g. SP-CREDITS-100, GIG-CREDITS-CUSTOM). Never reused.
name	string	Display name (e.g. "Placement Credits — 100 pack").
description	string	Longer description shown in the Team Portal.
is_active	boolean	true by default. Only active products appear in the "active catalog" list. Deactivating never deletes the row — historical invoice data stays intact.
unit_name	enum string	What unit is granted: placement_credit, gig_credit, or cent.
grants_units_per_quantity	integer	How many units one purchase quantity grants. E.g. 100 for SP-CREDITS-100 (1 quantity = 100 placement credits), 1 for GIG-CREDITS-CUSTOM (1 quantity = 1 gig credit cent).
created_at	datetime
updated_at	datetime

Key constraints:

• sku is globally unique and treated as immutable once created. Retiring a product means deactivating it, not changing its SKU.
• is_active = false hides the product from active-catalog queries but does not affect historical invoices or pricing data.
• grants_units_per_quantity × invoice_item.quantity must equal invoice_item.units_to_grant — enforced in Phase 2.

Phase 1 seed rows:

sku	unit_name	grants_units_per_quantity	Description
SP-CREDITS-100	placement_credit	100	100-pack of placement credits
SP-CREDITS-500	placement_credit	500	500-pack of placement credits
GIG-CREDITS-CUSTOM	gig_credit	1	Per-unit gig credits (quantity = amount in credits)

---

billing_product_prices

Represents the market-specific price for a product. A single product can have multiple price rows — one per legal entity × country combination — and multiple windows over time (e.g. a promotional rate for December).

This is where currency, tax code, tax rate, pricing model, and active dates live. The invoice code in Phase 2 will query this table to find the right price for a given company.

Column	Type	Notes
id	bigint PK
billing_product_id	bigint FK	billing_products.id — which product this price is for.
billing_legal_entity_id	bigint FK	billing_legal_entities.id — who is selling (determines tax regime).
billing_account_id	bigint FK (nullable)	billing_accounts.id — when set, this is a private price for that specific client only. NULL means a standard catalog price visible to all. Phase 1 only creates standard prices (NULL); private prices are created by sales in later phases. The column must exist now because Phase 2 price resolution filters WHERE billing_account_id IS NULL for self-serve flows.
geo_country_id	bigint FK	geo_countries.id — the customer's market. Drives currency and tax code lookup.
currency	string	Snapshotted from geo_countries.currency at create time (e.g. SGD, IDR). Stored as a snapshot so historical invoices render correctly if the country's currency ever changes.
pricing_model	enum string	package — customer pays a fixed price for a fixed quantity (e.g. 100 credits for SGD 1,000). per_unit — customer pays per unit consumed (e.g. SGD 10 per gig credit cent).
unit_price_cents	integer	Price in the currency's minor unit (cents). IDR has no subunit — stored as ×100 and displayed divided by 100 in the UI.
tax_code	string	Tax code valid for the legal entity's regime. See tax codes table below.
tax_rate	decimal(5,4)	E.g. 0.0900 for SG 9% GST, 0.1100 for ID 11% PPN. Used for display and Xero export — not used in arithmetic (BPS is used internally).
active_from	timestamp (nullable)	Price window start. NULL means "always valid from the beginning".
active_until	timestamp (nullable)	Price window end. NULL means "no expiry". Can be set after creation to end-date a price before creating a successor row.
discarded_at	timestamp (nullable)	Soft-delete timestamp. A discarded price is removed from all active-price queries but never deleted, preserving the audit trail.
platform_fee_rate_bps	integer (nullable)	Required for gig product prices — the negotiated platform fee rate in basis points (e.g. 2000 = 20%). Used to split gig invoices into a principal line and a platform fee line. NULL for placement product prices.
created_at	datetime
updated_at	datetime

Price lifecycle (derived states):

A price row has no explicit status column — its state is derived from timestamps:

State	Condition
Scheduled	active_from IS NOT NULL AND active_from > now()
Active	(active_from IS NULL OR active_from <= now()) AND (active_until IS NULL OR active_until > now()) AND discarded_at IS NULL
Expired	active_until IS NOT NULL AND active_until <= now() AND discarded_at IS NULL
Discarded	discarded_at IS NOT NULL

Key constraints:

• Composite unique index on (billing_product_id, billing_legal_entity_id, geo_country_id, active_from) — allows stacked price windows for the same market as long as active_from differs.
• tax_code must be valid for the legal entity's tax_regime (validator rejects mismatches).
• currency must equal the snapshot of geo_countries.currency at create time.
• A price row that has already been referenced by an invoice cannot be discarded — only end-dated (active_until set) to prevent gaps in the audit trail.

Tax codes by regime:

tax_regime	Allowed tax_code values	Typical tax_rate
sg_gst	SR (standard-rated), ZR (zero-rated), ES / ESN33 (exempt), OS (out of scope), DS (deemed supply)	0.0900 for SR
id_vat	PPN_STD (standard), PPN_ZERO (zero-rated)	0.1100 for PPN_STD

---

Relationships

From	To	Cardinality	Notes
billing_legal_entities.country_id	geo_countries.id	N:1	Country of incorporation. Determines default tax regime and currency.
billing_products.billing_entitlement_id	billing_entitlements.id	N:1	Entitlement instrument granted on purchase. Phase 1 only needs placement and gig to exist.
billing_product_prices.billing_product_id	billing_products.id	N:1	One product can have many prices (one per market × legal entity × time window).
billing_product_prices.billing_legal_entity_id	billing_legal_entities.id	N:1	Who sells in this market. Determines invoice prefix and tax regime.
billing_product_prices.geo_country_id	geo_countries.id	N:1	Customer market. Drives currency and valid tax code list.

---

What Phase 1 does not add

• No price column on billing_products — price always lives on billing_product_prices.
• No FK from ads_placement_prices to billing_products — that mapping is introduced in Phase 4 once invoicing exists.
• No uuid column on these tables — consistent with billing_agreements convention but deferred to a follow-up migration.
• No private price rows (billing_account_id set) — the column exists but Phase 1 only seeds standard catalog prices (billing_account_id = NULL). Private prices (client-specific rates) are created by sales in later phases.