Tag Slots Beat JSONB: How AACFlow Filters Millions of Knowledge Rows in 5ms

The flexibility is real. So is the cost. Customers who care about latency above 100ms will leave you the moment they grow.

Fixed Typed Slots: The Schema-on-Write Trade-off

AACFlow's knowledge base has documents with a metadata column for raw key/value data and a parallel set of typed slot columns:

• tag1, tag2, tag3, tag4, tag5, tag6, tag7 — short text values, case-sensitive equality.
• number1, number2, number3, number4, number5 — numeric values, range and equality queries.
• date1, date2 — timestamp values, range and equality queries.
• boolean1, boolean2, boolean3 — booleans, equality only.

When a workspace creates a knowledge base, it defines which logical fields map to which slots. "Customer ID" goes into tag1. "ARR" goes into number3. "Is internal" goes into boolean1. "Last seen" goes into date1. The mapping is stored alongside the knowledge base definition and is enforced at write time — every document inserted into that knowledge base goes through the slot transformer.

The cost is that the mapping is fixed at creation. You cannot add a 12th tag slot or a 6th number slot to an existing knowledge base — you'd migrate to a new one. The benefit is that every query becomes a typed predicate on a real column with a real index.

Composite Btree Indexes Do the Work

The single line that makes this whole architecture pay off is the index strategy. AACFlow creates composite btree indexes per knowledge base, ordered by selectivity:

The leading column scopes to the knowledge base. The remaining columns are ordered by which fields are most often filtered first (highest selectivity). For a query like WHERE knowledgeBaseId = 42 AND tag1 = 'customer-A' AND number3 > 1000 AND boolean1 = true, Postgres performs an index-only scan, returning the matching document IDs without touching the heap.

On a knowledge base with 5 million rows, that query runs in 5 milliseconds. The same query against metadata->>'customer' and (metadata->>'arr')::numeric would take seconds. The difference is not "tuning" — it is "the planner can actually use this index because the columns are typed and present."

Why Seven Tag Slots, Not Ten

The slot count is not arbitrary. We benchmarked the index size and write throughput on common shapes of knowledge base. The findings:

• Most customers use 3–5 tag slots in production. Seven is comfortable headroom without indexing waste.
• Five number slots covers "amount," "ARR," "count," "score," and "version" — the universals.
• Two date slots handle "created" and "last activity" — the only two timestamps that get filtered on. Original createdAt and updatedAt are separate.
• Three boolean slots cover "is internal," "is archived," and one customer-defined flag — anything more is rare in practice.

If you give customers fifteen of each, the average index gets bloated, write throughput drops, and the customer still uses three. Engineering the right number means looking at real workloads.

Schema-on-Write Has a Migration Story

The obvious objection to fixed slots is "but our metadata is dynamic." In practice, two patterns cover almost every case:

The first is the stable schema — when the customer knows in advance what they want to filter on, they declare it on knowledge base creation, AACFlow allocates the slots, and there are no migrations.

The second is the evolving schema — when a customer adds a new dimension six months in (e.g., "now we also need to filter by 'is GDPR-relevant'"), the platform offers a guided slot allocation. Either an unused slot picks up the new field, or — for big migrations — a background Trigger.dev v4 task copies the knowledge base into a new one with the expanded slot mapping. Read traffic switches over once the copy is complete. Customers can run this themselves; ops never has to.

The thing we explicitly do not support is "free-form filtering on arbitrary JSON paths." If you want that, you are asking for a sequential scan. We won't pretend otherwise.

The Raw Metadata Is Still There

Worth saying clearly: the metadata JSONB column still exists on every document. Tag slots are not a replacement for raw metadata — they are a denormalization on top of it, optimized for filtering.

You can store the original payload in metadata exactly as you received it from the source (Notion, Drive, Confluence, S3, a custom upload), keep all of it for downstream use, and only project the fields you actually filter into typed slots. The slot transformer is a small function: read the field name from the knowledge base's slot mapping, write the value into the right typed column, leave everything else in the JSONB blob.

That separation means agents and retrieval still have access to the full metadata at inference time — they just don't pay for that flexibility on the filter path.

What Filterable Metadata Should Feel Like

When a customer hits "Filter" in the AACFlow knowledge base UI, the dropdowns show the named fields they configured at creation — "Customer," "ARR," "Last Activity," "Is Internal." Behind the scenes, those map to tag1, number3, date1, boolean1. The query is a typed predicate on real columns hitting a composite btree index.

The customer never sees tag1. The platform never runs a sequential scan. Filter results come back in single-digit milliseconds even on five million rows.

This is what filterable metadata should feel like. Anything slower is a JSONB query in disguise.

Try AACFlow today and see what 5 million rows of knowledge feels like when the filter path was designed on day one.