[DISCUSSION] Scalable Architecture for Dynamic Custom Post Types (CPTs) 🧱

[JMauclair]

We want to allow users (via the admin UI) to create their own content types in Klickbee CMS:

• Define a new "content model" (e.g. Products, Events, Testimonials)
• Add custom fields (text, image, select, etc.)
• Perform full CRUD on entries
• Possibly expose entries via static or dynamic routes

We're currently evaluating the best data architecture to make this both flexible and scalable.

---

🗉 Option 1: One table per CPT

Like WordPress — each model results in a separate table.

✅ Pros

• Strong typing possible
• Fast read/write

❌ Cons

• Requires migrations per model
• Hard to scale dynamically
• Complicates multi-tenant setups

---

🧩 Option 2: cpt_model + cpt_entry with JSON schema

cpt_model: { id, name, schema (JSON), user_id }
cpt_entry: { id, model_id, data (jsonb), created_at }

✅ Pros

• Fully dynamic
• No migrations needed
• Clean for multi-tenant use cases

❌ Cons

• No static typing at compile time
• Requires a solid admin UI to manage fields & validation
• Harder to index/filter values inside data

---

⚙️ Option 3: Structured fields & values

A bit like Strapi: fields defined in their own table, values stored per-entry.

cpt_field: { id, model_id, name, type, required }
cpt_entry: { id, model_id }
cpt_entry_value: { entry_id, field_id, value }

✅ Pros

• More structure
• Easier to validate/filter/query

❌ Cons

• More complex to manage
• More joins
• Less intuitive for frontend consumption

---

🤔 Questions for discussion

• Have you implemented dynamic CPT systems before?
• What’s your preferred DB structure for content builders?
• Any lessons learned using JSON vs relational approaches?
• Thoughts on performance with jsonb in PostgreSQL?
• How would you approach static/dynamic route generation from user-defined models?

Looking forward to your ideas and feedback! 🙏

[nadmax]

Or, you can go for the option below.

Option 4: JSON Schema + Virtual Indexing Layer

Actually, it is a hybrid option between option 2 and 3:

• You store entries as jsonb
• You maintain a virual field index for searchable, and filterable fields

Schema's exemple:

cpt_model (id, name, schema_json, user_id)
cpt_entry (id, model_id, data_jsonb, created_at)
cpt_entry_index (entry_id, model_id, field_name, value_text, value_num, value_date)
cpt_route (id, model_id, entry_id, path, is_static)

Pros

• Fully dynamic schema, no migrations needed
• Easy to scope models and entries per tenant
• jsonb allows flexible content modeling and easy frontend consumption
• Selective indexing supports filtering and querying where needed
• Clean integration with static/dynamic route generation
• Schema JSON enables automatic form generation in admin UI

Cons

• Requires index sync logic for filterable fields
• Validation and relationships must be handled at the application layer
• Querying deeply nested fields can be inefficient without planning
• Slightly more complex than pure jsonb or pure relational setups

[JMauclair]

Nice idea, what are value_num, value_date for ?

[nadmax]

Nice idea, what are value_num, value_date for ?

Good question! value_num and value_date are used to support filtering and sorting on fields with numeric or date types.

Since all content is stored in jsonb, we extract indexable fields into cpt_entry_index for better query performance.
To support type-specific operations (range filters, sorting), we store the same value in the appropriate column based on its type:

• value_text for string-based fields (title, slug, etc.)
• value_num for numeric fields (price, quantity, etc.)
• value_date for date/time fields (published_at, event_date, etc.)

Only one of them is typically populated per row, depending on the field type.
This avoids type-casting at query time and enables efficient indexes.

[JMauclair]

But doesn't value_text, value_num, or value_date a duplicate of data_jsonb in cpt_entry ?

[JMauclair]

@nadmax or what type of data will be storing data_jsonb ?