Storage

Postgres as the system of record

CTI graphs are wide and shallow. A realistic query walks one to three hops — indicator → malware → actor → campaign — not deep pathfinding across a sprawling network. The scaling pain in a threat intel feed is ingest volume, deduplication, and lookup latency; it is not traversal depth.

A dedicated graph database solves a problem Hoard CTI does not have yet, so Postgres is the primary store.

Split the hot path early

Two workloads do not belong in the same tables as relational data, and separating them later is far more painful than separating them at the start.

Sightings live in time-partitioned Postgres, which holds up comfortably to a few hundred million rows. Beyond that the answer is ClickHouse — compression on IOC data is excellent, and the access pattern is exactly what a columnar store is built for.

Lookups are served from Redis/Valkey, keyed by the normalised indicator value and holding the current verdict. Postgres should never be answering this question at query volume.

Canonicalisation is the highest-value decision

Normalise at ingest, never at query time. That means handling defanged IPs, punycode domains, hash casing, and URL trailing slashes once, on the way in.

The normalised value is then protected by a database-level guarantee:

UNIQUE (type, canonical_value)

Schema ownership

The core constraint: two languages, one database, exactly one source of truth.

• Drizzle owns the schema. A TypeScript schema definition goes through drizzle-kit generate to produce plain SQL migrations, which drizzle-kit migrate applies.
• Go never runs migrations. The aggregators are pure readers and writers of a schema that already exists.
• Go tooling derives from the migrated database. A schema change that breaks a Go query fails codegen in CI, rather than failing at 3am in the middle of an ingest run.

Watch items

Casing. Set Drizzle's casing option explicitly — camelCase in TypeScript, snake_case in the database — and keep it consistent. Otherwise Go codegen targets column names Drizzle never created.

Types Drizzle doesn't model natively. inet, cidr, tsvector, partitioned tables, and custom domains belong in hand-written migrations via drizzle-kit generate --custom, rather than being forced through the TypeScript schema builder. For IOC storage specifically: inet for IP addresses, and bytea or fixed-width text for hashes.

Don't consume Drizzle's meta/ snapshot JSON. It is an internal format with no stability guarantee and it reshapes on minor version bumps. The stable cross-language contract is SQL DDL, exported with drizzle-kit export.

Go database tooling

Driver: pgx v5

Use the native pgx interface rather than database/sql. That buys:

• CopyFrom, using the COPY protocol — roughly an order of magnitude faster than row-by-row upserts
• SendBatch pipelining
• Native JSONB, array, and inet handling
• LISTEN / NOTIFY

The bulk ingest pattern is CopyFrom into a staging table, followed by a single INSERT ... SELECT ... ON CONFLICT DO UPDATE.

Query layer: go-jet

go-jet is a type-safe SQL builder with code generation and automatic result mapping — explicitly not an ORM. Queries read like SQL because they are: SELECT(...).FROM(...).WHERE(...), with dot-imports giving it a native feel.

It covers the full surface needed here: DISTINCT, GROUP BY, HAVING, window functions, sub-queries, CTEs via WITH, and INSERT ... ON_CONFLICT ... RETURNING.

Being database-first is a feature rather than a limitation: Drizzle keeps owning the schema, and jet derives from it. The generator runs as a pre-build step against the migrated database.

One pool, two access paths

jet executes through database/sql, so it does not expose pgx's native CopyFrom. There is no need to choose between them — pgx v5's stdlib.OpenDBFromPool(pool) wraps a *pgxpool.Pool as a *sql.DB.

One connection pool. jet for typed relational queries, native pgx for bulk ingest.

Alternatives considered

Deferred decisions

When to add a graph database

The trigger is a concrete, user-facing query that cannot be served — for example, "every entity within four hops of this actor, ranked by path confidence."

At that point the first thing to try is Apache AGE, a graph extension that runs inside Postgres, before taking on Neo4j or Memgraph as separate infrastructure.

Full-text search over reports

Postgres FTS goes a long way. OpenSearch is worth reaching for only when relevance ranking becomes a genuine product concern rather than a nice-to-have.

Summary

Next: Ingest covers how data reaches this store, and Scrapers & Collectors covers where it comes from.