A multi-tenant product lets every customer define their own extra fields on a record, and some customers want to filter and report on those fields. How would you decide among the storage strategies for that, and what would you build?

## What the question is really testing There is no correct answer; there is a defensible one. The interviewer wants to see you separate the metadata problem from the storage problem, quantify the workload, and pick something with an exit path. ## The invariant: a field-definition catalog Every viable design needs tenant-scoped metadata: field key, display name, datatype, required flag, default, allowed values, uniqueness, and whether it is filterable. This catalog is what the UI renders, what validation enforces, what the query builder consults, and what migrations read. Teams that skip it end up with the storage layer as the only source of truth, which is exactly what makes flexible schemas rot. A single write gate - one service path that validates every write against the catalog - is what substitutes for the constraints the database can no longer give you. ## The storage candidates **Attribute-value rows.** One row per (record, field, value). Strength: per-field granularity - you can attach per-value metadata, per-field history, per-field permissions, and index `(field_id, value)` for cross-tenant "find records where field X = Y". Weakness: everything covered by the classic critique - no typing, no required-ness, pivoting on every read, blended statistics, huge row counts. Choose it when per-field auditing or per-field access control is a product requirement, not merely when fields are dynamic. **A JSON column on the record.** One document per record holding the tenant's custom fields. Strength: keeps one row per record, so reads never pivot; nesting and arrays work; an inverted index supports "documents containing this key/value" so a tenant can filter on any field they defined; a specific path can be promoted to an indexed generated column. Weakness: no foreign keys into the document, constraints only via CHECKs on extracted expressions, weak selectivity estimates for path predicates, whole-value rewrites, key names repeated per row. **Pre-created generic columns.** The table ships with, say, `cf_text_1..40`, `cf_num_1..20`, `cf_date_1..10`, and the catalog maps tenant field "Contract Value" to `cf_num_3`. Strength: real typed columns with real indexes and real statistics; queries become ordinary SQL after a metadata-driven rewrite. Weakness: a hard ceiling per type, index budget shared across tenants (an index on `cf_num_3` serves one field for tenant A and a different field for tenant B, so it must usually be a filtered/partial index per tenant), and gnarly reassignment when a tenant deletes and re-adds fields. This is the classic large-SaaS approach and it works, but it demands a mature metadata layer. **Real per-tenant DDL.** Add actual columns, or a per-tenant extension table or schema. Strength: everything the relational engine offers - types, NOT NULL, CHECK, foreign keys, statistics, plain indexes. Weakness: DDL becomes a runtime operation driven by customer actions; with thousands of tenants you get catalog bloat, backup and migration times that scale with tenant count, lock risk on hot tables, and a deployment pipeline that must tolerate per-tenant divergence. Reasonable at tens to low hundreds of tenants, especially with per-tenant schemas or databases already in place for isolation reasons; unreasonable at tens of thousands of small tenants. ## The deciding questions 1. **Scale shape.** Few large tenants favours per-tenant physical schema; many small tenants favours a shared, document-shaped tail. 2. **Query pattern.** Fields that are only displayed on a record page need no index at all - fetch the row, render the document. Fields used in list filters, saved searches, sorting, or reporting need an access path, and that is what drives you toward materialised columns. 3. **Enforcement contract.** If a custom field must be unique per tenant, or must reference another record, only real columns or per-tenant tables give you database-enforced guarantees; everything else is application-enforced and will drift. 4. **Operational maturity.** Can you run online schema changes safely, at customer-action frequency, on your largest tables? If not, designs that require runtime DDL are off the table regardless of their elegance. 5. **Reporting path.** If heavy analytics on custom fields is a selling point, the honest answer is often to keep the operational store simple and project into a per-tenant read model or warehouse where custom fields become real columns, rather than making the OLTP schema carry analytics. ## What I would build - Field-definition catalog plus one validating write gate; the catalog also declares which fields are filterable. - Custom values in a JSON column on the record, always tenant-scoped in every query. - An inverted index on the document for ad-hoc containment filters, sized and monitored because it is the expensive index. - A promotion mechanism: when a field is marked filterable, or when telemetry shows it in hot queries, materialise it as a generated column with a partial index restricted to that tenant, and let the query builder use it transparently. Nothing about the tenant-facing contract changes. - Hard limits per tenant on field count, document size, and how many fields may be filterable, published as product limits from day one - retrofitting limits is much harder than launching with them. - Telemetry per field: writes, reads, filter usage. This is what tells you which fields to promote and which tenants to move to a dedicated read model. ## Failure modes to name Unbounded field counts; per-tenant index sprawl; queries that omit the tenant predicate and therefore scan every tenant's data; migrations that must rewrite every document; and the slow slide where core product attributes get created as custom fields because that path is easier than shipping a column.