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

> Explore Instatic's unified content model using SQLite data_tables and data_rows. Discover how Instatic creates a single source of truth for all your site content, enabling powerful querying for pages and collections.

- Repository: [CoreBunch/Instatic](https://github.com/CoreBunch/Instatic)
- Tags: deep-dive
- Published: 2026-07-31

---

**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)](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)](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)](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)](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)](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`:

```typescript
// 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:

```typescript
// 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:

```typescript
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)](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)](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)](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)](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)](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)](https://github.com/CoreBunch/Instatic/blob/main/server/repositories/data/publish.ts).