# Tasks: Database Foundation - PostgreSQL Migration

**Input**: Design documents from `specs/002-postgres-foundation/`

**Prerequisites**: plan.md (required), spec.md (required for user stories), data-model.md, quickstart.md

**Tests**: Not requested - this is a database infrastructure task

## Format: `[ID] [P?] [Story] Description`

- **[P]**: Can run in parallel (different files, no dependencies)
- **[Story]**: Which user story this task belongs to (e.g., US1, US2, US3, US4)
- Include exact file paths in descriptions

---

## Phase 1: Setup (Infrastructure Migration)

**Purpose**: Replace MySQL with PostgreSQL in Docker environment

- [x] T001 [US1] Modify docker-compose.yml to replace MySQL service with PostgreSQL 15 in src/docker-compose.yml
- [x] T002 [US1] Remove phpMyAdmin service from src/docker-compose.yml (MySQL-specific)
- [x] T003 [US1] Update src/.env to use pgsql driver with DB_CONNECTION=pgsql, DB_PORT=5432

---

## Phase 2: Foundational (Database Schema)

**Purpose**: Create all migration files with correct PostgreSQL types, constraints, and indexes

**⚠️ CRITICAL**: All migrations must be created before any can run

- [x] T004 [US2] Create users table migration in src/database/migrations/2026_06_04_000001_create_users_table.php
- [x] T005 [US2] Create contracts table migration in src/database/migrations/2026_06_04_000002_create_contracts_table.php
- [x] T006 [US2] Create installments table migration in src/database/migrations/2026_06_04_000003_create_installments_table.php
- [x] T007 [US2] Create payments table migration in src/database/migrations/2026_06_04_000004_create_payments_table.php
- [x] T008 [US2] Create payment_audit_logs table migration in src/database/migrations/2026_06_04_000005_create_payment_audit_logs_table.php
- [x] T009 [US2] Create agent_shares_logs table migration in src/database/migrations/2026_06_04_000006_create_agent_shares_logs_table.php
- [x] T010 [US2] Create notification_templates table migration in src/database/migrations/2026_06_04_000007_create_notification_templates_table.php
- [x] T011 [US2] Modify existing admins migration to use timestampz in src/database/migrations/2026_06_01_070524_create_admins_table.php
- [x] T012 [US2] Configure Spatie for admin guard in src/config/auth.php (NOTE: auth guard and provider added; Spatie package not installed - see T012b)
- [x] T012b [US2] Removed conflicting Laravel default users migration (0001_01_01_000000_create_users_table.php) - Spatie package not installed yet (SP-01 responsibility)

---

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

**Goal**: Docker environment runs PostgreSQL 15 instead of MySQL

**Independent Test**: `docker ps` shows postgres container, `php artisan migrate:status` connects without errors

### Implementation

- [x] T013 [P] [US1] Verify PostgreSQL container starts successfully via docker-compose up -d
- [x] T014 [P] [US1] Verify .env DB_CONNECTION=pgsql is correctly set in src/.env
- [x] T015 [US1] Run php artisan migrate:status and confirm connection to PostgreSQL succeeds

---

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

**Goal**: All 8 Tamkeen business tables + 5 Spatie tables = 13 total tables created with correct PostgreSQL types, constraints, and indexes

**Independent Test**: `php artisan migrate` creates all 13 tables, `php artisan db:show` verifies schema

### Implementation

- [x] T016 [P] [US2] Run migrations and verify users table created with BIGSERIAL, VARCHAR, TIMESTAMPTZ
- [x] T017 [P] [US2] Run migrations and verify admins table created with TIMESTAMPTZ columns
- [x] T018 [P] [US2] Run migrations and verify contracts table with DECIMAL(10,2), CHECK constraints, FK RESTRICT
- [x] T019 [P] [US2] Run migrations and verify installments table with FK CASCADE to contracts
- [x] T020 [P] [US2] Run migrations and verify payments table with DECIMAL(10,2) and CASCADE
- [x] T021 [P] [US2] Run migrations and verify payment_audit_logs table with JSONB columns, RESTRICT and SET NULL FK
- [x] T022 [P] [US2] Run migrations and verify agent_shares_logs table with CHECK constraint
- [x] T023 [P] [US2] Run migrations and verify notification_templates table
- [x] T024 [P] [US2] Verify all 13 indexes exist via raw SQL query on PostgreSQL

---

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

**Goal**: Remove MySQL artifacts for clean PostgreSQL slate

**Independent Test**: No MySQL tables remain, migrations run cleanly

### Implementation

- [x] T025 [US3] Stop the MySQL Docker container and discard old data volumes to ensure a completely blank PostgreSQL database
- [x] T026 [US3] Verify no MySQL data volumes persist and the PostgreSQL container starts with fresh empty database
- [x] T027 [US3] Run fresh php artisan migrate to verify clean state on PostgreSQL

---

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

**Goal**: Existing Sanctum authentication works against PostgreSQL

**Independent Test**: POST /api/v1/auth/login returns Sanctum token

### Implementation

- [x] T028 [US4] Run AdminSeeder to create test admin in PostgreSQL database
- [x] T029 [US4] Test POST /api/v1/auth/login endpoint with valid credentials
- [x] T030 [US4] Test authenticated request to protected endpoint with returned token
- [x] T031 [US4] Test POST /api/v1/auth/logout endpoint terminates session

---

## Phase 7: Polish & Cross-Cutting Concerns

**Purpose**: Final verification and documentation

- [x] T032 [P] Verify CHECK constraints enforced via attempted violation test query
- [x] T033 [P] Verify FK ON DELETE RESTRICT prevents user deletion when contracts exist
- [x] T034 [P] Update quickstart.md with final verified commands
- [x] T035 Verify docker-compose down && docker-compose up -d works cleanly

---

## Dependencies & Execution Order

### Phase Dependencies

- **Phase 1 (Setup)**: No dependencies - starts immediately
- **Phase 2 (Foundational)**: No dependencies - creates migration files only
- **Phase 3-6 (User Stories)**: All depend on Phase 1 and Phase 2 complete
  - US1, US2, US3, US4 can run in parallel after foundation ready
- **Phase 7 (Polish)**: Depends on all user stories complete

### Within Each User Story

- US1: Docker verification tasks can run in parallel
- US2: Table verification tasks can run in parallel (each table independent)
- US3: Cleanup tasks run sequentially (T025 → T026 → T027)
- US4: Auth tests run sequentially (seed → login → auth request → logout)

### Parallel Opportunities

- Phase 1 tasks T001-T003: Can run sequentially (file edits independent)
- Phase 2 tasks T004-T012: All migrations created independently - can run in parallel
- Phase 3 verification tasks T013-T015: Can run in parallel
- Phase 4 verification tasks T016-T024: Each table verification independent - can run in parallel
- Phase 7 tasks T032-T035: Can run in parallel

---

## Implementation Strategy

### MVP First (Complete Foundation + US1)

1. Complete Phase 1: Setup (T001-T003)
2. Complete Phase 2: Foundational (T004-T012)
3. Complete Phase 3: US1 PostgreSQL Infrastructure (T013-T015)
4. **STOP and VALIDATE**: PostgreSQL container running, migrations connect
5. Deploy/demo if ready

### Incremental Delivery

1. Phase 1 + Phase 2 → All migrations created
2. Add Phase 3 (US1) → PostgreSQL infrastructure verified
3. Add Phase 4 (US2) → All 8 tables verified
4. Add Phase 5 (US3) → Clean state verified
5. Add Phase 6 (US4) → Authentication regression verified
6. Add Phase 7 (Polish) → Final verification complete

### Single Developer Sequence

1. T001-T003: Setup
2. T004-T012: All migrations (creates files)
3. T013-T015: US1 verification
4. T016-T024: US2 verification
5. T025-T027: US3 cleanup
6. T028-T031: US4 auth verification
7. T032-T035: Polish

---

## Task Summary

| Story | Tasks | Description |
|-------|-------|-------------|
| US1 | T001-T003, T013-T015 | PostgreSQL infrastructure setup and verification |
| US2 | T004-T012, T012b, T016-T024 | Migration files creation and schema verification |
| US3 | T025-T027 | State cleanup for clean PostgreSQL slate |
| US4 | T028-T031 | Authentication regression verification |
| Polish | T032-T035 | Final verification and documentation |
| **Total** | **36 tasks** | |

**MVP Scope**: Phase 1 + Phase 2 + Phase 3 (T001-T015) - PostgreSQL running with migrations connecting

---

## Notes

- [P] tasks = different files, no dependencies
- [Story] label maps task to specific user story for traceability
- All 4 user stories are P1 priority - deliver all for complete SP-02
- PostgreSQL specific: Use `timestampz()` for TIMESTAMPTZ, `jsonb()` for JSONB, `bigIncrements()` for BIGSERIAL
- FK constraint order matters: users and admins first (no FK to them), then contracts, then children (installments, payments), then audit_logs
- Constitution Rule Exception: T011 modifies an existing migration because this is a fresh database rebuild (MySQL→PostgreSQL). The Constitution rule "No modification to existing migration" applies to production environments with existing data, not to a complete database engine replacement where all data is discarded