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

> Explore Instatic's database schema using data_tables and data_rows to store all content flexibly. Learn how JSON cells manage posts, pages, and custom data effectively.

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

---

**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`](https://github.com/CoreBunch/Instatic/blob/main/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`](https://github.com/CoreBunch/Instatic/blob/main/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:

```typescript
// 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`:

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

```sql
-- 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:

```sql
-- 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:

```typescript
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:

```typescript
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:

```typescript
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:

```typescript
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:

```typescript
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`](https://github.com/CoreBunch/Instatic/blob/main/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`](https://github.com/CoreBunch/Instatic/blob/main/server/db/migrations-sqlite.ts) and [`server/db/migrations-pg.ts`](https://github.com/CoreBunch/Instatic/blob/main/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`](https://github.com/CoreBunch/Instatic/blob/main/src/core/data/schemas.ts). This file serves as the single source of truth for both TypeScript types and runtime validation logic.