# Asset Management — V1 Database Schema (Locked DDL)

**DDL lock task:** `AST-SCHEMA-1`  
**Migration task:** `AST-SCHEMA-MIG` (single owner of all V1 `CREATE TABLE` migrations)  
**Source:** [ASSET_MANAGEMENT_IMPLEMENTATION_PLAN.md](./ASSET_MANAGEMENT_IMPLEMENTATION_PLAN.md) §H  
**WBS:** [ASSET_MANAGEMENT_WBS.md](./ASSET_MANAGEMENT_WBS.md)  
**Status:** **LOCKED & ACCEPTED** (Signed via `AST-SCHEMA-1`). All V1 table definitions are frozen. **`AST-SCHEMA-MIG` creates every V1 table**. Feature BE cards must not add domain table migrations (except measured indexes / Employee bridge ALTER).

**STOP Gate Status:** `SIGNED / ACCEPTED` (Schema DDL locked on 2026-09-30)

Conventions (mandatory):

- Primary key: `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY
- Tenant: `company_id` + `BelongsToCompany`
- Soft delete: **not** on ledger, assignment, or verification lines
- Do **not** create a second table named `assets` (Employee owns that name)
- Do **not** modify `assets` / `employee_assets` until Phase 4 bridge card

```sql
-- ============================================================
-- Asset Module V1 — Full DDL (MySQL / MariaDB draft)
-- ============================================================

-- ------------------------------------------------------------
-- REUSE ONLY (do not recreate)
-- ------------------------------------------------------------
-- companies, users, employee_personal_infos, employee_employments,
-- employee_organization_assignments, employee_timeline_events,
-- work_locations, branches, departments, cost_centers,
-- document_types, activity_logs, approval_* ,
-- Project contracts (customer contracts — NOT AMC)

-- ------------------------------------------------------------
-- DO NOT TOUCH until Phase 4 bridge
-- ------------------------------------------------------------
-- assets, employee_assets

-- ------------------------------------------------------------
-- Phase 1 — Domain foundation
-- ------------------------------------------------------------

CREATE TABLE asset_categories (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  company_id BIGINT UNSIGNED NOT NULL,
  name VARCHAR(150) NOT NULL,
  code VARCHAR(50) NOT NULL,
  parent_id BIGINT UNSIGNED NULL,
  status ENUM('active','inactive') NOT NULL DEFAULT 'active',
  created_by BIGINT UNSIGNED NULL,
  updated_by BIGINT UNSIGNED NULL,
  created_at TIMESTAMP NULL,
  updated_at TIMESTAMP NULL,
  UNIQUE KEY asset_categories_company_code_unique (company_id, code),
  KEY asset_categories_company_parent_index (company_id, parent_id),
  KEY asset_categories_company_status_index (company_id, status),
  CONSTRAINT asset_categories_company_id_foreign FOREIGN KEY (company_id) REFERENCES companies (id) ON DELETE CASCADE,
  CONSTRAINT asset_categories_parent_id_foreign FOREIGN KEY (parent_id) REFERENCES asset_categories (id) ON DELETE RESTRICT,
  CONSTRAINT asset_categories_created_by_foreign FOREIGN KEY (created_by) REFERENCES users (id) ON DELETE SET NULL,
  CONSTRAINT asset_categories_updated_by_foreign FOREIGN KEY (updated_by) REFERENCES users (id) ON DELETE SET NULL
);

CREATE TABLE asset_units (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  company_id BIGINT UNSIGNED NOT NULL,
  name VARCHAR(100) NOT NULL,
  code VARCHAR(50) NOT NULL,
  decimal_places TINYINT UNSIGNED NOT NULL DEFAULT 0,
  status ENUM('active','inactive') NOT NULL DEFAULT 'active',
  created_by BIGINT UNSIGNED NULL,
  updated_by BIGINT UNSIGNED NULL,
  created_at TIMESTAMP NULL,
  updated_at TIMESTAMP NULL,
  UNIQUE KEY asset_units_company_code_unique (company_id, code),
  CONSTRAINT asset_units_company_id_foreign FOREIGN KEY (company_id) REFERENCES companies (id) ON DELETE CASCADE,
  CONSTRAINT asset_units_created_by_foreign FOREIGN KEY (created_by) REFERENCES users (id) ON DELETE SET NULL,
  CONSTRAINT asset_units_updated_by_foreign FOREIGN KEY (updated_by) REFERENCES users (id) ON DELETE SET NULL
);

CREATE TABLE asset_items (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  company_id BIGINT UNSIGNED NOT NULL,
  category_id BIGINT UNSIGNED NOT NULL,
  unit_id BIGINT UNSIGNED NOT NULL,
  name VARCHAR(255) NOT NULL,
  code VARCHAR(50) NOT NULL,
  tracking_mode ENUM('SERIALIZED','QUANTITY') NOT NULL,
  status ENUM('active','inactive') NOT NULL DEFAULT 'active',
  description TEXT NULL,
  created_by BIGINT UNSIGNED NULL,
  updated_by BIGINT UNSIGNED NULL,
  created_at TIMESTAMP NULL,
  updated_at TIMESTAMP NULL,
  UNIQUE KEY asset_items_company_code_unique (company_id, code),
  KEY asset_items_company_tracking_category_index (company_id, tracking_mode, category_id),
  CONSTRAINT asset_items_company_id_foreign FOREIGN KEY (company_id) REFERENCES companies (id) ON DELETE CASCADE,
  CONSTRAINT asset_items_category_id_foreign FOREIGN KEY (category_id) REFERENCES asset_categories (id) ON DELETE RESTRICT,
  CONSTRAINT asset_items_unit_id_foreign FOREIGN KEY (unit_id) REFERENCES asset_units (id) ON DELETE RESTRICT,
  CONSTRAINT asset_items_created_by_foreign FOREIGN KEY (created_by) REFERENCES users (id) ON DELETE SET NULL,
  CONSTRAINT asset_items_updated_by_foreign FOREIGN KEY (updated_by) REFERENCES users (id) ON DELETE SET NULL
);

CREATE TABLE asset_attribute_definitions (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  company_id BIGINT UNSIGNED NOT NULL,
  category_id BIGINT UNSIGNED NOT NULL,
  code VARCHAR(50) NOT NULL,
  label VARCHAR(150) NOT NULL,
  data_type ENUM('TEXT','NUMBER','BOOLEAN','DATE','SELECT','MULTI_SELECT') NOT NULL,
  is_required TINYINT(1) NOT NULL DEFAULT 0,
  sort_order INT UNSIGNED NOT NULL DEFAULT 0,
  status ENUM('active','inactive') NOT NULL DEFAULT 'active',
  created_by BIGINT UNSIGNED NULL,
  updated_by BIGINT UNSIGNED NULL,
  created_at TIMESTAMP NULL,
  updated_at TIMESTAMP NULL,
  UNIQUE KEY asset_attr_defs_category_code_unique (category_id, code),
  KEY asset_attr_defs_company_category_index (company_id, category_id),
  CONSTRAINT asset_attr_defs_company_id_foreign FOREIGN KEY (company_id) REFERENCES companies (id) ON DELETE CASCADE,
  CONSTRAINT asset_attr_defs_category_id_foreign FOREIGN KEY (category_id) REFERENCES asset_categories (id) ON DELETE CASCADE,
  CONSTRAINT asset_attr_defs_created_by_foreign FOREIGN KEY (created_by) REFERENCES users (id) ON DELETE SET NULL,
  CONSTRAINT asset_attr_defs_updated_by_foreign FOREIGN KEY (updated_by) REFERENCES users (id) ON DELETE SET NULL
);

CREATE TABLE asset_attribute_options (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  company_id BIGINT UNSIGNED NOT NULL,
  definition_id BIGINT UNSIGNED NOT NULL,
  value VARCHAR(150) NOT NULL,
  label VARCHAR(150) NOT NULL,
  sort_order INT UNSIGNED NOT NULL DEFAULT 0,
  created_at TIMESTAMP NULL,
  updated_at TIMESTAMP NULL,
  UNIQUE KEY asset_attr_options_def_value_unique (definition_id, value),
  CONSTRAINT asset_attr_options_company_id_foreign FOREIGN KEY (company_id) REFERENCES companies (id) ON DELETE CASCADE,
  CONSTRAINT asset_attr_options_definition_id_foreign FOREIGN KEY (definition_id) REFERENCES asset_attribute_definitions (id) ON DELETE CASCADE
);

CREATE TABLE asset_item_attribute_values (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  company_id BIGINT UNSIGNED NOT NULL,
  item_id BIGINT UNSIGNED NOT NULL,
  definition_id BIGINT UNSIGNED NOT NULL,
  value_text TEXT NULL,
  created_at TIMESTAMP NULL,
  updated_at TIMESTAMP NULL,
  UNIQUE KEY asset_item_attr_values_item_def_unique (item_id, definition_id),
  CONSTRAINT asset_item_attr_values_company_id_foreign FOREIGN KEY (company_id) REFERENCES companies (id) ON DELETE CASCADE,
  CONSTRAINT asset_item_attr_values_item_id_foreign FOREIGN KEY (item_id) REFERENCES asset_items (id) ON DELETE CASCADE,
  CONSTRAINT asset_item_attr_values_definition_id_foreign FOREIGN KEY (definition_id) REFERENCES asset_attribute_definitions (id) ON DELETE RESTRICT
);

CREATE TABLE asset_locations (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  company_id BIGINT UNSIGNED NOT NULL,
  work_location_id BIGINT UNSIGNED NOT NULL,
  branch_id BIGINT UNSIGNED NULL,
  parent_id BIGINT UNSIGNED NULL,
  name VARCHAR(150) NOT NULL,
  code VARCHAR(50) NOT NULL,
  kind ENUM('site','floor','room','store') NOT NULL DEFAULT 'room',
  status ENUM('active','inactive') NOT NULL DEFAULT 'active',
  created_by BIGINT UNSIGNED NULL,
  updated_by BIGINT UNSIGNED NULL,
  created_at TIMESTAMP NULL,
  updated_at TIMESTAMP NULL,
  UNIQUE KEY asset_locations_company_code_unique (company_id, code),
  KEY asset_locations_work_parent_index (work_location_id, parent_id),
  KEY asset_locations_company_status_index (company_id, status),
  CONSTRAINT asset_locations_company_id_foreign FOREIGN KEY (company_id) REFERENCES companies (id) ON DELETE CASCADE,
  CONSTRAINT asset_locations_work_location_id_foreign FOREIGN KEY (work_location_id) REFERENCES work_locations (id) ON DELETE RESTRICT,
  CONSTRAINT asset_locations_branch_id_foreign FOREIGN KEY (branch_id) REFERENCES branches (id) ON DELETE SET NULL,
  CONSTRAINT asset_locations_parent_id_foreign FOREIGN KEY (parent_id) REFERENCES asset_locations (id) ON DELETE RESTRICT,
  CONSTRAINT asset_locations_created_by_foreign FOREIGN KEY (created_by) REFERENCES users (id) ON DELETE SET NULL,
  CONSTRAINT asset_locations_updated_by_foreign FOREIGN KEY (updated_by) REFERENCES users (id) ON DELETE SET NULL
);

-- ------------------------------------------------------------
-- Phase 2 — Serialized registry & tags
-- ------------------------------------------------------------

CREATE TABLE serialized_assets (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  company_id BIGINT UNSIGNED NOT NULL,
  item_id BIGINT UNSIGNED NOT NULL,
  asset_code VARCHAR(50) NOT NULL,
  serial_number VARCHAR(100) NULL,
  status VARCHAR(40) NOT NULL DEFAULT 'RECEIVED',
  -- RECEIVED | AVAILABLE | ASSIGNED | UNDER_MAINTENANCE | LOST | RETIRED | DISPOSED | WRITTEN_OFF
  `condition` VARCHAR(40) NOT NULL DEFAULT 'NEW',
  -- NEW | GOOD | FAIR | POOR | DAMAGED | UNUSABLE
  current_location_id BIGINT UNSIGNED NULL,
  acquired_on DATE NULL,
  acquisition_cost DECIMAL(15,2) NULL,
  currency VARCHAR(10) NULL,
  cost_center_id BIGINT UNSIGNED NULL,
  legacy_asset_id BIGINT UNSIGNED NULL,
  created_by BIGINT UNSIGNED NULL,
  updated_by BIGINT UNSIGNED NULL,
  created_at TIMESTAMP NULL,
  updated_at TIMESTAMP NULL,
  UNIQUE KEY serialized_assets_company_code_unique (company_id, asset_code),
  UNIQUE KEY serialized_assets_company_item_serial_unique (company_id, item_id, serial_number),
  KEY serialized_assets_company_status_location_index (company_id, status, current_location_id),
  KEY serialized_assets_legacy_asset_id_index (legacy_asset_id),
  CONSTRAINT serialized_assets_company_id_foreign FOREIGN KEY (company_id) REFERENCES companies (id) ON DELETE CASCADE,
  CONSTRAINT serialized_assets_item_id_foreign FOREIGN KEY (item_id) REFERENCES asset_items (id) ON DELETE RESTRICT,
  CONSTRAINT serialized_assets_location_id_foreign FOREIGN KEY (current_location_id) REFERENCES asset_locations (id) ON DELETE RESTRICT,
  CONSTRAINT serialized_assets_cost_center_id_foreign FOREIGN KEY (cost_center_id) REFERENCES cost_centers (id) ON DELETE SET NULL,
  CONSTRAINT serialized_assets_created_by_foreign FOREIGN KEY (created_by) REFERENCES users (id) ON DELETE SET NULL,
  CONSTRAINT serialized_assets_updated_by_foreign FOREIGN KEY (updated_by) REFERENCES users (id) ON DELETE SET NULL
);
-- NOTE: MySQL unique on (company_id, item_id, serial_number) allows multiple NULLs in some modes;
-- application must enforce uniqueness when serial_number is present. Prefer generated unique
-- when serial is required by policy (Section N).

CREATE TABLE serialized_asset_attribute_values (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  company_id BIGINT UNSIGNED NOT NULL,
  serialized_asset_id BIGINT UNSIGNED NOT NULL,
  definition_id BIGINT UNSIGNED NOT NULL,
  value_text TEXT NULL,
  created_at TIMESTAMP NULL,
  updated_at TIMESTAMP NULL,
  UNIQUE KEY serialized_asset_attr_values_asset_def_unique (serialized_asset_id, definition_id),
  CONSTRAINT serialized_asset_attr_values_company_id_foreign FOREIGN KEY (company_id) REFERENCES companies (id) ON DELETE CASCADE,
  CONSTRAINT serialized_asset_attr_values_asset_id_foreign FOREIGN KEY (serialized_asset_id) REFERENCES serialized_assets (id) ON DELETE CASCADE,
  CONSTRAINT serialized_asset_attr_values_definition_id_foreign FOREIGN KEY (definition_id) REFERENCES asset_attribute_definitions (id) ON DELETE RESTRICT
);

CREATE TABLE asset_tags (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  company_id BIGINT UNSIGNED NOT NULL,
  serialized_asset_id BIGINT UNSIGNED NOT NULL,
  token VARCHAR(64) NOT NULL,
  symbology ENUM('QR','BARCODE') NOT NULL DEFAULT 'QR',
  status ENUM('active','replaced','void') NOT NULL DEFAULT 'active',
  created_by BIGINT UNSIGNED NULL,
  created_at TIMESTAMP NULL,
  updated_at TIMESTAMP NULL,
  UNIQUE KEY asset_tags_token_unique (token),
  KEY asset_tags_asset_status_index (serialized_asset_id, status),
  CONSTRAINT asset_tags_company_id_foreign FOREIGN KEY (company_id) REFERENCES companies (id) ON DELETE CASCADE,
  CONSTRAINT asset_tags_asset_id_foreign FOREIGN KEY (serialized_asset_id) REFERENCES serialized_assets (id) ON DELETE CASCADE,
  CONSTRAINT asset_tags_created_by_foreign FOREIGN KEY (created_by) REFERENCES users (id) ON DELETE SET NULL
);

CREATE TABLE asset_tag_events (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  company_id BIGINT UNSIGNED NOT NULL,
  tag_id BIGINT UNSIGNED NOT NULL,
  event ENUM('issued','replaced','voided') NOT NULL,
  reason VARCHAR(255) NULL,
  actor_id BIGINT UNSIGNED NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY asset_tag_events_tag_id_index (tag_id),
  CONSTRAINT asset_tag_events_company_id_foreign FOREIGN KEY (company_id) REFERENCES companies (id) ON DELETE CASCADE,
  CONSTRAINT asset_tag_events_tag_id_foreign FOREIGN KEY (tag_id) REFERENCES asset_tags (id) ON DELETE CASCADE,
  CONSTRAINT asset_tag_events_actor_id_foreign FOREIGN KEY (actor_id) REFERENCES users (id) ON DELETE SET NULL
);

-- ------------------------------------------------------------
-- Phase 3 — Quantity ledger
-- ------------------------------------------------------------

CREATE TABLE inventory_transactions (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  company_id BIGINT UNSIGNED NOT NULL,
  item_id BIGINT UNSIGNED NOT NULL,
  location_id BIGINT UNSIGNED NOT NULL,
  type ENUM(
    'RECEIPT','ISSUE','TRANSFER_OUT','TRANSFER_IN','RETURN',
    'ADJUSTMENT','DAMAGE','DISPOSAL','OPENING_BALANCE'
  ) NOT NULL,
  quantity DECIMAL(18,4) NOT NULL,
  balance_after DECIMAL(18,4) NOT NULL,
  reference_type VARCHAR(100) NULL,
  reference_id BIGINT UNSIGNED NULL,
  idempotency_key VARCHAR(100) NOT NULL,
  occurred_on DATE NOT NULL,
  unit_cost DECIMAL(15,2) NULL,
  created_by BIGINT UNSIGNED NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY inventory_transactions_company_idempotency_unique (company_id, idempotency_key),
  KEY inventory_transactions_item_location_id_index (item_id, location_id, id),
  KEY inventory_transactions_reference_index (reference_type, reference_id),
  CONSTRAINT inventory_transactions_company_id_foreign FOREIGN KEY (company_id) REFERENCES companies (id) ON DELETE CASCADE,
  CONSTRAINT inventory_transactions_item_id_foreign FOREIGN KEY (item_id) REFERENCES asset_items (id) ON DELETE RESTRICT,
  CONSTRAINT inventory_transactions_location_id_foreign FOREIGN KEY (location_id) REFERENCES asset_locations (id) ON DELETE RESTRICT,
  CONSTRAINT inventory_transactions_created_by_foreign FOREIGN KEY (created_by) REFERENCES users (id) ON DELETE SET NULL
);

CREATE TABLE inventory_balances (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  company_id BIGINT UNSIGNED NOT NULL,
  item_id BIGINT UNSIGNED NOT NULL,
  location_id BIGINT UNSIGNED NOT NULL,
  quantity DECIMAL(18,4) NOT NULL DEFAULT 0,
  reserved_quantity DECIMAL(18,4) NOT NULL DEFAULT 0,
  updated_at TIMESTAMP NULL,
  created_at TIMESTAMP NULL,
  UNIQUE KEY inventory_balances_company_item_location_unique (company_id, item_id, location_id),
  CONSTRAINT inventory_balances_company_id_foreign FOREIGN KEY (company_id) REFERENCES companies (id) ON DELETE CASCADE,
  CONSTRAINT inventory_balances_item_id_foreign FOREIGN KEY (item_id) REFERENCES asset_items (id) ON DELETE RESTRICT,
  CONSTRAINT inventory_balances_location_id_foreign FOREIGN KEY (location_id) REFERENCES asset_locations (id) ON DELETE RESTRICT
);

CREATE TABLE asset_receipt_inbox (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  company_id BIGINT UNSIGNED NOT NULL,
  source_type VARCHAR(50) NOT NULL,
  source_id VARCHAR(100) NOT NULL,
  source_line_id VARCHAR(100) NOT NULL,
  item_id BIGINT UNSIGNED NOT NULL,
  location_id BIGINT UNSIGNED NOT NULL,
  quantity DECIMAL(18,4) NOT NULL,
  expected_qty DECIMAL(18,4) NULL,
  created_qty DECIMAL(18,4) NULL,
  unit_cost DECIMAL(15,2) NULL,
  currency VARCHAR(10) NULL,
  status ENUM('pending','applied','failed') NOT NULL DEFAULT 'pending',
  error TEXT NULL,
  created_at TIMESTAMP NULL,
  updated_at TIMESTAMP NULL,
  UNIQUE KEY asset_receipt_inbox_source_unique (company_id, source_type, source_id, source_line_id),
  CONSTRAINT asset_receipt_inbox_company_id_foreign FOREIGN KEY (company_id) REFERENCES companies (id) ON DELETE CASCADE,
  CONSTRAINT asset_receipt_inbox_item_id_foreign FOREIGN KEY (item_id) REFERENCES asset_items (id) ON DELETE RESTRICT,
  CONSTRAINT asset_receipt_inbox_location_id_foreign FOREIGN KEY (location_id) REFERENCES asset_locations (id) ON DELETE RESTRICT
);

-- ------------------------------------------------------------
-- Phase 4 — Assignment & handover
-- ------------------------------------------------------------

CREATE TABLE serialized_asset_assignments (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  company_id BIGINT UNSIGNED NOT NULL,
  serialized_asset_id BIGINT UNSIGNED NOT NULL,
  holder_type ENUM('employee','department') NOT NULL,
  employee_id BIGINT UNSIGNED NULL,
  department_id BIGINT UNSIGNED NULL,
  location_id BIGINT UNSIGNED NOT NULL,
  assigned_at DATETIME NOT NULL,
  returned_at DATETIME NULL,
  assigned_by BIGINT UNSIGNED NULL,
  returned_by BIGINT UNSIGNED NULL,
  condition_at_issue VARCHAR(40) NULL,
  condition_at_return VARCHAR(40) NULL,
  handover_id BIGINT UNSIGNED NULL,
  created_at TIMESTAMP NULL,
  updated_at TIMESTAMP NULL,
  KEY serialized_asset_assignments_asset_returned_index (serialized_asset_id, returned_at),
  KEY serialized_asset_assignments_employee_returned_index (company_id, employee_id, returned_at),
  CONSTRAINT serialized_asset_assignments_company_id_foreign FOREIGN KEY (company_id) REFERENCES companies (id) ON DELETE CASCADE,
  CONSTRAINT serialized_asset_assignments_asset_id_foreign FOREIGN KEY (serialized_asset_id) REFERENCES serialized_assets (id) ON DELETE RESTRICT,
  CONSTRAINT serialized_asset_assignments_employee_id_foreign FOREIGN KEY (employee_id) REFERENCES employee_personal_infos (id) ON DELETE RESTRICT,
  CONSTRAINT serialized_asset_assignments_department_id_foreign FOREIGN KEY (department_id) REFERENCES departments (id) ON DELETE RESTRICT,
  CONSTRAINT serialized_asset_assignments_location_id_foreign FOREIGN KEY (location_id) REFERENCES asset_locations (id) ON DELETE RESTRICT,
  CONSTRAINT serialized_asset_assignments_assigned_by_foreign FOREIGN KEY (assigned_by) REFERENCES users (id) ON DELETE SET NULL,
  CONSTRAINT serialized_asset_assignments_returned_by_foreign FOREIGN KEY (returned_by) REFERENCES users (id) ON DELETE SET NULL
);

CREATE TABLE asset_handovers (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  company_id BIGINT UNSIGNED NOT NULL,
  assignment_id BIGINT UNSIGNED NOT NULL,
  direction ENUM('issue','return') NOT NULL,
  status ENUM('pending','acknowledged','rejected') NOT NULL DEFAULT 'pending',
  `condition` VARCHAR(40) NOT NULL,
  issued_by BIGINT UNSIGNED NOT NULL,
  received_by BIGINT UNSIGNED NULL,
  acknowledged_at DATETIME NULL,
  notes TEXT NULL,
  file_path VARCHAR(255) NULL,
  original_name VARCHAR(255) NULL,
  created_at TIMESTAMP NULL,
  updated_at TIMESTAMP NULL,
  KEY asset_handovers_assignment_id_index (assignment_id),
  CONSTRAINT asset_handovers_company_id_foreign FOREIGN KEY (company_id) REFERENCES companies (id) ON DELETE CASCADE,
  CONSTRAINT asset_handovers_assignment_id_foreign FOREIGN KEY (assignment_id) REFERENCES serialized_asset_assignments (id) ON DELETE RESTRICT,
  CONSTRAINT asset_handovers_issued_by_foreign FOREIGN KEY (issued_by) REFERENCES users (id) ON DELETE RESTRICT,
  CONSTRAINT asset_handovers_received_by_foreign FOREIGN KEY (received_by) REFERENCES users (id) ON DELETE SET NULL
);

-- Optional bridge column on existing table (Phase 4 only, after Section N):
-- ALTER TABLE employee_assets ADD COLUMN serialized_asset_id BIGINT UNSIGNED NULL;
-- ADD INDEX / FK to serialized_assets ON DELETE SET NULL;

-- ------------------------------------------------------------
-- Phase 5 — Verification
-- ------------------------------------------------------------

CREATE TABLE verification_sessions (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  company_id BIGINT UNSIGNED NOT NULL,
  location_id BIGINT UNSIGNED NULL,
  category_id BIGINT UNSIGNED NULL,
  mode ENUM('serialized','quantity','mixed') NOT NULL,
  status ENUM('draft','in_progress','completed','reviewed','cancelled') NOT NULL DEFAULT 'draft',
  started_by BIGINT UNSIGNED NOT NULL,
  reviewed_by BIGINT UNSIGNED NULL,
  started_at DATETIME NULL,
  completed_at DATETIME NULL,
  created_at TIMESTAMP NULL,
  updated_at TIMESTAMP NULL,
  KEY verification_sessions_company_status_index (company_id, status),
  CONSTRAINT verification_sessions_company_id_foreign FOREIGN KEY (company_id) REFERENCES companies (id) ON DELETE CASCADE,
  CONSTRAINT verification_sessions_location_id_foreign FOREIGN KEY (location_id) REFERENCES asset_locations (id) ON DELETE RESTRICT,
  CONSTRAINT verification_sessions_category_id_foreign FOREIGN KEY (category_id) REFERENCES asset_categories (id) ON DELETE RESTRICT,
  CONSTRAINT verification_sessions_started_by_foreign FOREIGN KEY (started_by) REFERENCES users (id) ON DELETE RESTRICT,
  CONSTRAINT verification_sessions_reviewed_by_foreign FOREIGN KEY (reviewed_by) REFERENCES users (id) ON DELETE SET NULL
);

CREATE TABLE verification_lines (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  company_id BIGINT UNSIGNED NOT NULL,
  session_id BIGINT UNSIGNED NOT NULL,
  line_type ENUM('serialized','quantity') NOT NULL,
  serialized_asset_id BIGINT UNSIGNED NULL,
  item_id BIGINT UNSIGNED NULL,
  location_id BIGINT UNSIGNED NOT NULL,
  expected_qty DECIMAL(18,4) NOT NULL DEFAULT 0,
  counted_qty DECIMAL(18,4) NULL,
  result ENUM('pending','found','missing','extra','wrong_location','wrong_holder','damaged') NOT NULL DEFAULT 'pending',
  `condition` VARCHAR(40) NULL,
  notes TEXT NULL,
  created_at TIMESTAMP NULL,
  updated_at TIMESTAMP NULL,
  UNIQUE KEY verification_lines_session_asset_unique (session_id, serialized_asset_id),
  UNIQUE KEY verification_lines_session_item_location_unique (session_id, item_id, location_id),
  CONSTRAINT verification_lines_company_id_foreign FOREIGN KEY (company_id) REFERENCES companies (id) ON DELETE CASCADE,
  CONSTRAINT verification_lines_session_id_foreign FOREIGN KEY (session_id) REFERENCES verification_sessions (id) ON DELETE CASCADE,
  CONSTRAINT verification_lines_asset_id_foreign FOREIGN KEY (serialized_asset_id) REFERENCES serialized_assets (id) ON DELETE RESTRICT,
  CONSTRAINT verification_lines_item_id_foreign FOREIGN KEY (item_id) REFERENCES asset_items (id) ON DELETE RESTRICT,
  CONSTRAINT verification_lines_location_id_foreign FOREIGN KEY (location_id) REFERENCES asset_locations (id) ON DELETE RESTRICT
);

-- ------------------------------------------------------------
-- Phase 6 — Approval documents + finance outbox
-- ------------------------------------------------------------

CREATE TABLE asset_transfers (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  company_id BIGINT UNSIGNED NOT NULL,
  serialized_asset_id BIGINT UNSIGNED NOT NULL,
  from_location_id BIGINT UNSIGNED NOT NULL,
  to_location_id BIGINT UNSIGNED NOT NULL,
  from_assignment_id BIGINT UNSIGNED NULL,
  to_holder_type ENUM('employee','department') NULL,
  to_employee_id BIGINT UNSIGNED NULL,
  to_department_id BIGINT UNSIGNED NULL,
  status ENUM('draft','pending_approval','rejected','completed','cancelled') NOT NULL DEFAULT 'draft',
  approval_request_id BIGINT UNSIGNED NULL,
  requested_by BIGINT UNSIGNED NOT NULL,
  reason TEXT NULL,
  created_at TIMESTAMP NULL,
  updated_at TIMESTAMP NULL,
  KEY asset_transfers_company_status_index (company_id, status),
  CONSTRAINT asset_transfers_company_id_foreign FOREIGN KEY (company_id) REFERENCES companies (id) ON DELETE CASCADE,
  CONSTRAINT asset_transfers_asset_id_foreign FOREIGN KEY (serialized_asset_id) REFERENCES serialized_assets (id) ON DELETE RESTRICT,
  CONSTRAINT asset_transfers_from_location_id_foreign FOREIGN KEY (from_location_id) REFERENCES asset_locations (id) ON DELETE RESTRICT,
  CONSTRAINT asset_transfers_to_location_id_foreign FOREIGN KEY (to_location_id) REFERENCES asset_locations (id) ON DELETE RESTRICT,
  CONSTRAINT asset_transfers_requested_by_foreign FOREIGN KEY (requested_by) REFERENCES users (id) ON DELETE RESTRICT
);

CREATE TABLE asset_disposals (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  company_id BIGINT UNSIGNED NOT NULL,
  serialized_asset_id BIGINT UNSIGNED NOT NULL,
  reason TEXT NOT NULL,
  status ENUM('draft','pending_approval','rejected','completed','cancelled') NOT NULL DEFAULT 'draft',
  approval_request_id BIGINT UNSIGNED NULL,
  requested_by BIGINT UNSIGNED NOT NULL,
  disposed_at DATETIME NULL,
  created_at TIMESTAMP NULL,
  updated_at TIMESTAMP NULL,
  CONSTRAINT asset_disposals_company_id_foreign FOREIGN KEY (company_id) REFERENCES companies (id) ON DELETE CASCADE,
  CONSTRAINT asset_disposals_asset_id_foreign FOREIGN KEY (serialized_asset_id) REFERENCES serialized_assets (id) ON DELETE RESTRICT,
  CONSTRAINT asset_disposals_requested_by_foreign FOREIGN KEY (requested_by) REFERENCES users (id) ON DELETE RESTRICT
);

CREATE TABLE asset_write_offs (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  company_id BIGINT UNSIGNED NOT NULL,
  serialized_asset_id BIGINT UNSIGNED NOT NULL,
  reason TEXT NOT NULL,
  amount DECIMAL(15,2) NOT NULL,
  cost_center_id BIGINT UNSIGNED NULL,
  status ENUM('draft','pending_approval','rejected','completed','cancelled') NOT NULL DEFAULT 'draft',
  approval_request_id BIGINT UNSIGNED NULL,
  requested_by BIGINT UNSIGNED NOT NULL,
  written_off_at DATETIME NULL,
  created_at TIMESTAMP NULL,
  updated_at TIMESTAMP NULL,
  CONSTRAINT asset_write_offs_company_id_foreign FOREIGN KEY (company_id) REFERENCES companies (id) ON DELETE CASCADE,
  CONSTRAINT asset_write_offs_asset_id_foreign FOREIGN KEY (serialized_asset_id) REFERENCES serialized_assets (id) ON DELETE RESTRICT,
  CONSTRAINT asset_write_offs_cost_center_id_foreign FOREIGN KEY (cost_center_id) REFERENCES cost_centers (id) ON DELETE SET NULL,
  CONSTRAINT asset_write_offs_requested_by_foreign FOREIGN KEY (requested_by) REFERENCES users (id) ON DELETE RESTRICT
);

CREATE TABLE inventory_adjustments (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  company_id BIGINT UNSIGNED NOT NULL,
  reason TEXT NOT NULL,
  status ENUM('draft','pending_approval','rejected','posted','cancelled') NOT NULL DEFAULT 'draft',
  approval_request_id BIGINT UNSIGNED NULL,
  verification_session_id BIGINT UNSIGNED NULL,
  requested_by BIGINT UNSIGNED NOT NULL,
  posted_at DATETIME NULL,
  created_at TIMESTAMP NULL,
  updated_at TIMESTAMP NULL,
  CONSTRAINT inventory_adjustments_company_id_foreign FOREIGN KEY (company_id) REFERENCES companies (id) ON DELETE CASCADE,
  CONSTRAINT inventory_adjustments_session_id_foreign FOREIGN KEY (verification_session_id) REFERENCES verification_sessions (id) ON DELETE SET NULL,
  CONSTRAINT inventory_adjustments_requested_by_foreign FOREIGN KEY (requested_by) REFERENCES users (id) ON DELETE RESTRICT
);

CREATE TABLE inventory_adjustment_lines (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  company_id BIGINT UNSIGNED NOT NULL,
  adjustment_id BIGINT UNSIGNED NOT NULL,
  item_id BIGINT UNSIGNED NOT NULL,
  location_id BIGINT UNSIGNED NOT NULL,
  expected_qty DECIMAL(18,4) NOT NULL,
  counted_qty DECIMAL(18,4) NOT NULL,
  variance_qty DECIMAL(18,4) NOT NULL,
  verification_line_id BIGINT UNSIGNED NULL,
  created_at TIMESTAMP NULL,
  updated_at TIMESTAMP NULL,
  CONSTRAINT inventory_adjustment_lines_company_id_foreign FOREIGN KEY (company_id) REFERENCES companies (id) ON DELETE CASCADE,
  CONSTRAINT inventory_adjustment_lines_adjustment_id_foreign FOREIGN KEY (adjustment_id) REFERENCES inventory_adjustments (id) ON DELETE CASCADE,
  CONSTRAINT inventory_adjustment_lines_item_id_foreign FOREIGN KEY (item_id) REFERENCES asset_items (id) ON DELETE RESTRICT,
  CONSTRAINT inventory_adjustment_lines_location_id_foreign FOREIGN KEY (location_id) REFERENCES asset_locations (id) ON DELETE RESTRICT
);

CREATE TABLE asset_finance_events (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  company_id BIGINT UNSIGNED NOT NULL,
  event ENUM('acquisition','disposal','write_off') NOT NULL,
  serialized_asset_id BIGINT UNSIGNED NULL,
  amount DECIMAL(15,2) NOT NULL,
  currency VARCHAR(10) NOT NULL,
  cost_center_id BIGINT UNSIGNED NULL,
  reference_type VARCHAR(100) NOT NULL,
  reference_id BIGINT UNSIGNED NOT NULL,
  status ENUM('pending','accepted','void') NOT NULL DEFAULT 'pending',
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NULL,
  UNIQUE KEY asset_finance_events_unique (company_id, event, reference_type, reference_id),
  KEY asset_finance_events_status_index (company_id, status),
  CONSTRAINT asset_finance_events_company_id_foreign FOREIGN KEY (company_id) REFERENCES companies (id) ON DELETE CASCADE,
  CONSTRAINT asset_finance_events_asset_id_foreign FOREIGN KEY (serialized_asset_id) REFERENCES serialized_assets (id) ON DELETE SET NULL,
  CONSTRAINT asset_finance_events_cost_center_id_foreign FOREIGN KEY (cost_center_id) REFERENCES cost_centers (id) ON DELETE SET NULL
);

-- ------------------------------------------------------------
-- Phase 7 — Warranty & AMC
-- ------------------------------------------------------------

CREATE TABLE asset_service_providers (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  company_id BIGINT UNSIGNED NOT NULL,
  name VARCHAR(150) NOT NULL,
  code VARCHAR(50) NOT NULL,
  phone VARCHAR(50) NULL,
  email VARCHAR(150) NULL,
  status ENUM('active','inactive') NOT NULL DEFAULT 'active',
  created_by BIGINT UNSIGNED NULL,
  updated_by BIGINT UNSIGNED NULL,
  created_at TIMESTAMP NULL,
  updated_at TIMESTAMP NULL,
  UNIQUE KEY asset_service_providers_company_code_unique (company_id, code),
  CONSTRAINT asset_service_providers_company_id_foreign FOREIGN KEY (company_id) REFERENCES companies (id) ON DELETE CASCADE,
  CONSTRAINT asset_service_providers_created_by_foreign FOREIGN KEY (created_by) REFERENCES users (id) ON DELETE SET NULL,
  CONSTRAINT asset_service_providers_updated_by_foreign FOREIGN KEY (updated_by) REFERENCES users (id) ON DELETE SET NULL
);

CREATE TABLE asset_warranties (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  company_id BIGINT UNSIGNED NOT NULL,
  serialized_asset_id BIGINT UNSIGNED NOT NULL,
  provider_id BIGINT UNSIGNED NOT NULL,
  starts_on DATE NOT NULL,
  ends_on DATE NOT NULL,
  coverage TEXT NULL,
  terms TEXT NULL,
  reference_no VARCHAR(100) NULL,
  file_path VARCHAR(255) NULL,
  original_name VARCHAR(255) NULL,
  created_by BIGINT UNSIGNED NULL,
  created_at TIMESTAMP NULL,
  updated_at TIMESTAMP NULL,
  KEY asset_warranties_company_ends_on_index (company_id, ends_on),
  CONSTRAINT asset_warranties_company_id_foreign FOREIGN KEY (company_id) REFERENCES companies (id) ON DELETE CASCADE,
  CONSTRAINT asset_warranties_asset_id_foreign FOREIGN KEY (serialized_asset_id) REFERENCES serialized_assets (id) ON DELETE CASCADE,
  CONSTRAINT asset_warranties_provider_id_foreign FOREIGN KEY (provider_id) REFERENCES asset_service_providers (id) ON DELETE RESTRICT,
  CONSTRAINT asset_warranties_created_by_foreign FOREIGN KEY (created_by) REFERENCES users (id) ON DELETE SET NULL
);

CREATE TABLE asset_service_contracts (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  company_id BIGINT UNSIGNED NOT NULL,
  provider_id BIGINT UNSIGNED NOT NULL,
  contract_no VARCHAR(100) NOT NULL,
  starts_on DATE NOT NULL,
  ends_on DATE NOT NULL,
  coverage TEXT NULL,
  sla TEXT NULL,
  frequency VARCHAR(100) NULL,
  cost DECIMAL(15,2) NULL,
  currency VARCHAR(10) NULL,
  renewal_status ENUM('active','expired','renewed','cancelled') NOT NULL DEFAULT 'active',
  created_by BIGINT UNSIGNED NULL,
  updated_by BIGINT UNSIGNED NULL,
  created_at TIMESTAMP NULL,
  updated_at TIMESTAMP NULL,
  UNIQUE KEY asset_service_contracts_company_no_unique (company_id, contract_no),
  CONSTRAINT asset_service_contracts_company_id_foreign FOREIGN KEY (company_id) REFERENCES companies (id) ON DELETE CASCADE,
  CONSTRAINT asset_service_contracts_provider_id_foreign FOREIGN KEY (provider_id) REFERENCES asset_service_providers (id) ON DELETE RESTRICT,
  CONSTRAINT asset_service_contracts_created_by_foreign FOREIGN KEY (created_by) REFERENCES users (id) ON DELETE SET NULL,
  CONSTRAINT asset_service_contracts_updated_by_foreign FOREIGN KEY (updated_by) REFERENCES users (id) ON DELETE SET NULL
);

CREATE TABLE asset_service_contract_assets (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  company_id BIGINT UNSIGNED NOT NULL,
  contract_id BIGINT UNSIGNED NOT NULL,
  serialized_asset_id BIGINT UNSIGNED NOT NULL,
  created_at TIMESTAMP NULL,
  updated_at TIMESTAMP NULL,
  UNIQUE KEY asset_service_contract_assets_unique (contract_id, serialized_asset_id),
  CONSTRAINT asset_service_contract_assets_company_id_foreign FOREIGN KEY (company_id) REFERENCES companies (id) ON DELETE CASCADE,
  CONSTRAINT asset_service_contract_assets_contract_id_foreign FOREIGN KEY (contract_id) REFERENCES asset_service_contracts (id) ON DELETE CASCADE,
  CONSTRAINT asset_service_contract_assets_asset_id_foreign FOREIGN KEY (serialized_asset_id) REFERENCES serialized_assets (id) ON DELETE RESTRICT
);

CREATE TABLE asset_service_contract_files (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  company_id BIGINT UNSIGNED NOT NULL,
  contract_id BIGINT UNSIGNED NOT NULL,
  file_path VARCHAR(255) NOT NULL,
  original_name VARCHAR(255) NOT NULL,
  mime VARCHAR(100) NULL,
  size BIGINT UNSIGNED NULL,
  uploaded_by BIGINT UNSIGNED NULL,
  created_at TIMESTAMP NULL,
  updated_at TIMESTAMP NULL,
  KEY asset_service_contract_files_contract_index (company_id, contract_id),
  CONSTRAINT asset_service_contract_files_company_id_foreign FOREIGN KEY (company_id) REFERENCES companies (id) ON DELETE CASCADE,
  CONSTRAINT asset_service_contract_files_contract_id_foreign FOREIGN KEY (contract_id) REFERENCES asset_service_contracts (id) ON DELETE CASCADE,
  CONSTRAINT asset_service_contract_files_uploaded_by_foreign FOREIGN KEY (uploaded_by) REFERENCES users (id) ON DELETE SET NULL
);

-- ------------------------------------------------------------
-- Phase 8 — Opening import
-- ------------------------------------------------------------

CREATE TABLE asset_import_batches (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  company_id BIGINT UNSIGNED NOT NULL,
  file_name VARCHAR(255) NOT NULL,
  status ENUM('uploaded','validated','confirmed','failed','cancelled') NOT NULL DEFAULT 'uploaded',
  total_rows INT UNSIGNED NOT NULL DEFAULT 0,
  valid_rows INT UNSIGNED NOT NULL DEFAULT 0,
  failed_rows INT UNSIGNED NOT NULL DEFAULT 0,
  error_report_path VARCHAR(500) NULL,
  created_by BIGINT UNSIGNED NULL,
  updated_by BIGINT UNSIGNED NULL,
  created_at TIMESTAMP NULL,
  updated_at TIMESTAMP NULL,
  KEY asset_import_batches_company_status_index (company_id, status),
  CONSTRAINT asset_import_batches_company_id_foreign FOREIGN KEY (company_id) REFERENCES companies (id) ON DELETE CASCADE,
  CONSTRAINT asset_import_batches_created_by_foreign FOREIGN KEY (created_by) REFERENCES users (id) ON DELETE SET NULL,
  CONSTRAINT asset_import_batches_updated_by_foreign FOREIGN KEY (updated_by) REFERENCES users (id) ON DELETE SET NULL
);

CREATE TABLE asset_import_rows (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  company_id BIGINT UNSIGNED NOT NULL,
  batch_id BIGINT UNSIGNED NOT NULL,
  row_number INT UNSIGNED NOT NULL,
  payload JSON NOT NULL,
  errors JSON NULL,
  result ENUM('pending','valid','invalid','created','skipped') NOT NULL DEFAULT 'pending',
  created_entity_type VARCHAR(100) NULL,
  created_entity_id BIGINT UNSIGNED NULL,
  created_at TIMESTAMP NULL,
  updated_at TIMESTAMP NULL,
  UNIQUE KEY asset_import_rows_batch_row_unique (batch_id, row_number),
  CONSTRAINT asset_import_rows_company_id_foreign FOREIGN KEY (company_id) REFERENCES companies (id) ON DELETE CASCADE,
  CONSTRAINT asset_import_rows_batch_id_foreign FOREIGN KEY (batch_id) REFERENCES asset_import_batches (id) ON DELETE CASCADE
);

-- ------------------------------------------------------------
-- Phase 9 — Maintenance handoff (thin log)
-- ------------------------------------------------------------

CREATE TABLE asset_service_events (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  company_id BIGINT UNSIGNED NOT NULL,
  serialized_asset_id BIGINT UNSIGNED NOT NULL,
  contract_id BIGINT UNSIGNED NULL,
  warranty_id BIGINT UNSIGNED NULL,
  occurred_on DATE NOT NULL,
  summary VARCHAR(255) NOT NULL,
  notes TEXT NULL,
  cost DECIMAL(15,2) NULL,
  external_reference VARCHAR(100) NULL,
  created_by BIGINT UNSIGNED NULL,
  created_at TIMESTAMP NULL,
  updated_at TIMESTAMP NULL,
  KEY asset_service_events_asset_occurred_index (serialized_asset_id, occurred_on),
  CONSTRAINT asset_service_events_company_id_foreign FOREIGN KEY (company_id) REFERENCES companies (id) ON DELETE CASCADE,
  CONSTRAINT asset_service_events_asset_id_foreign FOREIGN KEY (serialized_asset_id) REFERENCES serialized_assets (id) ON DELETE RESTRICT,
  CONSTRAINT asset_service_events_contract_id_foreign FOREIGN KEY (contract_id) REFERENCES asset_service_contracts (id) ON DELETE SET NULL,
  CONSTRAINT asset_service_events_warranty_id_foreign FOREIGN KEY (warranty_id) REFERENCES asset_warranties (id) ON DELETE SET NULL,
  CONSTRAINT asset_service_events_created_by_foreign FOREIGN KEY (created_by) REFERENCES users (id) ON DELETE SET NULL
);
```

## Table ownership by phase

| Phase | Tables |
|-------|--------|
| 1 | `asset_categories`, `asset_units`, `asset_items`, `asset_attribute_*`, `asset_item_attribute_values`, `asset_locations` |
| 2 | `serialized_assets`, `serialized_asset_attribute_values`, `asset_tags`, `asset_tag_events` |
| 3 | `inventory_transactions`, `inventory_balances`, `asset_receipt_inbox` |
| 4 | `serialized_asset_assignments`, `asset_handovers` (+ optional `employee_assets.serialized_asset_id`) |
| 5 | `verification_sessions`, `verification_lines` |
| 6 | `asset_transfers`, `asset_disposals`, `asset_write_offs`, `inventory_adjustments`, `inventory_adjustment_lines`, `asset_finance_events` |
| 7 | `asset_service_providers`, `asset_warranties`, `asset_service_contracts`, `asset_service_contract_assets`, `asset_service_contract_files` |
| 8 | `asset_import_batches`, `asset_import_rows` |
| 9 | `asset_service_events` |
| 10–11 | No new tables (indexes / APIs only) |

---

## DDL Lock Sign-off (AST-SCHEMA-1)

| Field | Detail |
|---|---|
| **Task Serial** | `AST-SCHEMA-1` (Full V1 Database Schema Lock · Spike) |
| **Status** | **LOCKED & ACCEPTED** |
| **Sign-off Date** | 2026-09-30 |
| **Downstream Migration Owner** | `AST-SCHEMA-MIG` |
| **Scope Boundary** | No Laravel migrations in this card. All 31 V1 domain tables frozen for centralized migration in `AST-SCHEMA-MIG`. |