Entity Relationship Document (ERD)

Domus — Digital Administration for Kristus Raja Barong Tongkok Catholic Parish

MVP Version

Document Version: 1.3.0 Status: In Progress Last Updated: 12 April 2026

1. Enum Definitions

All enums use the const object + as const pattern. Defined in packages/core/src/entity/enums.ts.

User & Parishioner

export const AccountStatus = {
  Pending: 'pending',
  Approved: 'approved',
  Rejected: 'rejected',
} as const

export const Gender = {
  Male: 'male',
  Female: 'female',
} as const

export const EducationLevel = {
  Kindergarten: 'kindergarten',
  Primary: 'primary',
  JuniorHigh: 'junior-high',
  SeniorHigh: 'senior-high',
  Diploma1: 'diploma-1',
  Diploma2: 'diploma-2',
  Diploma3: 'diploma-3',
  Diploma4: 'diploma-4',
  Bachelor: 'bachelor',
  Master: 'master',
  Doctorate: 'doctorate',
  SpecialNeeds: 'special-needs',
  NonFormal: 'non-formal',
  Other: 'other',
} as const

Migration note: Previous EducationLevel values (junior, senior, diploma) must be migrated via Drizzle migration before deployment. Mapping: junior → junior-high, senior → senior-high, diploma → diploma-3.

Organization

export const OrgType = {
  Region: 'region',
  Station: 'station',
  BEC: 'bec',
  Categorical: 'categorical',
} as const

export const EnrollmentStatus = {
  Pending: 'pending',
  Active: 'active',
  Inactive: 'inactive',
} as const

Event

export const EventVisibility = {
  Public: 'public',
  Private: 'private',
} as const

export const EventStatus = {
  Published: 'published',
  Completed: 'completed',
} as const

Attendance & RSVP

export const AttendanceMethod = {
  QrCode: 'qr-code',
  Gps: 'gps',
  Manual: 'manual',
} as const

export const AttendanceStatus = {
  Present: 'present',
  Pending: 'pending',
  Absent: 'absent',
} as const

export const RsvpStatus = {
  Attending: 'attending',
  NotAttending: 'not-attending',
  Maybe: 'maybe',
} as const

Finance

export const TransactionType = {
  Income: 'income',
  Expense: 'expense',
} as const

export const PeriodStatus = {
  Open: 'open',
  Locked: 'locked',
} as const

Notification

export const NotificationChannel = {
  InApp: 'in-app',
  Email: 'email',
  Push: 'push',
} as const

export const NotificationStatus = {
  Pending: 'pending',
  Sent: 'sent',
  Failed: 'failed',
} as const

Ordo (Liturgical Calendar)

export const CelebrationRank = {
  Solemnity: 'solemnity',
  Feast: 'feast',
  Memorial: 'memorial',
  Commemoration: 'commemoration',
  Feria: 'feria',
} as const

export const LiturgicalColor = {
  Purple: 'purple',
  White: 'white',
  Red: 'red',
  Green: 'green',
  Rose: 'rose',
  Black: 'black',
} as const

export const OrdoSource = {
  Lagumisa: 'lagumisa',
  Manual: 'manual',
} as const

2. Entities & Table Schemas

Storage Legend

Fields that store file references use one of two storage backends:

Tag	Storage	Value Stored	Access
`[R2]`	Cloudflare R2	Public URL	Direct `<img src>`
`[GDrive]`	Google Drive	Drive Viewer URL	Google Drive Viewer

PII Note: Fields tagged [GDrive — PII] are permanently deleted from Google Drive upon enrollment approval or rejection. The field is then set to null in the database.

2.1 Users & Parishioners

`users` (Better Auth — auto-generated + additional fields)

Field	Type	Description
`id`	uuid v7	Primary key (managed by Better Auth)
`name`	string	Name from Google OAuth
`email`	string	Email from Google OAuth, unique
`emailVerified`	boolean	Email verification status
`image`	string?	`[R2]` Custom profile photo URL. Defaults to Google OAuth photo URL if no custom upload.
`accountStatus`	AccountStatus	`pending` \| `approved` \| `rejected`. Default: `pending`
`createdAt`	datetime
`updatedAt`	datetime
`deletedAt`	datetime?	Soft delete

Tables sessions and accounts are fully managed by Better Auth.

`parishioners`

Parish member demographic data. Can exist without a Domus account (for parishioners without a phone/email).

Field	Type	Description
`id`	uuid v7	Primary key
`userId`	uuid v7?	FK → `users.id`. Nullable if they don't have an account yet. `ON DELETE CASCADE`.
`syncId`	integer?	DUK system ID. `UNIQUE`. Nullable for parishioners created manually in Domus.
`syncedAt`	datetime?	Timestamp of last successful sync-back to DUK.
`fullName`	string	Required
`honorific`	string?	Title prefix. Free text, autocomplete from existing values
`suffix`	string?	Title suffix. Free text, autocomplete from existing values
`baptismName`	string?	Baptism name
`familyCardNumber`	string?	Family card number (No. KK)
`birthPlace`	string?
`birthDate`	date?
`gender`	Gender?	`male` \| `female`
`bloodType`	string?	`a` \| `b` \| `o` \| `ab` \| `unknown`
`ethnicity`	string?	Free text. Autocomplete from existing values.
`religion`	string?	`catholic` \| `catechumen` \| `christian` \| `confucian` \| `islam` \| `hindu` \| `buddhist` \| `other`
`citizenship`	string?	`citizen` \| `foreigner`
`educationLevel`	EducationLevel?	See enum definition above
`educationMajor`	string?	Faculty / major
`occupation`	string?	Employment type
`profession`	string?	Professional field
`skills`	string?	Free text
`familyRelation`	string?	`husband` \| `wife` \| `child` \| `grandchild` \| `in-law` \| `parent` \| `household-assistant` \| `other-family` \| `other`
`maritalStatus`	string?	`single` \| `married` \| `divorced` \| `widower` \| `widow` \| `single-parent` \| `separated` \| `unregistered-marriage`
`marriageDate`	date?
`idCardNumber`	string?	National ID number (NIK)
`idCardPhoto`	string?	`[GDrive — PII]` Google Drive Viewer URL. Permanently deleted after verification (approved/rejected).
`address`	string?	Street address
`regencyId`	integer?	FK → `regencies.id`
`districtId`	integer?	FK → `districts.id`
`villageId`	integer?	FK → `villages.id`
`houseStatus`	string?	`owned` \| `family-owned` \| `borrowed` \| `company-provided` \| `rented`
`phone`	string?	Mobile / telephone number
`physicalCondition`	string?	`normal` \| `hearing-impaired` \| `speech-impaired` \| `visually-impaired` \| `mentally-impaired` \| `behavioral-impaired` \| `multiple-disabilities` \| `physically-impaired` \| `autism` \| `gifted` \| `mental-disorder` \| `intellectually-impaired`
`economicStatus`	string?	`pre-prosperous` \| `prosperous`
`socialActivity`	string?	`neighborhood-official` \| `political-party` \| `ngo` \| `mass-organization` \| `regular-citizen` \| `traditional-institution`
`createdAt`	datetime
`updatedAt`	datetime
`deletedAt`	datetime?	Soft delete

2.2 Diocese, Vicariate & Parish

`dioceses`

Field	Type	Description
`id`	uuid v7	Primary key
`name`	string	Required
`address`	string?
`phone`	string?
`email`	string?
`logo`	string?	`[R2]` Cloudflare R2 public URL
`website`	string?
`description`	string?
`createdAt`	datetime
`updatedAt`	datetime
`deletedAt`	datetime?	Soft delete

`vicariates`

Represents the Kevikepan level — a territorial unit between diocese and parish.

Field	Type	Description
`id`	uuid v7	Primary key
`dioceseId`	uuid v7	FK → `dioceses.id`
`syncId`	integer?	DUK system ID. Nullable. Used for sync reference.
`name`	string	Required
`address`	string?
`phone`	string?
`email`	string?
`createdAt`	datetime
`updatedAt`	datetime
`deletedAt`	datetime?	Soft delete

`parishes`

Field	Type	Description
`id`	uuid v7	Primary key
`vicariateId`	uuid v7	FK → `vicariates.id`
`syncId`	integer?	DUK system ID. Nullable. Used for sync reference.
`name`	string	Required
`address`	string?
`phone`	string?
`email`	string?
`logo`	string?	`[R2]` Cloudflare R2 public URL
`website`	string?
`description`	string?
`createdAt`	datetime
`updatedAt`	datetime
`deletedAt`	datetime?	Soft delete

2.3 Administrative Regions

Lookup tables seeded from BPS/Kemendagri data. Used to store and query parishioner domicile data. These tables use the BPS integer ID directly as the primary key. No soft delete — they are seeded once and not independently managed in Domus.

Data source: Kemendagri/BPS regional data (4-level hierarchy: province → regency → district → village).

`provinces`

Field	Type	Description
`id`	integer	Primary key. BPS province ID (e.g. `64` for Kalimantan Timur)
`name`	text	Required

`regencies`

Represents kabupaten/kota level.

Field	Type	Description
`id`	integer	Primary key. BPS regency ID (e.g. `6401`)
`provinceId`	integer	FK → `provinces.id`
`syncId`	integer?	DUK system ID. Nullable. Used for sync-back reference.
`name`	text	Required

`districts`

Represents kecamatan level.

Field	Type	Description
`id`	integer	Primary key. BPS district ID (e.g. `6401010`)
`regencyId`	integer	FK → `regencies.id`
`syncId`	integer?	DUK system ID. Nullable. Used for sync-back reference.
`name`	text	Required

`villages`

Represents kelurahan/desa level.

Field	Type	Description
`id`	integer	Primary key. BPS village ID (e.g. `6401010009`)
`districtId`	integer	FK → `districts.id`
`syncId`	integer?	DUK system ID. Nullable. Used for sync-back reference.
`name`	text	Required

2.4 Organizations

`organizations` (Better Auth — auto-generated + additional fields)

Field	Type	Description
`id`	uuid v7	Primary key (managed by Better Auth)
`parishId`	uuid v7	FK → `parishes.id`
`name`	string	Required
`shortName`	string?	Abbreviation or alternative name
`slug`	string	Unique. Auto-generated from name, can be manually changed by admin
`logo`	string?	`[R2]` Cloudflare R2 public URL
`cover`	string?	`[R2]` Cloudflare R2 public URL
`type`	OrgType	`region` \| `station` \| `bec` \| `categorical`
`parentId`	uuid v7?	FK → `organizations.id`. Self-referencing for nested orgs
`joinId`	string?	Nano ID for member registration link. `null` = org is not accepting new members
`syncId`	integer?	DUK system ID. Nullable. Used for sync reference.
`createdAt`	datetime
`updatedAt`	datetime
`deletedAt`	datetime?	Soft delete

Territorial hierarchy rules:

region → parentId: null
station → parentId: region (optional)
bec → parentId: region | station | null

Categorical hierarchy rules:

Maximum 3 levels deep
parentId optional at all levels

joinId rotation:

Rotated periodically via cron job (Cloudflare Workers)
Organizations with joinId set to null do not accept new members
Rotation is performed simultaneously for all organizations that have a joinId (not null)

`org_units`

Field	Type	Description
`id`	uuid v7	Primary key
`organizationId`	uuid v7	FK → `organizations.id`
`name`	string	Required
`description`	string?
`position`	integer	Sort order for display. Default: `0`
`createdAt`	datetime
`updatedAt`	datetime
`deletedAt`	datetime?	Soft delete

`members` (Better Auth — access control only)

Field	Type	Description
`id`	uuid v7	Primary key (managed by Better Auth)
`organizationId`	uuid v7	FK → `organizations.id`
`userId`	uuid v7	FK → `users.id`
`role`	string	`owner` \| `admin` \| `member`
`createdAt`	datetime

`org_enrollments`

Field	Type	Description
`id`	uuid v7	Primary key
`parishionerId`	uuid v7	FK → `parishioners.id`
`organizationId`	uuid v7	FK → `organizations.id`
`unitId`	uuid v7?	FK → `org_units.id`
`status`	EnrollmentStatus	`pending` \| `active` \| `inactive`
`joinedAt`	date?	Official join date, can be filled in manually
`createdAt`	datetime
`updatedAt`	datetime
`deletedAt`	datetime?	Soft delete

Semantics for unitId in org_enrollments:

Represents the unit selected by the parishioner during self-registration via the joinId form.
This is not necessarily the active unit; active unit/position is managed separately via org_placements.

Constraint: UNIQUE (parishionerId, organizationId)

`org_terms`

Field	Type	Description
`id`	uuid v7	Primary key
`organizationId`	uuid v7	FK → `organizations.id`
`name`	string	Required. e.g. "Kepengurusan 2024-2027"
`startDate`	date?	Nullable
`endDate`	date?	Nullable
`skNumber`	string?	Nullable
`skDate`	date?	Nullable
`description`	string?	Nullable
`createdAt`	datetime
`updatedAt`	datetime
`deletedAt`	datetime?	Soft delete

`org_placements`

Field	Type	Description
`id`	uuid v7	Primary key
`enrollmentId`	uuid v7	FK → `org_enrollments.id`
`termId`	uuid v7?	FK → `org_terms.id`. Nullable
`unitId`	uuid v7?	FK → `org_units.id`. Nullable
`position`	string	Position/role name. Default: `'anggota'`
`order`	integer	Sort order within unit. Default: `0`
`startDate`	date?	Nullable
`endDate`	date?	Nullable
`createdAt`	datetime
`updatedAt`	datetime
`deletedAt`	datetime?	Soft delete

2.5 Ordo (Liturgical Calendar)

`ordo`

Master liturgical calendar. One record per celebration per day. For days with multiple masses (e.g., Christmas: Midnight, Dawn, Day), each mass is a separate record distinguished by massLabel.

Field	Type	Description
`id`	uuid v7	Primary key
`date`	date	Liturgical date. Required.
`name`	string	Celebration name. e.g. `"HARI MINGGU ADVEN IV"`. Required.
`rank`	CelebrationRank	`solemnity` \| `feast` \| `memorial` \| `commemoration` \| `feria`. Required.
`color`	LiturgicalColor	`purple` \| `white` \| `red` \| `green` \| `rose` \| `black`. Required.
`massLabel`	string?	Mass label for days with multiple masses. e.g. `"Misa Malam"`, `"Misa Fajar"`, `"Misa Siang"`. Null if only one mass.
`readings`	text[]	Array of scripture reading references. e.g. `["Yes. 9:1-6", "Mzm. 96:1-2a", "Tit. 2:11-14", "Luk. 2:1-14"]`
`songs`	string?	Raw Puji Syukur song number suggestions. e.g. `"PS 451, 452, 453, 454"`
`source`	OrdoSource	`lagumisa` \| `manual`. Default: `manual`.
`createdBy`	text?	FK → `users.id`. Null if seeded from scraper.
`createdAt`	datetime
`updatedAt`	datetime
`deletedAt`	datetime?	Soft delete

Constraint: UNIQUE (date, massLabel) — prevents duplicate entries for the same mass on the same day. massLabel uses NULLS NOT DISTINCT so null is treated as a unique value per date.

Note: songs is stored as raw text to preserve the original Puji Syukur numbering format from lagumisa.web.id, including range notations (e.g., "464 (1, 2, 4)"). Parsing into structured data is deferred to a future iteration.

2.6 Events & Attendance

`events`

Field	Type	Description
`id`	uuid v7	Primary key
`organizationId`	uuid v7?	FK → `organizations.id`. Nullable for public events
`ordoId`	uuid v7?	FK → `ordo.id`. Optional link to the liturgical celebration this event corresponds to.
`name`	string	Required
`description`	string?
`location`	string	Required
`latitude`	decimal?	For GPS validation
`longitude`	decimal?	For GPS validation
`radiusMeters`	integer	Default: `100`
`startDateTime`	datetime	Required
`endDateTime`	datetime?
`qrCode`	string	Auto-generated when the event is created
`visibility`	EventVisibility	`public` \| `private`. Default: `private`
`isRsvpEnabled`	boolean	Default: `false`
`status`	EventStatus	`published` \| `completed`. Auto `completed` when `endTime` is reached
`createdBy`	uuid v7	FK → `users.id`
`createdAt`	datetime
`updatedAt`	datetime
`deletedAt`	datetime?	Soft delete

Visibility rules:

private: created by an Organization Admin, organizationId required
public: created by parish-admin, organizationId nullable

`rsvp`

Field	Type	Description
`id`	uuid v7	Primary key
`eventId`	uuid v7	FK → `events.id`
`parishionerId`	uuid v7	FK → `parishioners.id`
`status`	RsvpStatus	`attending` \| `notAttending` \| `maybe`
`createdAt`	datetime
`updatedAt`	datetime
`deletedAt`	datetime?	Soft delete

Constraint: UNIQUE (eventId, parishionerId)

`attendances`

Field	Type	Description
`id`	uuid v7	Primary key
`eventId`	uuid v7	FK → `events.id`
`parishionerId`	uuid v7	FK → `parishioners.id`
`method`	AttendanceMethod	`qrCode` \| `gps` \| `manual`
`status`	AttendanceStatus	`present` \| `pending` \| `absent`
`verifiedBy`	uuid v7?	FK → `users.id`. Nullable, filled upon manual approval
`verifiedAt`	datetime?	Nullable
`createdAt`	datetime
`updatedAt`	datetime
`deletedAt`	datetime?	Soft delete

Constraint: UNIQUE (eventId, parishionerId)

2.7 Finance

`financial_periods`

Field	Type	Description
`id`	uuid v7	Primary key
`month`	integer	1–12
`year`	integer
`status`	PeriodStatus	`open` \| `locked`
`lockedBy`	uuid v7?	FK → `users.id`. Treasurer only
`lockedAt`	datetime?
`createdAt`	datetime
`updatedAt`	datetime
`deletedAt`	datetime?	Soft delete

Constraint: UNIQUE (month, year)

`transaction_categories`

Field	Type	Description
`id`	uuid v7	Primary key
`name`	string	Required
`type`	TransactionType	`income` \| `expense`
`createdAt`	datetime
`updatedAt`	datetime
`deletedAt`	datetime?	Soft delete

`transactions`

Field	Type	Description
`id`	uuid v7	Primary key
`periodId`	uuid v7	FK → `financial_periods.id`
`categoryId`	uuid v7	FK → `transaction_categories.id`
`type`	TransactionType	`income` \| `expense`
`amount`	decimal	Required
`description`	string	Required
`date`	date	Required
`receiptPhoto`	string?	`[GDrive]` Google Drive Viewer URL. Optional
`createdBy`	uuid v7	FK → `users.id`
`updatedBy`	uuid v7?	FK → `users.id`. Nullable
`createdAt`	datetime
`updatedAt`	datetime
`deletedAt`	datetime?	Soft delete

2.8 Attachments

`attachments`

Field	Type	Description
`id`	uuid v7	Primary key
`referenceId`	string	ID of the related entity
`referenceType`	string	Entity name in PascalCase: `Event` \| `Organization` \| `Term`
`name`	string	Display name of the file
`fileId`	string	Google Drive file ID
`viewerUrl`	string	Google Drive Viewer URL
`mimeType`	string?	MIME type of the file
`size`	integer?	File size in bytes
`uploadedBy`	uuid v7	FK → `users.id`
`createdAt`	datetime
`deletedAt`	datetime?	Soft delete

2.9 Notifications

`notifications`

Field	Type	Description
`id`	uuid v7	Primary key
`userId`	uuid v7	FK → `users.id`
`title`	string	Required
`body`	string	Required
`type`	string	Free text (e.g., `approval`, `rejection`, `event`)
`channel`	NotificationChannel	`inApp` \| `email` \| `push`
`isRead`	boolean	Default: `false`. Relevant only for the `inApp` channel
`status`	NotificationStatus	`pending` \| `sent` \| `failed`
`referenceId`	string?	ID of the related entity
`referenceType`	string?	Name of the related entity in PascalCase
`sentAt`	datetime?
`createdAt`	datetime
`deletedAt`	datetime?	Soft delete

3. Entity Relationships

erDiagram
    dioceses ||--o{ vicariates : "has"
    vicariates ||--o{ parishes : "has"
    parishes ||--o{ organizations : "has"

    provinces ||--o{ regencies : "has"
    regencies ||--o{ districts : "has"
    districts ||--o{ villages : "has"

    users ||--o| parishioners : "has"
    users ||--o{ members : "has"
    users ||--o{ notifications : "receives"
    users ||--o{ transactions : "records"
    users ||--o{ financial_periods : "locks"
    users ||--o{ attendances : "verifies"
    users ||--o{ events : "creates"
    users ||--o{ attachments : "uploads"
    users ||--o{ ordo : "creates"

    parishioners ||--o{ org_enrollments : "enrolled in"
    parishioners ||--o{ rsvp : "submits"
    parishioners ||--o{ attendances : "records"
    parishioners }o--|| regencies : "lives in"
    parishioners }o--|| districts : "lives in"
    parishioners }o--|| villages : "lives in"

    organizations ||--o{ members : "has"
    organizations ||--o{ org_units : "has"
    organizations ||--o{ org_enrollments : "has"
    organizations ||--o{ org_terms : "has"
    organizations ||--o{ events : "hosts"
    organizations ||--o| organizations : "parentId"

    org_units ||--o{ org_placements : "linked to"
    org_terms ||--o{ org_placements : "linked to"
    org_enrollments ||--o{ org_placements : "has"

    ordo ||--o{ events : "linked to"

    events ||--o{ rsvp : "receives"
    events ||--o{ attendances : "records"

    financial_periods ||--o{ transactions : "has"
    transaction_categories ||--o{ transactions : "categorizes"

4. Key Flows

4.1 Self-Registration Flow

flowchart TD
    A[User visits /join/joinId] --> B[Redirect to /login]
    B --> C[Google OAuth]
    C --> D{Email registered?}
    D -- No --> E[Create users\naccountStatus: pending]
    D -- Yes --> F[Continue]
    E --> F
    F --> G[Show organization info\n+ org_units options]
    G --> H[User fills form:\n- unitId optional\n- position default: anggota\n- fullName required\n- idCardPhoto required]
    H --> I[User clicks Register]
    I --> J[Upload idCardPhoto to Google Drive\nStore fileId + viewerUrl]
    J --> K[Create parishioners record]
    K --> L[Create org_enrollments record\nstatus: pending]

4.2 Approval / Rejection by Admin

flowchart TD
    A[Admin reviews registration] --> B{Decision}
    B -- Approve --> C[Capture email & name data for notification]
    C --> D[org_enrollments.status → active]
    D --> E{users.accountStatus = pending?}
    E -- Yes --> F[users.accountStatus → approved]
    E -- No --> G[Continue]
    F --> G
    G --> H[IPrivateStorage.delete idCardPhoto fileId]
    H --> I[parishioners.idCardPhoto → null]
    I --> J[Send notification: inApp + email]

    B -- Reject --> K[Capture email & name data for notification]
    K --> L[IPrivateStorage.delete idCardPhoto fileId]
    L --> M{users.accountStatus = approved?}
    M -- No --> N[Delete user record\nCascades to parishioners & enrollments]
    M -- Yes --> O[users.accountStatus → rejected]
    O --> P[Soft delete org_enrollments]
    P --> Q[Soft delete parishioners]
    Q --> R[Send notification: email]
    N --> R

4.3 Attendance Flow

flowchart TD
    A[User checks in] --> B{Method}

    B --> C[QR Code]
    C --> C1[Validate eventId from QR]
    C1 --> C2[Create attendances\nmethod: qrCode\nstatus: present]

    B --> D[GPS]
    D --> D1{Coordinates valid\nwithin radius?}
    D1 -- Yes --> D2[Create attendances\nmethod: gps\nstatus: present]
    D1 -- No --> D3[User submits manual request]
    D3 --> D4[Create attendances\nmethod: manual\nstatus: pending]
    D4 --> D5{Admin reviews}
    D5 -- Approve --> D6[status → present]
    D5 -- Reject --> D7[status → absent]

    B --> E[Manual by Admin]
    E --> E1[Create attendances\nmethod: manual\nstatus: present\nno approval needed]

4.4 Join ID Rotation

flowchart TD
    A[Cron job runs\nCloudflare Workers] --> B[Query all organizations\nWHERE joinId IS NOT NULL]
    B --> C[Generate new Nano ID\nfor each organization]
    C --> D[Update joinId]
    D --> E[Old link automatically invalidated]

4.5 Attachment Upload Flow

flowchart TD
    A[Admin uploads file] --> B[Validate referenceType & referenceId]
    B --> C[Upload file to Google Drive\nvia IPrivateStorage.upload]
    C --> D[Receive fileId + viewerUrl]
    D --> E[Create attachments record\nreferenceType: Event | Organization | Term]
    E --> F[Return viewerUrl to client]

4.6 Ordo Seeding Flow

flowchart TD
    A[Cron / manual trigger] --> B[SyncClient scrapes lagumisa.web.id/saranps.php]
    B --> C[Write raw data to sync_staging.liturgi]
    C --> D[Transform: map color & rank to English enums]
    D --> E[Upsert into ordo\nON CONFLICT date + massLabel DO UPDATE]
    E --> F[source = lagumisa\ncreatedBy = null]

5. Soft Delete Strategy

All tables use soft delete via the deletedAt field:

A null value means the record is active.
A timestamp value means the record has been deleted.
All default queries must include the filter WHERE deletedAt IS NULL.
Hard delete is only performed for sensitive data that must be permanently removed (e.g., idCardPhoto after verification via IPrivateStorage.delete(fileId)).

Exception: provinces, regencies, districts, and villages do not use soft delete — these are lookup tables seeded from BPS/Kemendagri data and are not independently managed in Domus.

6. External Systems

6.1 DUK Sync — Staging Tables

Temporary staging tables used during the DUK data import process. Raw scraped data is stored here before being transformed and loaded into Domus production tables. Defined in the sync_staging PostgreSQL schema.

`sync_staging.umat`

Stores raw parishioner data scraped from DUK.

Field	Type	Description
`id`	serial	Primary key
`syncId`	integer	DUK umat ID. `UNIQUE`.
`rawData`	jsonb	Full raw key-value data from DUK detail page
`scrapedAt`	timestamptz	Default: `NOW()`
`transformedAt`	timestamptz?	Null if not yet transformed into production tables
`error`	text?	Null if no error. Populated when transform fails for this record.

`sync_staging.referensi`

Stores raw reference data scraped from DUK (diocese, vicariate, parish, organization, city, district, village).

Field	Type	Description
`id`	serial	Primary key
`type`	text	`diocese` \| `vicariate` \| `parish` \| `station` \| `bec` \| `regency` \| `district` \| `village`
`syncId`	integer	DUK system ID
`label`	text	Display name
`parentSyncId`	integer?	Parent entity DUK ID. Used for hierarchical data.
`scrapedAt`	timestamptz	Default: `NOW()`

Constraint: UNIQUE (type, syncId)

`sync_staging.sakramen`

Stores raw sacrament data scraped from DUK. Imported as-is — not transformed into production tables in MVP.

Field	Type	Description
`id`	serial	Primary key
`syncId`	integer	DUK umat ID
`type`	text	`baptism` \| `marriage`
`rawData`	jsonb	Full raw sacrament data
`scrapedAt`	timestamptz	Default: `NOW()`

`sync_staging.liturgi`

Stores raw liturgical calendar data scraped from lagumisa.web.id. Transformed into the ordo table.

Field	Type	Description
`id`	serial	Primary key
`celebrationName`	text	Raw celebration name from HTML
`month`	text	Month name (e.g., `"Desember"`)
`dayName`	text	Day name (e.g., `"Minggu"`)
`dateNumber`	integer	Date number (e.g., `25`)
`isSunday`	boolean	`true` if `class="iconminggu"` on `<time>` element
`liturgicalColor`	text	Raw color id from HTML (e.g., `"ccungu"`, `"ccputih"`)
`massLabel`	text?	Sub-mass label if applicable (e.g., `"Misa Malam"`)
`readings`	text[]	Array of raw scripture reference strings
`songs`	text?	Raw Puji Syukur song suggestion string
`scrapedAt`	timestamptz	Default: `NOW()`

Constraint: UNIQUE (celebration_name, month, date_number, mass_label) with NULLS NOT DISTINCT

This document is a living document and will be updated as the Domus database schema evolves.

FilesExpand file tree

erd.md

Latest commit

History

erd.md

File metadata and controls

Entity Relationship Document (ERD)

Domus — Digital Administration for Kristus Raja Barong Tongkok Catholic Parish

MVP Version

1. Enum Definitions

User & Parishioner

Organization

Event

Attendance & RSVP

Finance

Notification

Ordo (Liturgical Calendar)

2. Entities & Table Schemas

Storage Legend

2.1 Users & Parishioners

users (Better Auth — auto-generated + additional fields)

parishioners

2.2 Diocese, Vicariate & Parish

dioceses

vicariates

parishes

2.3 Administrative Regions

provinces

regencies

districts

villages