# MySQL → PostgreSQL Type Mapping

**Sprint:** DX002A  
**Purpose:** Reference guide for Supabase schema generation (DX002B)  
**Status:** Documentation only — no synchronization performed yet

---

## Overview

This document defines the authoritative data type mapping strategy from INVEETAIRE's MySQL (MariaDB) schema to PostgreSQL (Supabase). This mapping is used by DX002B when generating Supabase DDL from the existing MySQL schema.

**The MySQL database remains the sole source of truth at all times.**

---

## Type Mapping Reference

### Primary Keys

| MySQL | PostgreSQL | Notes |
|-------|-----------|-------|
| `BIGINT UNSIGNED AUTO_INCREMENT` | `BIGINT GENERATED ALWAYS AS IDENTITY` | Modern PostgreSQL standard |
| `INT UNSIGNED AUTO_INCREMENT` | `INTEGER GENERATED ALWAYS AS IDENTITY` | |
| `TINYINT UNSIGNED AUTO_INCREMENT` | `SMALLINT GENERATED ALWAYS AS IDENTITY` | |
| `SMALLINT UNSIGNED AUTO_INCREMENT` | `SMALLINT GENERATED ALWAYS AS IDENTITY` | |

### Foreign Keys

| MySQL | PostgreSQL | Notes |
|-------|-----------|-------|
| `BIGINT UNSIGNED` FK | `BIGINT` FK | Remove UNSIGNED — PostgreSQL has no unsigned types |
| `INT UNSIGNED` FK | `INTEGER` FK | Same |
| `TINYINT UNSIGNED` FK | `SMALLINT` FK | |

### Auto Increment

| MySQL | PostgreSQL | Notes |
|-------|-----------|-------|
| `AUTO_INCREMENT` | `GENERATED ALWAYS AS IDENTITY` | Preferred for PostgreSQL 10+ |
| `SERIAL` | `SERIAL` | Legacy — still valid |
| `BIGSERIAL` | `BIGSERIAL` | Legacy — still valid |

### Boolean

| MySQL | PostgreSQL | Notes |
|-------|-----------|-------|
| `TINYINT(1)` | `BOOLEAN` | MySQL uses TINYINT(1) for booleans; PostgreSQL has native BOOLEAN |
| `BIT(1)` | `BOOLEAN` | |
| Values: `0 / 1` | Values: `FALSE / TRUE` | |

### DateTime & Timestamp

| MySQL | PostgreSQL | Notes |
|-------|-----------|-------|
| `DATETIME` | `TIMESTAMP WITHOUT TIME ZONE` | INVEETAIRE stores UTC — no TZ needed |
| `DATETIME DEFAULT CURRENT_TIMESTAMP` | `TIMESTAMP WITHOUT TIME ZONE DEFAULT NOW()` | |
| `TIMESTAMP` | `TIMESTAMP WITH TIME ZONE` | MySQL TIMESTAMP is UTC-stored; use TIMESTAMPTZ |
| `DATE` | `DATE` | Direct mapping |
| `TIME` | `TIME WITHOUT TIME ZONE` | |

### JSON

| MySQL | PostgreSQL | Notes |
|-------|-----------|-------|
| `JSON` | `JSONB` | Always use JSONB — binary JSON with GIN index support |
| `JSON` (any) | `JSONB` | Never use `JSON` type in PostgreSQL for new tables |

### Text Types

| MySQL | PostgreSQL | Notes |
|-------|-----------|-------|
| `VARCHAR(n)` | `VARCHAR(n)` | Direct mapping |
| `VARCHAR(191)` | `VARCHAR(191)` | Same (MySQL index limit workaround — not needed in PG) |
| `CHAR(n)` | `CHAR(n)` | Direct mapping |
| `TEXT` | `TEXT` | Direct mapping — PG TEXT is unlimited |
| `MEDIUMTEXT` | `TEXT` | PG TEXT covers all sizes |
| `LONGTEXT` | `TEXT` | PG TEXT covers all sizes |

### ENUM

| MySQL | PostgreSQL | Notes |
|-------|-----------|-------|
| `ENUM('a','b','c')` | `TEXT` with `CHECK (col IN ('a','b','c'))` | Recommended for flexibility |
| `ENUM('a','b','c')` | `CREATE TYPE name AS ENUM (...)` | Alternative — PostgreSQL native ENUM |
| `SET('a','b')` | `TEXT[]` | MySQL SET maps to PostgreSQL text arrays |

> **Recommendation:** Use TEXT + CHECK constraint for INVEETAIRE status columns. Easier to ALTER later.

### DECIMAL / Numeric

| MySQL | PostgreSQL | Notes |
|-------|-----------|-------|
| `DECIMAL(p, s)` | `NUMERIC(p, s)` | Interchangeable in PostgreSQL |
| `FLOAT` | `REAL` | Single precision |
| `DOUBLE` | `DOUBLE PRECISION` | |
| `TINYINT` | `SMALLINT` | Closest PostgreSQL equivalent (8-bit → 16-bit) |
| `SMALLINT` | `SMALLINT` | Direct mapping |
| `MEDIUMINT` | `INTEGER` | 24-bit → 32-bit |
| `INT` / `INTEGER` | `INTEGER` | Direct mapping |
| `BIGINT` | `BIGINT` | Direct mapping |

### Binary

| MySQL | PostgreSQL | Notes |
|-------|-----------|-------|
| `BLOB` | `BYTEA` | |
| `MEDIUMBLOB` | `BYTEA` | PG BYTEA is unlimited |
| `LONGBLOB` | `BYTEA` | PG BYTEA is unlimited |

### Other

| MySQL | PostgreSQL | Notes |
|-------|-----------|-------|
| `YEAR` | `SMALLINT` | 4-digit year |
| `NULL` / `NOT NULL` | `NULL` / `NOT NULL` | Same |
| `DEFAULT value` | `DEFAULT value` | Verify function-based defaults use PG syntax |

---

## INVEETAIRE Schema-Specific Migration Notes

### 1. UNSIGNED Integers

All `UNSIGNED` qualifiers must be removed when generating PostgreSQL DDL.

```sql
-- MySQL
workspace_id BIGINT UNSIGNED NOT NULL

-- PostgreSQL
workspace_id BIGINT NOT NULL
```

### 2. TINYINT(1) Booleans

Every `TINYINT(1)` column in the INVEETAIRE schema is a boolean field.

```sql
-- MySQL
is_deleted TINYINT(1) NOT NULL DEFAULT 0

-- PostgreSQL
is_deleted BOOLEAN NOT NULL DEFAULT FALSE
```

Affected columns include: `is_deleted`, `is_active`, `is_system`, `is_primary`, `is_visible`, `is_correction`, `rsvp_enabled`, `wish_wall_enabled`, `music_enabled`, etc.

### 3. Soft Delete Pattern

The INVEETAIRE soft delete pattern uses `is_deleted + deleted_at`:

```sql
-- MySQL
is_deleted TINYINT(1) NOT NULL DEFAULT 0
deleted_at DATETIME NULL

-- PostgreSQL
is_deleted BOOLEAN NOT NULL DEFAULT FALSE
deleted_at TIMESTAMP WITHOUT TIME ZONE NULL
```

### 4. JSON Columns

All `JSON` columns should be migrated to `JSONB` in PostgreSQL:

```sql
-- MySQL
section_config JSON NULL
before_state   JSON NULL

-- PostgreSQL
section_config JSONB NULL
before_state   JSONB NULL
```

### 5. Status VARCHAR Columns

INVEETAIRE uses `VARCHAR(20)` status columns with application-enforced enum values.
In PostgreSQL, keep as `VARCHAR(20)` or use `TEXT` with CHECK constraints:

```sql
-- MySQL
workspace_status VARCHAR(20) NOT NULL DEFAULT 'provisioned'

-- PostgreSQL (option 1: keep as VARCHAR)
workspace_status VARCHAR(20) NOT NULL DEFAULT 'provisioned'

-- PostgreSQL (option 2: CHECK constraint)
workspace_status TEXT NOT NULL DEFAULT 'provisioned'
    CHECK (workspace_status IN ('provisioned','setup','active','live','post_event','archived','purged'))
```

### 6. Circular Foreign Key (workspaces ↔ users)

The `workspaces.created_by → users.id` and `users.workspace_id → workspaces.id` create a circular FK dependency.

In PostgreSQL, use `DEFERRABLE INITIALLY DEFERRED`:

```sql
-- PostgreSQL
ALTER TABLE workspaces ADD CONSTRAINT fk_workspaces_created_by
    FOREIGN KEY (created_by) REFERENCES users(id)
    ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED;
```

### 7. Audit Logs — Soft References

The `audit_logs` table intentionally uses no FK constraints (soft references).
This pattern transfers directly to PostgreSQL — no changes needed.

---

## AUTO_INCREMENT → IDENTITY Example

Full column migration example for a table with PK:

```sql
-- MySQL
CREATE TABLE guests (
    id          BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    workspace_id BIGINT UNSIGNED NOT NULL,
    ...
);

-- PostgreSQL
CREATE TABLE guests (
    id          BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    workspace_id BIGINT NOT NULL REFERENCES workspaces(id),
    ...
);
```

---

## Generated By

- **Sprint:** DX002A  
- **Generated:** 2026-07-21  
- **Used by:** DX002B (Synchronization Sprint)  
- **Maintained by:** Lead Database Architect

---

## Update Policy

This document must be updated whenever:
1. A new data type is added to the MySQL schema
2. A migration decision changes for any existing type
3. PostgreSQL or Supabase updates affect type compatibility