d-bis/smoa
4
0
Fork 0
Code Issues Pull Requests Actions Packages Projects Releases Wiki Activity
Files
main
smoa/docs/database/DATABASE_SCHEMA.md
defiQUG 97f75e144f Initial commit
2025-12-26 10:48:33 -08:00

299 lines
9.1 KiB
Markdown
Raw Permalink Blame History

# 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
Reference in New Issue View Git Blame Copy Permalink