# DX002B — Supabase Mirror Synchronization Engine

You are continuing the INVEETAIRE project.

DX002A has been completed successfully.

DO NOT redesign or replace the existing architecture.

Instead, extend the current implementation while preserving its modular structure.

------------------------------------------------------------
CURRENT STATUS
------------------------------------------------------------

DX002A already provides:

- Supabase configuration layer
- Secure credential handling
- Connection Test
- MySQL Inspector
- Supabase Inspector
- SchemaReaderService foundation
- Mapping documentation
- Developer Console pages
- Placeholder synchronization buttons

The application still uses MySQL as the ONLY source of truth.

Supabase is ONLY used for:

- Cloud Mirror
- Developer Utilities
- AI Development
- Disaster Recovery

The application itself MUST NEVER switch runtime database from MySQL to Supabase.

------------------------------------------------------------
OBJECTIVE
------------------------------------------------------------

Implement the complete synchronization engine between MySQL and Supabase.

Synchronization MUST always be MANUAL.

No automatic synchronization.

No background jobs.

No cron.

No realtime replication.

------------------------------------------------------------
FEATURE 1
SCHEMA COMPARISON ENGINE
------------------------------------------------------------

Extend the existing SchemaReaderService.

Compare:

- Tables
- Columns
- Data Types
- Nullable
- Default Values
- Primary Keys
- Foreign Keys
- Indexes

Return structured comparison:

- Match
- Missing in Supabase
- Missing in MySQL
- Modified
- Additional

Comparison only.

No synchronization here.

------------------------------------------------------------
FEATURE 2
DDL GENERATOR
------------------------------------------------------------

Generate PostgreSQL-compatible SQL from MySQL metadata.

Support:

- CREATE TABLE
- ALTER TABLE
- ADD COLUMN
- MODIFY COLUMN
- DROP COLUMN
- PRIMARY KEY
- FOREIGN KEY
- INDEX

Generate SQL Preview only.

Do NOT execute automatically.

------------------------------------------------------------
FEATURE 3
PUSH TO SUPABASE
------------------------------------------------------------

Implement manual synchronization.

Workflow:

Compare

↓

Generate SQL

↓

Preview

↓

User Confirmation

↓

Execute

↓

Verify Mirror

↓

Create Log

Requirements:

- Manual execution only
- Never automatic
- Server-side execution
- Transaction whenever possible
- Stop immediately on critical error

------------------------------------------------------------
FEATURE 4
VERIFY MIRROR
------------------------------------------------------------

Implement mirror verification.

Verify:

- Table Count
- Column Count
- Missing Tables
- Missing Columns
- Missing Indexes
- Missing Foreign Keys
- Datatype Compatibility

Return status:

Healthy

Warning

Error

Display complete summary.

------------------------------------------------------------
FEATURE 5
DRY RUN
------------------------------------------------------------

Every synchronization must support Dry Run.

Dry Run must:

- Compare schema
- Generate SQL
- Estimate affected tables
- Estimate affected columns

Dry Run MUST NEVER execute SQL.

------------------------------------------------------------
FEATURE 6
SYNCHRONIZATION LOG
------------------------------------------------------------

Implement synchronization history.

Store:

- Timestamp
- Developer/User
- Direction
- Dry Run
- Tables Affected
- Result
- Duration
- Error Message

Display newest first inside Developer Console.

------------------------------------------------------------
FEATURE 7
DEVELOPER CONSOLE
------------------------------------------------------------

Enable these buttons:

- Push to Supabase
- Verify Mirror
- Compare Schema
- Generate Schema

Keep this button disabled:

Pull from Supabase

Reason:

MySQL remains the single source of truth.

Display:

Reserved for Disaster Recovery

Not recommended during normal operation.

------------------------------------------------------------
FEATURE 8
SYNCHRONIZATION SERVICE
------------------------------------------------------------

Create a dedicated SynchronizationService.

Responsibilities:

- Compare
- Generate SQL
- Push
- Verify
- Logging

Controllers must remain thin.

Business logic belongs inside services.

------------------------------------------------------------
FEATURE 9
ERROR HANDLING
------------------------------------------------------------

Handle:

- Network Timeout
- Authentication Failure
- SQL Failure
- Missing Dependency
- Foreign Key Conflict
- Datatype Conflict
- Transaction Failure

Every failure must:

- Stop synchronization
- Rollback whenever possible
- Record synchronization log
- Return human-friendly error

Never expose stack traces.

------------------------------------------------------------
FEATURE 10
SECURITY
------------------------------------------------------------

Maintain DX002A security model.

Requirements:

- Service Role Key remains server-side only
- Never expose credentials
- Never expose raw SQL errors
- Validate requests
- CSRF protection
- Developer authentication required

------------------------------------------------------------
FEATURE 11
DOCUMENTATION
------------------------------------------------------------

Update Developer Documentation.

Include:

- Synchronization Workflow
- Architecture Diagram
- Datatype Mapping
- Dry Run Guide
- Push Workflow
- Recovery Recommendation
- Troubleshooting

------------------------------------------------------------
FEATURE 12
DEVELOPER EXPERIENCE
------------------------------------------------------------

Display status badges:

Mirror Healthy

Schema Drift Detected

Synchronization Required

Dry Run

Synchronizing...

Synchronization Successful

Synchronization Failed

Provide confirmation dialog before synchronization.

------------------------------------------------------------
VALIDATION
------------------------------------------------------------

Before finishing:

✔ Run php -l on every modified PHP file

✔ No duplicated business logic

✔ Controllers remain thin

✔ Architecture remains modular

✔ MySQL remains production database

✔ Supabase remains mirror only

✔ Synchronization is manual

✔ Dry Run works

✔ SQL Preview works

✔ Compare Schema works

✔ Verify Mirror works

✔ Synchronization Log works

------------------------------------------------------------
EXPLICIT NON-GOALS
------------------------------------------------------------

DO NOT implement:

- Automatic Synchronization
- Cron Jobs
- Scheduler
- Background Workers
- Webhooks
- Realtime Replication
- Runtime Database Switching
- Multi-master Replication
- Automatic Conflict Resolution
- Production Failover

These belong to future DX releases.

------------------------------------------------------------
ARCHITECTURE
------------------------------------------------------------

Reuse existing INVEETAIRE architecture.

Extend existing classes whenever appropriate.

Avoid replacing DX002A implementation.

Maintain separation between:

- Controllers
- Services
- Repositories
- Views
- Configuration

Write clean, modular, production-ready PHP.

------------------------------------------------------------
EXPECTED RESULT
------------------------------------------------------------

After DX002B is completed, Developer Console must be capable of:

✓ Compare MySQL ↔ Supabase Schema

✓ Preview PostgreSQL DDL

✓ Manual Push MySQL → Supabase

✓ Verify Mirror Integrity

✓ Synchronization History

✓ Dry Run

✓ SQL Preview

Without affecting the production application's runtime database.

Finally:

1. Summarize all files created and modified.
2. Explain the synchronization flow.
3. List any assumptions made.
4. Run PHP lint (`php -l`) on every modified PHP file and report the results.