Php Postgres Document Store Laravel Package

Technical Evaluation

Architecture Fit

• Event Sourcing/CQRS Alignment: The package is explicitly designed for Event Engine, suggesting a strong fit for architectures leveraging event sourcing, CQRS, or event-driven microservices. It abstracts document storage in PostgreSQL, enabling efficient event persistence while maintaining query flexibility.
• Schema Flexibility: As a document store, it avoids rigid relational constraints, aligning with polyglot persistence strategies where events may require dynamic attributes (e.g., nested objects, arrays).
• PostgreSQL Leverage: Exploits PostgreSQL’s JSON/JSONB, full-text search, and indexing capabilities, reducing the need for a separate NoSQL layer while retaining relational integrity for event metadata (e.g., event_id, aggregate_id, timestamp).

Integration Feasibility

• Laravel Compatibility:
  • Pros: Laravel’s Eloquent ORM and query builder can interoperate with raw PostgreSQL queries (used by this package). The BSD-3 license permits seamless integration without legal barriers.
  • Cons: The package lacks Laravel-specific abstractions (e.g., no Eloquent model integration). Direct SQL usage may require custom query builders or repositories.
• Event Engine Dependency: Assumes adherence to Event Engine’s event model (e.g., Event interface, EventStore contract). Non-compliant event structures may need adapters.
• Transaction Support: Critical for event sourcing. The package must support PostgreSQL transactions to ensure atomicity between event appends and side effects (e.g., projections).

Technical Risk

• Performance Overhead:
  • JSONB operations (e.g., ->> for path queries) can be slower than native relational queries. Benchmark with expected event volume (e.g., 10K events/sec).
  • Locking: High concurrency on the same aggregate may lead to PostgreSQL row-level locks. Test with SELECT ... FOR UPDATE SKIP LOCKED.
• Schema Evolution:
  • JSONB schema changes (e.g., adding a field) are backward-compatible but may require migrations. Forward-compatibility risks arise if the package assumes a fixed event structure.
• Debugging Complexity:
  • Lack of Laravel tooling (e.g., Tinker, Scout) for inspecting stored events. Custom CLI commands or admin panels may be needed.
• Vendor Lock-in:
  • Tight coupling to Event Engine’s abstractions could complicate swapping stores (e.g., for MongoDB) later.

Key Questions

1. Event Structure:
   • Are events in our domain fully dynamic (arbitrary JSON) or structured (known schema)? Does the package support both?
2. Query Patterns:
   • How will we project events into read models? Does the package support materialized views or require custom logic?
3. Concurrency Model:
   • Will we use optimistic concurrency (e.g., version field) or pessimistic locking? How does the package handle conflicts?
4. Observability:
   • Are there hooks for auditing (e.g., tracking who appended an event) or metrics (e.g., event store latency)?
5. Backup/Recovery:
   • How does PostgreSQL’s WAL archiving interact with event durability requirements? Are point-in-time recoveries tested?

Integration Approach

Stack Fit

• Laravel Integration Layers:

  • Option 1: Direct SQL Layer
    • Use raw PostgreSQL queries via Laravel’s DB facade or a custom repository. Example:

      DB::table('event_store')->insert([
          'aggregate_id' => $aggregateId,
          'event_data' => json_encode($event),
          'metadata' => ['timestamp' => now()],
      ]);
      

    • Pros: Minimal abstraction overhead.
    • Cons: Manual error handling, no built-in Laravel features (e.g., queues, jobs).
  • Option 2: Event Engine Adapter
    • Wrap the package in a Laravel service provider to expose an EventStore interface compatible with Laravel’s event system (e.g., dispatch()).
    • Example:

      $eventStore = app(EventEnginePostgresStore::class);
      $eventStore->append($aggregateId, $event);
      

    • Pros: Cleaner separation of concerns, reusable across projects.
    • Cons: Requires additional boilerplate.
  • Option 3: Eloquent Hybrid Model
    • Extend Eloquent to support JSONB fields for events. Useful if events need to be queried frequently.
    • Pros: Leverages Laravel’s query builder, relationships, and caching.
    • Cons: May violate Event Engine’s design intent (e.g., treating events as mutable).

• PostgreSQL Configuration:

  • Enable JSONB extension: CREATE EXTENSION IF NOT EXISTS "uuid-ossp"; CREATE EXTENSION IF NOT EXISTS "pg_trgm";.
  • Configure postgresql.conf for high concurrency:

    max_connections = 200
    shared_buffers = 4GB
    work_mem = 16MB
    

Migration Path

1. Phase 1: Proof of Concept
   • Implement a single aggregate type (e.g., Order) with the document store.
   • Compare performance with existing storage (e.g., Redis, DynamoDB) for:
     • Append latency (target: <5ms p99).
     • Query latency (e.g., GET events FOR aggregate X).
2. Phase 2: Full Adoption
   • Migrate all event-sourced aggregates to the new store.
   • Backward Compatibility: Maintain a dual-write phase if old stores are still queried.
3. Phase 3: Optimization
   • Add indexes for frequent query patterns:

     CREATE INDEX idx_event_store_aggregate_id ON event_store(aggregate_id);
     CREATE INDEX idx_event_store_type_timestamp ON event_store(event_type, occurred_on);
     

   • Implement partitioning by aggregate_id if sharding is needed.

Compatibility

• Laravel Versions:
  • Test with Laravel 10.x (PHP 8.2+) for best compatibility with PostgreSQL’s latest JSONB features.
  • Avoid PHP 8.1 or below due to potential json_encode/decode performance quirks.
• PostgreSQL Versions:
  • Requires PostgreSQL 13+ for optimal JSONB performance (e.g., jsonb_path_query).
  • Test with TimescaleDB if time-series event queries are needed.
• Event Engine Compliance:
  • Ensure all events implement EventEngine\Event interface. Use a data transfer object (DTO) layer to normalize event structures.

Sequencing

1. Pre-Integration:
   • Audit existing event storage for schema drift (e.g., missing fields, deprecated events).
   • Design a migration script to backfill historical events into the new store.
2. During Integration:
   • Start with read-only mode: Query the new store alongside the old one for validation.
   • Gradually shift writes to the new store, using feature flags to toggle storage.
3. Post-Integration:
   • Deprecate old event stores in favor of the new one.
   • Implement schema validation (e.g., using JSON Schema) for all new events.

Operational Impact

Maintenance

• Schema Management:
  • Use Laravel Migrations to manage PostgreSQL schema changes (e.g., adding indexes, altering JSONB paths).
  • Example migration:

    Schema::table('event_store', function (Blueprint $table) {
        $table->jsonb('metadata')->nullable()->after('event_data');
    });
    

• Dependency Updates:
  • Monitor the package for breaking changes (e.g., new Event Engine versions).
  • Pin dependencies in composer.json to avoid surprises:

    "event-engine/php-postgres-document-store": "1.2.*"
    

• Documentation:
  • Maintain a runbook for:
    • Rebuilding indexes.
    • Handling corrupted JSONB data (e.g., jsonb_set repairs).
    • PostgreSQL vacuum/analyze procedures.

Support

• Troubleshooting:
  • Common Issues:
    • Deadlocks: Use pg_stat_activity to identify blocking queries.
    • Slow Queries: Enable pg_stat_statements and analyze with EXPLAIN ANALYZE.
    • JSONB Errors: Validate event data with jsonb_typeof() and jsonb_path_exists().
  • Logging:
    • Log raw event data and query plans for debugging:

      \Log::debug('Appended event', ['data' => $event, 'query' => $query->toSql()]);
      

• Support Channels:
  • Leverage the package’s GitHub issues for Event Engine-specific bugs.
  • Engage the PostgreSQL community for JSONB optimizations (e.g., pgsql-general mailing list).