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

> Master the Instatic database schema for custom data tables and collections. This guide details its two-table architecture, simplifying content management without migrations.

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

---

**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](https://github.com/CoreBunch/Instatic/blob/main/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`](https://github.com/CoreBunch/Instatic/blob/main/server/db/migrations-sqlite.ts):

```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`](https://github.com/CoreBunch/Instatic/blob/main/server/db/migrations-sqlite.ts) establishes the universal table structure:

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

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

```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`](https://github.com/CoreBunch/Instatic/blob/main/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`](https://github.com/CoreBunch/Instatic/blob/main/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`](https://github.com/CoreBunch/Instatic/blob/main/server/publish/publicRouter.ts) file handles this resolution, looking up the table by slug first, then fetching the corresponding row to render the content.