# Instatic Database Schema for Universal Content Models: A Unified Two-Table Architecture

> Discover the Instatic database schema for universal content models. Learn how its unified two-table architecture (`data_tables` and `data_rows`) simplifies headless CMS development and content management.

- Repository: [CoreBunch/Instatic](https://github.com/CoreBunch/Instatic)
- Tags: architecture
- Published: 2026-07-26

---

**Instatic stores every content type—posts, pages, components, and custom collections—in a unified database schema consisting of two core tables: `data_tables` for collection definitions and `data_rows` for individual records, enabling a flexible, headless CMS architecture without dedicated tables per content type.**

The Instatic platform (CoreBunch/Instatic) implements a universal content model approach that eliminates traditional table-per-content-type database design. By leveraging a polymorphic schema architecture compatible with SQLite and PostgreSQL, the system treats blogs, pages, components, and custom data collections as configurable entities within a standardized storage layer defined in [`server/db/migrations-sqlite.ts`](https://github.com/CoreBunch/Instatic/blob/main/server/db/migrations-sqlite.ts).

## Core Schema Architecture

Content persistence in Instatic centers on exactly two database tables. This minimalist design supports the platform's headless CMS capabilities while maintaining referential integrity and query performance across diverse content types.

### The `data_tables` Definition Layer

The `data_tables` table stores metadata about each content collection or "kind" within the system. According to the SQLite migration at [`server/db/migrations-sqlite.ts`](https://github.com/CoreBunch/Instatic/blob/main/server/db/migrations-sqlite.ts) (lines 96-113), this table defines the structure and behavior of content collections through the following schema:

- **`id`**: Primary key
- **`name`**, **`slug`**: Human-readable and URL-safe identifiers
- **`kind`**: Discriminator for content type behavior (postType, data, page, component, layout)
- **`route_base`**: URL routing configuration
- **`singular_label`**, **`plural_label`**: UI display labels
- **`primary_field_id`**: Reference to the main identifier field
- **`fields_json`**: JSON array containing the field schema definition
- **`system`**: Boolean flag indicating core system tables
- **Timestamps and soft delete**: `created_at`, `updated_at`, `deleted_at`

The `fields_json` column contains a serialized array of `DataField` objects that define the structure for all records belonging to this table. This JSON schema approach eliminates the need for DDL operations when users create new content types.

### The `data_rows` Storage Layer

Individual content entries reside in the `data_rows` table, which implements a sparse, EAV-like (Entity-Attribute-Value) pattern optimized for JSON storage. The migration at lines 124-142 defines this table with columns including:

- **`id`**: UUID primary key
- **`table_id`**: Foreign key referencing `data_tables.id`
- **`cells_json`**: JSON object containing the actual field values keyed by field ID
- **`slug`**: URL-safe identifier for the row
- **`status`**: Publication state (draft, published, unpublished, scheduled)
- **`active_version_id`**: Reference to the current version (for versioned content)
- **`scheduled_publish_at`**: ISO-8601 datetime for delayed publication
- **User references and timestamps**: `created_by`, `updated_by`, `created_at`, `updated_at`, `deleted_at`

The `cells_json` column stores typed values that correspond to the field definitions in the parent table's `fields_json`. For example, a field defined as type `media` in the schema stores a media asset ID string in the corresponding cell.

## Content Type Discrimination via `kind`

The `data_tables.kind` column distinguishes five universal content models, each triggering specific behaviors within the Instatic application layer:

**`postType`**: Blog-style posts with built-in versioning and draft/publish workflows. These include system-defined fields (title, slug, body, featuredMedia, SEO) declared in [`src/core/data/schemas.ts`](https://github.com/CoreBunch/Instatic/blob/main/src/core/data/schemas.ts) (lines 10-18).

**`data`**: Arbitrary user-defined collections without versioning capabilities. These serve as flexible buckets for custom content types like products, testimonials, or portfolio items.

**`page`**: Editor-managed site pages stored with a `pageTree` cell type. These support visual page building and nested hierarchies.

**`component`**: Reusable visual components that can be instantiated across pages. The schema treats these as template definitions separate from page instances.

**`layout`**: Saved layout snapshots added in migration 017 (lines 1048-1060), allowing users to save and reuse page arrangements across the site.

## Field Definition System

Field schemas are stored as JSON arrays in `data_tables.fields_json`, enabling dynamic schema evolution without database migrations.

### Supported Field Types

The TypeBox schema in [`src/core/data/schemas.ts`](https://github.com/CoreBunch/Instatic/blob/main/src/core/data/schemas.ts) (lines 28-44) defines the union of available field types:

- **Text fields**: `text`, `longText`, `richText`
- **Numeric and boolean**: `number`, `boolean`
- **Temporal**: `date`, `dateTime`
- **Selection**: `select`, `multiSelect`
- **Validation-constrained**: `url`, `email`
- **Relational**: `media` (asset references), `relation` (row-to-row links), `pageTree` (hierarchical content)
- **Meta**: `fieldSchema` (for field definition storage)

Each field definition in `fields_json` (lines 87-93) includes common properties: `id`, `label`, `required` (optional), `description` (optional), and `builtIn` (optional). The specific field type determines the validation rules and storage format within `data_rows.cells_json` (lines 75-84).

## Publishing Workflow and Row Status

The `data_rows.status` column implements a state machine controlling content visibility. The `DataRowStatus` enum defined in [`src/core/data/schemas.ts`](https://github.com/CoreBunch/Instatic/blob/main/src/core/data/schemas.ts) includes four states: `draft`, `published`, `unpublished`, and `scheduled`.

### Scheduled Publishing Implementation

When `status` equals `'scheduled'`, the system utilizes the `scheduled_publish_at` column added by migration 006 (lines 125-133). This ISO-8601 datetime field integrates with Instatic's publishing scheduler to automatically transition rows to `published` status when the specified time arrives, enabling time-based content releases without manual intervention.

## Repository Layer Implementation

Developers interact with this unified schema through the repository layer at [`server/repositories/data/tables.ts`](https://github.com/CoreBunch/Instatic/blob/main/server/repositories/data/tables.ts) rather than writing raw SQL. This abstraction provides CRUD operations, pagination, and search functionality while transparently handling the relationship between `data_tables` definitions and `data_rows` storage.

The repository pattern ensures that calling code does not need to distinguish between "posts," "pages," or custom collections—all operations use the same interface regardless of the underlying `kind` or field structure.

### Practical API Usage

The following examples demonstrate creating a custom collection and inserting records via the universal endpoints:

```typescript
// Create a new custom collection (Products table)
await apiRequest('/admin/api/cms/data/tables', {
  method: 'POST',
  json: {
    name: 'Products',
    slug: 'products',
    kind: 'data',
    singularLabel: 'Product',
    pluralLabel: 'Products',
    primaryFieldId: 'title',
    fields: [
      { type: 'text', id: 'title', label: 'Title', required: true, builtIn: true },
      { type: 'number', id: 'price', label: 'Price', format: 'currency', currency: 'USD' },
      { type: 'media', id: 'image', label: 'Image', mediaKind: 'image' },
    ],
  },
});

// Insert a row into the Products collection
await apiRequest('/admin/api/cms/data/rows', {
  method: 'POST',
  json: {
    tableId: 'products',
    cells: {
      title: 'Acme Widget',
      price: 19.99,
      image: 'media-12345',
    },
  },
});

```

These API calls target the generic content endpoints that operate directly on `data_tables` and `data_rows`, illustrating the schema's universal nature where no dedicated "products" table exists in the underlying database.

## Summary

- **Unified two-table architecture**: Instatic uses `data_tables` for schema definitions and `data_rows` for all content storage, eliminating table proliferation.
- **Five content kinds**: The `kind` enum (`postType`, `data`, `page`, `component`, `layout`) distinguishes behavior while sharing physical storage.
- **JSON-based schemas**: Field definitions in `fields_json` and values in `cells_json` enable dynamic content modeling without DDL changes.
- **Built-in publishing workflow**: The `status` column and `scheduled_publish_at` support draft, published, and scheduled content states.
- **Repository abstraction**: The [`server/repositories/data/tables.ts`](https://github.com/CoreBunch/Instatic/blob/main/server/repositories/data/tables.ts) layer provides type-safe access without exposing the underlying polymorphic structure.

## Frequently Asked Questions

### How does Instatic handle different content types without separate database tables?

Instatic utilizes a **polymorphic database schema** where a single `data_rows` table stores all content entries regardless of type. The `data_tables` table defines the schema and behavior for each collection through JSON field definitions and a `kind` discriminator. This EAV pattern allows the system to support blogs, pages, components, and custom collections without creating new physical tables for each content type, as implemented in [`server/db/migrations-sqlite.ts`](https://github.com/CoreBunch/Instatic/blob/main/server/db/migrations-sqlite.ts).

### What field types are available in Instatic's universal content model?

The schema supports **14 distinct field types** defined in [`src/core/data/schemas.ts`](https://github.com/CoreBunch/Instatic/blob/main/src/core/data/schemas.ts): `text`, `longText`, `richText`, `number`, `boolean`, `date`, `dateTime`, `select`, `multiSelect`, `url`, `email`, `media`, `relation`, and `pageTree`. Each type determines the validation rules and JSON storage format within `data_rows.cells_json`, enabling everything from simple text inputs to complex relational links between content entries.

### How does the publishing scheduler interact with the database schema?

The schema supports scheduled publishing through the `scheduled_publish_at` datetime column (added in migration 006) and the `DataRowStatus` enum. When a row's `status` is set to `'scheduled'`, the system checks `scheduled_publish_at` against the current time to automatically transition the record to `'published'` status. This mechanism enables time-based content releases while maintaining the unified `data_rows` table structure for all content states.

### Where are the database schemas defined in the Instatic source code?

The database schema is defined across three primary locations: **[`server/db/migrations-sqlite.ts`](https://github.com/CoreBunch/Instatic/blob/main/server/db/migrations-sqlite.ts)** contains the CREATE TABLE statements for `data_tables` and `data_rows`; **[`src/core/data/schemas.ts`](https://github.com/CoreBunch/Instatic/blob/main/src/core/data/schemas.ts)** defines the TypeBox schemas for field types, table definitions, and status enums; and **[`server/repositories/data/tables.ts`](https://github.com/CoreBunch/Instatic/blob/main/server/repositories/data/tables.ts)** implements the data access layer that enforces these schemas at the application level.