LOE: 13 (XL) | Rationale: Spans schema migration (DDL rewrites + new audit table), cross-cutting infrastructure work (8 Pub/Sub topics + 10 subscriptions across 2 environments), DDD model relocation, immutability enforcement at both DB and application layers, and documentation of retention and type-mapping contracts. No single change is isolated — each gap has cascading effects on publishers, subscribers, migrations, and the Eloquent model. Risk is HIGH: incorrect schema or topic naming will corrupt production event ordering and deduplication. # Jira Task: Refactor **Title:** Harden Event Backbone: Correct Schema, Audit Log, Pub/Sub Topology, and Domain Model Placement **Priority:** High **Story Points:** 13 **Assignee:** TBD ## Context Epic 2 established the Event Backbone and Dedicated Event Store as the foundation for all downstream data-sync flows. An audit revealed that the current implementation diverges from the [V10 specification](https://github.com/SuiteDynamics/SuiteX/wiki/Design-%7C-NetSuite-Sync-%E2%80%94-Design,-Requirements,-Deliverables,-and-Runbook) in multiple high-risk areas: the `events` table schema uses incorrect primary key design and missing columns; the `event_audit_log` table (required for fingerprint-based deduplication) does not exist; the Pub/Sub topology is missing the `events.error` topic, has no environment isolation, and lacks the full subscription set; and the Eloquent model violates core DDD path conventions while providing no technical immutability guard. These gaps collectively create a system that cannot safely deduplicate events, will silently mix sandbox and production data, and will route unrecoverable failures incorrectly. All gaps must be resolved before any downstream epic can rely on the event backbone. ### Current System Behavior - **Relevant Files:** `app/Models/EventStore.php`, `database/migrations/*_create_events_table.php` - **Current Pattern:** The `events` table uses a dual-key design (`id` auto-increment + `event_id` UUID), includes `aggregate_type`/`aggregate_id` column names, stores all change data in a single `payload` JSON column, and is missing `source`, `timestamp`, `base_version`, `processed`, and `full_snapshot_ref` columns. The Eloquent model lives in `app/Models/` (Laravel convention path) rather than the DDD-required `src/Domain/Events/Models/` namespace. No `event_audit_log` table exists. Only 3 Pub/Sub topics are provisioned (`events.raw`, `events.merged`, `events.dlq`) with no environment suffixes and no `events.error` topic. Only `events.raw-sub` is mentioned in IaC; the remaining 9 subscriptions are absent. ## Description Resolve all schema, infrastructure, and model placement gaps identified in the Epic 2 audit to bring the Event Backbone into full compliance with the [V10 specification](https://github.com/SuiteDynamics/SuiteX/wiki/Design-%7C-NetSuite-Sync-%E2%80%94-Design,-Requirements,-Deliverables,-and-Runbook). The work covers: (1) replacing the current `events` table DDL with the correct MySQL 8.x schema including UUID primary key, renamed aggregate columns, split payload columns, and all missing fields; (2) creating the `event_audit_log` table for fingerprint-based deduplication; (3) provisioning the missing `events.error` topic and all 8 environment-isolated topics across both `production` and `sandbox` environments; (4) provisioning the complete set of 10 subscriptions with correct routing and retry configuration; (5) relocating the Eloquent model to the correct DDD path and enforcing immutability at both the database and application layers; and (6) documenting the PostgreSQL→MySQL type mapping and the data retention/archival strategy. ### Areas to Review - `app/Models/EventStore.php` (Primary Target — to be deleted and replaced) - `src/Domain/Events/Models/Event.php` (Primary Target — new DDD model location) - `database/migrations/*_create_events_table.php` (Primary Target — DDL replacement) - `infrastructure/pubsub/` or equivalent IaC directory (Primary Target — topic and subscription provisioning) - `src/Domain/Events/` (Context — verify namespace and autoloading) - `config/database.php` (Context — confirm `event_store` connection is defined) ## Deliverables 1. A new migration file that drops the current `events` table and recreates it with the corrected MySQL 8.x DDL (UUID primary key, `record_type`, `record_id`, `source`, `timestamp`, `base_version`, `changes`, `full_snapshot_ref`, `processed`, correct indexes). 2. A new migration file that creates the `event_audit_log` table with the full DDL including fingerprint and outcome columns. 3. Updated IaC (Terraform or equivalent) that provisions all 8 environment-isolated Pub/Sub topics (`events.raw.{env}`, `events.merged.{env}`, `events.error.{env}`, `events.dlq.{env}` × `production` + `sandbox`) and all 10 subscriptions with correct routing, ordering key support, exponential backoff on the error subscription, and DLQ routing after 5 failed attempts. 4. A new Eloquent model at `src/Domain/Events/Models/Event.php` with the correct namespace, `event_store` connection binding, non-incrementing UUID key, `$fillable`, `$casts`, and `boot()` immutability guards. The old `app/Models/EventStore.php` file is deleted. 5. A companion Eloquent model at `src/Domain/Events/Models/EventAuditLog.php` with the same `event_store` connection, non-incrementing UUID key, and `boot()` immutability guards. 6. Database-level immutability: a restricted SQL user (`sync_worker`) granted only `SELECT, INSERT` on both `events` and `event_audit_log` — no `UPDATE` or `DELETE` privileges. 7. A written record (inline in migration comments or a [V10](https://github.com/SuiteDynamics/SuiteX/wiki/Design-%7C-NetSuite-Sync-%E2%80%94-Design,-Requirements,-Deliverables,-and-Runbook) appendix cross-reference note in a code comment) of the PostgreSQL→MySQL type mapping and the data retention/archival strategy (hot tier: Cloud SQL 90-day minimum; cold tier: GCS/BigQuery archive; partitioning path via `PARTITION BY RANGE (UNIX_TIMESTAMP(created_at))`). ## Acceptance Criteria - [ ] The `events` table has a single `CHAR(36)` UUID primary key named `id`; no `event_id` or auto-increment column exists. - [ ] The `events` table contains `record_type`, `record_id`, `source`, `timestamp TIMESTAMP(6)`, `base_version`, `changes JSON NOT NULL`, `full_snapshot_ref TEXT DEFAULT NULL`, and `processed TINYINT(1) NOT NULL DEFAULT 0` with all three specified indexes. - [ ] The `event_audit_log` table exists with `fingerprint CHAR(64) NOT NULL` and all specified indexes (`idx_audit_fingerprint`, `idx_audit_record`, `idx_audit_event`). - [ ] All 8 Pub/Sub topics exist in both `production` and `sandbox` environments: `events.raw.{env}`, `events.merged.{env}`, `events.error.{env}`, `events.dlq.{env}`. - [ ] All 10 subscriptions are provisioned with correct downstream routing: Merge Service, NetSuite Writer, SuiteX Writer, Error Handler, and DLQ Handler — each × 2 environments. - [ ] The `events.error.{env}` Pub/Sub subscription has exponential backoff configured and routes to `events.dlq.{env}` as a backstop after 5 failed delivery attempts. Note: this is a Pub/Sub-level safety net; the application layer must implement the per-error-class retry policy from [V10 Appendix O6](https://github.com/SuiteDynamics/SuiteX/wiki/Design-%7C-NetSuite-Sync-%E2%80%94-Design,-Requirements,-Deliverables,-and-Runbook#o6-suitex-retry-policy) (e.g., transient = 10 retries, 429 = 5, auth = 3, validation/conflict = 0 — immediate DLQ). - [ ] The Eloquent model exists at `src/Domain/Events/Models/Event.php` under namespace `Domain\Events\Models`; `app/Models/EventStore.php` no longer exists. - [ ] A companion `EventAuditLog` model exists at `src/Domain/Events/Models/EventAuditLog.php` with the same `event_store` connection, non-incrementing UUID key, and immutability boot guards. - [ ] Calling `->save()` on an existing `Event` model instance throws a `RuntimeException` with the message "Event records are immutable and cannot be updated." - [ ] Calling `->delete()` on an existing `Event` model instance throws a `RuntimeException` with the message "Event records are immutable and cannot be deleted." - [ ] The `sync_worker` DB user cannot execute `UPDATE` or `DELETE` statements against `events` or `event_audit_log`; a direct SQL attempt returns a permissions error. - [ ] No publisher sets a topic name without an environment suffix; topic names are derived from a config value, not hardcoded strings. - [ ] All publishers to `events.raw.{env}` and `events.merged.{env}` set `orderingKey` = `{recordType}:{recordId}` (e.g., `project:12345`). - [ ] A negative test: publishing a message to `events.raw.production` from a `sandbox` environment context is prevented by configuration (wrong topic name is never constructed). ## Validation & Testing 1. Run `php artisan migrate:fresh` against a local MySQL 8.x instance and verify `DESCRIBE events` and `DESCRIBE event_audit_log` match the DDL specifications exactly — column names, types, nullability, defaults, and indexes. 2. In a feature test, instantiate an `Event` model, persist it via `::create([...])`, then attempt `$event->source = 'changed'; $event->save()` and assert a `RuntimeException` is thrown. Repeat for `$event->delete()`. 3. Attempt to call `UPDATE events SET processed = 1 WHERE id = '...'` as the `sync_worker` MySQL user and assert the query is rejected with an access-denied error. 4. In IaC plan output (`terraform plan` or equivalent), verify exactly 8 topics and 10 subscriptions are declared, each carrying the correct environment suffix. 5. Publish a test message to `events.error.sandbox` and verify it is delivered to the Error Handler subscriber; after 5 nack cycles (Pub/Sub backstop), verify the message appears on `events.dlq.sandbox` and is NOT redelivered to the Error Handler. Application-level retry counts per [V10 O6](https://github.com/SuiteDynamics/SuiteX/wiki/Design-%7C-NetSuite-Sync-%E2%80%94-Design,-Requirements,-Deliverables,-and-Runbook#o6-suitex-retry-policy) are enforced by the consumer, not the subscription config. 6. Verify that the `Event` model's `$connection` property resolves to the `event_store` database connection defined in `config/database.php`, not the default connection. 7. Confirm that a fresh instantiation of the model and a call to `Event::create([...])` with a pre-generated UUID persists without auto-generating a new ID. ## Related Files (No Changes Required) - [V10 Design Specification](https://github.com/SuiteDynamics/SuiteX/wiki/Design-%7C-NetSuite-Sync-%E2%80%94-Design,-Requirements,-Deliverables,-and-Runbook) — Authoritative specification; [Appendix D (ERD)](https://github.com/SuiteDynamics/SuiteX/wiki/Design-%7C-NetSuite-Sync-%E2%80%94-Design,-Requirements,-Deliverables,-and-Runbook#appendix-d--erd-mermaid-format) and [Section M7](https://github.com/SuiteDynamics/SuiteX/wiki/Design-%7C-NetSuite-Sync-%E2%80%94-Design,-Requirements,-Deliverables,-and-Runbook#m7-polling-deduplication) define fingerprint-based deduplication and `event_audit_log` requirements; [Appendix E](https://github.com/SuiteDynamics/SuiteX/wiki/Design-%7C-NetSuite-Sync-%E2%80%94-Design,-Requirements,-Deliverables,-and-Runbook#appendix-e--topic--consumer-map) defines the 4-topic Pub/Sub topology; [Appendix A5](https://github.com/SuiteDynamics/SuiteX/wiki/Design-%7C-NetSuite-Sync-%E2%80%94-Design,-Requirements,-Deliverables,-and-Runbook#a5-event-consumers) defines the consumer/subscription list; [Section P2.10](https://github.com/SuiteDynamics/SuiteX/wiki/Design-%7C-NetSuite-Sync-%E2%80%94-Design,-Requirements,-Deliverables,-and-Runbook#p210-netsuite-sandbox-vs-production-isolation) mandates environment isolation on all topic names; [Appendix O6](https://github.com/SuiteDynamics/SuiteX/wiki/Design-%7C-NetSuite-Sync-%E2%80%94-Design,-Requirements,-Deliverables,-and-Runbook#o6-suitex-retry-policy) defines per-error-class retry policies. ## Technical Notes ### Implementation Guidance **Corrected `events` Table DDL (MySQL 8.x):** ```sql CREATE TABLE events ( id CHAR(36) NOT NULL, account_id VARCHAR(255) NOT NULL, record_type VARCHAR(255) NOT NULL, record_id VARCHAR(255) NOT NULL, source VARCHAR(50) NOT NULL, `timestamp` TIMESTAMP(6) NOT NULL, base_version BIGINT UNSIGNED DEFAULT NULL, changes JSON NOT NULL, full_snapshot_ref TEXT DEFAULT NULL, processed TINYINT(1) NOT NULL DEFAULT 0, created_at TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6), PRIMARY KEY (id), INDEX idx_events_aggregate (account_id, record_type, record_id), INDEX idx_events_unprocessed (processed, created_at), INDEX idx_events_source_time (source, `timestamp`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; ``` **`event_audit_log` Table DDL (MySQL 8.x):** ```sql CREATE TABLE event_audit_log ( id CHAR(36) NOT NULL, event_id CHAR(36) NOT NULL, record_type VARCHAR(255) NOT NULL, record_id VARCHAR(255) NOT NULL, source VARCHAR(50) NOT NULL, fingerprint CHAR(64) NOT NULL, processing_outcome VARCHAR(50) DEFAULT NULL, created_at TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6), PRIMARY KEY (id), INDEX idx_audit_fingerprint (fingerprint), INDEX idx_audit_record (record_type, record_id), INDEX idx_audit_event (event_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; ``` > **Design decision — no FK constraint on `event_id`:** The gap analysis DDL included `CONSTRAINT fk_audit_event FOREIGN KEY (event_id) REFERENCES events(id)`. This was intentionally omitted here. On an append-only event store, a FK constraint forces insert ordering (event row must exist before audit row) and blocks bulk/parallel inserts. The `idx_audit_event` index is sufficient for join queries; referential integrity is guaranteed by the application layer (both rows are inserted in the same transaction). > **Design decision — `fingerprint` + `processing_outcome` vs [V10](https://github.com/SuiteDynamics/SuiteX/wiki/Design-%7C-NetSuite-Sync-%E2%80%94-Design,-Requirements,-Deliverables,-and-Runbook#appendix-d--erd-mermaid-format) ER diagram `payload`:** [V10 Appendix D (ERD)](https://github.com/SuiteDynamics/SuiteX/wiki/Design-%7C-NetSuite-Sync-%E2%80%94-Design,-Requirements,-Deliverables,-and-Runbook#appendix-d--erd-mermaid-format) shows a `json payload` column on `event_audit_log`. However, the spec's text descriptions ([Event storage and canonical identity](https://github.com/SuiteDynamics/SuiteX/wiki/Design-%7C-NetSuite-Sync-%E2%80%94-Design,-Requirements,-Deliverables,-and-Runbook#event-storage-and-canonical-identity), [Section M7](https://github.com/SuiteDynamics/SuiteX/wiki/Design-%7C-NetSuite-Sync-%E2%80%94-Design,-Requirements,-Deliverables,-and-Runbook#m7-polling-deduplication)) consistently describe fingerprint-based deduplication and processing outcome tracking — neither of which map to a raw `payload` blob. The `event_id` FK already provides the reference to the event's payload in the `events` table. This DDL follows the spec's detailed text over the simplified ER diagram. **DDD Eloquent Model (`src/Domain/Events/Models/Event.php`):** ```php namespace Domain\Events\Models; use Illuminate\Database\Eloquent\Model; class Event extends Model { protected $connection = 'event_store'; protected $table = 'events'; protected $keyType = 'string'; public $incrementing = false; protected $fillable = [ 'id', 'account_id', 'record_type', 'record_id', 'source', 'timestamp', 'base_version', 'changes', 'full_snapshot_ref', 'processed', ]; protected $casts = [ 'changes' => 'array', 'processed' => 'boolean', 'base_version' => 'integer', 'timestamp' => 'datetime', ]; protected static function boot(): void { parent::boot(); static::updating(static function (): never { throw new \RuntimeException('Event records are immutable and cannot be updated.'); }); static::deleting(static function (): never { throw new \RuntimeException('Event records are immutable and cannot be deleted.'); }); } } ``` **DDD Eloquent Model (`src/Domain/Events/Models/EventAuditLog.php`):** ```php namespace Domain\Events\Models; use Illuminate\Database\Eloquent\Model; use Illuminate\Database\Eloquent\Relations\BelongsTo; class EventAuditLog extends Model { protected $connection = 'event_store'; protected $table = 'event_audit_log'; protected $keyType = 'string'; public $incrementing = false; public $timestamps = false; protected $fillable = [ 'id', 'event_id', 'record_type', 'record_id', 'source', 'fingerprint', 'processing_outcome', ]; protected $casts = [ 'created_at' => 'datetime', ]; public function event(): BelongsTo { return $this->belongsTo(Event::class, 'event_id', 'id'); } protected static function boot(): void { parent::boot(); static::updating(static function (): never { throw new \RuntimeException('Audit log records are immutable and cannot be updated.'); }); static::deleting(static function (): never { throw new \RuntimeException('Audit log records are immutable and cannot be deleted.'); }); } } ``` **DB-Level Immutability Grant:** ```sql GRANT SELECT, INSERT ON events.events TO 'sync_worker'@'%'; GRANT SELECT, INSERT ON events.event_audit_log TO 'sync_worker'@'%'; -- No UPDATE or DELETE privileges are granted. ``` **Pub/Sub Topic Naming Contract:** All topic names MUST be derived from config — never hardcoded. The naming pattern is: ``` events.raw.{env} events.merged.{env} events.error.{env} events.dlq.{env} ``` where `{env}` is `production` or `sandbox`. This yields 8 topics total. **Full Subscription Topology (10 subscriptions × 2 environments):** | Subscription | Topic | Downstream Consumer | Notes | |:---|:---|:---|:---| | `events.raw.{env}-merge-sub` | `events.raw.{env}` | Merge Service | — | | `events.merged.{env}-netsuite-sub` | `events.merged.{env}` | NetSuite Writer | — | | `events.merged.{env}-suitex-sub` | `events.merged.{env}` | SuiteX Writer | — | | `events.error.{env}-retry-sub` | `events.error.{env}` | Error Handler | Exponential backoff; routes to DLQ after 5 attempts | | `events.dlq.{env}-sub` | `events.dlq.{env}` | DLQ Handler | Terminal — no retry | **Publisher Ordering Key Contract:** Every publisher writing to `events.raw.{env}` or `events.merged.{env}` MUST set the Pub/Sub `orderingKey` field to: ``` {recordType}:{recordId} ``` Example: `project:12345`. This is mandatory for in-order delivery guarantees. Publishers that omit the ordering key will break per-record event sequencing. **PostgreSQL → MySQL 8.x Type Mapping:** | PostgreSQL Type | MySQL 8.x Equivalent | Notes | |:---|:---|:---| | `uuid` | `CHAR(36)` | Store as hyphenated UUID string | | `jsonb` | `JSON` | MySQL JSON type; use `->` and `->>` operators | | `timestamptz` | `TIMESTAMP(6)` | Store in UTC; 6-digit microsecond precision | | `bigint` | `BIGINT UNSIGNED` | Unsigned preferred for version counters | | `boolean DEFAULT false` | `TINYINT(1) DEFAULT 0` | Laravel `boolean` cast maps correctly | **Retention & Archival Strategy:** - **Hot tier:** Cloud SQL — minimum 90-day retention on the `events` table. - **Cold tier:** Archive rows older than 90 days to GCS (raw Parquet) and/or BigQuery for long-term analytics. - **Future partitioning path:** The `TIMESTAMP(6)` type on `created_at` is intentional — it enables `PARTITION BY RANGE (UNIX_TIMESTAMP(created_at))` without a schema change when the table volume justifies it.