Closing the Loop: What We Actually Shipped from the Roadmap

Published: 2026-06-04 Reading time: ~7 minutes Tags: sentinel-l7 architecture, compliance export, idempotency, Redis Streams

Post #05 ended with a list of half-built features and a promise to ship them. This is the delivery receipt.

Three things were on the roadmap: a compliance export endpoint, EventHorizon deep-linking, and multi-tenancy. Two shipped. One was deliberately descoped. Here’s what happened and why.

What Shipped: Compliance Export

The export endpoint is the most user-visible piece. A compliance officer needs to pull a date-filtered, risk-filtered snapshot of flagged events into a spreadsheet. The feature is a single GET /compliance/export route with three optional query parameters: from, to, and flagged.

The implementation reaches for response()->streamDownload() rather than loading the result set into memory first. This matters once the compliance_events table has months of data — building a 50k-row array in a controller before writing it is a straightforward OOM waiting to happen. Instead, the response body is streamed directly to php://output in chunks of 500 rows:

return response()->streamDownload(function () use ($query) {
    $handle = fopen('php://output', 'w');

    fputcsv($handle, [
        'id', 'source_id', 'domain', 'status', 'metric_value',
        'anomaly_score', 'routed_to_ai', 'risk_level', 'driver_used',
        'audit_narrative', 'emitted_at', 'created_at',
    ]);

    $query->chunk(500, function ($events) use ($handle) {
        foreach ($events as $e) {
            fputcsv($handle, [
                $e->id, $e->source_id, $e->domain, $e->status,
                $e->metric_value, $e->anomaly_score,
                $e->routed_to_ai ? 'true' : 'false',
                $e->risk_level ?? '',
                $e->driver_used, $e->audit_narrative,
                $e->emitted_at?->toISOString(),
                $e->created_at->toISOString(),
            ]);
        }
    });

    fclose($handle);
}, $filename, ['Content-Type' => 'text/csv']);

->chunk(500, ...) issues a series of LIMIT 500 OFFSET n queries, handing each batch to the callback before fetching the next. Laravel releases each batch from memory before requesting the next, so peak memory stays near-constant regardless of total row count. The fputcsv calls write each row directly into the HTTP response body as it is streamed to the client.

The flagged filter maps to routed_to_ai = true — events where the anomaly score crossed the threshold and Gemini produced an audit narrative. The default is flagged-only, matching what a compliance reviewer actually wants to see.

The filename is date-stamped at request time: compliance-events-2026-06-04.csv. Simple, but it means two exports from the same day overwrite each other in a downloads folder — which is fine for a single-user portfolio system and worth noting if this ever moves to a multi-user context.

What Shipped: Idempotent Event Persistence

The harder piece to get right wasn’t the export — it was making sure there was clean data to export.

The Axiom pipeline reads messages from the synapse:axioms Redis Stream via XREADGROUP. A message stays in Redis’s Pending Entry List (PEL) until the worker explicitly calls XACK. This creates a gap: a worker can complete all the hard work (embed, query the vector cache, call Gemini, write to Postgres), then crash before it ACKs. The next iteration — or a sibling worker — will re-deliver the same message and attempt to write the same ComplianceEvent row again.

Before this fix, ComplianceEvent::create() was called unconditionally. A crash-then-redeliver scenario would produce a duplicate row. In a compliance audit trail, a duplicate is not a minor inconvenience — it’s a correctness violation.

The fix has three layers.

Layer 1 — Application-level dedup before the AI call. At the top of process(), before routing to Gemini, there’s a cheap existence check:

if ($sourceId !== 'unknown' && ComplianceEvent::where('source_id', $sourceId)->exists()) {
    Log::info('AxiomProcessorService: duplicate source_id — skipping AI call', [
        'source_id' => $sourceId,
    ]);
    return [..., 'risk_level' => 'skipped', 'routed_to_ai' => false];
}

This handles the common re-delivery case — the worker crashed after writing to Postgres but before ACKing. On the next delivery, source_id already exists, the Gemini call is skipped entirely, and the worker ACKs cleanly. No wasted API quota.

Layer 2 — firstOrCreate at the write site. Even if the existence check passes (because this is the first delivery), the actual write uses firstOrCreate keyed on source_id:

ComplianceEvent::firstOrCreate(['source_id' => $sourceId], $fields);

First write wins. On re-delivery, the SELECT finds the existing row and returns without inserting. This handles the narrow window between the existence check and the write, and it handles any future code path that writes a ComplianceEvent directly rather than going through process().

Layer 3 — PostgreSQL partial unique index. The application guards are not atomic — firstOrCreate is a SELECT followed by an INSERT, and two workers can theoretically pass the SELECT simultaneously before either has committed. To handle the concurrent race, the database enforces the constraint independently:

CREATE UNIQUE INDEX compliance_events_source_id_unique
    ON compliance_events (source_id)
    WHERE source_id != 'unknown';

The WHERE source_id != 'unknown' exclusion deserves explanation. Some Axioms arrive without a stable identifier — a malformed payload, a misconfigured upstream emitter. These get source_id = 'unknown' and cannot be deduplicated by ID. Requiring uniqueness on 'unknown' would mean only one such event could ever be stored, which is worse than allowing occasional duplicates for the malformed case. The partial index covers the meaningful case; the application logs a warning when it sees 'unknown' so a volume spike is detectable.

When two workers do race and the DB constraint fires for the loser, a UniqueConstraintViolationException is caught in persist(), a log entry records the suppression, and the method returns normally. Both workers ACK cleanly. Without this catch, the exception would propagate, skip the ACK, and trigger an infinite retry loop.

} catch (\Illuminate\Database\UniqueConstraintViolationException) {
    Log::info('AxiomProcessorService: duplicate source_id suppressed by DB constraint', [
        'source_id' => $sourceId,
    ]);
}

The three layers defend different scenarios: the common crash-before-ACK case (Layer 1 short-circuits before Gemini; Layer 2 catches it at write time), and the rare concurrent-worker race (Layer 3 catches what Layer 2 can’t atomically prevent). Any single layer is defeatable; all three together are not.

What Was Descoped: Multi-Tenancy

Multi-tenancy was deferred to a different project entirely (ADR-0020). The reasoning: Sentinel-L7 exists as a portfolio piece demonstrating AI pipeline patterns, Redis Streams, and semantic caching. The target employers are hiring for TypeScript roles and using WorkOS or Auth0 for B2B auth. Implementing multi-tenancy in PHP/Laravel produces a portfolio signal in the wrong language for the wrong audience.

The multi-tenancy work belongs in rhizo-book, a TypeScript health appointment scheduler with existing provider/patient role distinctions that maps naturally onto WorkOS Organizations. The placeholder TODO comments in routes/web.php and DashboardController remain in place — not removed, because honest markers of known scope are more useful than a clean codebase that pretends the feature was never planned.

Sentinel-L7 stays focused on what it does well.

Still Open: EventHorizon Deep-Linking

The third item from the #05 roadmap — deep-linking compliance_events.source_id back to the originating EventHorizon event — is still open. The source_id column exists and is populated; the cross-system lookup layer that would make it a live link does not. This gets its own post when it ships.

What’s Next

The next four posts go deeper into the systems patterns that were built alongside the export and idempotency work: self-healing worker pools, graduated backpressure, LLM quality scoring, and a more detailed tour of the idempotency layers in their stream context. Those posts form a mini-series — Sentinel-L7 Systems Patterns — starting with Post #06.

The short version: what feels like plumbing in a compliance system is where the actual complexity lives.

Q: Why use chunk(500) for the CSV export rather than a cursor? A: chunk() issues paginated queries with LIMIT/OFFSET. A cursor (lazyById()) uses keyset pagination — faster and more correct for large ordered datasets because OFFSET scans grow slower as the offset increases. For a compliance export that runs infrequently and typically covers days-to-weeks of data, chunk() is fine. If this endpoint starts getting called on multi-year datasets, lazyById() is the upgrade path.

Q: Why is the early-exit EXISTS check not in ADR-0021? A: ADR-0021 deliberately rejected it for the common path: adding a DB round-trip to every single Axiom to guard against the rare re-delivery scenario is a bad trade. The early-exit check was added in a follow-up commit after the triple-defense was in place — once the value was clear (avoid wasting Gemini quota on re-deliveries) and the cost was known (one extra SELECT only on re-deliveries, not on every message). The ADR documents the decision at the time it was made; the subsequent commit reflects what was learned from watching the system run.