# Database Schema Documentation

Generated from local database `licence_permit` on 2026-06-03.

This document describes the current database structure for the NCS Licence & Permit portal. It should be updated whenever migrations add, remove, or repurpose tables/columns.

## 1. Schema Overview

The database is organized around these areas:

| Area | Tables |
| --- | --- |
| Authentication and access | `users`, `officer_profiles`, `applicant_profiles`, `roles`, `permissions`, `model_has_roles`, `model_has_permissions`, `role_has_permissions`, `personal_access_tokens`, `password_setup_tokens`, `password_reset_tokens`, `sessions` |
| Admin configuration | `commands`, `ranks`, `licence_types`, `document_types`, `licence_type_document_requirements`, `licence_type_fees`, `licence_type_other_fees`, `licence_type_bonds`, `licence_type_workflow_settings`, `licence_serial_settings` |
| Applicant workflow | `applicant_applications`, `applicant_application_documents`, `applicant_application_activities`, `applicant_application_inspection_reports`, `applicant_application_payments`, `applicant_directors`, `applicant_field_agents`, `application_congratulatory_letters` |
| Renewal and onboarding lookup | `existing_licence`, `legacy_applicants`, `legacy_field_agents`, `legacy_field_agent_transmissions`, `field_agent_onboarding_requests`, `field_agent_transmissions` |
| Legacy source/import staging | `singlepage_applicants`, `singlepage_authorized_users`, `singlepage_transmit` |
| Queue/cache/runtime | `jobs`, `job_batches`, `failed_jobs`, `cache`, `cache_locks`, `migrations` |

## 2. Key Workflow Values

These are stored as strings rather than database enums.

### Application Status

| Value | Meaning |
| --- | --- |
| `pending` | Draft/pre-submission state. |
| `submitted` | Submitted and currently inside the review/payment/issuance workflow. |
| `queried` | Returned to applicant for correction. |
| `issued` | Final approval completed and certificate/licence issued. |

### Application Stage

| Value | Meaning |
| --- | --- |
| `documents` | Applicant is uploading required documents/directors. |
| `review_officer_review` | Area Command Review Officer initial check. |
| `bond_seat_review` | Bond Seat item-level review of directors/documents and inspection report. |
| `hq_review` | First HQ review before payment approval. |
| `payment` | Applicant payment/bond upload stage. |
| `payment_review` | Accounts/payment review stage. |
| `bond_review` | Bond Seat post-payment bond document review. |
| `final_hq_review` | Final HQ review after payment and bond validation. |
| `validation` | HQ validator stage. |
| `issuance` | Comptroller L&P final approval/issuance stage. |
| `issued` | Licence issued. |

### Review Item Status

Used by directors, uploaded documents, payment assessment, bond documents, and onboarding requests.

| Value | Meaning |
| --- | --- |
| `pending` | Awaiting review. |
| `approved` | Validated/approved by relevant officer. |
| `queried` | Returned for correction. |
| `declined` | Onboarding request rejected/closed. |
| `failed` | External transmission failed and may need retry. |

## 3. Relationship Summary

| Parent | Child | Relationship |
| --- | --- | --- |
| `users` | `applicant_profiles`, `officer_profiles` | One user has one profile by account type. |
| `users` | `applicant_applications`, `applicant_directors`, `applicant_field_agents` | Applicant-owned records. |
| `commands` | `officer_profiles`, `applicant_applications`, `existing_licence`, `field_agent_onboarding_requests` | Command ownership/routing. |
| `licence_types` | `licence_type_*`, `applicant_applications`, `existing_licence` | Licence/permit configuration and issued/legacy records. |
| `document_types` | `licence_type_document_requirements`, `applicant_application_documents` | Document library and uploaded files. |
| `applicant_applications` | documents, activities, payments, inspection reports, congratulatory letters | Application file and lifecycle records. |
| `applicant_field_agents` / `legacy_field_agents` | `field_agent_onboarding_requests` | Field agent onboarding requests. |

## 4. Domain Tables

### `users`

Primary login table for applicants and officers.

| Column | Type | Null | Default | Key |
| --- | --- | --- | --- | --- |
| `id` | bigint unsigned | no | auto | PK |
| `name` | varchar(255) | no |  |  |
| `email` | varchar(255) | no |  | unique |
| `phone` | varchar(255) | yes | null |  |
| `account_type` | varchar(255) | no | `applicant` | index |
| `status` | varchar(255) | no | `active` | index |
| `email_verified_at` | timestamp | yes | null |  |
| `password` | varchar(255) | no |  |  |
| `remember_token` | varchar(100) | yes | null |  |
| `created_at`, `updated_at` | timestamp | yes | null |  |

### `applicant_profiles`

Company profile populated from validated/manual data.

| Column | Type | Null | Default | Key |
| --- | --- | --- | --- | --- |
| `id` | bigint unsigned | no | auto | PK |
| `user_id` | bigint unsigned | no |  | unique, FK `users.id` cascade |
| `company_name` | varchar(255) | yes | null |  |
| `rc_number` | varchar(255) | yes | null | index |
| `tin` | varchar(255) | yes | null | index |
| `jtbtin` | varchar(255) | yes | null | index |
| `company_phone` | varchar(255) | yes | null |  |
| `company_address` | text | yes | null |  |
| `tax_office_id` | varchar(255) | yes | null |  |
| `tax_office_name` | varchar(255) | yes | null |  |
| `taxpayer_type` | varchar(10) | yes | null |  |
| `created_at`, `updated_at` | timestamp | yes | null |  |

### `officer_profiles`

Officer metadata used for admin, command, HQ, and role-based routing.

| Column | Type | Null | Default | Key |
| --- | --- | --- | --- | --- |
| `id` | bigint unsigned | no | auto | PK |
| `user_id` | bigint unsigned | no |  | unique, FK `users.id` cascade |
| `command_id` | bigint unsigned | yes | null | FK `commands.id` set null |
| `service_number` | varchar(255) | yes | null | unique |
| `rank` | varchar(255) | yes | null |  |
| `created_at`, `updated_at` | timestamp | yes | null |  |

### `commands`

Area command/HQ/command registry.

| Column | Type | Null | Default | Key |
| --- | --- | --- | --- | --- |
| `id` | bigint unsigned | no | auto | PK |
| `name` | varchar(255) | no |  | unique |
| `code` | varchar(255) | no |  | unique |
| `type` | varchar(255) | no | `area_command` | index |
| `state` | varchar(255) | yes | null |  |
| `is_active` | tinyint(1) | no | 1 | index |
| `created_at`, `updated_at` | timestamp | yes | null |  |

### `ranks`

Rank lookup for officer setup.

| Column | Type | Null | Default | Key |
| --- | --- | --- | --- | --- |
| `id` | bigint unsigned | no | auto | PK |
| `code` | varchar(255) | no |  | unique |
| `name` | varchar(255) | no |  |  |
| `sort_order` | smallint unsigned | no | 0 |  |
| `created_at`, `updated_at` | timestamp | yes | null |  |

### `licence_types`

Master licence/permit type table.

| Column | Type | Null | Default | Key |
| --- | --- | --- | --- | --- |
| `id` | bigint unsigned | no | auto | PK |
| `name` | varchar(255) | no |  | unique |
| `code` | varchar(255) | no |  | unique |
| `category` | varchar(255) | no |  | index |
| `validity_period_months` | smallint unsigned | no | 12 |  |
| `expiry_calculation_type` | varchar(255) | no | `validity_period` |  |
| `fixed_expiry_month` | tinyint unsigned | yes | null |  |
| `fixed_expiry_day` | tinyint unsigned | yes | null |  |
| `is_active` | tinyint(1) | no | 1 | index |
| `created_at`, `updated_at` | timestamp | yes | null |  |

### `document_types`

Reusable document library.

| Column | Type | Null | Default | Key |
| --- | --- | --- | --- | --- |
| `id` | bigint unsigned | no | auto | PK |
| `name` | varchar(255) | no |  | unique |
| `code` | varchar(255) | no |  | unique |
| `description` | text | yes | null |  |
| `is_active` | tinyint(1) | no | 1 | index |
| `created_at`, `updated_at` | timestamp | yes | null |  |

### `licence_type_document_requirements`

Document requirements per licence type and application type.

| Column | Type | Null | Default | Key |
| --- | --- | --- | --- | --- |
| `id` | bigint unsigned | no | auto | PK |
| `licence_type_id` | bigint unsigned | no |  | FK `licence_types.id` cascade |
| `document_type_id` | bigint unsigned | no |  | FK `document_types.id` cascade |
| `application_type` | varchar(255) | no | `both` | index |
| `is_active` | tinyint(1) | no | 1 | index |
| `is_required` | tinyint(1) | no | 1 |  |
| `allowed_file_types` | longtext/json | yes | null |  |
| `max_file_size_kb` | int unsigned | no | 5120 |  |
| `sort_order` | smallint unsigned | no | 0 |  |
| `created_at`, `updated_at` | timestamp | yes | null |  |

Unique: `licence_type_id`, `document_type_id`, `application_type`.

### `licence_type_fees`

Main licence fee by licence type, application type, and year.

| Column | Type | Null | Default | Key |
| --- | --- | --- | --- | --- |
| `id` | bigint unsigned | no | auto | PK |
| `licence_type_id` | bigint unsigned | no |  | FK `licence_types.id` cascade |
| `application_type` | varchar(255) | no |  | index |
| `year` | smallint unsigned | no |  | index |
| `amount` | decimal(14,2) | no |  |  |
| `currency` | varchar(3) | no | `NGN` |  |
| `is_active` | tinyint(1) | no | 1 | index |
| `created_at`, `updated_at` | timestamp | yes | null |  |

Unique: `licence_type_id`, `application_type`, `year`.

### `licence_type_other_fees`

Additional configurable fees, such as operations fee.

| Column | Type | Null | Default | Key |
| --- | --- | --- | --- | --- |
| `id` | bigint unsigned | no | auto | PK |
| `licence_type_id` | bigint unsigned | no |  | FK `licence_types.id` cascade |
| `application_type` | varchar(255) | no |  | index |
| `year` | smallint unsigned | no |  | index |
| `name` | varchar(255) | no |  |  |
| `amount` | decimal(14,2) | no |  |  |
| `currency` | varchar(3) | no | `NGN` |  |
| `description` | text | yes | null |  |
| `sort_order` | smallint unsigned | no | 0 |  |
| `is_active` | tinyint(1) | no | 1 | index |
| `created_at`, `updated_at` | timestamp | yes | null |  |

Unique: `licence_type_id`, `application_type`, `year`, `name`.

### `licence_type_bonds`

Bond/deposit configuration by licence type, application type, and year.

| Column | Type | Null | Default | Key |
| --- | --- | --- | --- | --- |
| `id` | bigint unsigned | no | auto | PK |
| `licence_type_id` | bigint unsigned | no |  | FK `licence_types.id` cascade |
| `application_type` | varchar(255) | no |  | index |
| `year` | smallint unsigned | no |  | index |
| `amount` | decimal(14,2) | no |  |  |
| `currency` | varchar(3) | no | `NGN` |  |
| `description` | text | yes | null |  |
| `is_active` | tinyint(1) | no | 1 | index |
| `created_at`, `updated_at` | timestamp | yes | null |  |

Unique: `licence_type_id`, `application_type`, `year`.

### `licence_type_workflow_settings`

Workflow switches for each licence type.

| Column | Type | Null | Default | Key |
| --- | --- | --- | --- | --- |
| `id` | bigint unsigned | no | auto | PK |
| `licence_type_id` | bigint unsigned | no |  | unique, FK `licence_types.id` cascade |
| `requires_command_review` | tinyint(1) | no | 1 |  |
| `requires_bond` | tinyint(1) | no | 0 |  |
| `requires_inspection` | tinyint(1) | no | 0 |  |
| `requires_payment_review` | tinyint(1) | no | 1 |  |
| `requires_field_agents` | tinyint(1) | no | 0 |  |
| `requires_authority_cards` | tinyint(1) | no | 0 |  |
| `allow_renewal` | tinyint(1) | no | 1 |  |
| `created_at`, `updated_at` | timestamp | yes | null |  |

### `licence_serial_settings`

Controls generated licence serials and legacy serial continuation.

| Column | Type | Null | Default | Key |
| --- | --- | --- | --- | --- |
| `id` | bigint unsigned | no | auto | PK |
| `licence_type_id` | bigint unsigned | yes | null | FK `licence_types.id` cascade |
| `scope` | varchar(255) | no | `licence` |  |
| `number_format` | varchar(255) | no | `customs_agent` |  |
| `last_issued_licence_number` | varchar(255) | yes | null |  |
| `last_issued_serial` | int unsigned | no | 0 |  |
| `updated_by` | bigint unsigned | yes | null | FK `users.id` set null |
| `created_at`, `updated_at` | timestamp | yes | null |  |

Unique: `licence_type_id`, `number_format`.

## 5. Applicant Application Tables

### `applicant_applications`

Central application file table.

| Column | Type | Null | Default | Key |
| --- | --- | --- | --- | --- |
| `id` | bigint unsigned | no | auto | PK |
| `user_id` | bigint unsigned | no |  | FK `users.id` cascade |
| `command_id` | bigint unsigned | no |  | FK `commands.id` restrict |
| `licence_type_id` | bigint unsigned | no |  | FK `licence_types.id` restrict |
| `licence_type_fee_id` | bigint unsigned | yes | null | FK `licence_type_fees.id` set null |
| `reference_number` | varchar(255) | no |  | unique |
| `serial_number` | int unsigned | no |  | composite unique |
| `application_type` | varchar(255) | no |  | index |
| `application_year` | smallint unsigned | no |  | index |
| `status` | varchar(255) | no | `pending` | index |
| `current_stage` | varchar(255) | no | `documents` | index |
| `query_note` | text | yes | null |  |
| `return_stage_after_query` | varchar(255) | yes | null |  |
| `legacy_licence_number` | varchar(255) | yes | null | index |
| `licence_number` | varchar(255) | yes | null | index |
| `certificate_verification_code` | varchar(40) | yes | null | unique |
| `certificate_verification_generated_at` | timestamp | yes | null |  |
| `licence_serial_number` | int unsigned | yes | null | index |
| `licence_generated_at` | timestamp | yes | null |  |
| `licence_generated_by` | bigint unsigned | yes | null | FK `users.id` set null |
| `issued_at` | timestamp | yes | null |  |
| `issued_by` | bigint unsigned | yes | null | FK `users.id` set null |
| `fee_amount` | decimal(14,2) | yes | null | fee snapshot |
| `fee_currency` | varchar(3) | no | `NGN` |  |
| `created_at`, `updated_at` | timestamp | yes | null |  |

Unique: `command_id`, `licence_type_id`, `application_year`, `serial_number`.

### `applicant_application_documents`

Uploaded files against configured requirements.

| Column | Type | Null | Default | Key |
| --- | --- | --- | --- | --- |
| `id` | bigint unsigned | no | auto | PK |
| `applicant_application_id` | bigint unsigned | no |  | FK `applicant_applications.id` cascade |
| `licence_type_document_requirement_id` | bigint unsigned | no |  | FK `licence_type_document_requirements.id` restrict |
| `document_type_id` | bigint unsigned | no |  | FK `document_types.id` restrict |
| `original_file_name` | varchar(255) | no |  |  |
| `stored_file_name` | varchar(255) | no |  |  |
| `file_path` | varchar(255) | no |  |  |
| `mime_type` | varchar(255) | yes | null |  |
| `file_size_kb` | int unsigned | no |  |  |
| `status` | varchar(255) | no | `pending` | index |
| `query_note` | text | yes | null |  |
| `created_at`, `updated_at` | timestamp | yes | null |  |

Unique: `applicant_application_id`, `licence_type_document_requirement_id`.

### `applicant_application_activities`

Activity trail shown to command/HQ/applicant where relevant.

| Column | Type | Null | Default | Key |
| --- | --- | --- | --- | --- |
| `id` | bigint unsigned | no | auto | PK |
| `applicant_application_id` | bigint unsigned | no |  | FK `applicant_applications.id` cascade |
| `actor_user_id` | bigint unsigned | yes | null | FK `users.id` set null |
| `actor_name` | varchar(255) | yes | null |  |
| `actor_role` | varchar(255) | yes | null |  |
| `event_type` | varchar(255) | no |  | index |
| `title` | varchar(255) | no |  |  |
| `description` | text | yes | null |  |
| `metadata` | longtext/json | yes | null |  |
| `occurred_at` | timestamp | no | current timestamp | index |
| `created_at`, `updated_at` | timestamp | yes | null |  |

Index: `applicant_application_id`, `occurred_at`.

### `applicant_application_inspection_reports`

Inspection report added by Bond Seat before forwarding to HQ.

| Column | Type | Null | Default | Key |
| --- | --- | --- | --- | --- |
| `id` | bigint unsigned | no | auto | PK |
| `applicant_application_id` | bigint unsigned | no |  | unique, FK cascade |
| `inspected_by` | bigint unsigned | yes | null | FK `users.id` set null |
| `inspecting_officers` | text | no |  |  |
| `inspection_date` | date | no |  |  |
| `inspection_time` | time | no |  |  |
| `location` | text | no |  |  |
| `vehicles_count` | int unsigned | no | 0 |  |
| `vehicles_notes` | text | yes | null |  |
| `power` | text | no |  |  |
| `security_arrangement` | varchar(255) | no |  |  |
| `security_notes` | text | yes | null |  |
| `recommendation` | text | no |  |  |
| `created_at`, `updated_at` | timestamp | yes | null |  |

### `applicant_application_payments`

Payment assessment, bond document, and invoice snapshot.

| Column | Type | Null | Default | Key |
| --- | --- | --- | --- | --- |
| `id` | bigint unsigned | no | auto | PK |
| `applicant_application_id` | bigint unsigned | no |  | unique, FK cascade |
| `invoice_number` | varchar(255) | yes | null | unique |
| `submitted_by` | bigint unsigned | yes | null | FK `users.id` set null |
| `licence_fee_amount` | decimal(14,2) | yes | null |  |
| `other_fee_amount` | decimal(14,2) | no | 0.00 |  |
| `bond_amount` | decimal(14,2) | no | 0.00 | separate from invoice total |
| `total_amount` | decimal(14,2) | no |  | licence + other fees |
| `currency` | varchar(3) | no | `NGN` |  |
| `payment_channel` | varchar(255) | no | `bodogwu_assessment` | index |
| `bank_name` | varchar(255) | yes | null |  |
| `bank_code` | varchar(255) | yes | null | index |
| `bank_address` | text | yes | null |  |
| `bank_branch` | varchar(255) | yes | null |  |
| `bank_country` | varchar(2) | yes | null |  |
| `payment_reference` | varchar(255) | yes | null |  |
| `payment_date` | date | yes | null |  |
| `payment_evidence_original_name` | varchar(255) | yes | null |  |
| `payment_evidence_path` | varchar(255) | yes | null |  |
| `bond_document_original_name` | varchar(255) | yes | null |  |
| `bond_document_path` | varchar(255) | yes | null |  |
| `bond_number` | varchar(255) | yes | null |  |
| `bond_status` | varchar(255) | no | `pending` | index |
| `bond_query_note` | text | yes | null |  |
| `gateway_provider` | varchar(255) | yes | null |  |
| `gateway_reference` | varchar(255) | yes | null |  |
| `gateway_response` | longtext/json | yes | null |  |
| `assessment_state_id` | varchar(255) | yes | null | index |
| `assessment_notice_number` | varchar(255) | yes | null | index |
| `assessment_notice_date` | timestamp | yes | null |  |
| `assessment_status` | varchar(255) | no | `pending` | index |
| `assessment_request_payload` | longtext/json | yes | null |  |
| `status` | varchar(255) | no | `pending` | index |
| `query_note` | text | yes | null |  |
| `created_at`, `updated_at` | timestamp | yes | null |  |

### `applicant_directors`

Applicant-maintained directors, reviewed by Bond Seat.

| Column | Type | Null | Default | Key |
| --- | --- | --- | --- | --- |
| `id` | bigint unsigned | no | auto | PK |
| `user_id` | bigint unsigned | no |  | FK `users.id` cascade |
| `surname`, `firstname` | varchar(255) | no |  |  |
| `email` | varchar(255) | no |  |  |
| `phone` | varchar(11) | no |  |  |
| `nin` | varchar(11) | no |  | composite unique with `user_id` |
| `tin` | varchar(255) | yes | null |  |
| `gender` | varchar(20) | yes | null | deprecated/not displayed |
| `linked_to_licence` | tinyint(1) | no | 0 | deprecated/not displayed |
| `licence_number` | varchar(255) | yes | null | deprecated/not displayed |
| `home_address` | text | no |  |  |
| `passport_path` | varchar(255) | yes | null |  |
| `passport_file_name` | varchar(255) | yes | null |  |
| `status` | varchar(255) | no | `pending` |  |
| `query_note` | text | yes | null |  |
| `created_at`, `updated_at` | timestamp | yes | null |  |

Unique: `user_id`, `nin`.

### `applicant_field_agents`

Field agents added by applicants, command officers, or imports.

| Column | Type | Null | Default | Key |
| --- | --- | --- | --- | --- |
| `id` | bigint unsigned | no | auto | PK |
| `user_id` | bigint unsigned | yes | null | FK `users.id` cascade |
| `company_tin` | varchar(100) | yes | null | index |
| `company_rc_number` | varchar(100) | yes | null | index |
| `surname`, `firstname` | varchar(255) | no |  |  |
| `email` | varchar(255) | no |  | unique |
| `phone` | varchar(11) | no |  |  |
| `nin` | varchar(11) | no |  | unique with `user_id` |
| `tin` | varchar(255) | yes | null |  |
| `home_address` | text | no |  |  |
| `passport_path` | varchar(255) | yes | null |  |
| `passport_file_name` | varchar(255) | yes | null |  |
| `authority_card_number` | varchar(255) | yes | null | index |
| `authority_card_linked_at` | timestamp | yes | null |  |
| `bodogwu_transmission_status` | varchar(255) | no | `not_transmitted` | index |
| `status` | varchar(255) | no | `pending` |  |
| `source` | varchar(50) | no | `applicant` | index |
| `created_by_user_id` | bigint unsigned | yes | null | FK `users.id` set null |
| `created_at`, `updated_at` | timestamp | yes | null |  |

## 6. Letters, Certificates, and Lookup Tables

### `application_congratulatory_letters`

Stores generated congratulatory letter snapshots.

Columns: `id`, `applicant_application_id`, `generated_by`, `reference_number`, `company_name`, `recipient_title`, `company_address`, `licence_name`, `application_year`, `command_name`, `licence_fee_amount`, `licence_fee_words`, `other_fees`, `operations_fee_amount`, `operations_fee_words`, `bond_amount`, `bond_currency`, `bond_amount_words`, `bond_description`, `signatory_name`, `signatory_title`, `generated_at`, timestamps.

Important keys:
- `applicant_application_id` unique, FK `applicant_applications.id` cascade.
- `generated_by` FK `users.id` set null.
- `reference_number` indexed.

### `existing_licence`

Existing/manual/system-issued licence lookup used for renewals and onboarding.

Columns: `id`, `company_name`, `company` (TIN), `licence`, `licence_type_id`, `command_id`, `source`, `is_onboarding_active`, `onboarding_activated_at`, `onboarding_activated_by_user_id`, `status`, `bodogwu_status`, `bodogwu_response`, `nsw_status`, `date_created`, `created_by`, `email`, `phone`, `rc_number`, `address`, `notes`, `created_by_user_id`, `updated_by_user_id`.

Important keys:
- Unique: `company`, `licence`, and composite `company + licence`.
- FKs to `licence_types`, `commands`, and audit users are nullable/set null.
- `is_onboarding_active` controls whether system-issued licences can be used for direct onboarding.

## 7. Onboarding and Legacy Tables

### `field_agent_onboarding_requests`

Applicant/Bond Seat/Admin request queue for Bodogwu onboarding.

Columns: `id`, `applicant_field_agent_id`, `legacy_field_agent_id`, `company_tin`, `company_rc_number`, `licence_number`, `licence_type_id`, `command_id`, `requested_by_user_id`, `requested_by_type`, `status`, `query_note`, `last_transmission_status`, `approved_by_user_id`, `approved_at`, `requested_at`, timestamps.

Important indexes:
- `company_tin`, `command_id`, `status`.
- `status`, `requested_by_type`, `last_transmission_status`.

### `field_agent_transmissions`

Audit log of payloads sent to Bodogwu.

Columns: `id`, `field_agent_onboarding_request_id`, `applicant_field_agent_id`, `legacy_field_agent_id`, `company_tin`, `licence_number`, `command_id`, `command_code`, `transmission_type`, `status`, `http_status`, `payload`, `response`, `error_message`, `transmitted_by_user_id`, `transmitted_at`, timestamps.

Note: There are currently no explicit foreign keys on this table; it is an audit/transmission ledger and stores IDs/payload snapshots.

### `legacy_applicants`

Normalized import target for legacy `singlePage_applicants`.

Columns: `id`, `legacy_applicant_id`, `company_name`, `tin`, `licence_number`, `rc_number`, `email`, `phone`, `address`, `status`, `raw_payload`, timestamps.

Unique: `tin`, `licence_number`.

### `legacy_field_agents`

Normalized import target for legacy `singlePage_authorized_users`.

Columns: `id`, `legacy_auth_user_id`, `company_tin`, `company_rc_number`, `surname`, `firstname`, `othername`, `email`, `phone`, `nin`, `home_address`, `passport`, `status`, `raw_payload`, timestamps.

Unique: `legacy_auth_user_id`.

### `legacy_field_agent_transmissions`

Normalized import target for legacy `singlePage_transmit`.

Columns: `id`, `legacy_transmit_id`, `tin`, `licence_number`, `legacy_auth_user_id`, `command_code`, `status`, `legacy_date_created`, `raw_payload`, timestamps.

Unique: `legacy_transmit_id`.

### Legacy Source Tables

These are legacy dump tables loaded before running:

```bash
cd backend
C:\xampp\php\php.exe artisan onboarding:import-legacy
```

| Source table | Import target |
| --- | --- |
| `singlepage_applicants` | `legacy_applicants`, missing `existing_licence` records |
| `singlepage_authorized_users` | `legacy_field_agents` |
| `singlepage_transmit` | `legacy_field_agent_transmissions` |

The import command also creates missing `existing_licence` rows for renewal lookup.

## 8. Authentication and Authorization Tables

### Laravel/Spatie Auth Tables

| Table | Purpose |
| --- | --- |
| `roles` | Role names and guard. Unique: `name`, `guard_name`. |
| `permissions` | Permission names and guard. Unique: `name`, `guard_name`. |
| `model_has_roles` | Assigns roles to users. Composite PK: `role_id`, `model_id`, `model_type`. |
| `model_has_permissions` | Direct permission assignment. Composite PK: `permission_id`, `model_id`, `model_type`. |
| `role_has_permissions` | Role-permission pivot. Composite PK: `permission_id`, `role_id`. |
| `personal_access_tokens` | Laravel Sanctum API tokens. Unique token hash. |
| `password_setup_tokens` | Secure applicant/officer password setup/reset links. |
| `password_reset_tokens` | Laravel password reset token table. |
| `sessions` | Session storage table. |

### `password_setup_tokens`

Columns: `id`, `user_id`, `email`, `token_hash`, `expires_at`, `used_at`, timestamps.

Important keys:
- `user_id` FK `users.id` cascade.
- `token_hash` unique.
- `email` indexed.

## 9. Queue and Cache Tables

| Table | Purpose |
| --- | --- |
| `cache` | Application cache values. |
| `cache_locks` | Cache lock records. |
| `jobs` | Queued jobs. |
| `job_batches` | Batch job metadata. |
| `failed_jobs` | Failed queue job payloads/errors. |
| `migrations` | Laravel migration history. |

## 10. Foreign Key Reference

| Table | Column | References | Delete rule |
| --- | --- | --- | --- |
| `applicant_applications` | `user_id` | `users.id` | cascade |
| `applicant_applications` | `command_id` | `commands.id` | restrict |
| `applicant_applications` | `licence_type_id` | `licence_types.id` | restrict |
| `applicant_applications` | `licence_type_fee_id` | `licence_type_fees.id` | set null |
| `applicant_applications` | `licence_generated_by`, `issued_by` | `users.id` | set null |
| `applicant_application_activities` | `applicant_application_id` | `applicant_applications.id` | cascade |
| `applicant_application_activities` | `actor_user_id` | `users.id` | set null |
| `applicant_application_documents` | `applicant_application_id` | `applicant_applications.id` | cascade |
| `applicant_application_documents` | `document_type_id` | `document_types.id` | restrict |
| `applicant_application_documents` | `licence_type_document_requirement_id` | `licence_type_document_requirements.id` | restrict |
| `applicant_application_inspection_reports` | `applicant_application_id` | `applicant_applications.id` | cascade |
| `applicant_application_inspection_reports` | `inspected_by` | `users.id` | set null |
| `applicant_application_payments` | `applicant_application_id` | `applicant_applications.id` | cascade |
| `applicant_application_payments` | `submitted_by` | `users.id` | set null |
| `applicant_directors` | `user_id` | `users.id` | cascade |
| `applicant_field_agents` | `user_id` | `users.id` | cascade |
| `applicant_field_agents` | `created_by_user_id` | `users.id` | set null |
| `applicant_profiles` | `user_id` | `users.id` | cascade |
| `application_congratulatory_letters` | `applicant_application_id` | `applicant_applications.id` | cascade |
| `application_congratulatory_letters` | `generated_by` | `users.id` | set null |
| `existing_licence` | `licence_type_id` | `licence_types.id` | set null |
| `existing_licence` | `command_id` | `commands.id` | set null |
| `existing_licence` | audit user columns | `users.id` | set null |
| `field_agent_onboarding_requests` | `applicant_field_agent_id` | `applicant_field_agents.id` | set null |
| `field_agent_onboarding_requests` | `legacy_field_agent_id` | `legacy_field_agents.id` | set null |
| `field_agent_onboarding_requests` | `licence_type_id` | `licence_types.id` | set null |
| `field_agent_onboarding_requests` | `command_id` | `commands.id` | restrict |
| `field_agent_onboarding_requests` | request/approval user columns | `users.id` | set null |
| `licence_serial_settings` | `licence_type_id` | `licence_types.id` | cascade |
| `licence_serial_settings` | `updated_by` | `users.id` | set null |
| `licence_type_bonds` | `licence_type_id` | `licence_types.id` | cascade |
| `licence_type_document_requirements` | `licence_type_id` | `licence_types.id` | cascade |
| `licence_type_document_requirements` | `document_type_id` | `document_types.id` | cascade |
| `licence_type_fees` | `licence_type_id` | `licence_types.id` | cascade |
| `licence_type_other_fees` | `licence_type_id` | `licence_types.id` | cascade |
| `licence_type_workflow_settings` | `licence_type_id` | `licence_types.id` | cascade |
| `officer_profiles` | `user_id` | `users.id` | cascade |
| `officer_profiles` | `command_id` | `commands.id` | set null |
| `password_setup_tokens` | `user_id` | `users.id` | cascade |

## 11. Important Unique Constraints

| Table | Constraint |
| --- | --- |
| `users` | `email` |
| `commands` | `name`, `code` |
| `document_types` | `name`, `code` |
| `licence_types` | `name`, `code` |
| `licence_type_document_requirements` | `licence_type_id`, `document_type_id`, `application_type` |
| `licence_type_fees` | `licence_type_id`, `application_type`, `year` |
| `licence_type_other_fees` | `licence_type_id`, `application_type`, `year`, `name` |
| `licence_type_bonds` | `licence_type_id`, `application_type`, `year` |
| `licence_type_workflow_settings` | `licence_type_id` |
| `licence_serial_settings` | `licence_type_id`, `number_format` |
| `applicant_applications` | `reference_number`; `command_id`, `licence_type_id`, `application_year`, `serial_number`; `certificate_verification_code` |
| `applicant_application_documents` | `applicant_application_id`, `licence_type_document_requirement_id` |
| `applicant_application_payments` | `applicant_application_id`; `invoice_number` |
| `applicant_application_inspection_reports` | `applicant_application_id` |
| `application_congratulatory_letters` | `applicant_application_id` |
| `applicant_directors` | `user_id`, `nin` |
| `applicant_field_agents` | `email`; `user_id`, `nin` |
| `existing_licence` | `company`; `licence`; `company`, `licence` |
| `legacy_applicants` | `tin`, `licence_number` |
| `legacy_field_agents` | `legacy_auth_user_id` |
| `legacy_field_agent_transmissions` | `legacy_transmit_id` |
| `password_setup_tokens` | `token_hash` |
| `roles`, `permissions` | `name`, `guard_name` |

## 12. Notes and Maintenance Guidance

- `applicant_applications.fee_amount`, `applicant_application_payments.*_amount`, and congratulatory letter amounts are snapshots. Updating admin fee setup does not automatically rewrite already-created application/payment/letter records.
- `licence_type_document_requirements` uses `is_active` for archival where uploaded documents already reference a requirement. Do not hard-delete linked requirements.
- `existing_licence` is both a renewal lookup table and an onboarding eligibility table. System-issued licences may remain hidden from onboarding until `is_onboarding_active = 1`.
- `field_agent_transmissions.payload` and `response` are audit snapshots of Bodogwu interaction and should not be treated as source-of-truth profile data.
- Legacy `singlepage_*` source tables are import staging tables. Production can drop them after import only if the import has been verified and the raw SQL dumps are retained elsewhere.