# Database Design

## 1. Overview
Sekaya uses a relational database (MySQL or PostgreSQL) optimized for transactional integrity, referential consistency, and auditability. The schema follows Laravel conventions: plural snake_case table names, singular foreign keys (`model_id`), pivot tables in alphabetical order, and explicit indexing for performance.

## 2. Core Entity Groups
1. **Identity & Access**: `users`, `roles`, `permissions`, pivot tables (`model_has_roles`, `model_has_permissions`, `role_has_permissions`).
2. **Associations & Licensing**: `associations`, `licenses`, supporting tables for geographic scope and verification metadata.
3. **Projects & Initiatives**: `projects`, `initiatives`, `project_charters`, relational tables linking associations and users.
4. **Orders & Transactions**: `orders`, related line or detail tables (if implemented), location references (states/cities).
5. **Geographic Data**: `states`, `cities` with latitude/longitude fields enabling map features.
6. **Media & Files**: Spatie Media Library tables (`media`, polymorphic relationships to attach files to entities).
7. **Activity & Audit**: `activity_log` table capturing model changes, actor, and state transitions.
8. **Legacy Migration Support**: Temporary or staging tables only during migration (commands transform old schema into target tables).

## 3. Naming Conventions
| Artifact | Convention | Example |
|----------|------------|---------|
| Table | snake_case plural | `project_charters` |
| Primary Key | `id` (BIGINT unsigned or UUID) | `id` |
| Foreign Key | singular + `_id` | `user_id` |
| Pivot Table | Alphabetical singular forms | `project_user` |
| Timestamps | `created_at`, `updated_at` | Standard |
| Soft Deletes | `deleted_at` | Nullable timestamp |

UUIDs may be used for externally exposed entities (e.g. licenses) to reduce enumeration risks.

## 4. Relationships (Illustrative)
| Model | Relationship | Type | Notes |
|-------|-------------|------|-------|
| User | Association | BelongsTo | Each user belongs to one association (if applicable) |
| User | Roles / Permissions | BelongsToMany | Spatie standard tables |
| Association | Users | HasMany | All members/admins |
| Association | Licenses | HasMany | License history or current active license |
| Association | Projects | HasMany | Projects owned/managed |
| Project | Users | BelongsToMany | Participation & assignments |
| License | Association | BelongsTo | Ownership |
| Order | Association | BelongsTo | Order raised by association |
| Order | User | BelongsTo | Creator or approver |
| Media | MorphTo | Polymorphic | Any model with attachments |
| ActivityLog | Subject | MorphTo | Tracks changes across entities |

## 5. Key Columns & Data Types
- IDs: BIGINT unsigned or UUID (CHAR(36)) depending on security requirement.
- Monetary / Numeric: DECIMAL with appropriate precision for financial or quantity metrics.
- Geo Coordinates: `lat` DECIMAL(10,8), `lng` DECIMAL(11,8).
- Status Fields: ENUM or string constrained by application logic (prefer constant mapping rather than DB ENUM for portability).
- JSON Columns: For flexible metadata (e.g., configuration snapshots) where needed.

## 6. Indexing Strategy
| Index Type | Purpose | Example |
|------------|---------|---------|
| Primary Key | Row identification | `id` |
| Foreign Key Index | Join performance | `association_id` on `users` |
| Composite Index | High-cardinality queries | `(association_id, status)` in `projects` |
| Unique Index | Business invariant | `email` on `users`, `license_number` on `licenses` |
| Partial / Filtered (PostgreSQL) | Optional optimization | Active-only records |
| Full-Text (Optional) | Search features | Names/descriptions (MySQL InnoDB FT / PG tsvector) |

Activity log table may include composite index (`subject_type`, `subject_id`) to accelerate retrieval of entity history.

## 7. Constraints & Integrity
- Foreign key constraints enforce referential integrity (cascade or restrict based on business rules).
- Cascading deletes used selectively (e.g., delete project_user pivot entries when project removed) while preserving audit logs.

## 8. Migration Patterns
- Descriptive migration class names (e.g., `2025_01_10_120000_create_projects_table.php`).
- `up()` defines full schema with indexes; `down()` cleanly reverts.
- Column modifications specify full previous attributes to avoid silent attribute drops (Laravel 12 requirement).
- Database agnostic design (avoid engine-specific features unless gated).
- Use batch creation for pivot tables and final adjustments for performance-critical indexes.

## 9. Performance Considerations
- Eager loading used to prevent N+1 in high-traffic Filament tables.
- Pagination default for large sets; limit selectors via relationship query scopes.
- Denormalization avoided unless profiling shows clear necessity (prefer computed attributes at application layer).
- Background jobs reduce write contention during imports.

## 10. Security & Compliance
- Unique constraints prevent impersonation via duplicated email/phone.
- Optional encryption for sensitive columns using Laravel's encryption casting.
- Access control enforced at application layer (Spatie Permissions) + row-level policies if needed.
- Activity logs meet audit/trail requirements.

## 11. Data Lifecycle & Retention
| Category | Retention | Notes |
|----------|-----------|-------|
| Activity Logs | Configurable (e.g., 12–24 months) | Archival job can purge old entries |
| Media | Lifecycle rules on S3 | Infrequent access tier after X days |

## 12. Legacy Data Migration
- Validation command verifies structure before import.
- Dry-run provides mapping stats (users/associations count, skipped records).
- Batch sizing prevents long transaction locks.
- Transformation layer normalizes inconsistent fields (names, coordinates parsing).

## 13. Cross-References
Related documents for broader architectural context:
- `SYSTEM_DESIGN.md` – High-level architecture & module interactions.
- `TECH_STACK.md` – Technology selection rationale impacting persistence.
- `HOSTING_SERVER_TYPE.md` – Infrastructure considerations (RDS, Redis).
- `SERVER_KEY_FEATURES.md` – Backup, resilience, and compliance features.

## 14. Summary
The database schema balances normalization, performance, and audit requirements. Clear conventions, robust indexing, and disciplined migrations support scalability and maintainability while ensuring data integrity for critical association, licensing, and project operations.
