A no-code builder without JSONB — flexibility without compromise

Look at the no-code market — Airtable, Notion, Bubble, Retool — and you’ll see the same pattern: flexibility at the cost of performance. Customer adds a field → it goes to JSONB. Searching? Table scan. Sorting? Slow. Reporting? Forget it.

At Avenit we built it entirely differently. Here’s how.

The problem with JSONB

PostgreSQL has a great JSONB type. It’s flexible, indexable (GIN), with query functions. But:

-- "Find customers in segment 'enterprise' in region 'PL'"
SELECT * FROM contractors
WHERE custom_fields->>'segment' = 'enterprise'
  AND custom_fields->>'region' = 'PL';

This query either does a sequential scan, or uses a GIN index which is nowhere near as fast as a B-tree on a plain column. At 10 million rows that’s the difference between 50ms and 5s.

Now imagine you want to:

• add a check constraint (segment IN ('enterprise', 'smb', 'startup'))
• enforce NOT NULL
• create a foreign key from a custom field
• index in compound order (segment, then region)

With JSONB — gymnastics. With real columns — just standard DDL.

Our solution: the DDL generator

In Avenit every system entity has two tables:

core_contractors        — columns we, the developers, write
core_contractors_ext    — columns added by the customer via the no-code builder

When a customer clicks “Add field customer_number as text” in the UI:

1. We validate the name (no collision with module prefixes, no reserved words).
2. We check pg_catalog to ensure the column doesn’t already exist.
3. We execute ALTER TABLE core_contractors_ext ADD COLUMN customer_number text.
4. We save the meta-definition in core_custom_field_definitions.

That’s a real ALTER TABLE — executed on that tenant’s own database (see our database-per-tenant post).

The cost I had to accept

A JOIN on every query for contractors. We have a “BASE + EXT” structure — to fetch the full contractor we do:

SELECT c.*, e.*
FROM core_contractors c
INNER JOIN core_contractors_ext e ON e.id = c.id
WHERE c.id = $1;

But INNER JOIN on PK from both sides, with the _ext row always existing (we create it with a trigger on insert into core_contractors) — it’s <1ms. Not measurable in a normal workflow.

What I get

1. Queries don’t need rewriting. Customer adds field segment_b2b, I write WHERE segment_b2b = 'enterprise' and it works.
2. Indexes go on plain columns. When a customer sorts by their field, PostgreSQL can use B-tree, bitmap filter, whatever.
3. Constraints work normally. Check, NOT NULL, foreign key, unique — everything PostgreSQL offers, available to custom columns.
4. Migrations make sense. When we ever need to change a customer’s column type — standard Drizzle / migration tooling.

When JSONB does make sense

I’m not an anti-JSONB zealot. We use it in:

• Per-tenant configuration (tenant_settings.config) — rarely read, never indexed by inner value.
• Webhook payloads (integration_events.payload) — append-only, serialize-once-deserialize-once.
• Audit snapshots (audit_log.diff) — compression, never queried by content.

JSONB shines where structure is dynamic and you don’t know in advance what’s in it. Where structure is known (because the customer defined it themselves!), a real column always wins.

The bottom line

The customer sees a standard field-builder UI — just like Airtable. But underneath, a real database schema is being generated, ready for queries, reports, BI and everything PostgreSQL has offered for 30 years.

That’s the “no-code without compromise” promise — and we keep it because we don’t lie to ourselves about the architecture.