Updated Data Model Reference

This document explains data-model.updated.dbml, the expanded PostgreSQL schema for AIS.

The model covers the full asset lifecycle:

Asset master data and organizational context
Receiving and inspection
Onboarding and tag issuance
Assignment and handoff
Maintenance and service
Compliance and disposal
Approvals, notifications, reporting, and audit

Model Characteristics

Characteristic	Meaning
Multi-tenant	Most business tables carry `organization_id` so data is isolated per organization.
Public vs internal IDs	`public_id` UUIDs are API-safe identifiers; `BIGSERIAL` IDs are internal relational keys.
PostgreSQL-first	The schema assumes PostgreSQL features such as `CITEXT`, `JSONB`, `INET`, generated UUIDs, and strong indexing support.
Audit-friendly	Business events and forensic audit events are both modeled explicitly.
Workflow-oriented	Receiving, onboarding, assignment, maintenance, approval, and disposal all have dedicated workflow tables and statuses.
Optimistic concurrency	Several mutable workflow tables include a `version` column.
Soft deletion where needed	Master/reference entities often use `deleted_at` instead of hard deletion.

Core Conventions

Convention	Use
`created_at`, `updated_at`	Standard timestamps for mutable tables
`deleted_at`	Soft delete marker on reference/master tables
`idempotency_key`	Deduplicates retried writes for workflow actions
`JSONB` payloads	Flexible import data, validation errors, binding proof, metadata, and audit detail
Status enums	Keep lifecycle transitions explicit and queryable

Functional Areas

Organization and Access Control

Table	Purpose
`organization`	Root tenant record with name, code, default currency, and timezone.
`department`	Department hierarchy within one organization.
`location`	Physical site or sub-location hierarchy for receiving, storage, and assignment.
`app_user`	System user tied to an organization and optionally a department.
`team`	Named team inside an organization.
`team_member`	Membership bridge between users and teams.
`role`	Organization-scoped role definition.
`user_role`	User-to-role assignment bridge.
`permission`	Global permission catalog by `module` and `action`.
`role_permission`	Role-to-permission grant bridge.

Parties, Vendors, and Asset Classification

Table	Purpose
`vendor`	Supplier, service provider, or manufacturer reference.
`party`	Reusable assignment/handoff participant abstraction over user, team, department, or vendor.
`asset_category`	Category hierarchy with default useful life and required field rules.
`asset_model`	Normalized equipment model linked to category and optional manufacturer vendor.
`document`	File metadata store for uploaded evidence and attachments.

Receiving and Inspection

Table	Purpose
`goods_receipt`	Receiving header for a delivery or purchase arrival.
`goods_receipt_line`	Expected, delivered, accepted, and rejected quantities per line item.
`receiving_inspection`	Inspection results for delivered units.
`replacement_request`	Follow-up request when delivered items fail or need vendor replacement.
`goods_receipt_document`	Receipt-to-document attachment bridge.

Receiving is designed for operational traceability:

goods_receipt.status tracks the business stage from draft through approval and closure.
goods_receipt_line separates expected, delivered, accepted, and rejected quantities.
Inspection and replacement are modeled separately so mismatches do not get flattened into one status field.

Onboarding and Asset Creation

Table	Purpose
`asset_onboarding_batch`	Import or manual creation batch for turning received items into assets.
`asset_onboarding_item`	Row-level onboarding decision, validation, and created asset/tag linkage.
`asset`	Current state projection for an asset.
`financial_profile`	One-to-one financial view for acquisition and depreciation data.

Important design detail:

asset is the current read model.
asset_event and audit_event preserve history.
asset_onboarding_item connects the source row to the eventual asset and optional asset_tag.

Tag Inventory and Scanning

Table	Purpose
`tag_batch`	Procurement or generation batch for asset tags.
`asset_tag`	Individual tag record that may be in storage, attached, replaced, or retired.
`tag_scan`	Immutable scan log for onboarding, handoff, maintenance, and other field operations.

The schema supports a staged tag lifecycle:

Tags can exist before attachment.
Tags can be stored at a location.
Replacements preserve linkage through replaced_tag_id.
Scan history is preserved separately from current tag state.

Assignment and Handoff

Table	Purpose
`asset_assignment`	Assignment of an asset to a person, team, or location.
`handoff_transaction`	Transfer workflow between parties or locations.
`handoff_line`	Asset-level release and receipt evidence within a handoff.
`discrepancy`	Mismatch or exception raised during receipt, handoff, or asset verification.

This part of the model separates steady-state custody from transfer workflow:

asset_assignment represents who or where the asset is assigned.
handoff_transaction represents the act of moving custody.
handoff_line captures release and acceptance evidence per asset.
asset.current_location_id and asset.current_custodian_party_id are read-optimized projections, not the only source of truth.

Maintenance and Service

Table	Purpose
`service_request`	User-submitted repair or support request for an asset.
`maintenance_schedule`	Recurring preventive maintenance definition.
`work_order`	Executable maintenance or repair job.
`maintenance_inspection`	Inspection findings during maintenance work.
`stock_item`	Spare part or consumable inventory.
`work_order_material`	Material requirement and issuance for a work order.
`work_order_document`	Work-order attachment bridge.

This lets AIS track both reactive and planned work:

Service requests can be triaged into work orders.
Maintenance schedules can generate recurring work.
Work orders can be in-house or vendor-managed.
Material issuance is normalized instead of being embedded in the work order payload.

Compliance, Review, and Disposal

Table	Purpose
`compliance_item`	Warranty, certificate, lease, inspection, license, or other due item.
`asset_review`	Review decision tied to an asset and optionally a compliance item.
`disposal_case`	Controlled disposal workflow with finance and execution fields.
`asset_document`	Asset-to-document attachment bridge.
`disposal_document`	Disposal-case attachment bridge.

This area supports end-of-life and obligation tracking:

Compliance items can recur and escalate through due dates.
Reviews capture management decisions such as retain, renew, or dispose.
Disposal is not a terminal flag on asset; it has its own governed case.

Approvals, Notifications, and Decisions

Table	Purpose
`approval_request`	Workflow header for approval of receipts, onboarding, work orders, or disposal.
`approval_step`	Individual approval routing steps by user or role.
`notification`	User notification queue with optional linkage to operational entities.
`management_decision`	Captures higher-level decisions derived from reports or operational findings.

Notable pattern:

approval_request uses multiple optional foreign keys but requires exactly one subject entity.
approval_step supports assignment to either a specific user or a role.
notification is a delivery log, not just a template catalog.

Events and Forensic Audit

Table	Purpose
`asset_event`	Business-facing asset timeline.
`audit_event`	Append-only forensic audit envelope for reads, writes, workflow actions, failures, and denied actions.
`audit_change`	Field-level before/after diff per audit event.
`audit_event_asset`	Bridge for relating one audit event to one or more assets.

The model deliberately keeps two history layers:

History Layer	Purpose
`asset_event`	Easy-to-read lifecycle narrative for asset operations
`audit_event` + `audit_change`	Compliance-grade evidence with actor, trigger, request, and diff details

Key Lifecycle Flow

flowchart LR
    A[goods_receipt] --> B[goods_receipt_line]
    B --> C[receiving_inspection]
    B --> D[asset_onboarding_batch]
    D --> E[asset_onboarding_item]
    E --> F[asset]
    E --> G[asset_tag]
    F --> H[asset_assignment]
    H --> I[handoff_transaction]
    F --> J[service_request]
    J --> K[work_order]
    F --> L[compliance_item]
    L --> M[asset_review]
    M --> N[disposal_case]
    F --> O[asset_event]
    O -. linked evidence .-> P[audit_event]

Important Enums

Asset and Tag Status

Enum	Values
`asset_status`	`pending_receipt`, `pending_registration`, `pending_tagging`, `in_storage`, `ready_for_use`, `assigned`, `in_use`, `in_transit`, `under_inspection`, `under_repair`, `on_hold`, `lost`, `retired`, `disposed`
`ownership_type`	`owned`, `leased`, `rented`, `borrowed`, `byod`, `vendor`
`tag_kind`	`permanent`, `temporary`, `alternate`
`tag_technology`	`qr`, `rfid`, `barcode`, `nfc`, `manual`, `other`
`asset_tag_status`	`ordered`, `received`, `generated`, `printed`, `stored`, `attached`, `verified`, `unreadable`, `damaged`, `replaced`, `removed`, `retired`

Receiving and Onboarding

Enum	Values
`receipt_status`	`draft`, `expected`, `received`, `inspecting`, `partially_accepted`, `accepted`, `rejected`, `pending_approval`, `approved`, `replacement_pending`, `closed`
`receipt_line_status`	`pending`, `matched`, `mismatched`, `partially_accepted`, `accepted`, `rejected`
`inspection_result`	`pending`, `passed`, `failed`, `conditional`
`onboarding_status`	`draft`, `validating`, `validation_failed`, `ready_for_review`, `confirmed`, `processing`, `completed`, `cancelled`
`onboarding_item_status`	`pending`, `invalid`, `valid`, `selected`, `tag_confirmed`, `created`, `skipped`
`onboarding_source`	`manual`, `file_import`, `gan`, `receipt`, `api`

Assignment, Maintenance, and Compliance

Enum	Values
`assignment_type`	`personal`, `team`, `location`
`assignment_status`	`pending_collection`, `in_transit`, `pending_acceptance`, `active`, `overdue`, `returned`, `cancelled`
`handoff_status`	`draft`, `scheduled`, `open`, `in_transit`, `overdue`, `closed`, `cancelled`
`service_request_status`	`submitted`, `triaged`, `converted`, `rejected`, `cancelled`, `closed`
`work_order_status`	`draft`, `pending_approval`, `approved`, `assigned`, `in_progress`, `waiting_for_parts`, `with_vendor`, `completed`, `closed`, `cancelled`
`compliance_status`	`upcoming`, `due`, `overdue`, `completed`, `waived`, `cancelled`
`compliance_type`	`warranty`, `certificate`, `maintenance`, `lease`, `inspection`, `license`, `other`
`review_decision`	`renew`, `retain`, `dispose`, `reinstate`, `investigate`
`disposal_status`	`draft`, `pending_financial_review`, `pending_approval`, `approved`, `executed`, `cancelled`

Audit and Notification

Enum	Values
`approval_status`	`pending`, `in_review`, `approved`, `rejected`, `cancelled`
`approval_step_status`	`pending`, `approved`, `rejected`, `skipped`
`notification_status`	`queued`, `sent`, `delivered`, `read`, `failed`, `cancelled`
`asset_event_type`	`created`, `updated`, `received`, `inspected`, `assigned`, `transferred`, `returned`, `tagged`, `retagged`, `scanned`, `repair_requested`, `repair_started`, `repaired`, `moved`, `lost`, `found`, `retained`, `reinstated`, `disposed`, `status_changed`, `discrepancy_reported`
`audit_actor_type`	`user`, `service_account`, `scheduled_job`, `integration`, `anonymous`
`audit_action_type`	`create`, `read`, `update`, `delete`, `restore`, `approve`, `reject`, `assign`, `transition`, `import`, `export`, `login`, `logout`, `system_action`
`audit_trigger_type`	`user_action`, `api`, `import`, `workflow`, `schedule`, `integration`, `migration`
`audit_outcome`	`success`, `failure`, `denied`, `partial`
`audit_change_type`	`add`, `replace`, `remove`

Critical Integrity Rules

The DBML notes several invariants that should be enforced with database constraints, partial indexes, triggers, or application safeguards.

Area	Rule
`party`	Exactly one of `user_id`, `team_id`, `department_id`, or `vendor_id` should be set, and it must match `party_type`.
`goods_receipt_line`	Quantities must be non-negative, and accepted plus rejected cannot exceed delivered.
`asset_tag`	A tag can remain unattached in inventory, but attached or verified states require a valid asset relationship.
`asset_assignment`	Location assignments require `assignee_location_id`; personal or team assignments require `assignee_party_id`.
`asset_assignment`	Only one active assignment per asset should exist at a time, using a partial unique index on open assignments.
`approval_request`	Exactly one subject foreign key should be populated.
`approval_step`	Exactly one approver target should be set: `approver_user_id` or `approver_role_id`.
`audit_event`	Treat as append-only; application roles should not update or delete it.

Indexing and Query Shape

The schema already reflects the main operational query patterns:

Per-organization unique business references such as receipt_no, batch_no, reference_no, sku, and asset_code
Dashboard and worklist filters by (organization_id, status, timestamp)
Timeline queries by (asset_id, occurred_at) for asset_event
Assignment, due, and notification queues filtered by due date or scheduled date
Search and reconciliation helpers such as serial numbers, vendor batch numbers, and scanned codes

Recommended Implementation Notes

Keep public_id as the external identifier in APIs and UI routes.
Enforce tenant isolation consistently in queries, foreign-key checks, and row-level security if enabled.
Use version for optimistic locking on mutable workflow records.
Treat asset.current_location_id and asset.current_custodian_party_id as projections that must stay transactionally aligned with assignment and handoff writes.
Partition audit_event by occurred_at if audit volume is expected to grow quickly.
Use immutable external storage for audit exports if regulatory retention matters.

Source of Truth

Use data-model.updated.dbml as the schema source of truth for detailed fields, indexes, and foreign keys. This document is the human-readable companion for architecture, implementation planning, and review.

Complete Field Catalog

This catalog lists every field declared in the DBML. Attributes are reproduced from the schema; a dash means the field has no explicit DBML attribute. Foreign-key targets and indexes remain defined in the source DBML.

`organization`

Field	PostgreSQL type	Declared attributes
`id`	`BIGSERIAL`	[pk, not null]
`public_id`	`UUID`	[not null, unique, default: `gen_random_uuid()`]
`name`	`TEXT`	[not null]
`code`	`TEXT`	[not null, unique]
`default_currency`	`CHAR(3)`	[not null, default: ‘BDT’]
`timezone`	`TEXT`	[not null, default: ‘Asia/Dhaka’]
`created_at`	`TIMESTAMPTZ`	[not null, default: `now()`]
`updated_at`	`TIMESTAMPTZ`	[not null, default: `now()`]

`department`

Field	PostgreSQL type	Declared attributes
`id`	`BIGSERIAL`	[pk, not null]
`public_id`	`UUID`	[not null, unique, default: `gen_random_uuid()`]
`organization_id`	`BIGINT`	[not null]
`parent_department_id`	`BIGINT`	-
`name`	`TEXT`	[not null]
`code`	`TEXT`	[not null]
`created_at`	`TIMESTAMPTZ`	[not null, default: `now()`]
`updated_at`	`TIMESTAMPTZ`	[not null, default: `now()`]
`deleted_at`	`TIMESTAMPTZ`	-

`location`

Field	PostgreSQL type	Declared attributes
`id`	`BIGSERIAL`	[pk, not null]
`public_id`	`UUID`	[not null, unique, default: `gen_random_uuid()`]
`organization_id`	`BIGINT`	[not null]
`parent_location_id`	`BIGINT`	-
`name`	`TEXT`	[not null]
`code`	`TEXT`	[not null]
`location_type`	`TEXT`	-
`address`	`TEXT`	-
`incharge_user_id`	`BIGINT`	-
`created_at`	`TIMESTAMPTZ`	[not null, default: `now()`]
`updated_at`	`TIMESTAMPTZ`	[not null, default: `now()`]
`deleted_at`	`TIMESTAMPTZ`	-

`app_user`

Field	PostgreSQL type	Declared attributes
`id`	`BIGSERIAL`	[pk, not null]
`public_id`	`UUID`	[not null, unique, default: `gen_random_uuid()`]
`organization_id`	`BIGINT`	[not null]
`department_id`	`BIGINT`	-
`employee_no`	`TEXT`	-
`name`	`TEXT`	[not null]
`email`	`CITEXT`	[not null]
`status`	`user_status`	[not null, default: ‘active’]
`created_at`	`TIMESTAMPTZ`	[not null, default: `now()`]
`updated_at`	`TIMESTAMPTZ`	[not null, default: `now()`]
`deleted_at`	`TIMESTAMPTZ`	-

`team`

Field	PostgreSQL type	Declared attributes
`id`	`BIGSERIAL`	[pk, not null]
`public_id`	`UUID`	[not null, unique, default: `gen_random_uuid()`]
`organization_id`	`BIGINT`	[not null]
`department_id`	`BIGINT`	-
`name`	`TEXT`	[not null]
`code`	`TEXT`	[not null]
`lead_user_id`	`BIGINT`	-
`created_at`	`TIMESTAMPTZ`	[not null, default: `now()`]
`updated_at`	`TIMESTAMPTZ`	[not null, default: `now()`]
`deleted_at`	`TIMESTAMPTZ`	-

`team_member`

Field	PostgreSQL type	Declared attributes
`team_id`	`BIGINT`	[not null]
`user_id`	`BIGINT`	[not null]
`joined_at`	`TIMESTAMPTZ`	[not null, default: `now()`]
`left_at`	`TIMESTAMPTZ`	-

`role`

Field	PostgreSQL type	Declared attributes
`id`	`BIGSERIAL`	[pk, not null]
`public_id`	`UUID`	[not null, unique, default: `gen_random_uuid()`]
`organization_id`	`BIGINT`	[not null]
`name`	`TEXT`	[not null]
`description`	`TEXT`	-
`is_system_role`	`BOOLEAN`	[not null, default: false]
`created_at`	`TIMESTAMPTZ`	[not null, default: `now()`]
`updated_at`	`TIMESTAMPTZ`	[not null, default: `now()`]

`user_role`

Field	PostgreSQL type	Declared attributes
`user_id`	`BIGINT`	[not null]
`role_id`	`BIGINT`	[not null]
`assigned_at`	`TIMESTAMPTZ`	[not null, default: `now()`]
`assigned_by`	`BIGINT`	-

`permission`

Field	PostgreSQL type	Declared attributes
`id`	`BIGSERIAL`	[pk, not null]
`public_id`	`UUID`	[not null, unique, default: `gen_random_uuid()`]
`module`	`TEXT`	[not null]
`action`	`TEXT`	[not null]
`description`	`TEXT`	-
`created_at`	`TIMESTAMPTZ`	[not null, default: `now()`]

`role_permission`

Field	PostgreSQL type	Declared attributes
`role_id`	`BIGINT`	[not null]
`permission_id`	`BIGINT`	[not null]
`assigned_at`	`TIMESTAMPTZ`	[not null, default: `now()`]
`assigned_by`	`BIGINT`	-

`vendor`

Field	PostgreSQL type	Declared attributes
`id`	`BIGSERIAL`	[pk, not null]
`public_id`	`UUID`	[not null, unique, default: `gen_random_uuid()`]
`organization_id`	`BIGINT`	[not null]
`vendor_code`	`TEXT`	[not null]
`name`	`TEXT`	[not null]
`contact_name`	`TEXT`	-
`email`	`CITEXT`	-
`phone`	`TEXT`	-
`active`	`BOOLEAN`	[not null, default: true]
`created_at`	`TIMESTAMPTZ`	[not null, default: `now()`]
`updated_at`	`TIMESTAMPTZ`	[not null, default: `now()`]
`deleted_at`	`TIMESTAMPTZ`	-

`party`

Field	PostgreSQL type	Declared attributes
`id`	`BIGSERIAL`	[pk, not null]
`public_id`	`UUID`	[not null, unique, default: `gen_random_uuid()`]
`organization_id`	`BIGINT`	[not null]
`party_type`	`party_type`	[not null]
`user_id`	`BIGINT`	-
`team_id`	`BIGINT`	-
`department_id`	`BIGINT`	-
`vendor_id`	`BIGINT`	-
`created_at`	`TIMESTAMPTZ`	[not null, default: `now()`]

`asset_category`

Field	PostgreSQL type	Declared attributes
`id`	`BIGSERIAL`	[pk, not null]
`public_id`	`UUID`	[not null, unique, default: `gen_random_uuid()`]
`organization_id`	`BIGINT`	[not null]
`parent_category_id`	`BIGINT`	-
`name`	`TEXT`	[not null]
`code`	`TEXT`	[not null]
`default_useful_life_months`	`INT`	-
`required_fields`	`JSONB`	[not null, default: ’{}‘]
`created_at`	`TIMESTAMPTZ`	[not null, default: `now()`]
`updated_at`	`TIMESTAMPTZ`	[not null, default: `now()`]
`deleted_at`	`TIMESTAMPTZ`	-

`asset_model`

Field	PostgreSQL type	Declared attributes
`id`	`BIGSERIAL`	[pk, not null]
`public_id`	`UUID`	[not null, unique, default: `gen_random_uuid()`]
`organization_id`	`BIGINT`	[not null]
`category_id`	`BIGINT`	[not null]
`manufacturer_vendor_id`	`BIGINT`	-
`model_name`	`TEXT`	[not null]
`model_number`	`TEXT`	-
`default_attributes`	`JSONB`	[not null, default: ’{}‘]
`created_at`	`TIMESTAMPTZ`	[not null, default: `now()`]
`updated_at`	`TIMESTAMPTZ`	[not null, default: `now()`]
`deleted_at`	`TIMESTAMPTZ`	-

`document`

Field	PostgreSQL type	Declared attributes
`id`	`BIGSERIAL`	[pk, not null]
`public_id`	`UUID`	[not null, unique, default: `gen_random_uuid()`]
`organization_id`	`BIGINT`	[not null]
`file_name`	`TEXT`	[not null]
`storage_key`	`TEXT`	[not null, unique]
`mime_type`	`TEXT`	-
`file_size_bytes`	`BIGINT`	-
`checksum_sha256`	`TEXT`	[not null]
`source`	`TEXT`	-
`ocr_status`	`TEXT`	-
`extracted_data`	`JSONB`	-
`uploaded_by`	`BIGINT`	-
`uploaded_at`	`TIMESTAMPTZ`	[not null, default: `now()`]
`retention_until`	`DATE`	-
`legal_hold`	`BOOLEAN`	[not null, default: false]
`metadata`	`JSONB`	[not null, default: ’{}‘]

`goods_receipt`

Field	PostgreSQL type	Declared attributes
`id`	`BIGSERIAL`	[pk, not null]
`public_id`	`UUID`	[not null, unique, default: `gen_random_uuid()`]
`organization_id`	`BIGINT`	[not null]
`receipt_no`	`TEXT`	[not null]
`delivery_note_no`	`TEXT`	-
`purchase_order_no`	`TEXT`	-
`vendor_id`	`BIGINT`	-
`receiving_location_id`	`BIGINT`	[not null]
`finance_owner_id`	`BIGINT`	-
`receiving_officer_id`	`BIGINT`	-
`status`	`receipt_status`	[not null, default: ‘draft’]
`source_method`	`onboarding_source`	[not null, default: ‘manual’]
`expected_at`	`TIMESTAMPTZ`	-
`arrived_at`	`TIMESTAMPTZ`	-
`received_at`	`TIMESTAMPTZ`	-
`approved_at`	`TIMESTAMPTZ`	-
`rejection_reason`	`TEXT`	-
`notes`	`TEXT`	-
`idempotency_key`	`TEXT`	-
`version`	`INT`	[not null, default: 1]
`created_by`	`BIGINT`	[not null]
`updated_by`	`BIGINT`	[not null]
`created_at`	`TIMESTAMPTZ`	[not null, default: `now()`]
`updated_at`	`TIMESTAMPTZ`	[not null, default: `now()`]

`goods_receipt_line`

Field	PostgreSQL type	Declared attributes
`id`	`BIGSERIAL`	[pk, not null]
`public_id`	`UUID`	[not null, unique, default: `gen_random_uuid()`]
`goods_receipt_id`	`BIGINT`	[not null]
`line_no`	`INT`	[not null]
`asset_model_id`	`BIGINT`	-
`item_description`	`TEXT`	[not null]
`vendor_item_reference`	`TEXT`	-
`expected_quantity`	`INT`	[not null]
`delivered_quantity`	`INT`	[not null, default: 0]
`accepted_quantity`	`INT`	[not null, default: 0]
`rejected_quantity`	`INT`	[not null, default: 0]
`status`	`receipt_line_status`	[not null, default: ‘pending’]
`mismatch_reason`	`TEXT`	-
`condition_notes`	`TEXT`	-
`unit_cost`	`NUMERIC(14,2)`	-
`currency`	`CHAR(3)`	-
`created_at`	`TIMESTAMPTZ`	[not null, default: `now()`]
`updated_at`	`TIMESTAMPTZ`	[not null, default: `now()`]

`receiving_inspection`

Field	PostgreSQL type	Declared attributes
`id`	`BIGSERIAL`	[pk, not null]
`public_id`	`UUID`	[not null, unique, default: `gen_random_uuid()`]
`goods_receipt_line_id`	`BIGINT`	[not null]
`inspected_by`	`BIGINT`	[not null]
`inspected_at`	`TIMESTAMPTZ`	[not null, default: `now()`]
`result`	`inspection_result`	[not null, default: ‘pending’]
`inspected_quantity`	`INT`	[not null]
`passed_quantity`	`INT`	[not null, default: 0]
`failed_quantity`	`INT`	[not null, default: 0]
`checklist`	`JSONB`	[not null, default: ’{}‘]
`condition_notes`	`TEXT`	-
`failure_reason`	`TEXT`	-

`replacement_request`

Field	PostgreSQL type	Declared attributes
`id`	`BIGSERIAL`	[pk, not null]
`public_id`	`UUID`	[not null, unique, default: `gen_random_uuid()`]
`organization_id`	`BIGINT`	[not null]
`goods_receipt_line_id`	`BIGINT`	[not null]
`vendor_id`	`BIGINT`	-
`requested_quantity`	`INT`	[not null]
`reason`	`TEXT`	[not null]
`status`	`TEXT`	[not null, default: ‘open’]
`vendor_reference`	`TEXT`	-
`requested_by`	`BIGINT`	[not null]
`requested_at`	`TIMESTAMPTZ`	[not null, default: `now()`]
`resolved_at`	`TIMESTAMPTZ`	-

`goods_receipt_document`

Field	PostgreSQL type	Declared attributes
`goods_receipt_id`	`BIGINT`	[not null]
`document_id`	`BIGINT`	[not null]
`document_type`	`TEXT`	[not null, note: ‘Examples: delivery_note, acceptance_letter, inspection_evidence’]
`created_at`	`TIMESTAMPTZ`	[not null, default: `now()`]

`asset_onboarding_batch`

Field	PostgreSQL type	Declared attributes
`id`	`BIGSERIAL`	[pk, not null]
`public_id`	`UUID`	[not null, unique, default: `gen_random_uuid()`]
`organization_id`	`BIGINT`	[not null]
`batch_no`	`TEXT`	[not null]
`source`	`onboarding_source`	[not null]
`mode`	`creation_mode`	[not null]
`goods_receipt_id`	`BIGINT`	-
`source_document_id`	`BIGINT`	-
`source_system`	`TEXT`	-
`status`	`onboarding_status`	[not null, default: ‘draft’]
`total_rows`	`INT`	[not null, default: 0]
`valid_rows`	`INT`	[not null, default: 0]
`invalid_rows`	`INT`	[not null, default: 0]
`validation_summary`	`JSONB`	[not null, default: ’{}‘]
`idempotency_key`	`TEXT`	-
`confirmed_by`	`BIGINT`	-
`confirmed_at`	`TIMESTAMPTZ`	-
`created_by`	`BIGINT`	[not null]
`created_at`	`TIMESTAMPTZ`	[not null, default: `now()`]
`updated_at`	`TIMESTAMPTZ`	[not null, default: `now()`]

`asset_onboarding_item`

Field	PostgreSQL type	Declared attributes
`id`	`BIGSERIAL`	[pk, not null]
`public_id`	`UUID`	[not null, unique, default: `gen_random_uuid()`]
`onboarding_batch_id`	`BIGINT`	[not null]
`row_no`	`INT`	[not null]
`goods_receipt_line_id`	`BIGINT`	-
`status`	`onboarding_item_status`	[not null, default: ‘pending’]
`raw_data`	`JSONB`	[not null, default: ’{}‘]
`normalized_data`	`JSONB`	[not null, default: ’{}‘]
`validation_errors`	`JSONB`	[not null, default: ’[]‘]
`selected`	`BOOLEAN`	[not null, default: true]
`asset_tag_id`	`BIGINT`	-
`asset_id`	`BIGINT`	-
`confirmed_by`	`BIGINT`	-
`confirmed_at`	`TIMESTAMPTZ`	-
`created_at`	`TIMESTAMPTZ`	[not null, default: `now()`]
`updated_at`	`TIMESTAMPTZ`	[not null, default: `now()`]

`asset`

Field	PostgreSQL type	Declared attributes
`id`	`BIGSERIAL`	[pk, not null]
`public_id`	`UUID`	[not null, unique, default: `gen_random_uuid()`]
`organization_id`	`BIGINT`	[not null]
`asset_code`	`TEXT`	[not null]
`name`	`TEXT`	[not null]
`description`	`TEXT`	-
`asset_model_id`	`BIGINT`	-
`category_id`	`BIGINT`	[not null]
`serial_number`	`TEXT`	-
`manufacturer_serial_normalized`	`TEXT`	-
`vendor_batch_no`	`TEXT`	-
`status`	`asset_status`	[not null, default: ‘pending_registration’]
`ownership_type`	`ownership_type`	[not null]
`department_id`	`BIGINT`	-
`current_location_id`	`BIGINT`	-
`current_custodian_party_id`	`BIGINT`	-
`goods_receipt_line_id`	`BIGINT`	-
`acquisition_date`	`DATE`	-
`commissioned_at`	`TIMESTAMPTZ`	-
`retired_at`	`TIMESTAMPTZ`	-
`disposed_at`	`TIMESTAMPTZ`	-
`condition_code`	`TEXT`	-
`attributes`	`JSONB`	[not null, default: ’{}‘]
`search_text`	`TEXT`	[note: ‘Maintained by trigger or generated column for full-text search’]
`version`	`INT`	[not null, default: 1, note: ‘Optimistic locking token’]
`created_by`	`BIGINT`	[not null]
`updated_by`	`BIGINT`	[not null]
`created_at`	`TIMESTAMPTZ`	[not null, default: `now()`]
`updated_at`	`TIMESTAMPTZ`	[not null, default: `now()`]
`deleted_at`	`TIMESTAMPTZ`	-

`financial_profile`

Field	PostgreSQL type	Declared attributes
`id`	`BIGSERIAL`	[pk, not null]
`public_id`	`UUID`	[not null, unique, default: `gen_random_uuid()`]
`asset_id`	`BIGINT`	[not null, unique]
`acquisition_cost`	`NUMERIC(14,2)`	-
`accumulated_depreciation`	`NUMERIC(14,2)`	[not null, default: 0]
`book_value`	`NUMERIC(14,2)`	-
`depreciation_method`	`TEXT`	-
`useful_life_months`	`INT`	-
`residual_value`	`NUMERIC(14,2)`	-
`capitalization_date`	`DATE`	-
`last_depreciation_date`	`DATE`	-
`currency`	`CHAR(3)`	[not null, default: ‘BDT’]
`updated_by`	`BIGINT`	-
`created_at`	`TIMESTAMPTZ`	[not null, default: `now()`]
`updated_at`	`TIMESTAMPTZ`	[not null, default: `now()`]

`tag_batch`

Field	PostgreSQL type	Declared attributes
`id`	`BIGSERIAL`	[pk, not null]
`public_id`	`UUID`	[not null, unique, default: `gen_random_uuid()`]
`organization_id`	`BIGINT`	[not null]
`batch_no`	`TEXT`	[not null]
`source`	`tag_source`	[not null]
`technology`	`tag_technology`	[not null]
`vendor_id`	`BIGINT`	-
`purchase_order_reference`	`TEXT`	-
`ordered_quantity`	`INT`	-
`received_quantity`	`INT`	-
`ordered_at`	`TIMESTAMPTZ`	-
`received_at`	`TIMESTAMPTZ`	-
`created_by`	`BIGINT`	[not null]
`created_at`	`TIMESTAMPTZ`	[not null, default: `now()`]

`asset_tag`

Field	PostgreSQL type	Declared attributes
`id`	`BIGSERIAL`	[pk, not null]
`public_id`	`UUID`	[not null, unique, default: `gen_random_uuid()`]
`organization_id`	`BIGINT`	[not null]
`asset_id`	`BIGINT`	-
`tag_batch_id`	`BIGINT`	-
`tag_code`	`TEXT`	[not null]
`kind`	`tag_kind`	[not null, default: ‘permanent’]
`technology`	`tag_technology`	[not null]
`source`	`tag_source`	[not null]
`status`	`asset_tag_status`	[not null, default: ‘generated’]
`stored_location_id`	`BIGINT`	-
`binding_proof`	`JSONB`	[not null, default: ’{}‘]
`generated_at`	`TIMESTAMPTZ`	-
`printed_at`	`TIMESTAMPTZ`	-
`attached_at`	`TIMESTAMPTZ`	-
`attached_by`	`BIGINT`	-
`verified_at`	`TIMESTAMPTZ`	-
`verified_by`	`BIGINT`	-
`readability_checked_at`	`TIMESTAMPTZ`	-
`is_readable`	`BOOLEAN`	-
`replaced_tag_id`	`BIGINT`	-
`removal_reason`	`TEXT`	-
`removed_at`	`TIMESTAMPTZ`	-
`created_by`	`BIGINT`	-
`created_at`	`TIMESTAMPTZ`	[not null, default: `now()`]
`updated_at`	`TIMESTAMPTZ`	[not null, default: `now()`]

`tag_scan`

Field	PostgreSQL type	Declared attributes
`id`	`BIGSERIAL`	[pk, not null]
`public_id`	`UUID`	[not null, unique, default: `gen_random_uuid()`]
`organization_id`	`BIGINT`	[not null]
`asset_tag_id`	`BIGINT`	-
`scanned_code`	`TEXT`	[not null]
`scanned_by`	`BIGINT`	-
`scanned_at`	`TIMESTAMPTZ`	[not null, default: `now()`]
`context`	`TEXT`	[not null, note: ‘Examples: onboarding, handoff_release, handoff_acceptance, maintenance’]
`device_id`	`TEXT`	-
`location_id`	`BIGINT`	-
`successful`	`BOOLEAN`	[not null]
`failure_code`	`TEXT`	-
`duplicate_of_scan_id`	`BIGINT`	-
`confirmed_at`	`TIMESTAMPTZ`	-

`asset_assignment`

Field	PostgreSQL type	Declared attributes
`id`	`BIGSERIAL`	[pk, not null]
`public_id`	`UUID`	[not null, unique, default: `gen_random_uuid()`]
`organization_id`	`BIGINT`	[not null]
`asset_id`	`BIGINT`	[not null]
`assignment_type`	`assignment_type`	[not null]
`assignee_party_id`	`BIGINT`	-
`assignee_location_id`	`BIGINT`	-
`responsible_party_id`	`BIGINT`	-
`intermediary_party_id`	`BIGINT`	-
`status`	`assignment_status`	[not null, default: ‘pending_collection’]
`purpose`	`TEXT`	-
`assigned_by`	`BIGINT`	[not null]
`assigned_at`	`TIMESTAMPTZ`	[not null, default: `now()`]
`collection_due_at`	`TIMESTAMPTZ`	-
`collected_at`	`TIMESTAMPTZ`	-
`acceptance_due_at`	`TIMESTAMPTZ`	-
`accepted_by`	`BIGINT`	-
`accepted_at`	`TIMESTAMPTZ`	-
`effective_from`	`TIMESTAMPTZ`	-
`effective_to`	`TIMESTAMPTZ`	-
`ended_reason`	`TEXT`	-
`version`	`INT`	[not null, default: 1]
`created_at`	`TIMESTAMPTZ`	[not null, default: `now()`]
`updated_at`	`TIMESTAMPTZ`	[not null, default: `now()`]

`handoff_transaction`

Field	PostgreSQL type	Declared attributes
`id`	`BIGSERIAL`	[pk, not null]
`public_id`	`UUID`	[not null, unique, default: `gen_random_uuid()`]
`organization_id`	`BIGINT`	[not null]
`reference_no`	`TEXT`	[not null]
`assignment_id`	`BIGINT`	-
`work_order_id`	`BIGINT`	-
`purpose`	`TEXT`	[not null]
`from_party_id`	`BIGINT`	-
`to_party_id`	`BIGINT`	-
`from_location_id`	`BIGINT`	-
`to_location_id`	`BIGINT`	-
`intermediary_party_id`	`BIGINT`	-
`status`	`handoff_status`	[not null, default: ‘draft’]
`initiated_by`	`BIGINT`	[not null]
`initiated_at`	`TIMESTAMPTZ`	[not null, default: `now()`]
`scheduled_at`	`TIMESTAMPTZ`	-
`due_at`	`TIMESTAMPTZ`	-
`closed_at`	`TIMESTAMPTZ`	-
`notes`	`TEXT`	-
`idempotency_key`	`TEXT`	-
`version`	`INT`	[not null, default: 1]
`created_at`	`TIMESTAMPTZ`	[not null, default: `now()`]
`updated_at`	`TIMESTAMPTZ`	[not null, default: `now()`]

`handoff_line`

Field	PostgreSQL type	Declared attributes
`id`	`BIGSERIAL`	[pk, not null]
`handoff_transaction_id`	`BIGINT`	[not null]
`asset_id`	`BIGINT`	[not null]
`condition_at_release`	`TEXT`	-
`released_scan_id`	`BIGINT`	-
`released_by`	`BIGINT`	-
`released_at`	`TIMESTAMPTZ`	-
`condition_at_receipt`	`TEXT`	-
`received_scan_id`	`BIGINT`	-
`received_by`	`BIGINT`	-
`received_at`	`TIMESTAMPTZ`	-
`acknowledgement_notes`	`TEXT`	-

`discrepancy`

Field	PostgreSQL type	Declared attributes
`id`	`BIGSERIAL`	[pk, not null]
`public_id`	`UUID`	[not null, unique, default: `gen_random_uuid()`]
`organization_id`	`BIGINT`	[not null]
`asset_id`	`BIGINT`	-
`goods_receipt_line_id`	`BIGINT`	-
`handoff_transaction_id`	`BIGINT`	-
`reported_by`	`BIGINT`	[not null]
`assigned_to`	`BIGINT`	-
`status`	`discrepancy_status`	[not null, default: ‘open’]
`discrepancy_type`	`TEXT`	[not null]
`description`	`TEXT`	[not null]
`expected_value`	`JSONB`	-
`actual_value`	`JSONB`	-
`resolution`	`TEXT`	-
`resolved_by`	`BIGINT`	-
`resolved_at`	`TIMESTAMPTZ`	-
`created_at`	`TIMESTAMPTZ`	[not null, default: `now()`]
`updated_at`	`TIMESTAMPTZ`	[not null, default: `now()`]

`service_request`

Field	PostgreSQL type	Declared attributes
`id`	`BIGSERIAL`	[pk, not null]
`public_id`	`UUID`	[not null, unique, default: `gen_random_uuid()`]
`organization_id`	`BIGINT`	[not null]
`reference_no`	`TEXT`	[not null]
`asset_id`	`BIGINT`	[not null]
`requested_by`	`BIGINT`	[not null]
`on_behalf_of_party_id`	`BIGINT`	-
`reported_location_id`	`BIGINT`	-
`title`	`TEXT`	[not null]
`description`	`TEXT`	[not null]
`priority`	`TEXT`	[not null, default: ‘normal’]
`is_emergency`	`BOOLEAN`	[not null, default: false]
`status`	`service_request_status`	[not null, default: ‘submitted’]
`submitted_at`	`TIMESTAMPTZ`	[not null, default: `now()`]
`triaged_by`	`BIGINT`	-
`triaged_at`	`TIMESTAMPTZ`	-
`rejection_reason`	`TEXT`	-
`created_at`	`TIMESTAMPTZ`	[not null, default: `now()`]
`updated_at`	`TIMESTAMPTZ`	[not null, default: `now()`]

`maintenance_schedule`

Field	PostgreSQL type	Declared attributes
`id`	`BIGSERIAL`	[pk, not null]
`public_id`	`UUID`	[not null, unique, default: `gen_random_uuid()`]
`organization_id`	`BIGINT`	[not null]
`asset_id`	`BIGINT`	[not null]
`title`	`TEXT`	[not null]
`schedule_rule`	`TEXT`	[not null, note: ‘RFC 5545 recurrence rule or approved scheduler expression’]
`next_due_at`	`TIMESTAMPTZ`	[not null]
`reminder_lead_minutes`	`INT`	[not null, default: 10080]
`owner_user_id`	`BIGINT`	-
`active`	`BOOLEAN`	[not null, default: true]
`last_generated_at`	`TIMESTAMPTZ`	-
`created_by`	`BIGINT`	[not null]
`created_at`	`TIMESTAMPTZ`	[not null, default: `now()`]
`updated_at`	`TIMESTAMPTZ`	[not null, default: `now()`]

`work_order`

Field	PostgreSQL type	Declared attributes
`id`	`BIGSERIAL`	[pk, not null]
`public_id`	`UUID`	[not null, unique, default: `gen_random_uuid()`]
`organization_id`	`BIGINT`	[not null]
`reference_no`	`TEXT`	[not null]
`asset_id`	`BIGINT`	[not null]
`service_request_id`	`BIGINT`	-
`maintenance_schedule_id`	`BIGINT`	-
`title`	`TEXT`	[not null]
`description`	`TEXT`	-
`status`	`work_order_status`	[not null, default: ‘draft’]
`priority`	`TEXT`	[not null, default: ‘normal’]
`is_emergency`	`BOOLEAN`	[not null, default: false]
`assigned_to`	`BIGINT`	-
`requested_by`	`BIGINT`	[not null]
`vendor_id`	`BIGINT`	-
`repairable`	`BOOLEAN`	-
`inhouse_repairable`	`BOOLEAN`	-
`hold_reason`	`TEXT`	-
`scheduled_at`	`TIMESTAMPTZ`	-
`started_at`	`TIMESTAMPTZ`	-
`sent_to_vendor_at`	`TIMESTAMPTZ`	-
`vendor_completed_at`	`TIMESTAMPTZ`	-
`completed_at`	`TIMESTAMPTZ`	-
`closed_at`	`TIMESTAMPTZ`	-
`downtime_started_at`	`TIMESTAMPTZ`	-
`downtime_ended_at`	`TIMESTAMPTZ`	-
`downtime_minutes`	`BIGINT`	-
`estimated_cost`	`NUMERIC(14,2)`	-
`actual_cost`	`NUMERIC(14,2)`	-
`resolution_summary`	`TEXT`	-
`version`	`INT`	[not null, default: 1]
`created_at`	`TIMESTAMPTZ`	[not null, default: `now()`]
`updated_at`	`TIMESTAMPTZ`	[not null, default: `now()`]

`maintenance_inspection`

Field	PostgreSQL type	Declared attributes
`id`	`BIGSERIAL`	[pk, not null]
`public_id`	`UUID`	[not null, unique, default: `gen_random_uuid()`]
`work_order_id`	`BIGINT`	[not null]
`inspected_by`	`BIGINT`	[not null]
`inspected_at`	`TIMESTAMPTZ`	[not null, default: `now()`]
`issue_found`	`BOOLEAN`	[not null]
`result`	`inspection_result`	[not null]
`checklist`	`JSONB`	[not null, default: ’{}‘]
`findings`	`TEXT`	-
`recommendation`	`TEXT`	-

`stock_item`

Field	PostgreSQL type	Declared attributes
`id`	`BIGSERIAL`	[pk, not null]
`public_id`	`UUID`	[not null, unique, default: `gen_random_uuid()`]
`organization_id`	`BIGINT`	[not null]
`sku`	`TEXT`	[not null]
`name`	`TEXT`	[not null]
`item_type`	`stock_item_type`	[not null]
`storage_location_id`	`BIGINT`	-
`unit_of_measure`	`TEXT`	[not null]
`quantity_on_hand`	`NUMERIC(14,3)`	[not null, default: 0]
`reorder_level`	`NUMERIC(14,3)`	-
`unit_cost`	`NUMERIC(14,2)`	-
`active`	`BOOLEAN`	[not null, default: true]
`created_at`	`TIMESTAMPTZ`	[not null, default: `now()`]
`updated_at`	`TIMESTAMPTZ`	[not null, default: `now()`]

`work_order_material`

Field	PostgreSQL type	Declared attributes
`id`	`BIGSERIAL`	[pk, not null]
`work_order_id`	`BIGINT`	[not null]
`stock_item_id`	`BIGINT`	[not null]
`quantity_required`	`NUMERIC(14,3)`	[not null]
`quantity_issued`	`NUMERIC(14,3)`	[not null, default: 0]
`unit_cost`	`NUMERIC(14,2)`	-
`issued_by`	`BIGINT`	-
`issued_at`	`TIMESTAMPTZ`	-
`returned_quantity`	`NUMERIC(14,3)`	[not null, default: 0]

`compliance_item`

Field	PostgreSQL type	Declared attributes
`id`	`BIGSERIAL`	[pk, not null]
`public_id`	`UUID`	[not null, unique, default: `gen_random_uuid()`]
`organization_id`	`BIGINT`	[not null]
`asset_id`	`BIGINT`	[not null]
`item_type`	`compliance_type`	[not null]
`title`	`TEXT`	[not null]
`reference_no`	`TEXT`	-
`owner_user_id`	`BIGINT`	-
`provider_vendor_id`	`BIGINT`	-
`start_date`	`DATE`	-
`due_at`	`TIMESTAMPTZ`	-
`reminder_lead_minutes`	`INT`	[not null, default: 10080]
`recurrence_rule`	`TEXT`	-
`status`	`compliance_status`	[not null, default: ‘upcoming’]
`completed_by`	`BIGINT`	-
`completed_at`	`TIMESTAMPTZ`	-
`completion_notes`	`TEXT`	-
`created_at`	`TIMESTAMPTZ`	[not null, default: `now()`]
`updated_at`	`TIMESTAMPTZ`	[not null, default: `now()`]

`asset_review`

Field	PostgreSQL type	Declared attributes
`id`	`BIGSERIAL`	[pk, not null]
`public_id`	`UUID`	[not null, unique, default: `gen_random_uuid()`]
`organization_id`	`BIGINT`	[not null]
`asset_id`	`BIGINT`	[not null]
`compliance_item_id`	`BIGINT`	-
`reviewed_by`	`BIGINT`	[not null]
`reviewed_at`	`TIMESTAMPTZ`	[not null, default: `now()`]
`decision`	`review_decision`	[not null]
`asset_found`	`BOOLEAN`	-
`condition_code`	`TEXT`	-
`findings`	`TEXT`	-
`action_due_at`	`TIMESTAMPTZ`	-

`disposal_case`

Field	PostgreSQL type	Declared attributes
`id`	`BIGSERIAL`	[pk, not null]
`public_id`	`UUID`	[not null, unique, default: `gen_random_uuid()`]
`organization_id`	`BIGINT`	[not null]
`reference_no`	`TEXT`	[not null]
`asset_id`	`BIGINT`	[not null]
`asset_review_id`	`BIGINT`	-
`status`	`disposal_status`	[not null, default: ‘draft’]
`reason`	`TEXT`	[not null]
`method`	`TEXT`	-
`proposed_by`	`BIGINT`	[not null]
`proposed_at`	`TIMESTAMPTZ`	[not null, default: `now()`]
`financial_reviewer_id`	`BIGINT`	-
`financial_reviewed_at`	`TIMESTAMPTZ`	-
`book_value_at_review`	`NUMERIC(14,2)`	-
`expected_proceeds`	`NUMERIC(14,2)`	-
`actual_proceeds`	`NUMERIC(14,2)`	-
`currency`	`CHAR(3)`	[not null, default: ‘BDT’]
`executed_by`	`BIGINT`	-
`executed_at`	`TIMESTAMPTZ`	-
`disposal_reference`	`TEXT`	-
`created_at`	`TIMESTAMPTZ`	[not null, default: `now()`]
`updated_at`	`TIMESTAMPTZ`	[not null, default: `now()`]

`asset_document`

Field	PostgreSQL type	Declared attributes
`asset_id`	`BIGINT`	[not null]
`document_id`	`BIGINT`	[not null]
`document_type`	`TEXT`	[not null]
`created_at`	`TIMESTAMPTZ`	[not null, default: `now()`]

`work_order_document`

Field	PostgreSQL type	Declared attributes
`work_order_id`	`BIGINT`	[not null]
`document_id`	`BIGINT`	[not null]
`document_type`	`TEXT`	[not null]
`created_at`	`TIMESTAMPTZ`	[not null, default: `now()`]

`disposal_document`

Field	PostgreSQL type	Declared attributes
`disposal_case_id`	`BIGINT`	[not null]
`document_id`	`BIGINT`	[not null]
`document_type`	`TEXT`	[not null]
`created_at`	`TIMESTAMPTZ`	[not null, default: `now()`]

`approval_request`

Field	PostgreSQL type	Declared attributes
`id`	`BIGSERIAL`	[pk, not null]
`public_id`	`UUID`	[not null, unique, default: `gen_random_uuid()`]
`organization_id`	`BIGINT`	[not null]
`workflow_code`	`TEXT`	[not null]
`subject_label`	`TEXT`	[not null, note: ‘Human-readable snapshot shown in approval queues’]
`goods_receipt_id`	`BIGINT`	-
`onboarding_batch_id`	`BIGINT`	-
`work_order_id`	`BIGINT`	-
`disposal_case_id`	`BIGINT`	-
`status`	`approval_status`	[not null, default: ‘pending’]
`requested_by`	`BIGINT`	[not null]
`requested_at`	`TIMESTAMPTZ`	[not null, default: `now()`]
`decided_at`	`TIMESTAMPTZ`	-
`cancelled_at`	`TIMESTAMPTZ`	-
`version`	`INT`	[not null, default: 1]

`approval_step`

Field	PostgreSQL type	Declared attributes
`id`	`BIGSERIAL`	[pk, not null]
`approval_request_id`	`BIGINT`	[not null]
`step_no`	`INT`	[not null]
`approver_user_id`	`BIGINT`	-
`approver_role_id`	`BIGINT`	-
`status`	`approval_step_status`	[not null, default: ‘pending’]
`due_at`	`TIMESTAMPTZ`	-
`decided_by`	`BIGINT`	-
`decided_at`	`TIMESTAMPTZ`	-
`comments`	`TEXT`	-

`notification`

Field	PostgreSQL type	Declared attributes
`id`	`BIGSERIAL`	[pk, not null]
`public_id`	`UUID`	[not null, unique, default: `gen_random_uuid()`]
`organization_id`	`BIGINT`	[not null]
`recipient_user_id`	`BIGINT`	[not null]
`channel`	`TEXT`	[not null]
`template_code`	`TEXT`	[not null]
`subject`	`TEXT`	-
`payload`	`JSONB`	[not null, default: ’{}‘]
`status`	`notification_status`	[not null, default: ‘queued’]
`goods_receipt_id`	`BIGINT`	-
`assignment_id`	`BIGINT`	-
`handoff_transaction_id`	`BIGINT`	-
`work_order_id`	`BIGINT`	-
`compliance_item_id`	`BIGINT`	-
`approval_request_id`	`BIGINT`	-
`escalation_level`	`INT`	[not null, default: 0]
`scheduled_at`	`TIMESTAMPTZ`	-
`sent_at`	`TIMESTAMPTZ`	-
`delivered_at`	`TIMESTAMPTZ`	-
`read_at`	`TIMESTAMPTZ`	-
`failure_reason`	`TEXT`	-
`created_at`	`TIMESTAMPTZ`	[not null, default: `now()`]

`management_decision`

Field	PostgreSQL type	Declared attributes
`id`	`BIGSERIAL`	[pk, not null]
`public_id`	`UUID`	[not null, unique, default: `gen_random_uuid()`]
`organization_id`	`BIGINT`	[not null]
`title`	`TEXT`	[not null]
`decision_type`	`TEXT`	[not null, note: ‘Examples: faulty_batch, bottleneck, at_risk_asset, portfolio’]
`decision_text`	`TEXT`	[not null]
`rationale`	`TEXT`	-
`source_report_type`	`TEXT`	-
`source_filters`	`JSONB`	[not null, default: ’{}‘]
`asset_id`	`BIGINT`	-
`asset_category_id`	`BIGINT`	-
`vendor_id`	`BIGINT`	-
`tag_batch_id`	`BIGINT`	-
`decided_by`	`BIGINT`	[not null]
`decided_at`	`TIMESTAMPTZ`	[not null, default: `now()`]
`follow_up_owner_id`	`BIGINT`	-
`follow_up_due_at`	`TIMESTAMPTZ`	-
`follow_up_status`	`TEXT`	-
`created_at`	`TIMESTAMPTZ`	[not null, default: `now()`]
`updated_at`	`TIMESTAMPTZ`	[not null, default: `now()`]

`asset_event`

Field	PostgreSQL type	Declared attributes
`id`	`BIGSERIAL`	[pk, not null]
`public_id`	`UUID`	[not null, unique, default: `gen_random_uuid()`]
`organization_id`	`BIGINT`	[not null]
`asset_id`	`BIGINT`	[not null]
`event_type`	`asset_event_type`	[not null]
`actor_user_id`	`BIGINT`	-
`occurred_at`	`TIMESTAMPTZ`	[not null, default: `now()`]
`reason`	`TEXT`	-
`source_entity_type`	`TEXT`	-
`source_entity_public_id`	`UUID`	-
`idempotency_key`	`TEXT`	-
`payload`	`JSONB`	[not null, default: ’{}‘]
`audit_event_id`	`BIGINT`	-

`audit_event`

Field	PostgreSQL type	Declared attributes
`id`	`BIGSERIAL`	[pk, not null]
`public_id`	`UUID`	[not null, unique, default: `gen_random_uuid()`]
`organization_id`	`BIGINT`	[not null]
`occurred_at`	`TIMESTAMPTZ`	[not null, default: `clock_timestamp()`]
`recorded_at`	`TIMESTAMPTZ`	[not null, default: `clock_timestamp()`]
`actor_type`	`audit_actor_type`	[not null]
`actor_user_id`	`BIGINT`	-
`actor_identifier`	`TEXT`	[note: ‘Immutable snapshot: username, service account, job, or integration identifier’]
`actor_display_name`	`TEXT`	-
`impersonator_user_id`	`BIGINT`	-
`action`	`audit_action_type`	[not null]
`outcome`	`audit_outcome`	[not null]
`entity_type`	`TEXT`	[not null]
`entity_id`	`BIGINT`	-
`entity_public_id`	`UUID`	-
`entity_label`	`TEXT`	-
`reason_code`	`TEXT`	-
`reason_text`	`TEXT`	-
`trigger_type`	`audit_trigger_type`	[not null]
`source_channel`	`TEXT`	[not null, note: ‘Examples: web, mobile, scanner, API, worker, import’]
`client_ip`	`INET`	-
`forwarded_for`	`TEXT`	-
`user_agent`	`TEXT`	-
`device_id`	`TEXT`	-
`session_id`	`TEXT`	-
`request_id`	`TEXT`	-
`correlation_id`	`UUID`	-
`trace_id`	`TEXT`	-
`transaction_id`	`UUID`	-
`service_name`	`TEXT`	-
`service_version`	`TEXT`	-
`http_method`	`TEXT`	-
`request_path`	`TEXT`	-
`error_code`	`TEXT`	-
`error_message`	`TEXT`	-
`metadata`	`JSONB`	[not null, default: ’{}‘]
`previous_event_hash`	`TEXT`	-
`event_hash`	`TEXT`	[not null]
`retention_until`	`DATE`	-

`audit_change`

Field	PostgreSQL type	Declared attributes
`id`	`BIGSERIAL`	[pk, not null]
`audit_event_id`	`BIGINT`	[not null]
`sequence_no`	`INT`	[not null]
`change_type`	`audit_change_type`	[not null]
`field_path`	`TEXT`	[not null, note: ‘JSON Pointer or stable dotted path’]
`before_value`	`JSONB`	-
`after_value`	`JSONB`	-
`before_value_hash`	`TEXT`	-
`after_value_hash`	`TEXT`	-
`is_sensitive`	`BOOLEAN`	[not null, default: false]
`is_redacted`	`BOOLEAN`	[not null, default: false]

`audit_event_asset`

Field	PostgreSQL type	Declared attributes
`audit_event_id`	`BIGINT`	[not null]
`asset_id`	`BIGINT`	[not null]
`relationship`	`TEXT`	[not null, default: ‘subject’]