Database Schema Structure in Instatic: How data_tables and data_rows Store All Content

Instatic replaces traditional CMS tables with a unified schema built on two core tables—data_tables for collection definitions and data_rows for content instances—enabling flexible storage of posts, pages, components, and custom data via JSON-structured cells.

The CoreBunch/Instatic repository implements a polymorphic database schema structure that eliminates the need for separate tables for posts, pages, and components. Instead, every content type is defined as a record in data_tables, while all actual content lives as JSON cells in data_rows. This design allows the CMS to handle arbitrary content structures without schema migrations when adding new fields.

The Unified Data Model

Instatic's schema centers on two complementary tables that separate structure from content.

The data_tables Definition Table

The data_tables table stores metadata about every collection in the system, whether built-in or user-defined. According to src/core/data/schemas.ts, each table record contains identifying fields, routing configuration, and a JSON array defining all available fields.

Key columns include:

• id, name, slug – Primary identifiers and human-readable labels
• kind – Discriminator indicating whether the table represents a post type, generic data collection, page, component, or layout snapshot
• singularLabel, pluralLabel – Display names for the UI
• routeBase – URL prefix for frontend routing
• primaryFieldId – The default field used for titles or links
• fields – JSON array of DataField objects describing every column/field in the collection
• system – Boolean flag distinguishing built-in tables from user-created ones
• Audit columns: createdByUserId, updatedByUserId, createdAt, updatedAt

The data_rows Instance Table

The data_rows table holds the actual content instances. Each row references its parent table via tableId and stores cell values as a JSON map in the cells column.

Key columns include:

• id, tableId – Primary key and foreign key to data_tables.id
• cells – JSON object mapping fieldId to values (text, numbers, media references, etc.)
• slug, status – URL-friendly identifier and publication state
• authorUserId – Reference to the content author
• Audit JSON columns: author, createdBy, updatedBy, publishedBy (denormalized user snapshots)
• Temporal columns: createdAt, updatedAt, publishedAt, scheduledPublishAt
• deletedAt – Nullable timestamp enabling soft deletion

Schema Implementation in TypeBox

The source of truth for the database schema structure resides in src/core/data/schemas.ts, where TypeBox schemas define valid shapes for tables, rows, and fields. This approach ensures runtime validation matches the database constraints.

Table Kinds

The DataTableKindSchema union restricts the kind column to five specific literals:

// src/core/data/schemas.ts
export const DataTableKindSchema = Type.Union([
  Type.Literal('postType'),   // CMS-managed content types with built-in fields
  Type.Literal('data'),       // Arbitrary user-defined collections
  Type.Literal('page'),       // Editor-managed pages
  Type.Literal('component'),  // Editor-managed visual components
  Type.Literal('layout'),     // Saved layout snapshots
])

Row Statuses

Content lifecycle states are enforced via DataRowStatusSchema:

// src/core/data/schemas.ts
export const DataRowStatusSchema = Type.Union([
  Type.Literal('draft'),
  Type.Literal('published'),
  Type.Literal('unpublished'),
  Type.Literal('scheduled'), // Published by the scheduler later
])

Field Definitions

Each field in a table's fields array follows the DataFieldSchema discriminated union, capturing type-specific configuration for text, number, media, relation, pageTree, and other field types. The runtime constant DATA_FIELD_TYPES enumerates all supported literals for iteration and validation.

Database Migrations

The concrete SQL schema is established through migration files for SQLite (server/db/migrations-sqlite.ts) and PostgreSQL (server/db/migrations-pg.ts). Both enforce identical structures with appropriate foreign key constraints.

The data_tables creation SQL includes a check constraint on the kind column:

-- server/db/migrations-sqlite.ts
create table if not exists data_tables (
  id               text primary key,
  name             text not null,
  slug             text not null unique,
  kind             text not null check (kind in ('postType','data','page','component','layout')),
  singular_label   text not null,
  plural_label     text not null,
  route_base       text not null,
  primary_field_id text not null,
  fields_json      text not null,            -- JSON array of DataField objects
  system           integer not null default 0,
  created_by_user_id text,
  updated_by_user_id text,
  created_at       text not null,
  updated_at       text not null
);

The data_rows table enforces referential integrity and status constraints:

-- server/db/migrations-sqlite.ts
create table if not exists data_rows (
  id                 text primary key,
  table_id           text not null references data_tables(id) on delete restrict,
  cells_json         text not null,            -- JSON map of fieldId → value
  slug               text not null,
  status             text not null check (status in ('draft','published','unpublished','scheduled')),
  author_user_id     text,
  created_by_user_id text,
  updated_by_user_id text,
  published_by_user_id text,
  author_json        text,
  created_by_json    text,
  updated_by_json    text,
  published_by_json  text,
  created_at         text not null,
  updated_at         text not null,
  published_at       text,
  scheduled_publish_at text,
  deleted_at         text
);

Working with the Schema

The repository layer in server/repositories/data/ provides type-safe methods for interacting with the unified schema.

Retrieving Table Definitions

Fetch a table definition by slug to inspect its fields before querying rows:

import { tables } from '@server/repositories/data'

// Get the table record for the built-in "posts" collection
const postsTable = await tables.getBySlug('posts')

Creating Custom Collections

Define new content types by inserting into data_tables with a structured fields array:

import { tables } from '@server/repositories/data'
import { DataTableKindSchema } from '@core/data/schemas'

await tables.create({
  name: 'Products',
  slug: 'products',
  kind: DataTableKindSchema.enum.data,
  singularLabel: 'Product',
  pluralLabel: 'Products',
  routeBase: '/products',
  primaryFieldId: 'title',
  fields: [
    { type: 'text', id: 'title', label: 'Title', required: true },
    { type: 'number', id: 'price', label: 'Price', required: true },
    { type: 'media', id: 'image', label: 'Image', mediaKind: 'image' },
  ],
  system: false,
  createdByUserId: userId,
  updatedByUserId: userId,
  createdAt: new Date().toISOString(),
  updatedAt: new Date().toISOString(),
})

Managing Row Data

Insert content as JSON cells linked to the table definition:

import { rows } from '@server/repositories/data'

await rows.create({
  tableId: postsTable.id,
  cells: {
    title: 'Hello World',
    body: '<p>First post!</p>',
    slug: 'hello-world',
  },
  slug: 'hello-world',
  status: 'draft',
  authorUserId: userId,
  createdAt: new Date().toISOString(),
  updatedAt: new Date().toISOString(),
})

Query published content using the repository's search method:

import { rows } from '@server/repositories/data'

const publishedPosts = await rows.search({
  tableId: postsTable.id,
  status: 'published',
})

Soft-delete rows to maintain referential integrity while hiding content:

await rows.softDelete(rowId)   // Sets deleted_at to current timestamp

Summary

• Unified Schema: Instatic stores all content types in two tables—data_tables for definitions and data_rows for instances—eliminating the need for separate migration files per content type.
• JSON Flexibility: Cell data and field definitions use JSON columns (cells_json, fields_json), allowing dynamic schemas without altering database structure.
• Type Safety: TypeBox schemas in src/core/data/schemas.ts enforce valid table kinds, row statuses, and field configurations at runtime.
• Referential Integrity: Foreign key constraints link data_rows.table_id to data_tables.id, while check constraints validate enum values for kind and status.
• Soft Deletion: The deleted_at column enables non-destructive deletes, filtered automatically by the repository layer.

Frequently Asked Questions

What are the five table kinds supported in Instatic?

Instatic supports five table kinds defined in DataTableKindSchema: postType for CMS-managed content with built-in fields, data for arbitrary user-defined collections, page for editor-managed pages, component for visual components, and layout for saved layout snapshots. These kinds determine how the CMS renders and manages each collection.

How does soft deletion work in the data_rows table?

Soft deletion is implemented via the nullable deleted_at column in data_rows. When rows.softDelete(rowId) is called, the repository sets deleted_at to the current timestamp rather than removing the record. All query methods in server/repositories/data/rows/ automatically filter out rows where deleted_at is not null, ensuring deleted content remains recoverable without breaking foreign key relationships.

What foreign key constraints exist between data_tables and data_rows?

The data_rows table enforces a foreign key constraint on table_id that references data_tables(id) with on delete restrict. This prevents deletion of a table definition while rows still reference it, maintaining data integrity. The constraint is defined identically in both server/db/migrations-sqlite.ts and server/db/migrations-pg.ts.

Where are the TypeBox schemas for the database structure defined?

All TypeBox schemas defining the database schema structure—including DataTableSchema, DataRowSchema, DataTableKindSchema, DataRowStatusSchema, and DataFieldSchema—are located in src/core/data/schemas.ts. This file serves as the single source of truth for both TypeScript types and runtime validation logic.

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