# Feature Specification: Database Foundation - PostgreSQL Migration

**Feature Branch**: `002-postgres-foundation`

**Created**: 2026-06-04

**Status**: Draft

**Input**: User description: "Database Foundation - PostgreSQL Migration and Schema Implementation"

## User Scenarios & Testing *(mandatory)*

### User Story 1 - PostgreSQL Infrastructure Migration (Priority: P1)

As a DevOps Engineer, I need the Tamkeen application's database infrastructure to run on PostgreSQL 15+ instead of MySQL.

**Why this priority**: This is a non-negotiable architectural decision for financial data integrity and JSONB capabilities required by the system.

**Independent Test**: Can be tested by starting the Docker containers and verifying the PostgreSQL container is running and accessible.

**Acceptance Scenarios**:

1. **Given** the Docker environment is running, **When** I inspect the container status, **Then** the MySQL container should be replaced by a PostgreSQL 15 container.
2. **Given** the Laravel application is configured, **When** the application attempts to connect to the database, **Then** it should successfully connect to the PostgreSQL container using credentials from `.env`.
3. **Given** the PostgreSQL container is running, **When** I run `php artisan migrate:status`, **Then** I should see pending migrations without connection errors.

---

### User Story 2 - Database Schema Implementation (Priority: P1)

As a System Architect, I need the complete database schema to be implemented exactly as defined in the DB_Schema.md reference document.

**Why this priority**: The schema defines the complete data model including constraints, indexes, and relationships that ensure financial data integrity.

**Independent Test**: Can be tested by running `php artisan migrate` and verifying all tables are created with correct column types and constraints.

**Acceptance Scenarios**:

1. **Given** PostgreSQL is running, **When** I execute `php artisan migrate`, **Then** the `users` table should be created with BIGSERIAL PRIMARY KEY, VARCHAR columns, and TIMESTAMPTZ timestamps.
2. **Given** PostgreSQL is running, **When** I execute `php artisan migrate`, **Then** the `admins` table should be created with proper structure for Spatie authentication integration.
3. **Given** PostgreSQL is running, **When** I execute `php artisan migrate`, **Then** the `contracts` table should include DECIMAL(10,2) for monetary fields, CHECK constraints for initial_payment and months validation, and proper Foreign Keys with RESTRICT on delete.
4. **Given** PostgreSQL is running, **When** I execute `php artisan migrate`, **Then** the `installments` table should be created as a State Table with CASCADE on contract delete.
5. **Given** PostgreSQL is running, **When** I execute `php artisan migrate`, **Then** the `payments` table should use DECIMAL(10,2) for amounts with CASCADE on contract delete.
6. **Given** PostgreSQL is running, **When** I execute `php artisan migrate`, **Then** the `payment_audit_logs` table should include JSONB columns for old_values and new_values, with RESTRICT on contract delete and SET NULL on payment delete.
7. **Given** PostgreSQL is running, **When** I execute `php artisan migrate`, **Then** the `agent_shares_logs` table should be append-only with RESTRICT on user delete.
8. **Given** PostgreSQL is running, **When** I execute `php artisan migrate`, **Then** the `notification_templates` table should be created as a standalone table.
9. **Given** migrations have run, **When** I inspect the database indexes, **Then** all indexes defined in the indexing strategy should exist (users phone/name, contracts customer_id/agent_id/is_signed, installments contract_id/due_date/status, payments contract_id/payment_date, audit_logs contract_id, agent_shares_logs user_id).

---

### User Story 3 - State Cleanup (Priority: P1)

As a System Administrator, I need to ensure a clean slate by removing any MySQL-specific artifacts before PostgreSQL migration.

**Why this priority**: Residual MySQL tables could cause conflicts or confusion during development.

**Independent Test**: Can be tested by verifying no MySQL-specific tables exist and the database is ready for fresh PostgreSQL migrations.

**Acceptance Scenarios**:

1. **Given** the application previously used MySQL, **When** I run database cleanup commands, **Then** any existing MySQL-specific tables or artifacts should be removed.
2. **Given** the cleanup is complete, **When** I run fresh migrations on PostgreSQL, **Then** all tables should be created without naming conflicts.

---

### User Story 4 - Authentication Regression Verification (Priority: P1)

As a Backend Developer, I need to verify that the existing SP-01 Sanctum authentication system works correctly with the new PostgreSQL database.

**Why this priority**: The authentication system must continue to function after the database migration to ensure no disruption to existing functionality.

**Independent Test**: Can be tested by making HTTP requests to the login endpoint and verifying authentication succeeds.

**Acceptance Scenarios**:

1. **Given** PostgreSQL is running with proper schema, **When** I send a POST request to `/api/v1/auth/login` with valid admin credentials, **Then** the response should indicate successful authentication with a Sanctum token.
2. **Given** an authenticated admin session, **When** I make a request to a protected endpoint with the Sanctum token, **Then** the request should be authorized successfully.
3. **Given** the authentication system is working, **When** I send a POST request to `/api/v1/auth/logout`, **Then** the session should be terminated successfully.

---

### Edge Cases

- What happens when the PostgreSQL container fails to start due to port conflicts?
- How does the system handle migration failures mid-way through the schema creation?
- What occurs when attempting to delete a user record that has associated contracts (should be prevented by RESTRICT)?
- How does the system behave if the `.env` file is misconfigured with incorrect PostgreSQL credentials?

## Requirements *(mandatory)*

### Functional Requirements

- **FR-001**: System MUST replace MySQL Docker service with PostgreSQL 15 service in docker-compose.yml
- **FR-002**: System MUST update Laravel `.env` file to use PostgreSQL driver with correct host, port, database, username, and password
- **FR-003**: System MUST ensure PostgreSQL container builds and starts successfully before migrations
- **FR-004**: System MUST reset database state to remove any MySQL-specific artifacts
- **FR-005**: System MUST create `users` table with BIGSERIAL PRIMARY KEY, VARCHAR(150) for name, VARCHAR(20) UNIQUE for phone, TEXT for description, VARCHAR(255) for avatar, and TIMESTAMPTZ for timestamps
- **FR-006**: System MUST create `admins` table for Spatie RBAC with guard_name='admin' configuration
- **FR-007**: System MUST create `contracts` table with DECIMAL(10,2) for monetary fields, CHECK constraints for initial_payment < purchase_amount and months BETWEEN 1 AND 60, and ON DELETE RESTRICT for customer_id and agent_id
- **FR-008**: System MUST create `installments` table as State Table with ON DELETE CASCADE referencing contracts, including status VARCHAR(20) with default 'pending'
- **FR-009**: System MUST create `payments` table with DECIMAL(10,2) for amount, DATE for payment_date, VARCHAR(30) for type, and ON DELETE CASCADE referencing contracts
- **FR-010**: System MUST create `payment_audit_logs` table with JSONB columns for old_values and new_values, ON DELETE RESTRICT for contract_id, and ON DELETE SET NULL for payment_id
- **FR-011**: System MUST create `agent_shares_logs` table with INT for shares_count, CHECK (shares_count > 0), and ON DELETE RESTRICT for user_id
- **FR-012**: System MUST create `notification_templates` table with VARCHAR(50) UNIQUE for type and TEXT for body_template
- **FR-013**: System MUST create all indexes as defined in the indexing strategy (6 index groups covering users, contracts, installments, payments, audit_logs, and shares_logs)
- **FR-014**: System MUST configure Spatie permission tables to work with the separate `admins` table using 'admin' guard
- **FR-015**: System MUST verify POST /api/v1/auth/login endpoint authenticates admin against PostgreSQL successfully

### Key Entities *(include if feature involves data)*

- **Users**: Unified human entity table representing customers, agents, and investors. Contains name, phone, description, avatar. No type/role column - type inferred from relationships.
- **Admins**: System administrator accounts separate from users table, integrated with Spatie RBAC using 'admin' guard.
- **Contracts**: Installment agreement records with financial details (purchase_amount, initial_payment, profit_percentage, total_after_profit, months, monthly_installment_amount). Becomes immutable after signing.
- **Installments**: State table pre-generated on contract creation for efficient sorting/filtering by due_date and status tracking.
- **Payments**: Actual financial ledger with LIFO modification/deletion capability. Contains payment_date for analytics.
- **PaymentAuditLogs**: Append-only narrative log using JSONB for capturing before/after states of payment changes.
- **AgentSharesLogs**: Append-only transaction log for agent/share movements with 30-day modification window.
- **NotificationTemplates**: Standalone template storage for dynamic notification generation.

## Success Criteria *(mandatory)*

### Measurable Outcomes

- **SC-001**: The application runs on a PostgreSQL 15 Docker container - verified by `docker ps` showing postgres container running
- **SC-002**: `php artisan migrate` successfully creates all 8 Tamkeen business tables (users, admins, contracts, installments, payments, payment_audit_logs, agent_shares_logs, notification_templates) plus 5 Spatie tables = 13 total without errors
- **SC-003**: All PostgreSQL-specific data types are correctly applied (BIGSERIAL, DECIMAL(10,2), TIMESTAMPTZ, JSONB)
- **SC-004**: All CHECK constraints are enforced (initial_payment < purchase_amount, months BETWEEN 1 AND 60, shares_count > 0)
- **SC-005**: All Foreign Key constraints have correct ON DELETE behavior (RESTRICT for users/contracts references, CASCADE for installments/payments, SET NULL for audit_logs payment_id)
- **SC-006**: All 13 indexes defined in the indexing strategy are created
- **SC-007**: The POST /api/v1/auth/login endpoint successfully authenticates an admin and returns a Sanctum token within 2 seconds
- **SC-008**: The PostgreSQL database correctly stores and retrieves admin credentials for authentication

## Assumptions

- Docker and Docker Compose are installed and working on the development machine
- The PostgreSQL port (5432) is available and not conflicting with any existing PostgreSQL installation
- Laravel Sanctum is already installed from SP-01 and only needs database driver change
- Spatie Laravel Permission is already installed from SP-01 and only needs configuration adjustment for 'admin' guard
- The `.env` file already exists with some configuration that needs to be updated
- No users, agents, or investors exist in the system yet (clean slate for migration)
- Admin credentials from SP-01 are available for testing authentication regression