# SMOA Database Schema Documentation

**Version:** 1.0  
**Last Updated:** 2024-12-20  
**Status:** Draft - In Progress

---

## Database Overview

### Database Technology
- **Database:** SQLite (via Room)
- **Version:** SQLite 3.x
- **Location:** Local device storage
- **Encryption:** AES-256-GCM encryption

### Database Purpose
SMOA uses Room database for local data storage, providing:
- Offline data access
- Fast local queries
- Encrypted data storage
- Data synchronization support

---

## Schema Diagrams

### Entity Relationship Diagram
[To be added: ER diagram showing all entities and relationships]

---

## Tables

### User Table

#### Table: users
- **Purpose:** Store user information
- **Primary Key:** user_id

| Column | Type | Constraints | Description |
|--------|------|-------------|-------------|
| user_id | TEXT | PRIMARY KEY | Unique user identifier |
| username | TEXT | NOT NULL, UNIQUE | Username |
| email | TEXT | | Email address |
| role | TEXT | NOT NULL | User role |
| created_at | INTEGER | NOT NULL | Creation timestamp |
| updated_at | INTEGER | NOT NULL | Update timestamp |

### Credential Table

#### Table: credentials
- **Purpose:** Store digital credentials
- **Primary Key:** credential_id

| Column | Type | Constraints | Description |
|--------|------|-------------|-------------|
| credential_id | TEXT | PRIMARY KEY | Unique credential identifier |
| user_id | TEXT | NOT NULL, FOREIGN KEY | User who owns credential |
| type | TEXT | NOT NULL | Credential type |
| title | TEXT | NOT NULL | Credential title |
| issuer | TEXT | NOT NULL | Issuing authority |
| issue_date | INTEGER | | Issue date (Unix timestamp) |
| expiration_date | INTEGER | | Expiration date |
| status | TEXT | NOT NULL | Status (active, expired, revoked) |
| barcode_data | TEXT | | PDF417 barcode data |
| created_at | INTEGER | NOT NULL | Creation timestamp |
| updated_at | INTEGER | NOT NULL | Update timestamp |

**Foreign Keys:**
- user_id → users(user_id)

### Order Table

#### Table: orders
- **Purpose:** Store digital orders
- **Primary Key:** order_id

| Column | Type | Constraints | Description |
|--------|------|-------------|-------------|
| order_id | TEXT | PRIMARY KEY | Unique order identifier |
| order_type | TEXT | NOT NULL | Order type |
| title | TEXT | NOT NULL | Order title |
| content | TEXT | NOT NULL | Order content |
| issued_by | TEXT | NOT NULL | Issuing authority |
| issued_to | TEXT | | Recipient |
| issue_date | INTEGER | NOT NULL | Issue date |
| effective_date | INTEGER | NOT NULL | Effective date |
| expiration_date | INTEGER | | Expiration date |
| status | TEXT | NOT NULL | Order status |
| created_at | INTEGER | NOT NULL | Creation timestamp |
| updated_at | INTEGER | NOT NULL | Update timestamp |

### Evidence Table

#### Table: evidence
- **Purpose:** Store evidence items
- **Primary Key:** evidence_id

| Column | Type | Constraints | Description |
|--------|------|-------------|-------------|
| evidence_id | TEXT | PRIMARY KEY | Unique evidence identifier |
| case_number | TEXT | NOT NULL | Case number |
| description | TEXT | NOT NULL | Evidence description |
| type | TEXT | NOT NULL | Evidence type |
| collection_date | INTEGER | NOT NULL | Collection date |
| collection_location | TEXT | | Collection location |
| collected_by | TEXT | NOT NULL | Collector |
| current_custodian | TEXT | NOT NULL | Current custodian |
| storage_location | TEXT | | Storage location |
| created_at | INTEGER | NOT NULL | Creation timestamp |
| updated_at | INTEGER | NOT NULL | Update timestamp |

### Custody Transfer Table

#### Table: custody_transfers
- **Purpose:** Track evidence custody transfers
- **Primary Key:** transfer_id

| Column | Type | Constraints | Description |
|--------|------|-------------|-------------|
| transfer_id | TEXT | PRIMARY KEY | Unique transfer identifier |
| evidence_id | TEXT | NOT NULL, FOREIGN KEY | Evidence item |
| from_custodian | TEXT | NOT NULL | Transferring custodian |
| to_custodian | TEXT | NOT NULL | Receiving custodian |
| transfer_date | INTEGER | NOT NULL | Transfer date |
| reason | TEXT | | Transfer reason |
| evidence_condition | TEXT | | Evidence condition |
| signature | TEXT | | Digital signature |
| created_at | INTEGER | NOT NULL | Creation timestamp |

**Foreign Keys:**
- evidence_id → evidence(evidence_id)

### Report Table

#### Table: reports
- **Purpose:** Store generated reports
- **Primary Key:** report_id

| Column | Type | Constraints | Description |
|--------|------|-------------|-------------|
| report_id | TEXT | PRIMARY KEY | Unique report identifier |
| template | TEXT | NOT NULL | Report template |
| format | TEXT | NOT NULL | Report format (PDF, XML, JSON, CSV) |
| parameters | TEXT | | Report parameters (JSON) |
| generated_by | TEXT | NOT NULL | Generator user |
| generated_at | INTEGER | NOT NULL | Generation timestamp |
| file_path | TEXT | | Report file path |
| file_size | INTEGER | | File size in bytes |

### Audit Log Table

#### Table: audit_logs
- **Purpose:** Store audit trail records
- **Primary Key:** log_id

| Column | Type | Constraints | Description |
|--------|------|-------------|-------------|
| log_id | TEXT | PRIMARY KEY | Unique log identifier |
| event_type | TEXT | NOT NULL | Event type |
| user_id | TEXT | | User who triggered event |
| module | TEXT | | Module where event occurred |
| action | TEXT | NOT NULL | Action performed |
| resource | TEXT | | Resource affected |
| result | TEXT | NOT NULL | Result (success, failure) |
| details | TEXT | | Additional details (JSON) |
| timestamp | INTEGER | NOT NULL | Event timestamp |
| ip_address | TEXT | | IP address (if applicable) |

---

## Indexes

### Performance Indexes
- **users(username):** Index on username for login
- **credentials(user_id):** Index on user_id for user credential queries
- **credentials(status):** Index on status for status queries
- **orders(status):** Index on order status
- **orders(order_type):** Index on order type
- **evidence(case_number):** Index on case number
- **audit_logs(timestamp):** Index on timestamp for time-based queries
- **audit_logs(user_id):** Index on user_id for user audit queries

---

## Data Dictionary

### Data Elements

#### User Data Elements
- **user_id:** Unique identifier for users
- **username:** User login name
- **role:** User role (administrator, operator, viewer, auditor)

#### Credential Data Elements
- **credential_id:** Unique identifier for credentials
- **type:** Credential type (id, badge, license, permit, other)
- **status:** Credential status (active, expired, revoked)

#### Order Data Elements
- **order_id:** Unique identifier for orders
- **order_type:** Order type (authorization, assignment, search_warrant, etc.)
- **status:** Order status (draft, pending_approval, approved, issued, etc.)

#### Evidence Data Elements
- **evidence_id:** Unique identifier for evidence
- **type:** Evidence type (physical, digital, biological, chemical, firearm, document)
- **current_custodian:** Current custodian of evidence

---

## Migrations

### Migration History

#### Migration 1: Initial Schema
- **Version:** 1
- **Date:** 2024-01-01
- **Description:** Initial database schema creation

#### Migration 2: Add Audit Logging
- **Version:** 2
- **Date:** 2024-02-01
- **Description:** Add audit log table and indexes

### Migration Procedures

#### Applying Migrations
1. **Backup Database:** Backup current database
2. **Review Migration:** Review migration script
3. **Test Migration:** Test migration in staging
4. **Apply Migration:** Apply migration to production
5. **Verify Migration:** Verify migration success

#### Rollback Procedures
1. **Identify Migration:** Identify migration to rollback
2. **Backup Current:** Backup current database
3. **Restore Previous:** Restore previous database version
4. **Verify Rollback:** Verify rollback success

---

## Data Protection

### Encryption
- **At Rest:** AES-256-GCM encryption
- **Key Storage:** Hardware-backed key storage
- **Key Management:** Automatic key rotation

### Access Control
- **Database Access:** Application-only access
- **User Access:** Role-based data access
- **Audit Logging:** All access logged

---

## Backup and Recovery

### Backup Procedures
- **Automated Backups:** Daily automated backups
- **Backup Location:** Encrypted backup storage
- **Backup Retention:** 90 days

### Recovery Procedures
- **Full Recovery:** Complete database restoration
- **Partial Recovery:** Selective data restoration
- **Point-in-Time Recovery:** Recovery to specific point

---

## Performance Optimization

### Query Optimization
- **Indexes:** Strategic index placement
- **Query Tuning:** Optimized queries
- **Caching:** Query result caching

### Database Maintenance
- **Vacuum:** Regular database vacuum
- **Analyze:** Regular statistics update
- **Optimization:** Periodic optimization

---

## References

- [Architecture Documentation](../architecture/ARCHITECTURE.md)
- [Administrator Guide](../admin/SMOA-Administrator-Guide.md)
- [Backup and Recovery Procedures](../operations/SMOA-Backup-Recovery-Procedures.md)

---

**Document Owner:** Database Administrator  
**Last Updated:** 2024-12-20  
**Status:** Draft - In Progress  
**Next Review:** 2024-12-27