# Implementation Plan: Database Foundation - PostgreSQL Migration

**Branch**: `003-postgres-foundation` | **Date**: 2026-06-04 | **Spec**: [spec.md](./spec.md)

**Input**: Feature specification from `/speckit.specify` for SP-02 Database Foundation

## Summary

Migrate the Tamkeen application from MySQL to PostgreSQL 15 and implement the complete database schema as defined in DB_Schema.md. This involves replacing the Docker database service, updating Laravel configuration, creating PostgreSQL-compatible migrations with proper constraints and indexes, and verifying the existing Sanctum authentication system works against the new database.

## Technical Context

**Language/Version**: PHP 8.2+ (Laravel 11+)

**Primary Dependencies**: 
- Laravel 11+ framework
- PostgreSQL 15+ (target database)
- Laravel Sanctum (authentication)
- Spatie Laravel Permission (RBAC)

**Storage**: PostgreSQL 15 with JSONB support for audit logs

**Testing**: PHPUnit (PHPUnit XML config exists at `src/phpunit.xml`)

**Target Platform**: Docker container environment (Linux)

**Performance Goals**: Support for thousands of contracts with efficient querying via proper indexing strategy

**Constraints**: 
- All IDs must use BIGSERIAL PRIMARY KEY
- Monetary values must use DECIMAL(10,2)
- Timestamps must use TIMESTAMPTZ
- No soft deletes (Constitution rule)
- No database-level ENUMs

**Scale/Scope**: 8 core tables plus Spatie permission tables, 13 indexes total

## Constitution Check

| Rule | Status | Notes |
|------|--------|-------|
| BIGSERIAL for IDs | ✅ Pass | All tables will use `$table->bigIncrements('id')` |
| DECIMAL(10,2) for money | ✅ Pass | All monetary columns use `decimal(10,2)` |
| TIMESTAMPTZ for timestamps | ✅ Pass | Will use `timestampz()` in migrations |
| No ENUMs | ✅ Pass | Using VARCHAR(20) for statuses |
| No soft deletes | ✅ Pass | Not included in any migration |
| Migration transaction | ✅ Pass | Default Laravel behavior |
| Index strategy | ✅ Pass | All indexes defined in DB_Schema.md |

## Project Structure

### Documentation (this feature)

```text
specs/002-postgres-foundation/
├── plan.md              # This file
├── research.md          # Phase 0 output (N/A - no unknowns)
├── data-model.md        # Phase 1 output
├── quickstart.md        # Phase 1 output
└── tasks.md             # Phase 2 output (/speckit.tasks)
```

### Source Code (repository root)

```text
src/
├── docker-compose.yml           # Modified: MySQL → PostgreSQL 15
├── .env                         # Modified: DB_CONNECTION, DB_HOST, DB_PORT, DB_DATABASE
├── app/
│   └── Domains/Auth/            # Existing SP-01 auth code
├── database/
│   └── migrations/
│       ├── 2026_06_01_070524_create_admins_table.php           # Modified: TIMESTAMPTZ
│       ├── 2026_06_01_072205_create_personal_access_tokens.php # Unchanged (Laravel default)
│       ├── 2026_06_XX_XXXXXX_create_users_table.php            # New
│       ├── 2026_06_XX_XXXXXX_create_contracts_table.php       # New
│       ├── 2026_06_XX_XXXXXX_create_installments_table.php    # New
│       ├── 2026_06_XX_XXXXXX_create_payments_table.php        # New
│       ├── 2026_06_XX_XXXXXX_create_payment_audit_logs_table.php  # New
│       ├── 2026_06_XX_XXXXXX_create_agent_shares_logs_table.php    # New
│       └── 2026_06_XX_XXXXXX_create_notification_templates_table.php # New
└── config/
    └── database.php             # May need adjustment for PostgreSQL
```

**Structure Decision**: Laravel standard structure with Domains folder for Auth domain. Migrations follow standard Laravel convention with timestamp prefixes.

## Phase 0: Research

No research required - all technical decisions are already specified in DB_Schema.md and Constitution.

## Phase 1: Design & Contracts

### Data Model Summary

**Entities and Relationships:**

```
users (BIGSERIAL, TIMESTAMPTZ)
  ├── contracts (customer_id) → ON DELETE RESTRICT
  ├── contracts (agent_id) → ON DELETE RESTRICT  
  └── agent_shares_logs → ON DELETE RESTRICT

admins (BIGSERIAL, TIMESTAMPTZ) - Spatie 'admin' guard

contracts (BIGSERIAL, TIMESTAMPTZ)
  ├── installments → ON DELETE CASCADE
  ├── payments → ON DELETE CASCADE
  └── payment_audit_logs → ON DELETE RESTRICT

payments (BIGSERIAL, TIMESTAMPTZ)
  └── payment_audit_logs (payment_id) → ON DELETE SET NULL

payment_audit_logs (BIGSERIAL, TIMESTAMPTZ, JSONB)

agent_shares_logs (BIGSERIAL, TIMESTAMPTZ)

notification_templates (BIGSERIAL, TIMESTAMPTZ)
```

### Key Migration Details

**users table:**
- BIGSERIAL PRIMARY KEY
- VARCHAR(150) name, VARCHAR(20) UNIQUE phone, TEXT description, VARCHAR(255) avatar
- TIMESTAMPTZ created_at, updated_at
- Indexes: idx_users_phone, idx_users_name

**contracts table:**
- BIGSERIAL PRIMARY KEY
- FK → users(id) ON DELETE RESTRICT for customer_id and agent_id
- DECIMAL(10,2) for monetary: purchase_amount, initial_payment, profit_percentage, total_after_profit, monthly_installment_amount
- CHECK: initial_payment < purchase_amount
- CHECK: months BETWEEN 1 AND 60
- BOOLEAN is_signed DEFAULT FALSE
- Indexes: idx_contracts_customer_id, idx_contracts_agent_id, idx_contracts_is_signed

**installments table:**
- BIGSERIAL PRIMARY KEY
- FK → contracts(id) ON DELETE CASCADE
- VARCHAR(20) status DEFAULT 'pending'
- DECIMAL(10,2) amount, paid_amount
- Indexes: idx_installments_contract_due_status

**payments table:**
- BIGSERIAL PRIMARY KEY
- FK → contracts(id) ON DELETE CASCADE
- DECIMAL(10,2) amount
- DATE payment_date (for analytics)
- VARCHAR(30) type
- Indexes: idx_payments_contract_date, idx_payments_payment_date

**payment_audit_logs table:**
- BIGSERIAL PRIMARY KEY
- FK → contracts(id) ON DELETE RESTRICT
- FK → payments(id) ON DELETE SET NULL
- JSONB old_values, new_values
- No updated_at (append-only)
- Indexes: idx_audit_logs_contract_id, idx_audit_logs_created_at

**agent_shares_logs table:**
- BIGSERIAL PRIMARY KEY
- FK → users(id) ON DELETE RESTRICT
- INT shares_count CHECK (shares_count > 0)
- VARCHAR(20) transaction_type
- No updated_at (append-only)
- Index: idx_shares_logs_user_id

**notification_templates table:**
- BIGSERIAL PRIMARY KEY
- VARCHAR(50) UNIQUE type
- TEXT body_template

### Spatie Configuration

- Use 'admin' guard for admins table
- guards.admin.provider = admins
- Model: App\Models\Admin
- Included in Spatie's model_morph_key

## Complexity Tracking

No violations of Constitution rules. Simpler alternatives (MySQL, SQLite) were explicitly rejected by architectural decision in Session 2 of Constitution.