# Supabase Mirror Synchronization Workflow
## Sprint DX002B

---

## Architecture Overview

```
┌─────────────────────────────────────────────────────────┐
│                   INVEETAIRE Application                │
│                                                         │
│  ┌─────────────────────────────────────────────────┐   │
│  │         MySQL (MariaDB)  ← SOURCE OF TRUTH      │   │
│  │                                                  │   │
│  │  All application queries run here.               │   │
│  │  No Supabase queries during normal operation.    │   │
│  └─────────────────────────────────────────────────┘   │
│                           │                             │
│                    MANUAL PUSH ONLY                     │
│                    (Developer Console)                  │
│                           │                             │
│  ┌─────────────────────────────────────────────────┐   │
│  │         Supabase PostgreSQL  ← MIRROR ONLY      │   │
│  │                                                  │   │
│  │  Uses:  - Cloud Mirror                           │   │
│  │         - Disaster Recovery (DX003)              │   │
│  │         - AI Development Reference               │   │
│  │         - Developer Utilities                    │   │
│  └─────────────────────────────────────────────────┘   │
└─────────────────────────────────────────────────────────┘
```

---

## Execution Modes

| Mode | Requirement | How |
|------|-------------|-----|
| **Management API** | `SUPABASE_ACCESS_TOKEN` set | SQL executed via `POST api.supabase.com/v1/projects/{ref}/database/query` |
| **SQL Export Mode** | No PAT required | SQL generated as downloadable `.sql` file — execute manually in Supabase Dashboard |

> [!IMPORTANT]
> `pdo_pgsql` is disabled in XAMPP. The Management API approach uses `file_get_contents()` — no Composer dependencies.

---

## Full Synchronization Workflow

### Step 1 — Dry Run (Always Do This First)

```
Developer clicks "Dry Run"
    ↓
SynchronizationService::dryRun()
    ↓
SchemaReaderService::getMysqlFullSchema()
    ↓
SchemaReaderService::getSupabaseFullSchema()  (via Management API)
    ↓
SchemaReaderService::compareFullSchema()
    ↓
DdlGeneratorService::generateIncrementalDdl()
    ↓
Returns:
  - comparison (tables/columns/types)
  - ddl_sql (preview)
  - tables_affected (count)
  - columns_affected (count)
  - sql_statements (count)
  - duration_ms

NO SQL IS EXECUTED. Log entry written (is_dry_run=true).
```

### Step 2 — Schema Comparison

```
Developer clicks "Compare Schema"
    ↓
SynchronizationService::compare()
    ↓
Returns structured diff:
  - missing_in_supabase: []
  - missing_in_mysql: []
  - matched: []
  - modified: {table: {column_diffs: []}}
  - drift_summary: "X tables missing..."
```

### Step 3 — SQL Preview

```
Developer clicks "Generate Schema"
    ↓
SynchronizationService::generatePreview(mode)
    ↓
mode = "incremental": only missing/modified tables
mode = "full":        entire MySQL schema as DDL
    ↓
Returns SQL string (copy / download)
```

### Step 4 — Push (Confirmation Required)

```
Developer clicks "Push to Supabase"
    ↓
Confirmation modal opens
    ↓
Developer must type "confirm" to proceed
    ↓
SynchronizationService::push(confirmed=true, mode)
    ↓
[Compare + Generate DDL]
    ↓
Is SUPABASE_ACCESS_TOKEN configured?
    ├── YES → Execute via Management API
    │         POST /v1/projects/{ref}/database/query
    │         (one statement at a time, stops on first error)
    │         → Auto-verify after push
    │         → Log entry (is_dry_run=false)
    │
    └── NO  → SQL Export Mode
              Return SQL for download
              Show "Execute manually in Supabase Dashboard" message
              → Log entry (execution_mode=sql_export)
```

### Step 5 — Verify Mirror

```
Developer clicks "Verify Mirror"
    ↓
MirrorVerifierService::verify()
    ↓
Read MySQL full schema
    ↓
Read Supabase schema via Management API
    ↓
Compare:
  ✓ Table count
  ✓ Column count per table
  ✓ Missing tables
  ✓ Missing columns
  ✓ Type compatibility
  ✓ Index presence
    ↓
Return:
  status: "healthy" | "warning" | "error" | "unconfigured"
  missing_tables: []
  column_issues: []
  type_issues: []
  index_issues: []
```

---

## Schema Comparison Engine

### Comparison Result Structure

```php
[
    'compared_at'         => '2026-07-21 02:00:00',
    'mysql_tables'        => 43,
    'supabase_tables'     => 43,
    'missing_in_supabase' => ['new_table_xyz'],
    'missing_in_mysql'    => [],
    'matched'             => ['guests', 'workspaces', ...],
    'modified'            => [
        'workspaces' => [
            'column_diffs' => [
                ['column' => 'new_col', 'status' => 'missing_in_supabase', ...],
                ['column' => 'old_col', 'status' => 'type_mismatch', ...],
            ]
        ]
    ],
    'has_drift'      => true,
    'drift_summary'  => '1 table(s) missing in Supabase',
]
```

### Column Diff Status Values

| Status | Meaning |
|--------|---------|
| `missing_in_supabase` | Column exists in MySQL, not in Supabase |
| `extra_in_supabase` | Column exists in Supabase, not in MySQL |
| `type_mismatch` | Column exists in both but types are incompatible |

---

## DDL Generator — Type Mapping Summary

See [MYSQL_TO_POSTGRES_MAPPING.md](./MYSQL_TO_POSTGRES_MAPPING.md) for complete mapping.

Key translations:

| MySQL | PostgreSQL |
|-------|-----------|
| `TINYINT(1)` | `BOOLEAN` |
| `BIGINT UNSIGNED AUTO_INCREMENT` | `BIGINT GENERATED ALWAYS AS IDENTITY` |
| `DATETIME` | `TIMESTAMP WITHOUT TIME ZONE` |
| `JSON` | `JSONB` |
| `TEXT/MEDIUMTEXT/LONGTEXT` | `TEXT` |
| `ENUM(...)` | `TEXT` (+ CHECK constraint recommended) |
| `BLOB/MEDIUMBLOB/LONGBLOB` | `BYTEA` |

---

## Synchronization Log

Stored at: `storage/developer/sync_log.json`  
Max entries: 200 (oldest trimmed automatically)

### Log Entry Fields

| Field | Type | Description |
|-------|------|-------------|
| `id` | string | Unique entry ID |
| `timestamp` | string | `Y-m-d H:i:s` |
| `user` | string | Session username |
| `direction` | string | `mysql_to_supabase` |
| `is_dry_run` | bool | True if no SQL was executed |
| `tables_affected` | int | Tables included in sync |
| `columns_affected` | int | Columns included in sync |
| `sql_statements` | int | SQL statements generated |
| `result` | string | `success`, `error`, `dry_run_complete` |
| `duration_ms` | int | Total operation duration |
| `execution_mode` | string | `management_api`, `sql_export`, `dry_run` |
| `error_message` | string\|null | Human-friendly error (no stack traces) |

---

## Security Rules (DX002B)

| Rule | Implementation |
|------|----------------|
| `SUPABASE_SERVICE_ROLE_KEY` never in browser | Not accessed in controller or view |
| `SUPABASE_ACCESS_TOKEN` never in browser | Stored in SupabaseService private property only |
| No raw SQL errors exposed | Human-friendly errors only in all catch blocks |
| Push requires explicit confirmation | Type-to-confirm modal + server-side confirmed=true check |
| Developer auth required | `AuthMiddleware` + `RoleMiddleware` on all routes |

---

## Status Badges

| Badge | Condition |
|-------|-----------|
| 🟢 **Mirror Healthy** | All tables/columns match |
| 🟡 **Schema Drift Detected** | Minor differences (type mismatches, missing indexes) |
| 🔴 **Synchronization Required** | Tables or columns missing in Supabase |
| 🔵 **Dry Run** | Operation simulated, no SQL executed |
| ⚙️ **Synchronizing...** | Push in progress (client-side indicator) |
| ✅ **Synchronization Successful** | Push completed via Management API |
| ❌ **Synchronization Failed** | Push failed — check error in Sync Log |

---

## Pull from Supabase — Disabled

Pull is intentionally **disabled during normal operation**.

MySQL is the source of truth. Pulling from Supabase would:
- Risk overwriting production MySQL data with mirror data
- Potentially lose changes made after the last push
- Violate the unidirectional sync principle

**Use Pull only for:** Disaster recovery when MySQL is unavailable.  
**Procedure:** Contact the database architect. Implement as part of DX003.

---

## Troubleshooting

### "Management API Not Configured"

Add to `.env`:
```
SUPABASE_ACCESS_TOKEN=sbp_xxxxxxxxxxxxxxxx
```
Generate at: [https://supabase.com/dashboard/account/tokens](https://supabase.com/dashboard/account/tokens)

### "Supabase Not Configured"

Add to `.env`:
```
SUPABASE_URL=https://your-project.supabase.co
SUPABASE_ANON_KEY=eyJhbGci...
SUPABASE_PROJECT_REF=your-project-ref
SUPABASE_REGION=ap-southeast-1
```

### "Network error during SQL execution"

1. Check that `SUPABASE_ACCESS_TOKEN` is valid (not expired)
2. Check that `SUPABASE_PROJECT_REF` matches your project
3. Verify outbound HTTPS is not blocked by firewall
4. Use SQL Export Mode as fallback

### "Type mismatch" in verification

Expected. MySQL types and PostgreSQL types have different internal names.
The verifier uses a compatibility alias map (`bigint` = `int8`, etc.).
Only critical mismatches are flagged as errors.

### Foreign key constraint errors during push

INVEETAIRE schema has circular FKs (`workspaces ↔ users`).
The DDL generator adds `DEFERRABLE INITIALLY DEFERRED` to all FK constraints.
If still failing, use SQL Export Mode and execute with `SET CONSTRAINTS ALL DEFERRED;` prefix.

---

## Non-Goals (DX002B)

The following are explicitly NOT implemented and belong to future sprints:

| Feature | Sprint |
|---------|--------|
| Automatic synchronization | Never (by design) |
| Cron/scheduler | Never |
| Background workers | Never |
| Real-time replication | Never |
| Pull from Supabase (active) | DX003 |
| Column-level conflict resolution | DX003 |
| Production failover automation | DX003 |

---

## File Reference

| File | Role |
|------|------|
| `config/supabase.php` | Config: URL, keys, PAT, Management API URL |
| `app/modules/Developer/SchemaReaderService.php` | MySQL + Supabase schema reading, comparison |
| `app/modules/Developer/DdlGeneratorService.php` | PostgreSQL DDL generation |
| `app/modules/Developer/MirrorVerifierService.php` | Mirror health check |
| `app/modules/Developer/SyncLogService.php` | JSON-file sync history |
| `app/modules/Developer/SynchronizationService.php` | Main orchestrator |
| `app/modules/Developer/DeveloperController.php` | Thin controller |
| `app/views/developer/sync.php` | Sync dashboard UI |
| `app/views/developer/sync_log.php` | Log history UI |
| `storage/developer/sync_log.json` | Runtime sync log file |
| `docs/database/SYNC_WORKFLOW.md` | This document |
| `docs/database/MYSQL_TO_POSTGRES_MAPPING.md` | Type mapping reference |