# Epic 2: Event Backbone & Dedicated Event Store — Design Spec

## Business Justification & Problem Statement

To safely decouple SuiteX from NetSuite, we need a highly available, asynchronous messaging backbone. Furthermore, we need a permanent, immutable ledger (Event Store) to audit and replay historical data. To protect our existing multi-tenant database from disk I/O exhaustion and to avoid a massive database restart/outage, we will provision a brand new, dedicated High Availability (HA) Cloud SQL instance strictly for this event ledger.

## Proposed Architectural Solution

We will provision **Google Cloud Pub/Sub** to handle ingestion and routing. Concurrently, we will provision a new, dedicated **HA Cloud SQL (MySQL 8.x)** instance. This enforces a strict **CQRS (Command Query Responsibility Segregation)** pattern. The SuiteX web application will never write to this new database; it will only push JSON to Pub/Sub. A separate background worker will pull from Pub/Sub and securely append the records to the `event_store` database, completely isolating the sync workload from the user-facing application.

## Explicit Functional Requirements

### A. Pub/Sub & Storage

- Provision three GCP Pub/Sub topics: `events.raw`, `events.merged`, `events.dlq` (with **Message Ordering**).
- Provision a GCS bucket (`suitex-payload-snapshots`) with a 30-day lifecycle for oversized payloads.

### B. Dedicated HA Database

Provision a new Cloud SQL MySQL 8.x instance with:

- **High Availability (Regional)** enabled
- **Automated Backups** / **Point In Time Recovery (PITR)** enabled
- Addition to the existing SuiteX network in GCP to ensure application access

### C. The Event Store Schema

Create the `event_store` table on this new instance.

- **Required columns:** `id`, `account_id` (Tenant ID), `event_id`, `aggregate_type`, `aggregate_id`, `payload` (JSON), `created_at`.
- **Index:** Composite B-Tree index on `(account_id, aggregate_type, aggregate_id)`.

### D. Laravel Connection Wiring

Update the SuiteX application to support the new database connection alongside the primary one:

- Add `DB_EVENT_STORE_*` variables to `.env`.
- Define the `event_store` connection array in `config/database.php`.
- Bind the Eloquent model (`protected $connection = 'event_store';`).

## Architectural Boundaries & Technical Constraints

- **Write Isolation:** The SuiteX web application UI must **never** execute INSERT statements against the `event_store`. The UI only emits to Pub/Sub. Only the background queue workers are permitted to write to this database.
- **Immutability:** The `event_store` table is strictly **append-only**. Application code must never execute UPDATE or DELETE statements.

## Testable Acceptance Criteria

### Scenario 1 (Zero-Downtime Provisioning)

- **Given** the new Cloud SQL instance is provisioned with HA.
- **When** it comes online.
- **Then** the existing SuiteX web application experiences zero dropped connections or downtime.

### Scenario 2 (Multi-Database Routing)

- **Given** the Laravel config is updated.
- **When** a developer calls `EventStore::create()`.
- **Then** Laravel routes the insert to the new dedicated instance without disrupting queries to the primary tenant database.

### Scenario 3 (Topic Routing)

- **Given** the Pub/Sub topics exist.
- **When** a message fails acknowledgment on `events.raw-sub` 5 times.
- **Then** it is automatically routed to `events.dlq`.

## Deliverables

| Deliverable | Description |
|-------------|-------------|
| **Infrastructure as Code (IaC)** | Terraform/gcloud scripts for Pub/Sub, the GCS bucket, and the new HA Cloud SQL instance. |
| **Laravel Configuration** | PR containing the updates to `.env.example`, `config/database.php`, and the EventStore Eloquent model. |
| **Database Migration** | The Laravel PHP migration file defining the `event_store` table. |