Instatic Universal Content Model: Understanding data_tables and data_rows

Instatic stores all site-wide content in just two unified database tables—data_tables for schema definitions and data_rows for actual content records—eliminating legacy table proliferation and creating a single source of truth for pages, posts, components, and custom collections.

The CoreBunch/Instatic headless CMS implements a revolutionary flat-file approach where every piece of content, from blog posts to layout components, lives within a universal content model powered by SQLite (or Postgres). Instead of maintaining separate tables for each content type, Instatic uses a pair of linked tables defined in src/core/data/schemas.ts to handle dynamic schemas and flexible content storage.

The Two-Table Architecture

data_tables: Schema Storage

The data_tables table acts as the registry for every content collection in your site. Defined in [src/core/data/schemas.ts](https://github.com/CoreBunch/Instatic/blob/main/src/core/data/schemas.ts#L4), this table stores metadata including the collection name, slug, kind (page, component, etc.), route base, and a JSON description of custom fields.

When you initialize Instatic, the migration in [server/db/migrations-sqlite.ts](https://github.com/CoreBunch/Instatic/blob/main/server/db/migrations-sqlite.ts#L196) creates this table and seeds it with four system collections: posts, pages, components, and layouts. The fields_json column contains a TypeBox-validated schema describing each field type, allowing the CMS to render appropriate editing interfaces without hard-coded table structures.

data_rows: Content Records

The data_rows table stores the actual content instances. Each row references its parent schema via table_id and stores the content payload in its own fields_json column. This design allows completely heterogeneous data to coexist in one table while maintaining relational integrity.

Content queries join these tables to resolve routing and metadata. For example, the data fetching logic in [src/core/loops/sources/dataRows.ts](https://github.com/CoreBunch/Instatic/blob/main/src/core/loops/sources/dataRows.ts#L5) joins data_rows with data_tables to filter by content type and ensure proper slug resolution.

How the Universal Model Works

1. Define a Table – When creating a new content type (e.g., "Article"), the system inserts a record into data_tables with a JSON schema describing the expected fields.

2. Store Content – Individual items are added to data_rows, linking to the parent table via table_id and storing field values as JSON in fields_json.

3. API Abstraction – All CMS and plugin APIs interact through api.cms.content.* methods that internally read from or write to these two tables, ensuring consistent data access patterns whether you're fetching a page or a custom collection item.

4. Route Resolution – The publishing pipeline in [server/repositories/data/publish.ts](https://github.com/CoreBunch/Instatic/blob/main/server/repositories/data/publish.ts#L242) joins data_rows with data_tables using slug and route_base columns to generate public URLs and determine which rendering engine to apply.

Practical Implementation Examples

Creating a Custom Content Table

To define a new "Article" collection programmatically, insert into data_tables with a field schema:

import { uuid } from './utils'

// Insert a new table definition
await db`
  insert into data_tables (
    id, name, slug, kind, route_base,
    singular_label, plural_label,
    primary_field_id, system, fields_json
  ) values (
    ${uuid()}, 'Article', 'article', 'page',
    '/article', 'Article', 'Articles',
    ${primaryFieldId}, false,
    ${JSON.stringify({ title: 'string', body: 'text' })}
  )
`

Adding Content Records

Store actual article data by inserting into data_rows with a reference to the table definition:

// Insert a content row linked to the "Article" table
await db`
  insert into data_rows (
    id, table_id, fields_json, created_at, updated_at
  ) values (
    ${uuid()}, ${articleTableId},
    ${JSON.stringify({ title: 'My First Article', body: 'Hello world!' })},
    ${new Date()}, ${new Date()}
  )
`

Querying Published Content

Fetch all articles by joining the tables to access both content and routing metadata:

const articles = await db`
  select
    r.id, r.fields_json,
    t.slug as table_slug, t.route_base as table_route_base
  from data_rows r
  join data_tables t on t.id = r.table_id
  where t.slug = 'article' and t.deleted_at is null
`

// articles contains the raw JSON fields and routing info for each record

Key Source Files

• src/core/data/schemas.ts – Defines TypeBox schemas for data_tables and data_rows and documents the universal content model structure.

• server/db/migrations-sqlite.ts (and the Postgres equivalent) – Creates the unified tables and seeds the four system collections (posts, pages, components, layouts).

• server/repositories/data/tables.ts – Implements CRUD operations for table definitions, including schema validation and field JSON manipulation.

• server/repositories/data/rows/mutations.ts – Handles inserts, updates, and deletes for content records in data_rows.

• server/repositories/data/publish.ts – Contains the publishing logic that joins tables to resolve public routes and generate static paths.

• src/core/loops/sources/dataRows.ts – Provides the data fetching layer used by rendering loops to resolve content by type.

Summary

• Instatic replaces legacy content tables with two unified tables: data_tables for schemas and data_rows for content.
• The fields_json columns in both tables store flexible, schema-defined data structures using JSON.
• All CMS functionality routes through this model, from the api.cms.content.* API surface to the publishing pipeline in server/repositories/data/publish.ts.
• This architecture supports the four system collections (posts, pages, components, layouts) and unlimited custom content types without database migrations.

Frequently Asked Questions

What is the difference between data_tables and data_rows?

data_tables stores the blueprint for each content collection, including field definitions, routing configuration, and labels, while data_rows contains the actual content instances with their field values. Every row in data_rows belongs to a specific table definition via the table_id foreign key.

How does Instatic handle URL routing with this flat table structure?

Instatic generates URLs by joining data_rows with data_tables to access the slug and route_base columns defined in the schema record. The publishing logic in server/repositories/data/publish.ts resolves these joins to create static routes for each piece of content.

Can I create custom content types without modifying the database schema?

Yes. Because data_tables stores field definitions as JSON in the fields_json column, you can create new content types by simply inserting a new record into data_tables with your desired schema. No DDL migrations or new SQL tables are required.

Which database systems does Instatic support for this content model?

The universal content model works with both SQLite (via server/db/migrations-sqlite.ts) and PostgreSQL (via the corresponding Postgres migration file). The table structure and JSON field handling adapt to each system's capabilities while maintaining identical application logic.

{
  "mcpServers": {
    "instagit": {
      "command": "npx",
      "args": ["-y", "instagit@latest"]
    }
  }
}