tor_id VARCHAR(50) );

-- Key-value payload CREATE TABLE event_properties ( property_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, event_id BIGINT NOT NULL REFERENCES system_events(event_id) ON DELETE CASCADE, metric_key VARCHAR(100) NOT NULL, metric_value TEXT NOT NULL, measurement_unit VARCHAR(20) );

-- Temporal metadata registry CREATE TABLE metric_registry ( registry_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, event_class VARCHAR(40) NOT NULL, metric_key VARCHAR(100) NOT NULL, display_label VARCHAR(200), expected_type VARCHAR(20) NOT NULL, valid_from DATE NOT NULL, valid_to DATE, created_by VARCHAR(50), CONSTRAINT uq_metric_version UNIQUE (event_class, metric_key, valid_from) );

**Architecture Rationale**: 
- `system_events` acts as the immutable anchor. It never changes shape.
- `event_properties` decouples data collection from schema design. New metrics require zero DDL changes.
- `metric_registry` solves the audit problem. By tracking `valid_from` and `valid_to`, you preserve historical context. A temperature reading from Q1 uses the Q1 spec definition, even if Q2 redefined acceptable ranges.

### Step 2: Multi-Format Ingestion Gateway

Departments will not standardize their workflows on command. The gateway accepts their native formats, validates against the registry, and writes normalized events.

```typescript
import { Pool, QueryResult } from 'pg';

interface IngestionPayload {
  batchRef: string;
  eventClass: string;
  originSystem: string;
  operatorId?: string;
  properties: Record<string, string | number>;
}

interface MetricRegistryRow {
  metric_key: string;
  expected_type: string;
  valid_from: Date;
  valid_to: Date | null;
}

export class DataGateway {
  private db: Pool;
  private registryCache: Map<string, MetricRegistryRow[]> = new Map();

  constructor(dbPool: Pool) {
    this.db = dbPool;
  }

  async ingest(payload: IngestionPayload): Promise<void> {
    const client = await this.db.connect();
    try {
      await client.query('BEGIN');

      // 1. Validate against active registry entries
      const activeMetrics = await this.loadActiveRegistry(payload.eventClass);
      this.validatePayload(payload, activeMetrics);

      // 2. Insert event anchor
      const eventResult = await client.query(
        `INSERT INTO system_events (batch_ref, event_class, origin_system, operator_id)
         VALUES ($1, $2, $3, $4) RETURNING event_id`,
        [payload.batchRef, payload.eventClass, payload.originSystem, payload.operatorId || null]
      );
      const eventId = eventResult.rows[0].event_id;

      // 3. Batch insert properties
      const propValues: string[] = [];
      const propParams: any[] = [];
      let paramIndex = 1;

      for (const [key, value] of Object.entries(payload.properties)) {
        propValues.push(`($${paramIndex++}, $${paramIndex++}, $${paramIndex++})`);
        propParams.push(eventId, key, String(value));
      }

      if (propValues.length > 0) {
        await client.query(
          `INSERT INTO event_properties (event_id, metric_key, metric_value)
           VALUES ${propValues.join(', ')}`,
          propParams
        );
      }

      await client.query('COMMIT');
    } catch (err) {
      await client.query('ROLLBACK');
      throw err;
    } finally {
      client.release();
    }
  }

  private async loadActiveRegistry(eventClass: string): Promise<MetricRegistryRow[]> {
    const cacheKey = `${eventClass}_active`;
    if (this.registryCache.has(cacheKey)) {
      return this.registryCache.get(cacheKey)!;
    }

    const res = await this.db.query(
      `SELECT metric_key, expected_type, valid_from, valid_to 
       FROM metric_registry 
       WHERE event_class = $1 AND valid_to IS NULL`,
      [eventClass]
    );
    this.registryCache.set(cacheKey, res.rows);
    return res.rows;
  }

  private validatePayload(payload: IngestionPayload, registry: MetricRegistryRow[]): void {
    const allowedKeys = new Set(registry.map(r => r.metric_key));
    for (const key of Object.keys(payload.properties)) {
      if (!allowedKeys.has(key)) {
        throw new Error(`Unregistered metric key: ${key} for class ${payload.eventClass}`);
      }
    }
  }
}

Architecture Rationale:

• Transactional boundary ensures atomicity. Partial writes corrupt audit trails.
• Registry validation prevents schema drift. Unknown keys are rejected before they pollute the database.
• Batch insertion minimizes round trips. Production gateways must handle hundreds of rows per second.
• Caching active registry entries reduces lookup latency during high-throughput ingestion.

Step 3: Reporting Strategy

EAV requires pivoting for tabular reports. Push this work to the database layer using conditional aggregation.

SELECT 
  se.batch_ref,
  se.recorded_at,
  MAX(CASE WHEN ep.metric_key = 'output_qty' THEN ep.metric_value END)::INT AS output_qty,
  MAX(CASE WHEN ep.metric_key = 'defect_count' THEN ep.metric_value END)::INT AS defect_count,
  MAX(CASE WHEN ep.metric_key = 'inspection_result' THEN ep.metric_value END) AS result,
  MAX(CASE WHEN ep.metric_key = 'spec_version' THEN ep.metric_value END) AS spec_version
FROM system_events se
JOIN event_properties ep ON ep.event_id = se.event_id
WHERE se.batch_ref LIKE 'PROD-%'
  AND se.recorded_at >= '2024-01-01T00:00:00Z'
GROUP BY se.batch_ref, se.recorded_at
ORDER BY se.recorded_at DESC;

Architecture Rationale:

• CASE aggregation avoids self-joins, which degrade performance on large EAV tables.
• Explicit casting (::INT) restores type safety at query time.
• Materialized views should wrap this query for dashboard consumption, refreshing on a schedule rather than computing live.

Pitfall Guide

1. Type Safety Erosion

Explanation: Storing all values as TEXT breaks downstream calculations. Engineers assume numeric fields are numeric, leading to runtime errors when non-numeric strings slip through. Fix: Enforce expected_type in metric_registry. Cast explicitly in queries. Add application-level validation that rejects values failing Number() or Date() parsing before insertion.

2. Index Neglect on High-Cardinality Keys

Explanation: EAV tables grow rapidly. Without proper indexing, WHERE metric_key = 'X' triggers full table scans. Fix: Create composite indexes: CREATE INDEX idx_event_props_lookup ON event_properties(event_id, metric_key); Add partial indexes for frequently filtered keys: CREATE INDEX idx_defect_events ON event_properties(event_id) WHERE metric_key = 'defect_count';

3. Over-Pivoting in Application Code

Explanation: Fetching raw key-value pairs and pivoting in JavaScript/Python consumes memory and CPU. It scales poorly beyond thousands of rows. Fix: Push pivoting to SQL using FILTER clauses or CASE aggregates. For dashboards, use materialized views with scheduled refreshes. Never pivot in the API layer.

4. Temporal Versioning Drift

Explanation: Forgetting to set valid_to when updating a metric definition causes overlapping validity periods. Historical queries return ambiguous results. Fix: Implement a stored procedure or application service that automatically closes the previous version (UPDATE metric_registry SET valid_to = CURRENT_DATE - INTERVAL '1 day' WHERE ...) before inserting the new one. Add a EXCLUDE constraint using btree_gist to prevent overlapping date ranges per key.

5. Missing Row-Level Security (RLS)

Explanation: EAV exposes all keys to all users. QA sees Engineering's internal thresholds. Manufacturing sees QA's scoring algorithms. Compliance auditors require strict data isolation. Fix: Enable PostgreSQL RLS. Create policies that filter event_properties based on origin_system or operator_id roles. Alternatively, implement application-level scoping that injects WHERE origin_system = $1 into every query based on the authenticated user's department.

6. Gateway Validation Gaps

Explanation: Accepting malformed data to "keep things moving" pollutes the registry. Dirty data breaks audit reports and forces costly cleanup scripts. Fix: Treat the gateway as a strict contract. Validate payload structure, enforce required keys, check value ranges against metric_registry metadata, and reject non-conforming rows with detailed error logs. Never silently drop invalid data.

7. Ignoring Batch Size Limits

Explanation: Inserting thousands of properties in a single transaction locks tables, exhausts connection pools, and triggers deadlocks under concurrent load. Fix: Chunk inserts into batches of 500-1000 rows. Use UNNEST with array parameters for bulk operations. Monitor pg_stat_activity for long-running transactions and implement backpressure in the gateway.

Production Bundle

Action Checklist

• Define initial metric_registry entries for all active departments before deployment
• Create composite indexes on (event_id, metric_key) and partial indexes for high-frequency filters
• Implement temporal versioning logic that auto-closes valid_to on updates
• Configure PostgreSQL Row-Level Security policies for departmental data isolation
• Build materialized views for all dashboard queries; schedule refreshes via cron or pg_cron
• Add gateway validation that rejects unregistered keys and enforces type constraints
• Set up structured logging for ingestion failures, including payload snapshots for debugging
• Run load tests simulating peak batch windows; verify connection pool limits and batch chunking

Decision Matrix

Scenario	Recommended Approach	Why	Cost Impact
Rapidly changing specs, multiple departments, strict audit requirements	EAV + Versioned Registry + Gateway	Decouples schema from business logic; preserves historical context; absorbs format diversity	Higher initial dev cost; lower long-term migration cost
Stable domain, fixed reporting columns, high-frequency real-time queries	Fixed Relational Schema	Optimized indexing; native type safety; simpler query planning	Low dev cost; high migration cost when requirements change
High-volume telemetry, semi-structured payloads, flexible querying	JSONB Document Store	Native format acceptance; GIN indexing; avoids EAV pivot overhead	Moderate query complexity; harder to enforce strict audit trails
Compliance-heavy environment requiring strict data isolation	EAV + RLS + Application Scoping	Row-level policies enforce departmental boundaries without duplicating tables	Requires careful policy design; audit-friendly

Configuration Template

-- Enable required extensions
CREATE EXTENSION IF NOT EXISTS btree_gist;

-- Temporal exclusion constraint to prevent overlapping metric definitions
ALTER TABLE metric_registry 
  ADD CONSTRAINT no_overlapping_versions 
  EXCLUDE USING gist (
    event_class WITH =,
    metric_key WITH =,
    daterange(valid_from, COALESCE(valid_to, '9999-12-31'), '[]') WITH &&
  );

-- Indexes for production workloads
CREATE INDEX idx_system_events_batch ON system_events(batch_ref);
CREATE INDEX idx_system_events_time ON system_events(recorded_at DESC);
CREATE INDEX idx_event_props_composite ON event_properties(event_id, metric_key);
CREATE INDEX idx_metric_registry_active ON metric_registry(event_class, metric_key) 
  WHERE valid_to IS NULL;

-- Row-Level Security setup (example for QA isolation)
ALTER TABLE system_events ENABLE ROW LEVEL SECURITY;
ALTER TABLE event_properties ENABLE ROW LEVEL SECURITY;

CREATE POLICY qa_isolation ON system_events
  USING (origin_system = 'qa_portal' OR current_user = 'admin');

CREATE POLICY qa_isolation_props ON event_properties
  USING (EXISTS (
    SELECT 1 FROM system_events se 
    WHERE se.event_id = event_properties.event_id 
    AND (se.origin_system = 'qa_portal' OR current_user = 'admin')
  ));

Quick Start Guide

1. Initialize Schema: Run the Configuration Template SQL against a PostgreSQL 14+ instance. Verify extensions and constraints are active.
2. Seed Registry: Insert baseline metrics for each department into metric_registry. Set valid_from to today's date and leave valid_to null.
3. Deploy Gateway: Instantiate the DataGateway class with a connection pool. Configure environment variables for database credentials and batch size limits.
4. Test Ingestion: Send a sample payload via cURL or Postman. Verify transaction commits, property insertion, and registry validation. Check logs for rejected keys.
5. Validate Reporting: Execute the pivot query against test data. Confirm casting works, grouping aligns with batch references, and performance stays under 200ms for 10k rows. Schedule a materialized view refresh if targeting dashboards.

The EAV pattern is not a silver bullet. It is a deliberate architectural choice that prioritizes adaptability over static optimization. When deployed with strict validation, temporal versioning, and proper indexing, it transforms schema churn from a deployment blocker into a metadata update. The gateway layer ensures departments keep working their way while IT maintains a single source of truth. Audit compliance becomes a query, not a negotiation.