Instatic’s Unified Content Model for data_tables and data_rows: A Technical Deep Dive

Instatic stores all site content in two SQLite tables—data_tables for schemas and data_rows for records—enabling a single, queryable source of truth for pages, posts, and custom collections.

The CoreBunch/Instatic project replaces traditional fragmented content tables with a unified content model for data_tables and data_rows. Instead of maintaining separate schemas for pages, posts, and components, every content type in Instatic lives in one extensible data store. This design eliminates legacy table proliferation and provides consistent APIs for routing, publishing, and data retrieval.

The Two-Table Architecture

Instatic’s universal store centers on two core tables defined in [src/core/data/schemas.ts](https://github.com/CoreBunch/Instatic/blob/main/src/core/data/schemas.ts) using TypeBox schemas. These tables are instantiated by the SQLite migration in [server/db/migrations-sqlite.ts](https://github.com/CoreBunch/Instatic/blob/main/server/db/migrations-sqlite.ts) and seeded with four system collections: posts, pages, components, and layouts.

data_tables: The Schema Registry

The data_tables table holds the blueprint for each content collection. It stores metadata including name, slug, kind, route_base, and field definitions.

• fields_json: Contains a JSON object describing custom fields for that content type (e.g., { title: "string", body: "text" }).
• Routing metadata: The slug and route_base columns drive URL generation across the site.
• System flag: Distinguishes built-in collections from user-defined tables.

data_rows: The Content Storage

The data_rows table stores the actual data for every content item.

• table_id: Foreign key referencing data_tables.id, linking each row to its schema definition.
• fields_json: Stores the actual content payload as JSON.
• Lifecycle columns: Tracks created_at and updated_at timestamps.

How the Unified Content Model Works

The system operates through a four-step pipeline that treats all content types identically:

1. Define the Table: When creating a new content type (e.g., "Article"), the system inserts a record into data_tables with the field schema encoded in fields_json.

2. Insert Rows: Content editors create items as rows in data_rows, each pointing to the appropriate table_id and storing field data in fields_json.

3. Unified Queries: All CMS and plugin APIs interact through the api.cms.content.* surface. Internally, these calls read from or write to the same two tables, regardless of content type.

4. Resolve Routes: Publishing pipelines join data_rows with data_tables using table_id to resolve public paths, as implemented in [server/repositories/data/publish.ts](https://github.com/CoreBunch/Instatic/blob/main/server/repositories/data/publish.ts).

Implementing the Content Model

The following examples demonstrate how to interact with Instatic’s unified store using the repository patterns found in [server/repositories/data/tables.ts](https://github.com/CoreBunch/Instatic/blob/main/server/repositories/data/tables.ts) and [server/repositories/data/rows/mutations.ts](https://github.com/CoreBunch/Instatic/blob/main/server/repositories/data/rows/mutations.ts).

Creating a New Content Table

To define a new "Article" collection, insert the schema metadata into data_tables:

// Define the table structure
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' })}
  )
`

This record establishes the routing base (/article) and declares the available fields for all future articles.

Adding Content Rows

Insert individual articles by referencing the table ID and storing field data:

// Create a content item 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()}
  )
`

The table_id ensures this row inherits the schema and routing rules defined in its parent table.

Querying with Unified Joins

Fetch all articles by joining data_rows with data_tables to resolve slugs and routes:

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
`

This query pattern appears in [src/core/loops/sources/dataRows.ts](https://github.com/CoreBunch/Instatic/blob/main/src/core/loops/sources/dataRows.ts), which provides the underlying data fetching for Instatic’s rendering engine.

Routing and Publishing Integration

The unified model enables dynamic route generation without hardcoded tables. When publishing content, the system:

1. Reads route_base and slug from data_tables
2. Joins with data_rows to construct public URLs
3. Applies the logic in [server/repositories/data/publish.ts](https://github.com/CoreBunch/Instatic/blob/main/server/repositories/data/publish.ts) to resolve paths for static generation

This approach ensures that custom collections receive the same routing treatment as system tables like posts and pages.

Summary

• Two-table design: data_tables stores schemas while data_rows stores content, eliminating legacy table fragmentation.
• JSON flexibility: Both tables use fields_json columns to support arbitrary field definitions without schema migrations.
• Single API surface: All content types interact through api.cms.content.* methods that query the unified store.
• Built-in routing: slug and route_base columns in data_tables drive automatic URL generation for any content collection.
• System extensibility: The four seeded tables (posts, pages, components, layouts) use the same infrastructure as user-defined collections.

Frequently Asked Questions

What is the relationship between data_tables and data_rows?

data_tables acts as the parent schema registry, while data_rows contains the child records. Each row in data_rows references its table definition via the table_id foreign key, ensuring content items follow the structure defined in their corresponding data_tables entry.

How does Instatic handle custom fields for different content types?

Custom field definitions are stored as JSON in the fields_json column of data_tables. When a content row is created, its own fields_json column stores the actual values for those fields. This schema-less approach allows dynamic content types without database schema alterations.

Where is the unified content model defined in the source code?

The TypeBox schemas are defined in [src/core/data/schemas.ts](https://github.com/CoreBunch/Instatic/blob/main/src/core/data/schemas.ts), while table CRUD operations live in [server/repositories/data/tables.ts](https://github.com/CoreBunch/Instatic/blob/main/server/repositories/data/tables.ts) and row mutations in [server/repositories/data/rows/mutations.ts](https://github.com/CoreBunch/Instatic/blob/main/server/repositories/data/rows/mutations.ts).

How does routing work with the unified model?

Routing relies on the slug and route_base columns within data_tables. During the publish phase, the system joins data_rows with data_tables to resolve the public path for each piece of content, as implemented in [server/repositories/data/publish.ts](https://github.com/CoreBunch/Instatic/blob/main/server/repositories/data/publish.ts).

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