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

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.

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 (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 (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 (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 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 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:

// 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 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.

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: 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 contains the CREATE TABLE statements for data_tables and data_rows; src/core/data/schemas.ts defines the TypeBox schemas for field types, table definitions, and status enums; and server/repositories/data/tables.ts implements the data access layer that enforces these schemas at the application level.

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