V11: Inbox/Outbox V2 for RDS — single JSON column, drop all other columns except Id

### **Motivation**

The current relational Inbox/Outbox schema has grown to **20+ columns** across multiple migration versions (V1→V7 for Outbox, V1→V2 for Inbox). Each time Brighter adds a new message property (e.g., `PartitionKey` in V4, CloudEvents columns in V5, `DataRef`/`SpecVersion` in V7), a new migration is required. This creates ongoing maintenance burden for:

- **BoxProvisioning** — complex migration chains per backend
- **DDL management** — operators must apply schema changes on every upgrade
- **Cross-backend parity** — PostgreSQL, MySQL, MariaDB, MSSQL, and SQLite each maintain their own migration catalog

Modern RDS databases (PostgreSQL, MySQL, MariaDB, SQL Server) all have mature, first-class JSON support. Storing the entire `Message` as a single JSON document eliminates the need for per-property columns and future migrations entirely.

---

### **Proposed Schema**

Replace the multi-column schema with **two columns only**:

```sql
-- PostgreSQL
CREATE TABLE Outbox (
Id UUID PRIMARY KEY,
Message JSONB NOT NULL
);

-- MySQL / MariaDB
CREATE TABLE Outbox (
Id CHAR(36) PRIMARY KEY,
Message JSON NOT NULL
);

-- MSSQL
CREATE TABLE Outbox (
Id UNIQUEIDENTIFIER PRIMARY KEY,
Message NVARCHAR(MAX) NOT NULL
);

-- SQLite
CREATE TABLE Outbox (
Id TEXT PRIMARY KEY,
Message TEXT NOT NULL
);
```

**Same shape for Inbox:**

```sql
CREATE TABLE Inbox (
Id UUID PRIMARY KEY,
Message JSONB NOT NULL
);
```

The `Message` JSON contains the full serialized `Message` object — all headers, body, metadata, and state:

```json
{
"MessageId": "abc-123",
"Topic": "order.placed",
"MessageType": "MT_EVENT",
"Timestamp": "2026-07-08T12:00:00Z",
"CorrelationId": "xyz-789",
"ReplyTo": "reply-queue",
"ContentType": "application/json",
"HeaderBag": { "key": "value" },
"Body": "{\"orderId\":\"123\",\"value\":99.99}",
"Dispatched": null,
"PartitionKey": "tenant-1",
"Source": "orders-service",
"Type": "OrderPlaced",
"Subject": "orders/123",
"TraceParent": "00-...",
"TraceState": "..."
}
```

---

### **Why This Works**

| Current (V1–V7) | Proposed V2 |
|-------------------|-------------|
| 20+ columns, growing with each release | 2 columns forever |
| `ALTER TABLE` migrations for new properties | No schema migrations — serializer handles new fields |
| Per-backend migration catalogs (V1..V7) | Single schema shape across all RDS backends |
| Complex BoxProvisioning logic | Simple `CREATE TABLE` — no migration chain |
| Columns are nullable/typed per backend | JSON is self-describing and uniform |

---

### **Indexing Strategy**

The sweeper and archiver need to query by `Dispatched` status and order by time. JSON path indexes solve this.

#### **PostgreSQL**

```sql
-- GIN index for JSON path queries
CREATE INDEX IX_Outbox_Dispatched ON Outbox
USING GIN ((Message -> 'Dispatched'));

-- B-tree index on timestamp for ordering
CREATE INDEX IX_Outbox_Timestamp ON Outbox
((Message ->> 'Timestamp'));
```

#### **MySQL / MariaDB**

**MySQL 8.0.13+ and MariaDB 10.4+** support functional indexes directly on JSON expressions:

```sql
CREATE INDEX IX_Outbox_Dispatched
ON Outbox ((JSON_UNQUOTE(JSON_EXTRACT(Message, '$.Dispatched'))));

CREATE INDEX IX_Outbox_Timestamp
ON Outbox ((JSON_UNQUOTE(JSON_EXTRACT(Message, '$.Timestamp'))));
```

> **Note on MariaDB:** MariaDB 10.2–10.3 support JSON via `LONGTEXT` with validation and require generated columns for indexing. V11 targets **MariaDB 10.4+** where functional indexes on JSON are fully supported. If running on MariaDB 10.2–10.3, the generated-column fallback is available but not officially supported by V11.

#### **MSSQL**

```sql
-- Computed column + filtered index for undispatched messages
ALTER TABLE Outbox
ADD Dispatched AS JSON_VALUE(Message, '$.Dispatched') PERSISTED;

CREATE INDEX IX_Outbox_Dispatched ON Outbox(Dispatched)
WHERE Dispatched IS NULL;
```

#### **SQLite**

```sql
-- JSON1 extension supports path queries
CREATE INDEX IX_Outbox_Dispatched ON Outbox(json_extract(Message, '$.Dispatched'));
```

---

### **Outbox Sweeper Query**

**PostgreSQL:**
```sql
SELECT Id, Message
FROM Outbox
WHERE Message ->> 'Dispatched' IS NULL
ORDER BY Message ->> 'Timestamp'
LIMIT @BatchSize;
```

**MySQL / MariaDB:**
```sql
SELECT Id, Message
FROM Outbox
WHERE JSON_UNQUOTE(JSON_EXTRACT(Message, '$.Dispatched')) IS NULL
ORDER BY JSON_UNQUOTE(JSON_EXTRACT(Message, '$.Timestamp'))
LIMIT @BatchSize;
```

---

### **Serialization**

The `Message` column stores the full `Message` object serialized by the configured `IMessageMapper` / serializer (default JSON). On read, the Outbox/Inbox deserializes the JSON back into a `Message` instance.

```csharp
// Deposit
var json = JsonSerializer.Serialize(message);
await command.ExecuteAsync(
"INSERT INTO Outbox (Id, Message) VALUES (@Id, @Message::jsonb)",
new { Id = message.Id, Message = json });

// Read
var row = await command.QuerySingleAsync(
"SELECT Id, Message FROM Outbox WHERE Id = @Id",
new { Id = messageId });
var message = JsonSerializer.Deserialize(row.Message);
```

---

### **Inbox (Deduplication)**

The Inbox V2 follows the same pattern:

```sql
CREATE TABLE Inbox (
Id UUID PRIMARY KEY, -- MessageId
Message JSONB NOT NULL
);
```

The `Message` JSON contains:
```json
{
"MessageId": "abc-123",
"CommandBody": "...",
"Timestamp": "2026-07-08T12:00:00Z",
"ContextKey": "handler-type-name"
}
```

No `ContextKey` column — it lives inside the JSON.

---

### **Migration Path**

This is a **V11 breaking change**. The V2 schema is **not** backward-compatible with V1 tables.

| Approach | Details |
|----------|---------|
| **Greenfield** | New services use V2 schema directly |
| **Brownfield** | Operators archive old V1 table, create new V2 table, backfill if needed |
| **Package naming** | New packages: `Paramore.Brighter.Outbox.PostgreSql.V2`, or bump major version of existing packages |

---

### **Supported Database Versions**

| Database | Minimum Version |
|----------|-----------------|
| PostgreSQL | 12+ |
| MySQL | 8.0.13+ |
| MariaDB | 10.4+ |
| SQL Server | 2016+ |
| SQLite | 3.38.0+ (JSON1) |

---

### **Benefits**

1. **Zero schema migrations** — Adding `TraceParent`, `Baggage`, or any future property requires no `ALTER TABLE`
2. **Simpler provisioning** — No BoxProvisioning migration chain; one `CREATE TABLE` per backend
3. **Uniform across backends** — Same logical schema for PostgreSQL, MySQL, MariaDB, MSSQL, SQLite
4. **Smaller DDL footprint** — Easy to manage via Terraform, Flyway, or hand-rolled scripts
5. **Flexible metadata** — Users can store custom headers/properties without schema changes

Start by inspecting BoxProvisioning and the existing PostgreSQL, MySQL, MariaDB, MSSQL, and SQLite migration catalogs. Trace the IMessageMapper/serializer paths plus the Outbox sweeper and Inbox deduplication entry points. Done means the V2 two-column schema, serialization, reads, writes, and status queries work across the supported RDS backends without the old migration chain.

Written by the indexing model from the issue text.