Instatic Database Schema for Custom Data Tables and Collections: Complete Technical Guide

Instatic stores all content—pages, posts, and custom collections—in a unified two-table architecture consisting of data_tables for schema metadata and data_rows for actual records, eliminating the need for traditional database migrations when creating new content types.

The CoreBunch/Instatic repository implements a headless CMS architecture where dynamic content types coexist with system tables through a single, flexible database schema. This design allows developers to define custom collections via JSON configuration rather than DDL statements, making schema evolution seamless and version-controlled.

The Unified Store Architecture

According to the docs/features/content-storage.md documentation, Instatic operates on a unified content store comprising two fundamental tables:

• data_tables – Stores schema metadata for every collection, including field definitions and routing configuration
• data_rows – Stores the actual content records, linked to their parent table via foreign key

This approach means pages, blog posts, visual components, and user-defined collections all share the same physical storage structure, differentiated only by metadata flags and JSON-encoded field schemas.

Table Kinds and Type Classification

The data_tables.kind column uses a SQLite CHECK constraint to enforce five distinct collection types:

Kind	Purpose
postType	Custom content types (e.g., Products, Events)
page	Routable pages with URL paths
component	Visual components (VCs) stored as data rows
layout	Template layout definitions
data	Generic key-value tables for specialized use

This enum is enforced at the database level in server/db/migrations-sqlite.ts:

kind text not null check (kind in ('postType','data','page','component','layout'))

Core Schema Columns

Understanding the column structure is critical for direct database access or custom queries:

data_tables columns:

• id – Primary key (UUID)
• name – Human-readable label for admin UI
• slug – URL-friendly identifier, uniquely indexed via data_tables_slug_active_idx
• route_base – Base path for public URLs (e.g., /blog)
• fields_json – JSON array defining custom fields (DataField schema)
• system – Boolean flag protecting built-in tables from deletion

data_rows columns:

• table_id – Foreign key to data_tables.id with ON DELETE RESTRICT
• slug – Row-level identifier for public URLs
• values_json – JSON object containing the actual field values
• created_at / updated_at – Audit timestamps

Creating Custom Collections

To define a new collection, you insert metadata into data_tables with kind: 'postType'. The fields_json column accepts an array of DataField objects defining text, number, boolean, markdown, or date fields.

Database Migration

The initial schema creation in server/db/migrations-sqlite.ts establishes the universal table structure:

await db`
  create table if not exists data_tables (
    id text primary key,
    name text not null,
    slug text not null,
    kind text not null check (kind in ('postType','data','page','component','layout')),
    route_base text,
    singular_label text,
    plural_label text,
    primary_field_id text,
    system integer not null default 0,
    fields_json text,
    created_at timestamp default CURRENT_TIMESTAMP,
    updated_at timestamp default CURRENT_TIMESTAMP
  );
  create unique index if not exists data_tables_slug_active_idx on data_tables (slug);
`;

Programmatic Table Creation

Using the repository layer in server/repositories/data/tables.ts:

import { createDataTable } from '@server/repositories/data/tables';
import { DataField } from '@core/data/schemas';

const fields: DataField[] = [
  { type: 'text', id: 'title', label: 'Title' },
  { type: 'markdown', id: 'body', label: 'Body' },
  { type: 'date', id: 'publishDate', label: 'Publish Date' },
];

await createDataTable({
  name: 'Blog Posts',
  slug: 'blog',
  kind: 'postType',
  route_base: 'blog',
  singular_label: 'Post',
  plural_label: 'Posts',
  fields_json: JSON.stringify(fields),
});

CRUD Operations via Repository Layer

All data access flows through typed repository functions in server/repositories/data/, providing type safety and referential integrity checks.

Inserting Records

To add content to a custom collection, use the insertRow function from server/repositories/data/rows/mutations.ts:

import { insertRow } from '@server/repositories/data/rows/mutations';

await insertRow({
  table_id: '<data_tables.id for blog>',
  slug: 'my-first-post',
  values_json: JSON.stringify({
    title: 'My First Post',
    body: '# Hello World\nWelcome to my blog.',

    publishDate: '2024-08-02',
  }),
});

The repository automatically handles JSON serialization and foreign key validation, ensuring table_id references existing records protected by ON DELETE RESTRICT constraints.

Public URL Resolution

When data_tables.route_base is non-null, Instatic constructs public URLs using the pattern:

/<route_base>/<row_slug>

The server/publish/publicRouter.ts file implements this resolution logic by first looking up the table via its slug, then fetching the specific row, and finally rendering the appropriate template for that collection type.

System Tables vs Custom Collections

Instatic seeds four protected system tables on first run, identified by system = 1:

Table	Kind	Purpose
pages	page	Static site pages
posts	post	Legacy content type
components	component	Visual component library
layouts	layout	Page layout templates

These tables cannot be deleted via the admin UI or API. All user-created collections have system = 0 and can be modified or removed without affecting core functionality.

Schema Evolution and Field Updates

To modify a collection's structure (adding or removing fields), update the fields_json column in the corresponding data_tables row. The repository layer validates changes against TypeBox schemas, and existing rows remain valid because field values are optional unless explicitly marked required in the JSON schema. This approach eliminates the need for database migrations when evolving content models.

Summary

• Instatic implements a unified content store using data_tables for metadata and data_rows for records, as implemented in the CoreBunch/Instatic repository.
• Five table kinds (postType, page, component, layout, data) categorize collections with database-level constraints.
• Custom fields are defined declaratively via JSON in the fields_json column, supporting text, markdown, date, and other DataField types.
• The repository layer in server/repositories/data/ provides type-safe CRUD operations without requiring raw SQL.
• System tables (pages, posts, components, layouts) are protected by the system flag and cannot be deleted.
• Public URLs resolve automatically based on route_base and row slug values in server/publish/publicRouter.ts.

Frequently Asked Questions

What is the Instatic database schema for custom data tables?

Instatic uses a two-table schema where data_tables stores collection metadata (names, slugs, field definitions) and data_rows stores the actual content records. Each row in data_rows links to its parent table via a foreign key, allowing unlimited custom collections without creating new database tables.

How does Instatic handle custom fields without database migrations?

Custom fields are stored as JSON in the fields_json column of data_tables, defining field types, labels, and validation rules. When you add fields to a collection, you update this JSON column rather than altering the database schema. The values_json column in data_rows stores the actual data as JSON, making the schema dynamically extensible.

What are the system tables in Instatic and can they be deleted?

The four system tables are pages (kind: page), posts (kind: post), components (kind: component), and layouts (kind: layout). These are protected by the system = 1 flag in their data_tables records and cannot be deleted through the API or admin interface, ensuring core functionality remains intact.

How are public URLs generated for custom collections in Instatic?

Public URLs follow the pattern /<route_base>/<row_slug>, where route_base comes from the data_tables record and slug comes from the specific row in data_rows. The server/publish/publicRouter.ts file handles this resolution, looking up the table by slug first, then fetching the corresponding row to render the content.

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