Data Model Reference
This reference condenses the original spreadsheet-derived model into implementation guidance.
Tables
| Table | Required purpose |
|---|---|
app_user | System user: name, email, status, timestamps |
role | Named role |
user_role | Many-to-many users to roles |
permission | Module/action permission |
role_permission | Many-to-many roles to permissions |
department | Owning unit with optional parent |
location | Physical location with optional parent |
party | User, department, or vendor participant |
asset | Current asset state/read model |
asset_tag | Tag code, technology, batch, status, binding proof |
asset_event | Append-only event log |
handoff_transaction | Custody transfer header |
handoff_line | Assets in a handoff |
discrepancy | Reported mismatch or conflict |
approval | Maker-checker workflow |
financial_profile | Acquisition cost, depreciation, book value |
compliance_item | Dated warranty/certificate/maintenance/lease obligation |
document | File attached to an asset |
work_order | Maintenance task |
Common Types
| Type | Use |
|---|---|
BIGSERIAL | database primary keys where server-generated IDs are acceptable |
BIGINT | foreign keys |
TEXT | names, statuses, references |
NUMERIC(14,2) | BDT money fields |
BOOLEAN | flags |
DATE | date-only business fields |
TIMESTAMPTZ | audit and lifecycle timestamps |
JSONB | event payloads and flexible metadata |
Key Status Fields
| Field | Values |
|---|---|
asset.status | in_use, in_storage, under_repair, in_transit, lost, disposed |
asset.ownership_type | owned, leased, rented, borrowed, byod, vendor |
asset_tag.status | printed, attached, verified, damaged, replaced |
handoff_transaction.status | open, overdue, closed, cancelled |
discrepancy.status | open, investigating, resolved, dismissed |
approval.status | pending, approved, rejected |
compliance_item.status | upcoming, due, overdue, closed |
work_order.status | open, in_progress, done, cancelled |
Implementation Notes
- Prefer UUID public identifiers even when using numeric database IDs internally.
- Add unique constraints for user email, active tag code, and idempotency keys.
- Index foreign keys and high-cardinality query filters.
- Keep financial precision in the database and API contract.
- Do not allow Hibernate auto-update outside disposable development databases.