Data Model

Core data model for the Atlas system. All entity names and field names follow the terminology defined in glossary.md.

Tables are grouped by domain. The group name is used as the table prefix.

Scope note: This document covers the operational layer only — the 38 LibSQL tables managed by Drizzle ORM, used for individual record lookup and dashboard drill-down. The analytical layer (DuckDB raw, staging, intermediate, and mart tables) is defined in dbt models and documented in analytics.md.

Table Groups

Group	Prefix	Contents
Core	`core_`	Org hierarchy: group, entity, unit
Database	`db_`	Master data: people, organizations, accounts, relations, and their lookups
Catalog	`catalog_`	Product catalog: brands, items, variants, pricing
Commerce	`commerce_`	Orders and order classification lookups
Finance	`finance_`	Transactions and payment lookups
Marketing	`marketing_`	Channel types, channels, segments, agendas, activities, and all join tables
Target	`target_`	Goals and metric lookups

Entity Relationship Diagram (Summary)

PKs and FKs only. Full field details are in the entity sections below and in the detailed diagram at the bottom.

`core_` — Core

`core_group`

The top-level parent organization. All entities and their units ultimately belong to a Group. In the current deployment, GNY (Grup Neutron Yogyakarta) is the only Group.

Field	Type	Required	Notes
id	ulid	yes	PK
name	string	yes	Short name, e.g. GNY
full_name	string	no	e.g. Grup Neutron Yogyakarta

A legal or business entity under a Group. Each Entity operates independently with its own units, targets, and reporting scope. IONs and EPN are the current entities under GNY. All data in the system is scoped to an Entity.

Field	Type	Required	Notes
id	ulid	yes	PK
group_id	ulid	yes	FK → core_group
code	string	yes	e.g. IONS, EPN — unique within a Group
name	string	yes
full_name	string	no

`core_unit`

An operating unit within an Entity. A Unit owns products, fulfills orders, and reports revenue independently. Units are the primary dimension for financial reporting and target-setting. Examples: TM, WLC_ENGLISH, WLC_NON_ENGLISH, REAL, NP, ICC, ICA, DTC.

Field	Type	Required	Notes
id	ulid	yes	PK
entity_id	ulid	yes	FK → core_entity
code	string	yes	e.g. TM, WLC_ENGLISH — unique within an Entity
name	string	yes	Display name

`db_` — Database

`db_sex`

Biological sex of a Person.

Field	Type	Required	Notes
id	ulid	yes	PK
name	string	yes	Male, Female
source_term	string	no	L, P

`db_organization_level`

The classification of an Organization by education or institutional level.

Field	Type	Required	Notes
id	ulid	yes	PK
name	string	yes	Elementary, Middle School, High School, University, Institution, General Public
source_term	string	no	TK / SD, SMP / MTs, SMA / SMK / MAN, Universitas / Perguruan Tinggi, Instansi, Lain-Lain / Umum

`db_relation_type`

The nature of the relationship between two Persons.

Field	Type	Required	Notes
id	ulid	yes	PK
name	string	yes	Guardian, Parent, Spouse, Employer, Emergency Contact
source_term	string	no

`db_account_type`

The billing classification of an Account.

Field	Type	Required	Notes
id	ulid	yes	PK
name	string	yes	Individual, Household, Corporate
source_term	string	no

`db_organization`

A school, university, company, or institution that a Person is affiliated with. Organizations are used for school segmentation reporting (School Progress) — tracking which institutions students come from and how that changes year over year.

Field	Type	Required	Notes
id	ulid	yes	PK
name	string	yes	e.g. Universitas Gadjah Mada, SMA N 1 Yogyakarta
level_id	ulid	yes	FK → db_organization_level
entity_id	ulid	no	nullable — scopes the organization to a specific Entity if needed

`db_person`

An individual human in the system. A Person can play different roles depending on context: as a Participant (attending a program), a Guardian (parent paying for a child), or a Payer. The same physical person may appear in multiple roles across different orders. Relationships between Persons (e.g. parent-child) are captured via db_relation.

Field	Type	Required	Notes
id	ulid	yes	PK
first_name	string	yes
last_name	string	no
date_of_birth	date	no	T/T/L (Tanggal Lahir)
birth_place	string	no	T/T/L (Tempat Lahir)
sex_id	ulid	no	FK → db_sex
national_id	string	no	KTP/ID — Indonesian national ID
phone	string	no
email	string	no
address	string	no
organization_id	ulid	no	FK → db_organization
is_verified	bool	no	default false

`db_relation`

A directed relationship between two Persons. Used to capture family, employment, or care relationships that exist independently of any order. For example, a parent-child pair is recorded here so the system knows who is guardian of whom regardless of purchase history.

Field	Type	Required	Notes
id	ulid	yes	PK
from_person_id	ulid	yes	FK → db_person — the person in the active role (e.g. the guardian)
to_person_id	ulid	yes	FK → db_person — the person in the passive role (e.g. the child)
relation_type_id	ulid	yes	FK → db_relation_type
notes	string	no

`db_account`

The billing entity for an Order — the party who is financially responsible for a payment. An Account may be an individual adult (paying for themselves), a household (a parent paying for a child), or a corporate entity (a company paying for employees). One Person may be linked to multiple Accounts.

Field	Type	Required	Notes
id	ulid	yes	PK
account_type_id	ulid	yes	FK → db_account_type
name	string	yes	Display name for the account
person_id	ulid	no	FK → db_person — main contact for this account
organization_id	ulid	no	FK → db_organization — for Corporate accounts

`catalog_` — Catalog

`catalog_item_type`

The delivery format of a catalog item.

Field	Type	Required	Notes
id	ulid	yes	PK
name	string	yes	Regular, Private, Other
source_term	string	no	Reguler, Privat

`catalog_brand`

A market-facing brand identity that may span one or more Units. For example, the WLC brand covers both WLC_ENGLISH and WLC_NON_ENGLISH units. Items belong to a Brand; Units fulfill the orders. The relationship between Brand and Unit is managed via the catalog_brand_unit join table.

Field	Type	Required	Notes
id	ulid	yes	PK
name	string	yes	e.g. WLC, True Mandarin, REAL
description	string	no

`catalog_brand_unit`

Join table linking Brands to Units (M2M). A Brand can be associated with multiple Units, and a Unit can carry multiple Brands.

Field	Type	Required	Notes
brand_id	ulid	yes	FK → catalog_brand
unit_id	ulid	yes	FK → core_unit

`catalog_item`

A service or course offering in the catalog. Items belong to both a Brand (market-facing identity) and a Unit (operational owner). Each item has a type (delivery format) and a category (for grouping in reports). Examples: Basic Adults (TM / True Mandarin), German Regular (WLC_NON_ENGLISH / WLC), IELTS Prep (REAL).

Field	Type	Required	Notes
id	ulid	yes	PK
brand_id	ulid	yes	FK → catalog_brand
unit_id	ulid	yes	FK → core_unit
name	string	yes	e.g. Basic Adults, German Regular, IELTS Prep
category	string	no	e.g. Mandarin Adults, WLC Non-English
item_type_id	ulid	yes	FK → catalog_item_type
source_term	string	no	Original name in source data

`catalog_item_variant`

A specific level or pricing tier within a catalog item. Variants represent both curriculum levels (e.g. Dragon Basic 1, Dragon Basic 2, German A1.1) and pricing tiers (e.g. Regular, Platinum). An Order references the specific variant purchased.

Field	Type	Required	Notes
id	ulid	yes	PK
item_id	ulid	yes	FK → catalog_item
name	string	yes	e.g. Dragon Basic 2, German A1.1, Platinum Basic Adults
level	string	no	Curriculum level label, e.g. A1.1, Basic, Intermediate
tier	string	no	Pricing tier label, e.g. Regular, Platinum, Gold
source_term	string	no	Original name in source data, e.g. KELAS/LEVEL value

`catalog_price_list`

A named set of prices valid for a given period. Price lists allow different pricing strategies (standard, platinum, corporate) and can be scoped to a specific Unit or apply globally across an Entity.

Field	Type	Required	Notes
id	ulid	yes	PK
entity_id	ulid	yes	FK → core_entity
unit_id	ulid	no	FK → core_unit — null means entity-wide price list
name	string	yes	e.g. Standard 2026, Platinum Tier, Corporate Rate
currency	string	yes	e.g. IDR
valid_from	date	yes
valid_to	date	no	null means currently active

`catalog_price`

A specific price for an item or variant within a price list. Either item_id or variant_id must be set — a price can be set at the item level (applies to all variants) or overridden at the variant level.

Field	Type	Required	Notes
id	ulid	yes	PK
price_list_id	ulid	yes	FK → catalog_price_list
item_id	ulid	no	FK → catalog_item — item-level price
variant_id	ulid	no	FK → catalog_item_variant — variant-level price overrides item-level
amount	decimal	yes	In the currency of the price list

`commerce_` — Commerce

`commerce_order_type`

The nature of a commercial transaction on an Order.

Field	Type	Required	Notes
id	ulid	yes	PK
name	string	yes	Enrollment, Book Sale, Event Fee, Institution Fee, Other
source_term	string	no

`commerce_customer_type`

The classification of a customer's relationship history at the time of an Order.

Field	Type	Required	Notes
id	ulid	yes	PK
name	string	yes	New, Renewal, Alumni
source_term	string	no	Siswa Baru, Siswa Lanjut, Siswa Alumni

`commerce_order`

A single commercial transaction between an Account and a Unit for an item. Orders are the central record linking people, products, and money. The order_type distinguishes enrollments (which require a Participant) from other purchase types such as book sales or event fees. customer_type is stored for performance and audit but can always be recomputed from a Person's order history.

Field	Type	Required	Notes
id	ulid	yes	PK
entity_id	ulid	yes	FK → core_entity
unit_id	ulid	yes	FK → core_unit
account_id	ulid	yes	FK → db_account — who pays
participant_id	ulid	no	FK → db_person — who attends; required for Enrollment orders
item_id	ulid	no	FK → catalog_item — null for ad-hoc orders
variant_id	ulid	no	FK → catalog_item_variant — specific level or tier purchased
order_type_id	ulid	yes	FK → commerce_order_type
customer_type_id	ulid	no	FK → commerce_customer_type — stored, also computable from history
period_year	int	no	Intake year
period_month	int	no	Intake month
channel_id	ulid	no	FK → marketing_channel — acquisition source
receipt_number	string	no	e.g. TM.026.02.0001
notes	string	no
source_file	string	no	Audit — name of the source file
source_sheet	string	no	Audit — sheet name within the source file

`finance_` — Finance

`finance_payment_method`

How a payment was physically made.

Field	Type	Required	Notes
id	ulid	yes	PK
name	string	yes	Cash, Transfer, EDC, QRIS
source_term	string	no	Tunai, Transfer, EDC, QRIS

`finance_payment_status`

The settlement state of a Transaction.

Field	Type	Required	Notes
id	ulid	yes	PK
name	string	yes	Paid in Full, Installment, Settlement
source_term	string	no	Lunas, Angsuran / Angsur, Pelunasan

`finance_transaction`

A single payment event against an Order. One Order may have multiple Transactions when payment is made in installments. Transactions are the financial ground truth — all revenue figures are derived from Transaction amounts grouped by period and unit.

Field	Type	Required	Notes
id	ulid	yes	PK
order_id	ulid	yes	FK → commerce_order
date	date	yes
amount	decimal	yes	In IDR
payment_channel	string	no	Bank account used, e.g. MANDIRI, BCA REAL
payment_method_id	ulid	no	FK → finance_payment_method
payment_status_id	ulid	yes	FK → finance_payment_status
batch_ref	string	no	Barsheet value from source
source_file	string	no	Audit — name of the source file
source_sheet	string	no	Audit — sheet name within the source file

`marketing_` — Marketing

`marketing_channel_type`

The classification of a Marketing Channel. Replaces the inline category string field — channel types are now a proper lookup table, extensible without schema changes. Initial values mirror the previous category field but can be expanded (e.g. Print, Outdoor, Social, Search).

Field	Type	Required	Notes
id	ulid	yes	PK
name	string	yes	e.g. Conventional, Digital
source_term	string	no	e.g. Konvensional, Digital Marketing

`marketing_channel`

A channel through which a Person discovered or was acquired. Channels are seeded from the registry and rarely change. Each channel belongs to a type defined in marketing_channel_type. Examples: KBM, Social Media, Referral, Website, Billboard.

Field	Type	Required	Notes
id	ulid	yes	PK
name	string	yes	e.g. KBM, Social Media, Referral
channel_type_id	ulid	yes	FK → marketing_channel_type
source_term	string	no	Original term in source data, e.g. Sosial Media, Teman

`marketing_segment`

A named audience segment used to scope marketing agendas and activities. Segments describe the intended target audience in human-readable terms. Structured query criteria may be added in a future iteration.

Field	Type	Required	Notes
id	ulid	yes	PK
name	string	yes	e.g. SMA Students, Working Adults, Parents of Kids Programs
description	string	no	Free-form notes describing the target audience

`marketing_agenda`

A planned marketing initiative or campaign. An Agenda represents intent — what the team plans to do and when. Channels, segments, and target organizations are attached via join tables, allowing one agenda to target multiple audiences and channels simultaneously. Agendas can be scoped to a specific Unit or set at the Entity level.

Field	Type	Required	Notes
id	ulid	yes	PK
entity_id	ulid	yes	FK → core_entity
unit_id	ulid	no	FK → core_unit — null means entity-level agenda
title	string	yes	Description of the planned initiative
planned_date	date	yes
target_leads	int	no	Expected total lead count across all channels
notes	string	no

`marketing_agenda_channel`

Join table linking Agendas to Channels (M2M). One agenda can use multiple channels simultaneously.

Field	Type	Required	Notes
agenda_id	ulid	yes	FK → marketing_agenda
channel_id	ulid	yes	FK → marketing_channel

`marketing_agenda_segment`

Join table linking Agendas to Segments (M2M). One agenda can target multiple audience segments.

Field	Type	Required	Notes
agenda_id	ulid	yes	FK → marketing_agenda
segment_id	ulid	yes	FK → marketing_segment

`marketing_agenda_organization`

Join table linking Agendas to Organizations (M2M). Records which specific schools or institutions are planned to be targeted by an agenda.

Field	Type	Required	Notes
agenda_id	ulid	yes	FK → marketing_agenda
organization_id	ulid	yes	FK → db_organization

`marketing_agenda_target`

Join table linking Agendas to Targets (M2M). One agenda can have multiple targets (e.g. leads, closings, revenue); one target can be shared across multiple agendas.

Field	Type	Required	Notes
agenda_id	ulid	yes	FK → marketing_agenda
target_id	ulid	yes	FK → target_item

`marketing_activity`

The execution record of a marketing effort — what was actually done and what it produced. An Activity may be linked to a planned Agenda or recorded ad-hoc. The closings count is cross-checked against Order channel data by the Format layer. Segments reached and organizations visited are attached via join tables.

Field	Type	Required	Notes
id	ulid	yes	PK
agenda_id	ulid	no	FK → marketing_agenda — nullable for ad-hoc activities
entity_id	ulid	yes	FK → core_entity
unit_id	ulid	no	FK → core_unit
channel_id	ulid	yes	FK → marketing_channel — one channel per execution
date	date	yes
leads	int	no	default 0
follow_ups	int	no	default 0
closings	int	no	default 0 — cross-checked against Order channel data by Format layer
notes	string	no

`marketing_activity_segment`

Join table linking Activities to Segments (M2M). Records which audience segments were actually reached during the activity.

Field	Type	Required	Notes
activity_id	ulid	yes	FK → marketing_activity
segment_id	ulid	yes	FK → marketing_segment

`marketing_activity_organization`

Join table linking Activities to Organizations (M2M). Records which specific schools or institutions were actually visited or reached during the activity.

Field	Type	Required	Notes
activity_id	ulid	yes	FK → marketing_activity
organization_id	ulid	yes	FK → db_organization

`target_` — Target

`target_metric`

The measurable quantity a Target is set against.

Field	Type	Required	Notes
id	ulid	yes	PK
name	string	yes	Revenue, Orders, Leads, Follow-ups, Closings
source_term	string	no

`target_item`

A goal set for a specific metric over a specific period. Targets are universal — they can be set at entity, unit, item, or marketing channel level by leaving the corresponding FK fields null. The report layer checks for misalignments between levels independently; targets at different levels are not required to sum to each other.

Field	Type	Required	Notes
id	ulid	yes	PK
entity_id	ulid	yes	FK → core_entity — always required
unit_id	ulid	no	FK → core_unit — null means entity-level
item_id	ulid	no	FK → catalog_item — null means unit or entity-level
channel_id	ulid	no	FK → marketing_channel
metric_id	ulid	yes	FK → target_metric
period_year	int	yes	e.g. 2026
period_month	int	no	1–12 — null means annual target
value	decimal	yes

Business Rules

Enrollment requires a Participant. A commerce_order with order type Enrollment must have a participant_id. All other order types may leave it null.
Customer type is stored and computable. customer_type_id on commerce_order is stored at sync time (taken from source data). It can be recomputed by counting all prior orders for the same participant_id across any Unit: none found → New; most recent order within 1 year → Renewal; most recent order over 1 year ago → Alumni.
Targets are independent across levels. A unit-level target and an entity-level target for the same metric and period are separate records. The Format layer reports both and flags misalignments; it does not enforce that they must sum.
Marketing closings are cross-checked, not enforced. The closings count on marketing_activity should equal the count of orders where channel_id matches the activity's channel for the same unit and date range. The Format layer reports discrepancies but does not block data entry.
Revenue is always derived from Transactions. No revenue field is stored on commerce_order. All revenue figures are computed by summing finance_transaction.amount grouped by unit, period, and any other dimension at query time.
Relation is directed. A db_relation record from Person A to Person B with type Guardian means A is guardian of B — not the reverse. The reverse relationship, if needed, requires a separate record.
Catalog price resolution. When resolving the price for an order, first check for a catalog_price record matching the variant_id in the active price list. If not found, fall back to the item_id price in the same list.
Brand-Unit consistency. A catalog_item references both a brand_id and a unit_id. The unit must be a member of the brand (i.e. a catalog_brand_unit record must exist for that brand-unit pair).

Entity Relationship Diagram (Detailed)

Full columns. PKs marked with PK, FKs marked with FK.