LOE: 7 (L) | Rationale: Nine schema-level corrections spanning JSON Schema authoring, SQL DDL constraints, enum ordering, field renaming, type correction, and a new Stage 2 validation rule. No production code changes, but each gap requires careful cross-referencing against the [design authority](https://github.com/SuiteDynamics/SuiteX/wiki/Design-%7C-NetSuite-Sync-%E2%80%94-Design,-Requirements,-Deliverables,-and-Runbook) to ensure the canonical envelope spec is internally consistent and unambiguous before any emitter or consumer is implemented. Risk is high: downstream epics depend on this spec as their source of truth. (`actorId` — Gap 3 from the original analysis — has already been implemented and is excluded from this ticket.) # Jira Task: Feature **Title:** Correct 9 Schema-Level Gaps in Canonical Envelope Spec Before Implementation Begins **Priority:** High **Story Points:** 7 **Assignee:** TBD ## Context A gap analysis comparing the Epic 1 Anti-Corruption Layer (Canonical Envelope Schema) against the [design authority](https://github.com/SuiteDynamics/SuiteX/wiki/Design-%7C-NetSuite-Sync-%E2%80%94-Design,-Requirements,-Deliverables,-and-Runbook) identified ten discrepancies. One gap (`actorId` folding into v1) has already been implemented and is excluded from this ticket. The remaining nine corrections are non-breaking and must be resolved in the spec before any further implementation work begins. Downstream epics (emitter construction, idempotency, merge orchestration, field sync) treat this envelope schema as the authoritative contract — shipping any of them against the uncorrected spec would require costly migration work later. ### Current System Behavior - **Relevant Files:** [Epic 1 Gap Analysis](https://github.com/SuiteDynamics/SuiteX/wiki/Design-%7C-NetSuite-Sync-%E2%80%94-Design,-Requirements,-Deliverables,-and-Runbook#epic-1-gap-analysis) (design-spec-gap-analysis.md) - **Current Pattern:** The envelope schema is missing `orderingKey` as a required field, uses `eventType` instead of `operation`, types `baseVersion` as a string, omits the `reconciliation` source value, limits `patternProperties` to `custentity_` only, leaves `source`/`sourceSystem` roles undocumented, constrains `fullSnapshotRef` to an `s3://` scheme, and the `field_metadata` DDL lacks NOT NULL constraints, DEFAULT values, a partial index, and a placement note. No Stage 2 enforcement rule exists for computed/formula field exclusion. ## Description This ticket corrects the remaining nine schema-level gaps in the Epic 1 canonical envelope specification. The work is entirely spec and documentation authoring — the deliverable is a corrected, internally consistent schema artifact that all downstream epics can implement against without ambiguity. Each gap correction must be reflected in the Deliverables below and validated against the [design authority](https://github.com/SuiteDynamics/SuiteX/wiki/Design-%7C-NetSuite-Sync-%E2%80%94-Design,-Requirements,-Deliverables,-and-Runbook) sections referenced in the Technical Notes. ### Areas to Review - [Appendix C1 — ChangeEvent Schema](https://github.com/SuiteDynamics/SuiteX/wiki/Design-%7C-NetSuite-Sync-%E2%80%94-Design,-Requirements,-Deliverables,-and-Runbook#c1-changeevent-schema) (canonical envelope and ordering key) - [Source priority and event classes](https://github.com/SuiteDynamics/SuiteX/wiki/Design-%7C-NetSuite-Sync-%E2%80%94-Design,-Requirements,-Deliverables,-and-Runbook#source-priority-and-event-classes) (source enum priority order) - [Appendix C1](https://github.com/SuiteDynamics/SuiteX/wiki/Design-%7C-NetSuite-Sync-%E2%80%94-Design,-Requirements,-Deliverables,-and-Runbook#c1-changeevent-schema) and [P2.1 Idempotency Key](https://github.com/SuiteDynamics/SuiteX/wiki/Design-%7C-NetSuite-Sync-%E2%80%94-Design,-Requirements,-Deliverables,-and-Runbook#p21-idempotency-key-schema) (operation field name) - [Circular Update Prevention & Event Source Attribution](https://github.com/SuiteDynamics/SuiteX/wiki/Design-%7C-NetSuite-Sync-%E2%80%94-Design,-Requirements,-Deliverables,-and-Runbook#appendix--circular-update-prevention--event-source-attribution) (source vs sourceSystem) - [Appendix K7 — Body vs Line Fields](https://github.com/SuiteDynamics/SuiteX/wiki/Design-%7C-NetSuite-Sync-%E2%80%94-Design,-Requirements,-Deliverables,-and-Runbook#k7-body-vs-line-fields) (custbody_/custcol_ patternProperties) - [Events table](https://github.com/SuiteDynamics/SuiteX/wiki/Design-%7C-NetSuite-Sync-%E2%80%94-Design,-Requirements,-Deliverables,-and-Runbook#events-table-immutable-store) and [Orchestrator algorithm](https://github.com/SuiteDynamics/SuiteX/wiki/Design-%7C-NetSuite-Sync-%E2%80%94-Design,-Requirements,-Deliverables,-and-Runbook#orchestrator--merge-worker--algorithm-summary) (baseVersion type) - [Appendix A2 — Platform Selection](https://github.com/SuiteDynamics/SuiteX/wiki/Design-%7C-NetSuite-Sync-%E2%80%94-Design,-Requirements,-Deliverables,-and-Runbook#a2-platform-selection) (fullSnapshotRef URI scheme) - [P2.2 Field-Level Metadata](https://github.com/SuiteDynamics/SuiteX/wiki/Design-%7C-NetSuite-Sync-%E2%80%94-Design,-Requirements,-Deliverables,-and-Runbook#p22-field-level-metadata-schema) (field_metadata DDL constraints and placement) - [Appendix K8 — Formula and Computed Fields](https://github.com/SuiteDynamics/SuiteX/wiki/Design-%7C-NetSuite-Sync-%E2%80%94-Design,-Requirements,-Deliverables,-and-Runbook#k8-formula-and-computed-fields) (is_readonly enforcement rule) ## Deliverables 1. Corrected canonical envelope JSON Schema with `orderingKey` added as a required field (pattern `^[a-z_]+:[a-zA-Z0-9_-]+$`) 2. `source` enum updated to four values: `["reconciliation", "netsuite-ue", "netsuite-poll", "suitex"]` (ordered by conflict resolution priority per [source priority and event classes](https://github.com/SuiteDynamics/SuiteX/wiki/Design-%7C-NetSuite-Sync-%E2%80%94-Design,-Requirements,-Deliverables,-and-Runbook#source-priority-and-event-classes) — note: the formal [C1 ChangeEvent schema](https://github.com/SuiteDynamics/SuiteX/wiki/Design-%7C-NetSuite-Sync-%E2%80%94-Design,-Requirements,-Deliverables,-and-Runbook#c1-changeevent-schema) only lists three values, omitting `reconciliation`; this ticket resolves that internal inconsistency) 3. `eventType` renamed to `operation` throughout the spec (enum values unchanged: `["create", "update", "delete"]`) 4. Inline documentation added to the schema distinguishing `source` (physical emission pathway) from `sourceSystem` (logical business actor), with a worked example 5. `patternProperties` extended to include `^custbody_[a-zA-Z0-9_]+$` and `^custcol_[a-zA-Z0-9_]+$`; a reference table mapping record types to applicable prefixes added to the spec 6. `baseVersion` retyped from `string` to `type: ["integer", "null"]` with `minimum: 0` 7. `fullSnapshotRef` pattern corrected from `s3://` to `^gs://` 8. Corrected `field_metadata` DDL including NOT NULL constraints, DEFAULT values, partial index, and a placement note (root/landlord database migration) 9. Stage 2 outbound validation rule documented: strip `is_readonly = true` fields from `changes`, emit a WARN log, and continue without dead-lettering ## Acceptance Criteria - [ ] The corrected envelope JSON Schema lists `orderingKey` in the `required` array with pattern `^[a-z_]+:[a-zA-Z0-9_-]+$` - [ ] The `source` enum contains exactly four values: `reconciliation`, `netsuite-ue`, `netsuite-poll`, `suitex` (priority ordering is an editorial decision by this ticket based on [design authority source-priority prose](https://github.com/SuiteDynamics/SuiteX/wiki/Design-%7C-NetSuite-Sync-%E2%80%94-Design,-Requirements,-Deliverables,-and-Runbook#source-priority-and-event-classes) — the formal [C1 schema](https://github.com/SuiteDynamics/SuiteX/wiki/Design-%7C-NetSuite-Sync-%E2%80%94-Design,-Requirements,-Deliverables,-and-Runbook#c1-changeevent-schema) only lists three, omitting `reconciliation`) - [ ] The field formerly named `eventType` is named `operation` everywhere in the spec artifact with no residual `eventType` references - [ ] The spec contains a dedicated explanation (with example) of the difference between `source` and `sourceSystem` - [ ] `patternProperties` covers all five NetSuite custom field prefixes (`custentity_`, `custbody_`, `custcol_`, `custitem_`, `custevent_`); the record-type-to-prefix reference table is present and complete - [ ] `baseVersion` is typed as `["integer", "null"]` with `minimum: 0` — the word `string` does not appear as its type - [ ] `fullSnapshotRef` pattern is `^gs://` — no `s3://` reference remains - [ ] The `field_metadata` DDL includes NOT NULL on all non-nullable columns, DEFAULT FALSE on `is_synced` and `is_readonly`, DEFAULT `'manual'` on `conflict_policy`, and the partial index on `(account_id, record_type) WHERE is_synced = TRUE` - [ ] The DDL is annotated as belonging to the root/landlord database migration, not a tenant migration - [ ] The Stage 2 validation rule specifies: strip `is_readonly = true` fields, emit WARN log with `field` and `event_id`, do not dead-letter the event - [ ] A reviewer unfamiliar with the [design authority](https://github.com/SuiteDynamics/SuiteX/wiki/Design-%7C-NetSuite-Sync-%E2%80%94-Design,-Requirements,-Deliverables,-and-Runbook) can implement each corrected item without needing to cross-reference any external document beyond the spec itself ## Validation & Testing 1. Open the corrected schema artifact and confirm every field in the `required` array has a corresponding `properties` entry — no orphaned required fields 2. Validate the JSON Schema against the corrected canonical envelope schema using a draft-07 validator with a sample payload: `{ "schemaVersion": "v1", "eventId": "", "accountId": "acct_1", "recordType": "project", "recordId": "proj_123", "operation": "update", "source": "netsuite-ue", "timestamp": "2026-03-13T00:00:00Z", "orderingKey": "project:proj_123", "changes": {}, "sourceSystem": "workflow", "writeId": "", "actorId": "user_42" }` — expect validation to pass 3. Attempt validation with `eventType` instead of `operation` — expect validation to fail (unknown property, `additionalProperties: false`) 4. Attempt validation with `orderingKey: "PROJECT:proj-123"` (uppercase record type) — expect failure against the pattern `^[a-z_]+:[a-zA-Z0-9_-]+$` 5. Attempt validation with `source: "webhook"` — expect failure (not in enum) 6. Attempt validation with `baseVersion: "v9"` — expect failure (type is integer, not string) 7. Attempt validation with `fullSnapshotRef: "s3://bucket/key"` — expect failure against `^gs://` 8. Confirm the `field_metadata` DDL can be executed against a PostgreSQL instance without error; verify the partial index is created and visible in `\d field_metadata` 9. Verify the Stage 2 pseudocode example covers the edge case where all keys in `event.changes` are `is_readonly = true` — event must still proceed (empty `changes` is valid, not dead-lettered) 10. Confirm no reference to `eventType` or `s3://` remains anywhere in the corrected spec artifact ## Technical Notes ### Implementation Guidance **Corrected Canonical Envelope v1 JSON Schema** ```json { "$schema": "http://json-schema.org/draft-07/schema#", "title": "CanonicalChangeEventEnvelope", "type": "object", "required": [ "schemaVersion", "eventId", "accountId", "recordType", "recordId", "operation", "source", "timestamp", "orderingKey", "changes", "sourceSystem", "writeId", "actorId" ], "properties": { "schemaVersion": { "type": "string", "enum": ["v1"] }, "eventId": { "type": "string", "format": "uuid" }, "accountId": { "type": "string" }, "recordType": { "type": "string" }, "recordId": { "type": "string" }, "operation": { "type": "string", "enum": ["create", "update", "delete"] }, "source": { "type": "string", "enum": ["reconciliation", "netsuite-ue", "netsuite-poll", "suitex"] }, "timestamp": { "type": "string", "format": "date-time" }, "orderingKey": { "type": "string", "pattern": "^[a-z_]+:[a-zA-Z0-9_-]+$", "description": "Pub/Sub ordering key in format 'recordType:recordId'" }, "baseVersion": { "type": ["integer", "null"], "minimum": 0 }, "changes": { "type": "object" }, "fullSnapshotRef": { "type": ["string", "null"], "pattern": "^gs://" }, "sourceSystem": { "type": "string", "enum": ["suitex", "netsuite", "workflow", "user"] }, "writeId": { "type": "string", "format": "uuid" }, "actorId": { "type": "string", "minLength": 1, "description": "SuiteX user ID or 'system' for automated processes" }, "transactionGroupId": { "type": ["string", "null"], "format": "uuid" } }, "additionalProperties": false } ``` **`source` vs `sourceSystem` — Roles and Example** These fields serve distinct purposes and must not be conflated: - `source`: the **physical emission pathway** — which producer published the event to Pub/Sub (e.g. the UE script, the poll worker, the reconciliation job, or SuiteX itself). - `sourceSystem`: the **logical business actor** — who or what initiated the underlying change in the data (e.g. a user, a workflow, NetSuite, or SuiteX automation). Example: a NetSuite workflow modifies a project record, which triggers the UE script to emit an event. - `source: "netsuite-ue"` — the UE script physically published it - `sourceSystem: "workflow"` — the workflow logically caused the change Review [Circular Update Prevention & Event Source Attribution](https://github.com/SuiteDynamics/SuiteX/wiki/Design-%7C-NetSuite-Sync-%E2%80%94-Design,-Requirements,-Deliverables,-and-Runbook#appendix--circular-update-prevention--event-source-attribution) for the full rationale. **`patternProperties` Record-Type Prefix Reference Table** | Record Type | Applicable Custom Field Prefixes | |--------------------------|----------------------------------| | `customer`, `contact` | `custentity_` | | `vendor`, `employee` | `custentity_` | | `project`, `projecttask` | `custbody_`, `custcol_` | | Items (`item`, etc.) | `custitem_` | | CRM Events | `custevent_` | Each payload schema's `patternProperties` block must include the prefixes applicable to its record type. The full set across all record types: ```json "patternProperties": { "^custentity_[a-zA-Z0-9_]+$": {}, "^custbody_[a-zA-Z0-9_]+$": {}, "^custcol_[a-zA-Z0-9_]+$": {}, "^custitem_[a-zA-Z0-9_]+$": {}, "^custevent_[a-zA-Z0-9_]+$": {} } ``` Review [Appendix K7 — Body vs Line Fields](https://github.com/SuiteDynamics/SuiteX/wiki/Design-%7C-NetSuite-Sync-%E2%80%94-Design,-Requirements,-Deliverables,-and-Runbook#k7-body-vs-line-fields) for the full list of record types and their applicable prefixes. **Corrected `field_metadata` DDL** ```sql -- Migration: database/migrations/ (root/landlord database — NOT a tenant migration) CREATE TABLE field_metadata ( account_id TEXT NOT NULL, record_type TEXT NOT NULL, field_id TEXT NOT NULL, field_type TEXT NOT NULL, is_synced BOOLEAN NOT NULL DEFAULT FALSE, is_readonly BOOLEAN NOT NULL DEFAULT FALSE, normalization_rule TEXT, conflict_policy TEXT NOT NULL DEFAULT 'manual', PRIMARY KEY (account_id, record_type, field_id) ); CREATE INDEX idx_field_metadata_sync_lookup ON field_metadata (account_id, record_type) WHERE is_synced = TRUE; ``` Review [P2.2 Field-Level Metadata Schema](https://github.com/SuiteDynamics/SuiteX/wiki/Design-%7C-NetSuite-Sync-%E2%80%94-Design,-Requirements,-Deliverables,-and-Runbook#p22-field-level-metadata-schema) for the full DDL context and migration sequencing rationale. **Stage 2 — Outbound Computed Field Exclusion Rule** Applies only when `source = "suitex"`. Must be documented as a named validation stage in the spec. ``` Stage 2 — Outbound Computed Field Exclusion (source = "suitex" only): FOR each key in event.changes: IF field_metadata(account_id, record_type, key).is_readonly = TRUE: STRIP key from event.changes LOG WARN "readonly field stripped — field: {key}, event_id: {eventId}" event proceeds (NOT dead-lettered) ``` An event where all `changes` keys are stripped results in an empty `changes` object — this is valid and must not trigger dead-lettering. Review [Appendix K8 — Formula and Computed Fields](https://github.com/SuiteDynamics/SuiteX/wiki/Design-%7C-NetSuite-Sync-%E2%80%94-Design,-Requirements,-Deliverables,-and-Runbook#k8-formula-and-computed-fields) for the full enforcement context.