What I Learned Separating Supabase Data from Hindsight Memory

What I Learned Separating Supabase Data from Hindsight Memory

Most competitive intelligence tools have the same blind spot: they know what happened right now, but they have no memory of what happened before. You can scrape a competitor's press releases today and get a clean list of announcements. Ask the same tool six months later "has their product focus shifted?" and you get nothing useful — because the context of those earlier events is gone.
I built a system to close that gap. The architecture is straightforward: a Node.js backend that ingests competitor events, stores them in Supabase with row-level security, and then writes every event into a long-term memory bank using Hindsight by Vectorize. On the query side, a reasoning agent backed by Google Gemini pulls from both the structured relational data and the Hindsight memory bank to answer questions like "what patterns has AgroTech AI shown over the past six months?" with actual evidence — not hallucinations.
This post explains the architecture decisions that made this work, the specific problems I ran into, and why Hindsight turned out to be the right primitive for this use case.

The Problem with Stateless Intelligence

Before building this, I tried the obvious approach: call an LLM with a dump of the latest events and ask it to summarize. It works. It is also completely useless for longitudinal analysis. If a competitor quietly pivots their product strategy over three quarters — accelerating hiring in one area, shifting partnership types, repricing — no single query captures that. You need something that retains the full sequence and can be asked about it later.
The standard answer is RAG. Embed your documents, store vectors, retrieve at query time. I have built enough RAG pipelines to know their failure modes: the embedding model collapses semantically distinct events into nearby vectors, temporal relationships get lost entirely, and you end up re-ranking by cosine similarity when what you actually want is reasoning about chronological causality.
Hindsight solves a more specific problem than generic RAG. It is designed around the concept of agent memory — persistent, structured, temporally-aware storage that an agent can query using natural language without you having to manage embedding strategies or retrieval tuning. The three primitives it exposes are retain, recall, and reflect. That maps almost exactly to what a competitive intelligence workflow needs.

Architecture Overview

The system has three layers: Ingestion → Memory → Reasoning.
External Sources (RSS, Web)
↓
Ingestion Pipeline
↓
Supabase (competitor_events table) ← RLS-protected
↓
Hindsight Retain → Memory Bank: "competitive-intelligence"
↑ ↑
Gemini + Agent Hindsight Recall / Reflect
↑

User Query

The backend is a plain Express server. I deliberately kept it framework-light because the complexity lives in the service layer, not the HTTP routing. The frontend is a vanilla JS SPA served as static files from the same Express process — no separate build step, no bundler. This made iteration fast and keeps the deployment surface minimal.

Supabase as the Source of Truth

Every competitor event lives in a competitor_events table in Supabase. The schema captures the fields you would expect — competitor ID, date, category, title, description, source — plus three Hindsight-specific columns: hindsight_retained, hindsight_retained_at, and hindsight_memory_ref.
Row-level security is enabled. The frontend uses the publishable anon key; the backend uses the service-role key kept strictly in .env and never in any browser-accessible variable. This matters because the competitor database is internal proprietary research — you do not want it publicly readable.
The hindsight_retained boolean serves as an idempotency flag. When an event is first written to Supabase, hindsight_retained is false. The retain step checks this flag before calling Hindsight, marks it true after a successful retain, and records the memory reference. This means the bulk sync operation — which runs at server startup to catch any backlog — is safe to run repeatedly without creating duplicate memories.

The Retain Step: Building Rich Memories

The most important design decision in the Hindsight integration is what you put into a memory. If you retain bare JSON blobs, recall becomes a keyword matching exercise. If you retain rich natural language with embedded context, Hindsight's multi-strategy retrieval — which combines semantic similarity, keyword extraction, entity graphs, and temporal reasoning — actually works.
Here is the function that converts a Supabase event row into a memory string:
function buildMemoryContent(event, competitorName) {
const date = event.event_date
? new Date(event.event_date).toLocaleDateString("en-US", {
year: "numeric", month: "long", day: "numeric",
})
: "an unspecified date";

let content =
${competitorName} had a ${event.importance.toLowerCase()}-importance +
${event.category} event on ${date}: "${event.title}".;

if (event.description) content += ${event.description};
if (event.source_name) content += (Source: ${event.source_name}.);

content +=
[Category: ${event.category} | Importance: ${event.importance} | +
Competitor: ${competitorName} | Event ID: ${event.id}];

return content;
}

There are two things worth noting here. First, the trailing structured metadata block in brackets is not for human readers — it gives Hindsight's entity extraction reliable signal for filtering. Second, and more critically, I pass the actual event_date as the memory's timestamp rather than the current time. Hindsight uses this for temporal queries. Without it, "what did AgroTech AI do in Q1?" would retrieve memories based on when they were stored, not when the underlying events happened.

The Reasoning Pipeline

When a user submits a query, the agent runs a six-step pipeline:

1. Intent and entity extraction — resolve which competitor the query is about, detect the event category if any, and decide whether the query needs pattern reasoning (trends, shifts, comparisons) or simple fact retrieval.
2. Hindsight Recall — submit the natural-language query to the memory bank. Filter results by competitor to eliminate cross-contamination when multiple competitors are tracked. For comparison queries, recall is run separately for each competitor.
3. Supabase event retrieval — fetch structured events filtered by competitor, category, and date range. These provide precise, citable facts.
4. Trend and Pattern Engine — if the query involves patterns or comparisons, run the quantitative pipeline: calculate event metrics across time windows (30d, 90d, 6m, 12m), detect algorithmic patterns (activity surges, category pivots, sequential clusters), and call Hindsight Reflect for long-term synthesis.
5. Gemini reasoning — pass the recalled memories, structured events, and pattern data to Gemini with a strict system prompt that enforces a three-tier evidence framework: observed (direct facts), interpretation (inferred patterns), and uncertainty (explicit knowledge gaps). The output schema is fixed JSON — this prevents the LLM from going narrative when you need structured data.
6. Fallback synthesis — if Gemini is unavailable or times out, a deterministic rule-based synthesizer produces a grounded response from the same evidence. The Gemini system prompt is worth examining because the temptation is to write something generic like "you are a helpful assistant." That produces fluent nonsense. The prompt I use explicitly forbids inventing facts, funding amounts, dates, or URLs, and requires that every claim be traceable to one of three named evidence sources. The temperature is set to 0.15, not 0 — a small amount of variation helps avoid repetitive phrasing while staying grounded.

The Trend Engine: Quantitative Before AI
The pattern detection layer deliberately runs before any AI is involved. It calculates event counts across current and previous periods, identifies category surges and declines, detects sequential patterns in chronologically adjacent events, and flags dormancy. Each detected pattern carries an evidenceEventIds array populated with actual Supabase UUIDs.
When Gemini is given these algorithmic patterns to refine, it can only reference IDs that exist in the event set it was provided. The merge step validates every evidenceEventId the model returns against the full event list and discards any that do not match:
const verifiedIds = (gp.evidenceEventIds || []).filter(id => validIds.has(id));
if (verifiedIds.length > 0 && gp.observation && gp.pattern) {
// accept this pattern
}
This is where treating Gemini as a refinement step rather than a generation step pays off. The model is not being asked to identify patterns from raw text — it is being asked to add language quality and strategic framing to patterns that have already been mathematically verified. The hallucination surface is dramatically smaller.

Hindsight Reflect: The Part That Required Discipline
reflect is the most powerful Hindsight primitive and the easiest to misuse. Unlike recall, which retrieves relevant memories for a query, reflect synthesizes across the full memory bank to produce higher-order analysis — recurring themes, strategic evolutions, behavioral shifts over time. You are effectively asking it to write an analyst report from memory.
The discipline required is knowing when not to call it. In the trend engine, reflect is only invoked when Hindsight is configured and skipAI is false, and its output is treated as contextual evidence passed to Gemini — not as a standalone answer. This is important: reflect can produce authoritative-sounding text about patterns that are statistically too weak to assert confidently. Gemini's evidence framework keeps that in check by forcing the reflection into the interpretation tier rather than the observed tier.
There is also a practical API budget concern. Reflect is a heavier operation than recall. I use budget: "low" for the trend engine calls because the pattern detection has already done the heavy lifting structurally — I only need Hindsight Reflect to add temporal context that the algorithmic layer cannot see.

What I Would Do Differently
The ingestion pipeline currently calls out to web sources and parses them with a mix of structured extraction and LLM-assisted classification. The event validation and duplicate detection work reasonably well, but source reliability varies significantly. I would build a more explicit source trust scoring system — right now, a press release and a tweet can both enter the system at the same confidence tier, which is wrong.
The competitor entity resolution in the agent is currently a hardcoded list with alias matching. For a production system, this should be a resolver backed by the competitors table itself, so adding a new competitor to the database automatically extends the agent's entity vocabulary.
The frontend avoids any framework, which was the right call for moving fast. If the UI continues to grow in complexity, the state management approach — scattered initialization flags and manually managed modal visibility — will become difficult to maintain. A lightweight reactive state layer would help.

The Core Insight
Building this sharpened one thing for me: the value of persistent agent memory is not in the retrieval. Retrieval is solved. The value is in what you retain and how you structure it at write time. If you dump raw events into a memory bank, you get mediocre recall. If you spend time on the memory content format — rich natural language with embedded entity signals, accurate timestamps, source attribution — you get something that actually supports reasoning over historical sequences.
Hindsight's retain → recall → reflect model maps well to how competitive intelligence actually works in practice: capture everything, query what's relevant, synthesize what it means over time. The tooling gets out of the way and lets you focus on the data quality problem, which is where the real work is.
The code is straightforward. The architecture is not novel. But putting these pieces together in a way that produces genuinely useful longitudinal intelligence required thinking carefully about every stage of the memory pipeline — and that's a useful frame for any system where history matters.

Hindsight is open source and available at github.com/vectorize-io/hindsight. The hosted API is at hindsight.vectorize.io. For more on the broader agent memory problem, Vectorize has a useful write-up at vectorize.io/what-is-agent-memory.