# Database Structure Documentation

**Database Name:** `btm_db`
**Engine:** MySQL
**Generated:** 2026-02-13

---

## Table of Contents

- [Application Tables](#application-tables)
  - [accounts](#accounts)
  - [audit_logs](#audit_logs)
  - [branches](#branches)
  - [financing_installments](#financing_installments)
  - [financing_payments](#financing_payments)
  - [financing_products](#financing_products)
  - [financings](#financings)
  - [journal_entries](#journal_entries)
  - [journal_entry_lines](#journal_entry_lines)
  - [members](#members)
  - [savings_accounts](#savings_accounts)
  - [savings_products](#savings_products)
  - [savings_transactions](#savings_transactions)
  - [users](#users)
- [Regional Tables](#regional-tables)
  - [reg_provinces](#reg_provinces)
  - [reg_regencies](#reg_regencies)
  - [reg_districts](#reg_districts)
  - [reg_villages](#reg_villages)
- [Authorization Tables](#authorization-tables)
  - [permissions](#permissions)
  - [roles](#roles)
  - [model_has_permissions](#model_has_permissions)
  - [model_has_roles](#model_has_roles)
  - [role_has_permissions](#role_has_permissions)
- [Laravel System Tables](#laravel-system-tables)

---

## Application Tables

### accounts

Chart of accounts for accounting system.

| Field | Type | Null | Key | Default | Extra |
|-------|------|------|-----|---------|-------|
| id | bigint(20) unsigned | NO | PRI | | auto_increment |
| code | varchar(20) | NO | UNI | | |
| name | varchar(200) | NO | | | |
| parent_id | bigint(20) unsigned | YES | MUL | NULL | |
| classification | enum('asset','liability','equity','revenue','expense') | NO | MUL | | |
| normal_balance | enum('debit','credit') | NO | | debit | |
| level | int(11) | NO | | 1 | |
| description | text | YES | | NULL | |
| is_header | tinyint(1) | NO | MUL | 0 | |
| is_active | tinyint(1) | NO | MUL | 1 | |
| balance | decimal(15,2) | NO | | 0.00 | |
| currency | varchar(3) | NO | | IDR | |
| legacy_code | varchar(20) | YES | | NULL | |
| created_at | timestamp | YES | | NULL | |
| updated_at | timestamp | YES | | NULL | |
| deleted_at | timestamp | YES | | NULL | |

**Indexes:**
- PRIMARY (`id`)
- UNIQUE (`code`)
- INDEX `accounts_classification_index` (`classification`)
- INDEX (`parent_id`)
- INDEX (`is_header`)
- INDEX (`is_active`)

---

### audit_logs

Audit trail for tracking user actions and data changes.

| Field | Type | Null | Key | Default | Extra |
|-------|------|------|-----|---------|-------|
| id | bigint(20) unsigned | NO | PRI | | auto_increment |
| user_id | bigint(20) unsigned | YES | MUL | NULL | |
| user_name | varchar(100) | YES | | NULL | |
| ip_address | varchar(45) | YES | | NULL | |
| user_agent | varchar(500) | YES | | NULL | |
| action | varchar(50) | NO | MUL | | |
| entity_type | varchar(100) | NO | MUL | | |
| entity_id | bigint(20) unsigned | NO | | | |
| entity_identifier | varchar(100) | YES | | NULL | |
| old_values | json | YES | | NULL | |
| new_values | json | YES | | NULL | |
| changed_fields | json | YES | | NULL | |
| description | text | YES | | NULL | |
| url | varchar(500) | YES | | NULL | |
| method | varchar(10) | YES | | NULL | |
| created_at | timestamp | YES | MUL | NULL | |
| updated_at | timestamp | YES | | NULL | |

**Indexes:**
- PRIMARY (`id`)
- INDEX (`user_id`)
- INDEX (`action`)
- INDEX (`entity_type`)
- INDEX (`created_at`)

---

### branches

Branch/office locations for the cooperative.

| Field | Type | Null | Key | Default | Extra |
|-------|------|------|-----|---------|-------|
| id | bigint(20) unsigned | NO | PRI | | auto_increment |
| code | varchar(10) | NO | UNI | | |
| name | varchar(100) | NO | | | |
| address | varchar(255) | YES | | NULL | |
| city | varchar(100) | YES | | NULL | |
| province | varchar(100) | YES | | NULL | |
| phone | varchar(20) | YES | | NULL | |
| email | varchar(255) | YES | | NULL | |
| is_headquarters | tinyint(1) | NO | | 0 | |
| is_active | tinyint(1) | NO | MUL | 1 | |
| created_at | timestamp | YES | | NULL | |
| updated_at | timestamp | YES | | NULL | |
| deleted_at | timestamp | YES | | NULL | |

**Indexes:**
- PRIMARY (`id`)
- UNIQUE (`code`)
- INDEX (`is_active`)

---

### financing_installments

Installment schedule for financing (loan) accounts.

| Field | Type | Null | Key | Default | Extra |
|-------|------|------|-----|---------|-------|
| id | bigint(20) unsigned | NO | PRI | | auto_increment |
| financing_id | bigint(20) unsigned | NO | MUL | | |
| sequence_number | int(11) | NO | | | |
| due_date | date | NO | MUL | | |
| principal_amount | decimal(15,2) | NO | | | |
| margin_amount | decimal(15,2) | NO | | | |
| total_amount | decimal(15,2) | NO | | | |
| status | enum('pending','partial','paid','overdue') | NO | MUL | pending | |
| paid_date | date | YES | | NULL | |
| paid_principal | decimal(15,2) | NO | | 0.00 | |
| paid_margin | decimal(15,2) | NO | | 0.00 | |
| paid_total | decimal(15,2) | NO | | 0.00 | |
| days_overdue | int(11) | NO | | 0 | |
| late_penalty | decimal(15,2) | NO | | 0.00 | |
| created_at | timestamp | YES | | NULL | |
| updated_at | timestamp | YES | | NULL | |
| deleted_at | timestamp | YES | | NULL | |

**Indexes:**
- PRIMARY (`id`)
- INDEX (`financing_id`)
- INDEX (`due_date`)
- INDEX (`status`)

---

### financing_payments

Payment records for financing installments.

| Field | Type | Null | Key | Default | Extra |
|-------|------|------|-----|---------|-------|
| id | bigint(20) unsigned | NO | PRI | | auto_increment |
| payment_number | varchar(30) | NO | UNI | | |
| financing_id | bigint(20) unsigned | NO | MUL | | |
| financing_installment_id | bigint(20) unsigned | YES | MUL | NULL | |
| branch_id | bigint(20) unsigned | NO | MUL | | |
| collector_id | bigint(20) unsigned | YES | MUL | NULL | |
| journal_entry_id | bigint(20) unsigned | YES | MUL | NULL | |
| principal_amount | decimal(15,2) | NO | | 0.00 | |
| margin_amount | decimal(15,2) | NO | | 0.00 | |
| penalty_amount | decimal(15,2) | NO | | 0.00 | |
| total_amount | decimal(15,2) | NO | | | |
| payment_date | date | NO | MUL | | |
| payment_method | enum('cash','transfer','savings_debit') | NO | | cash | |
| description | text | YES | | NULL | |
| notes | text | YES | | NULL | |
| is_posted | tinyint(1) | NO | MUL | 0 | |
| posted_at | timestamp | YES | | NULL | |
| created_at | timestamp | YES | | NULL | |
| updated_at | timestamp | YES | | NULL | |
| deleted_at | timestamp | YES | | NULL | |
| created_by | bigint(20) unsigned | YES | MUL | NULL | |
| updated_by | bigint(20) unsigned | YES | MUL | NULL | |

**Indexes:**
- PRIMARY (`id`)
- UNIQUE (`payment_number`)
- INDEX (`financing_id`)
- INDEX (`financing_installment_id`)
- INDEX (`branch_id`)
- INDEX (`collector_id`)
- INDEX (`journal_entry_id`)
- INDEX (`payment_date`)
- INDEX (`is_posted`)
- INDEX (`created_by`)
- INDEX (`updated_by`)

---

### financing_products

Product definitions for financing (loan) offerings.

| Field | Type | Null | Key | Default | Extra |
|-------|------|------|-----|---------|-------|
| id | bigint(20) unsigned | NO | PRI | | auto_increment |
| code | varchar(10) | NO | UNI | | |
| name | varchar(100) | NO | | | |
| description | text | YES | | NULL | |
| akad_type | enum('murabahah','salam','istishna','ijarah','qardh') | NO | MUL | murabahah | |
| margin_rate | decimal(5,2) | NO | | 10.00 | |
| installment_count | int(11) | NO | | 100 | |
| installment_frequency | enum('daily','weekly','monthly') | NO | | daily | |
| administration_fee | decimal(15,2) | NO | | 0.00 | |
| stamp_fee | decimal(15,2) | NO | | 0.00 | |
| provision_fee | decimal(15,2) | NO | | 0.00 | |
| insurance_fee | decimal(15,2) | NO | | 0.00 | |
| notary_fee | decimal(15,2) | NO | | 0.00 | |
| minimum_amount | decimal(15,2) | NO | | 0.00 | |
| maximum_amount | decimal(15,2) | NO | | 0.00 | |
| requires_collateral | tinyint(1) | NO | | 0 | |
| requires_guarantor | tinyint(1) | NO | | 0 | |
| is_active | tinyint(1) | NO | MUL | 1 | |
| created_at | timestamp | YES | | NULL | |
| updated_at | timestamp | YES | | NULL | |
| deleted_at | timestamp | YES | | NULL | |

**Indexes:**
- PRIMARY (`id`)
- UNIQUE (`code`)
- INDEX (`akad_type`)
- INDEX (`is_active`)

**Akad Types:**
- `murabahah` - Sale with markup (cost-plus financing)
- `salam` - Advance purchase financing
- `istishna` - Manufacturing/construction financing
- `ijarah` - Lease financing
- `qardh` - Benevolent loan

---

### financings

Main financing (loan) accounts for members.

| Field | Type | Null | Key | Default | Extra |
|-------|------|------|-----|---------|-------|
| id | bigint(20) unsigned | NO | PRI | | auto_increment |
| account_number | varchar(20) | NO | UNI | | |
| legacy_account_number | varchar(20) | YES | UNI | NULL | |
| member_id | bigint(20) unsigned | NO | MUL | | |
| branch_id | bigint(20) unsigned | NO | MUL | | |
| financing_product_id | bigint(20) unsigned | NO | MUL | | |
| collector_id | bigint(20) unsigned | YES | MUL | NULL | |
| principal_amount | decimal(15,2) | NO | | | |
| margin_amount | decimal(15,2) | NO | | | |
| total_repayment | decimal(15,2) | NO | | | |
| installment_count | int(11) | NO | | | |
| installment_amount | decimal(15,2) | NO | | | |
| installment_frequency | enum('daily','weekly','monthly') | NO | | daily | |
| interest_rate | decimal(5,2) | NO | | 0.00 | |
| application_date | date | NO | | | |
| rejected_date | date | YES | | NULL | |
| approval_date | date | YES | | NULL | |
| disbursement_date | date | YES | MUL | NULL | |
| due_date | date | YES | | NULL | |
| paid_off_date | date | YES | | NULL | |
| administration_fee | decimal(15,2) | NO | | 0.00 | |
| stamp_fee | decimal(15,2) | NO | | 0.00 | |
| provision_fee | decimal(15,2) | NO | | 0.00 | |
| insurance_fee | decimal(15,2) | NO | | 0.00 | |
| notary_fee | decimal(15,2) | NO | | 0.00 | |
| status | enum('proposed','approved','rejected','disbursed','active','paid_off','written_off') | NO | MUL | proposed | |
| collectibility_score | int(11) | NO | MUL | 1 | |
| status_notes | text | YES | | NULL | |
| principal_balance | decimal(15,2) | NO | | 0.00 | |
| margin_balance | decimal(15,2) | NO | | 0.00 | |
| total_balance | decimal(15,2) | NO | | 0.00 | |
| collateral_type | varchar(255) | YES | | NULL | |
| collateral_value | decimal(15,2) | YES | | NULL | |
| collateral_description | text | YES | | NULL | |
| guarantor_name | varchar(255) | YES | | NULL | |
| guarantor_address | varchar(255) | YES | | NULL | |
| guarantor_phone | varchar(255) | YES | | NULL | |
| guarantor_occupation | varchar(255) | YES | | NULL | |
| financing_purpose | varchar(255) | YES | | NULL | |
| economic_sector | varchar(255) | YES | | NULL | |
| notes | text | YES | | NULL | |
| migration_principal_balance | decimal(15,2) | YES | | NULL | |
| migration_margin_balance | decimal(15,2) | YES | | NULL | |
| created_at | timestamp | YES | | NULL | |
| updated_at | timestamp | YES | | NULL | |
| deleted_at | timestamp | YES | | NULL | |
| created_by | bigint(20) unsigned | YES | MUL | NULL | |
| updated_by | bigint(20) unsigned | YES | MUL | NULL | |
| approved_by | bigint(20) unsigned | YES | MUL | NULL | |
| rejected_by | bigint(20) unsigned | YES | MUL | NULL | |

**Indexes:**
- PRIMARY (`id`)
- UNIQUE (`account_number`)
- UNIQUE (`legacy_account_number`)
- INDEX (`member_id`)
- INDEX (`branch_id`)
- INDEX (`financing_product_id`)
- INDEX (`collector_id`)
- INDEX (`disbursement_date`)
- INDEX (`status`)
- INDEX (`collectibility_score`)
- INDEX (`created_by`)
- INDEX (`updated_by`)
- INDEX (`approved_by`)
- INDEX (`rejected_by`)

**Status Values:**
- `proposed` - Application submitted
- `approved` - Approved but not yet disbursed
- `rejected` - Application rejected
- `disbursed` - Funds disbursed, awaiting first payment
- `active` - Active repayment
- `paid_off` - Fully repaid
- `written_off` - Written off as bad debt

---

### journal_entries

Journal entry header for accounting transactions.

| Field | Type | Null | Key | Default | Extra |
|-------|------|------|-----|---------|-------|
| id | bigint(20) unsigned | NO | PRI | | auto_increment |
| voucher_number | varchar(30) | NO | UNI | | |
| transaction_date | date | NO | MUL | | |
| reference_number | varchar(50) | YES | | NULL | |
| description | text | YES | | NULL | |
| source_type | varchar(255) | YES | MUL | NULL | |
| source_id | bigint(20) unsigned | YES | | NULL | |
| status | enum('draft','posted','void') | NO | MUL | draft | |
| posted_at | timestamp | YES | | NULL | |
| voided_at | timestamp | YES | | NULL | |
| created_at | timestamp | YES | | NULL | |
| updated_at | timestamp | YES | | NULL | |
| deleted_at | timestamp | YES | | NULL | |
| created_by | bigint(20) unsigned | YES | MUL | NULL | |
| posted_by | bigint(20) unsigned | YES | MUL | NULL | |
| voided_by | bigint(20) unsigned | YES | MUL | NULL | |

**Indexes:**
- PRIMARY (`id`)
- UNIQUE (`voucher_number`)
- INDEX (`transaction_date`)
- INDEX (`source_type`)
- INDEX (`status`)
- INDEX (`created_by`)
- INDEX (`posted_by`)
- INDEX (`voided_by`)

---

### journal_entry_lines

Journal entry line items (debit/credit).

| Field | Type | Null | Key | Default | Extra |
|-------|------|------|-----|---------|-------|
| id | bigint(20) unsigned | NO | PRI | | auto_increment |
| journal_entry_id | bigint(20) unsigned | NO | MUL | | |
| account_id | bigint(20) unsigned | NO | MUL | | |
| description | text | YES | | NULL | |
| type | enum('debit','credit') | NO | MUL | | |
| amount | decimal(15,2) | NO | | | |
| branch_id | bigint(20) unsigned | YES | MUL | NULL | |
| created_at | timestamp | YES | | NULL | |
| updated_at | timestamp | YES | | NULL | |

**Indexes:**
- PRIMARY (`id`)
- INDEX (`journal_entry_id`)
- INDEX (`account_id`)
- INDEX (`type`)
- INDEX (`branch_id`)

---

### members

Member (customer) records.

| Field | Type | Null | Key | Default | Extra |
|-------|------|------|-----|---------|-------|
| id | bigint(20) unsigned | NO | PRI | | auto_increment |
| legacy_id | varchar(20) | YES | UNI | NULL | |
| branch_id | bigint(20) unsigned | YES | MUL | NULL | |
| name | varchar(200) | NO | MUL | | |
| id_type | varchar(20) | YES | | NULL | |
| id_number | varchar(50) | YES | UNI | NULL | |
| birth_date | date | YES | | NULL | |
| birth_place | varchar(255) | YES | | NULL | |
| gender | enum('male','female') | YES | | NULL | |
| religion | varchar(255) | YES | | NULL | |
| marital_status | enum('single','married','divorced','widowed') | YES | | NULL | |
| mother_maiden_name | varchar(100) | YES | | NULL | |
| alias_name | varchar(255) | YES | | NULL | |
| phone | varchar(20) | YES | | NULL | |
| mobile | varchar(20) | YES | | NULL | |
| email | varchar(255) | YES | | NULL | |
| address | varchar(255) | YES | | NULL | |
| address_id_card | varchar(255) | YES | | NULL | |
| village | varchar(255) | YES | | NULL | |
| district | varchar(255) | YES | | NULL | |
| city | varchar(255) | YES | | NULL | |
| province | varchar(255) | YES | | NULL | |
| postal_code | varchar(10) | YES | | NULL | |
| occupation | varchar(255) | YES | | NULL | |
| employer_name | varchar(255) | YES | | NULL | |
| employer_address | varchar(255) | YES | | NULL | |
| business_field | varchar(255) | YES | | NULL | |
| monthly_income | decimal(15,2) | NO | | 0.00 | |
| income_source | varchar(255) | YES | | NULL | |
| spouse_name | varchar(255) | YES | | NULL | |
| spouse_birth_date | date | YES | | NULL | |
| wedding_date | date | YES | | NULL | |
| spouse_occupation | varchar(255) | YES | | NULL | |
| heir_name | varchar(255) | YES | | NULL | |
| heir_address | varchar(255) | YES | | NULL | |
| heir_phone | varchar(255) | YES | | NULL | |
| heir_birth_date | date | YES | | NULL | |
| heir_gender | enum('male','female') | YES | | NULL | |
| heir_relationship | varchar(255) | YES | | NULL | |
| npwp | varchar(30) | YES | | NULL | |
| is_verified | tinyint(1) | NO | MUL | 0 | |
| registration_date | date | YES | MUL | NULL | |
| notes | text | YES | | NULL | |
| created_at | timestamp | YES | | NULL | |
| updated_at | timestamp | YES | | NULL | |
| deleted_at | timestamp | YES | | NULL | |
| created_by | bigint(20) unsigned | YES | MUL | NULL | |
| updated_by | bigint(20) unsigned | YES | MUL | NULL | |

**Indexes:**
- PRIMARY (`id`)
- UNIQUE (`legacy_id`)
- UNIQUE (`id_number`)
- INDEX (`branch_id`)
- INDEX (`name`)
- INDEX (`is_verified`)
- INDEX (`registration_date`)
- INDEX (`created_by`)
- INDEX (`updated_by`)

---

### savings_accounts

Savings account records for members.

| Field | Type | Null | Key | Default | Extra |
|-------|------|------|-----|---------|-------|
| id | bigint(20) unsigned | NO | PRI | | auto_increment |
| account_number | varchar(20) | NO | UNI | | |
| legacy_account_number | varchar(20) | YES | UNI | NULL | |
| member_id | bigint(20) unsigned | NO | MUL | | |
| branch_id | bigint(20) unsigned | NO | MUL | | |
| savings_product_id | bigint(20) unsigned | NO | MUL | | |
| account_name | varchar(200) | NO | | | |
| status | enum('active','dormant','closed','frozen') | NO | MUL | active | |
| balance | decimal(15,2) | NO | | 0.00 | |
| hold_amount | decimal(15,2) | NO | | 0.00 | |
| opened_date | date | NO | MUL | | |
| closed_date | date | YES | | NULL | |
| notes | text | YES | | NULL | |
| created_at | timestamp | YES | | NULL | |
| updated_at | timestamp | YES | | NULL | |
| deleted_at | timestamp | YES | | NULL | |
| created_by | bigint(20) unsigned | YES | MUL | NULL | |
| updated_by | bigint(20) unsigned | YES | MUL | NULL | |

**Indexes:**
- PRIMARY (`id`)
- UNIQUE (`account_number`)
- UNIQUE (`legacy_account_number`)
- INDEX (`member_id`)
- INDEX (`branch_id`)
- INDEX (`savings_product_id`)
- INDEX (`status`)
- INDEX (`opened_date`)
- INDEX (`created_by`)
- INDEX (`updated_by`)

**Status Values:**
- `active` - Normal operation
- `dormant` - Inactive for extended period
- `closed` - Account closed
- `frozen` - Frozen due to compliance/legal reasons

---

### savings_products

Product definitions for savings accounts.

| Field | Type | Null | Key | Default | Extra |
|-------|------|------|-----|---------|-------|
| id | bigint(20) unsigned | NO | PRI | | auto_increment |
| code | varchar(10) | NO | UNI | | |
| name | varchar(100) | NO | | | |
| description | text | YES | | NULL | |
| type | enum('wadiah') | NO | MUL | wadiah | |
| minimum_initial_deposit | decimal(15,2) | NO | | 0.00 | |
| minimum_balance | decimal(15,2) | NO | | 0.00 | |
| requires_initial_deposit | tinyint(1) | NO | | 1 | |
| administrative_fee | decimal(15,2) | NO | | 0.00 | |
| has_administrative_fee | tinyint(1) | NO | | 0 | |
| is_active | tinyint(1) | NO | MUL | 1 | |
| created_at | timestamp | YES | | NULL | |
| updated_at | timestamp | YES | | NULL | |
| deleted_at | timestamp | YES | | NULL | |

**Indexes:**
- PRIMARY (`id`)
- UNIQUE (`code`)
- INDEX (`type`)
- INDEX (`is_active`)

---

### savings_transactions

Transaction records for savings accounts.

| Field | Type | Null | Key | Default | Extra |
|-------|------|------|-----|---------|-------|
| id | bigint(20) unsigned | NO | PRI | | auto_increment |
| transaction_number | varchar(30) | NO | UNI | | |
| savings_account_id | bigint(20) unsigned | NO | MUL | | |
| branch_id | bigint(20) unsigned | NO | MUL | | |
| journal_entry_id | bigint(20) unsigned | YES | MUL | NULL | |
| type | enum('deposit','withdrawal','transfer_in','transfer_out','correction') | NO | MUL | | |
| amount | decimal(15,2) | NO | | | |
| balance_before | decimal(15,2) | NO | | | |
| balance_after | decimal(15,2) | NO | | | |
| transaction_date | date | NO | MUL | | |
| description | text | YES | | NULL | |
| notes | text | YES | | NULL | |
| is_posted | tinyint(1) | NO | MUL | 0 | |
| posted_at | timestamp | YES | | NULL | |
| created_at | timestamp | YES | | NULL | |
| updated_at | timestamp | YES | | NULL | |
| deleted_at | timestamp | YES | | NULL | |
| created_by | bigint(20) unsigned | YES | MUL | NULL | |
| updated_by | bigint(20) unsigned | YES | MUL | NULL | |

**Indexes:**
- PRIMARY (`id`)
- UNIQUE (`transaction_number`)
- INDEX (`savings_account_id`)
- INDEX (`branch_id`)
- INDEX (`journal_entry_id`)
- INDEX (`type`)
- INDEX (`transaction_date`)
- INDEX (`is_posted`)
- INDEX (`created_by`)
- INDEX (`updated_by`)

---

### users

System users (staff/admin) with authentication.

| Field | Type | Null | Key | Default | Extra |
|-------|------|------|-----|---------|-------|
| id | bigint(20) unsigned | NO | PRI | | auto_increment |
| name | varchar(255) | NO | | | |
| email | varchar(255) | NO | UNI | | |
| phone | varchar(20) | YES | | NULL | |
| email_verified_at | timestamp | YES | | NULL | |
| password | varchar(255) | NO | | | |
| two_factor_secret | text | YES | | NULL | |
| two_factor_recovery_codes | text | YES | | NULL | |
| two_factor_confirmed_at | timestamp | YES | | NULL | |
| remember_token | varchar(100) | YES | | NULL | |
| created_at | timestamp | YES | | NULL | |
| updated_at | timestamp | YES | | NULL | |
| branch_id | bigint(20) unsigned | YES | MUL | NULL | |

**Indexes:**
- PRIMARY (`id`)
- UNIQUE (`email`)
- INDEX (`branch_id`)

---

## Regional Tables

Indonesian administrative divisions data (Wilayah Indonesia).

### reg_provinces

| Field | Type | Null | Key | Default | Extra |
|-------|------|------|-----|---------|-------|
| id | char(2) | NO | PRI | | |
| name | varchar(255) | NO | | | |

**Indexes:**
- PRIMARY (`id`)

### reg_regencies

| Field | Type | Null | Key | Default | Extra |
|-------|------|------|-----|---------|-------|
| id | char(4) | NO | PRI | | |
| province_id | char(2) | NO | MUL | | |
| name | varchar(255) | NO | | | |

**Indexes:**
- PRIMARY (`id`)
- INDEX (`province_id`)

### reg_districts

| Field | Type | Null | Key | Default | Extra |
|-------|------|------|-----|---------|-------|
| id | char(6) | NO | PRI | | |
| regency_id | char(4) | NO | MUL | | |
| name | varchar(255) | NO | | | |

**Indexes:**
- PRIMARY (`id`)
- INDEX (`regency_id`)

### reg_villages

| Field | Type | Null | Key | Default | Extra |
|-------|------|------|-----|---------|-------|
| id | char(10) | NO | PRI | | |
| district_id | char(6) | NO | MUL | | |
| name | varchar(255) | NO | | | |

**Indexes:**
- PRIMARY (`id`)
- INDEX (`district_id`)

---

## Authorization Tables

Spatie Laravel Permission package tables.

### permissions

| Field | Type | Null | Key | Default | Extra |
|-------|------|------|-----|---------|-------|
| id | bigint(20) unsigned | NO | PRI | | auto_increment |
| name | varchar(255) | NO | MUL | | |
| guard_name | varchar(255) | NO | | | |
| created_at | timestamp | YES | | NULL | |
| updated_at | timestamp | YES | | NULL | |

**Indexes:**
- PRIMARY (`id`)
- INDEX (`name`)

### roles

| Field | Type | Null | Key | Default | Extra |
|-------|------|------|-----|---------|-------|
| id | bigint(20) unsigned | NO | PRI | | auto_increment |
| name | varchar(255) | NO | MUL | | |
| guard_name | varchar(255) | NO | | | |
| created_at | timestamp | YES | | NULL | |
| updated_at | timestamp | YES | | NULL | |

**Indexes:**
- PRIMARY (`id`)
- INDEX (`name`)

### model_has_permissions

Pivot table for permissions assigned to models.

| Field | Type | Null | Key | Default | Extra |
|-------|------|------|-----|---------|-------|
| permission_id | bigint(20) unsigned | NO | PRI | | |
| model_type | varchar(255) | NO | PRI | | |
| model_id | bigint(20) unsigned | NO | PRI | | |

**Indexes:**
- PRIMARY (`permission_id`, `model_type`, `model_id`)

### model_has_roles

Pivot table for roles assigned to models.

| Field | Type | Null | Key | Default | Extra |
|-------|------|------|-----|---------|-------|
| role_id | bigint(20) unsigned | NO | PRI | | |
| model_type | varchar(255) | NO | PRI | | |
| model_id | bigint(20) unsigned | NO | PRI | | |

**Indexes:**
- PRIMARY (`role_id`, `model_type`, `model_id`)

### role_has_permissions

Pivot table for permissions assigned to roles.

| Field | Type | Null | Key | Default | Extra |
|-------|------|------|-----|---------|-------|
| role_id | bigint(20) unsigned | NO | PRI | | |
| permission_id | bigint(20) unsigned | NO | PRI | | |

**Indexes:**
- PRIMARY (`role_id`, `permission_id`)

---

## Laravel System Tables

Standard Laravel framework tables.

### cache
| Field | Type | Null | Key | Default | Extra |
|-------|------|------|-----|---------|-------|
| key | varchar(255) | NO | PRI | | |
| value | mediumtext | NO | | | |
| expiration | int(11) | YES | | NULL | |

### cache_locks
| Field | Type | Null | Key | Default | Extra |
|-------|------|------|-----|---------|-------|
| key | varchar(255) | NO | PRI | | |
| owner | varchar(255) | NO | PRI | | |
| expiration | int(11) | YES | | NULL | |

### failed_jobs
| Field | Type | Null | Key | Default | Extra |
|-------|------|------|-----|---------|-------|
| id | bigint(20) unsigned | NO | PRI | | auto_increment |
| uuid | varchar(255) | NO | UNI | | |
| connection | text | NO | | | |
| queue | text | NO | | | |
| payload | longtext | NO | | | |
| exception | longtext | NO | | | |
| failed_at | timestamp | NO | | | |

### jobs
| Field | Type | Null | Key | Default | Extra |
|-------|------|------|-----|---------|-------|
| id | bigint(20) unsigned | NO | PRI | | auto_increment |
| queue | varchar(255) | NO | | | |
| payload | longtext | NO | | | |
| attempts | tinyint(3) unsigned | NO | | 0 | |
| reserved_at | int(10) unsigned | YES | | NULL | |
| available_at | int(10) unsigned | NO | | | |
| created_at | int(10) unsigned | NO | | | |

### job_batches
| Field | Type | Null | Key | Default | Extra |
|-------|------|------|-----|---------|-------|
| id | varchar(255) | NO | PRI | | |
| name | varchar(255) | NO | | | |
| total_jobs | int(11) | NO | | 0 | |
| pending_jobs | int(11) | NO | | 0 | |
| failed_jobs | int(11) | NO | | 0 | |
| failed_job_ids | longtext | YES | | NULL | |
| options | mediumtext | YES | | NULL | |
| cancelled_at | int(11) | YES | | NULL | |
| created_at | int(11) | NO | | | |
| finished_at | int(11) | YES | | NULL | |

### migrations
| Field | Type | Null | Key | Default | Extra |
|-------|------|------|-----|---------|-------|
| id | int(10) unsigned | NO | PRI | | auto_increment |
| migration | varchar(255) | NO | | | |
| batch | int(11) | NO | | | |

### password_reset_tokens
| Field | Type | Null | Key | Default | Extra |
|-------|------|------|-----|---------|-------|
| email | varchar(255) | NO | PRI | | |
| token | varchar(255) | NO | | | |
| created_at | timestamp | NO | | | |

### sessions
| Field | Type | Null | Key | Default | Extra |
|-------|------|------|-----|---------|-------|
| id | varchar(255) | NO | PRI | | |
| user_id | bigint(20) unsigned | YES | MUL | NULL | |
| ip_address | varchar(45) | YES | | NULL | |
| user_agent | text | YES | | NULL | |
| payload | longtext | NO | | | |
| last_activity | int(11) | NO | | | |

---

## Data Type Key

| Type | Description |
|------|-------------|
| bigint(20) unsigned | Large integer (0 - 18446744073709551615) |
| int(11) | Standard integer (-2147483648 - 2147483647) |
| tinyint(1) | Boolean/Flag (0 - 255, typically 0/1) |
| char(N) | Fixed-length string |
| varchar(N) | Variable-length string |
| text | Variable-length text (up to 65,535 chars) |
| mediumtext | Variable-length text (up to 16,777,215 chars) |
| longtext | Variable-length text (up to 4GB) |
| decimal(M,D) | Fixed-point number (M total digits, D decimal places) |
| date | Date (YYYY-MM-DD) |
| timestamp | Timestamp (YYYY-MM-DD HH:MM:SS) |
| json | JSON data |
| enum(...) | Enumerated value from predefined set |

---

## Index Key

| Key | Description |
|-----|-------------|
| PRI | Primary Key |
| UNI | Unique Index |
| MUL | Multiple occurrences allowed (non-unique index) |

---

## Common Field Patterns

**Audit/Tracking Fields:**
- `created_at`, `updated_at` - Timestamps for record lifecycle
- `deleted_at` - Soft delete timestamp (if present)
- `created_by`, `updated_by` - User IDs who created/modified
- `posted_by`, `approved_by`, `rejected_by`, `voided_by` - Action tracking

**Legacy/Migration Fields:**
- `legacy_id`, `legacy_code`, `legacy_account_number` - References to legacy system
- `migration_*_balance` - Migrated balances from old system

**Status Patterns:**
- `is_active` - Active/inactive flag
- `is_verified` - Verification status
- `is_header` - Account header indicator
- `status` - Various enum status fields per table