DEV Community

阿忠
阿忠

Posted on

Ghost CMS has 12 tables and 80+ columns. What does each one mean?

#opensource #sql #ai #database

Ghost is one of the most popular open-source blogging platforms — 30K+ GitHub stars, powering millions of blogs.

Its database has evolved over 10 years. From posts and users in 2015 to v6.0's big cleanup in 2025: 8 migrations, 12 tables, 80+ columns, plus Stripe payments, email tracking,
and a membership system.

The SQL migration files are all there in migrations/. But not a single comment explains what any field actually means.

Can you answer these questions?

Here's a migration from Ghost's codebase:

  ALTER TABLE posts DROP COLUMN created_by;
  ALTER TABLE posts DROP COLUMN updated_by;
  ALTER TABLE users DROP COLUMN created_by;
  ALTER TABLE users DROP COLUMN updated_by;
  ALTER TABLE tags DROP COLUMN created_by;
  ALTER TABLE tags DROP COLUMN updated_by;
  ALTER TABLE settings DROP COLUMN created_by;
  ALTER TABLE settings DROP COLUMN updated_by;
  ALTER TABLE members DROP COLUMN created_by;
  ALTER TABLE members DROP COLUMN updated_by;
Enter fullscreen mode Exit fullscreen mode

Safe cleanup or dangerous destruction? Why remove created_by? What replaced it?

Or take the posts table:

  `type` VARCHAR(50) DEFAULT 'post'
  `status` VARCHAR(50) DEFAULT 'draft'
  `visibility` VARCHAR(50) DEFAULT 'public'
  `locale` VARCHAR(6)
Enter fullscreen mode Exit fullscreen mode

What are the valid values for status? Is it draft/published or active/inactive? When did visibility gain a paid tier? Why is locale only 6 characters — can it store
zh-CN?

Nobody can answer these off the top of their head. The answers are scattered across source code, git history, and the fading memory of whoever wrote the migration three years ago.

One command. Thirty seconds. 

  npx schemalog generate
  npx schemalog dict
Enter fullscreen mode Exit fullscreen mode

8 migrations analyzed. Here's what comes out.

1. Migration Changelog

A human-readable summary of every migration, with risk levels:

| # | Timestamp | Change | Risk |
|---|-----------|--------|------|
| 1 | 2015-09-15 | Create posts | 🟢 safe |
| 2 | 2015-09-15 | Create users | 🟢 safe |
| 3 | 2015-09-15 | Create tags + posts_tags | 🟢 safe |
| 4 | 2016-04-22 | Create settings + seed data | 🟢 safe |
| 5 | 2019-05-22 | Create members (subscriptions) | 🟢 safe |
| 6 | 2020-06-15 | Stripe payments + subscriptions | 🟢 safe |
| 7 | 2023-05-23 | Email tracking system | 🟢 safe |
| 8 | 2025-07-22 | Drop 10 deprecated columns | 🔴 danger |

That last migration gets flagged red automatically. Ten columns dropped across five tables. In a decade-old project, that kind of change shouldn't pass silently.

2. Per-Migration Analysis 

  Analyzing: 20250722191500_v6_cleanup_deprecated_fields.sql
    🔴 danger  — Removes deprecated created_by and updated_by columns
                 from posts, users, tags, settings, and members,
                 plus deletes obsolete AMP settings.
    ⚠️  10 local danger(s) detected
Enter fullscreen mode Exit fullscreen mode

Not just what changed — why. The AI infers that created_by / updated_by were replaced by Ghost 6.0's Action Log system. All from reading the SQL.

3. Complete Data Dictionary

Every table, every column, explained. Here's the posts table:

| Column | Type | Description |
|--------|------|-------------|
| id | VARCHAR(24) | Primary key, unique identifier |
| uuid | VARCHAR(36) | Universally unique identifier |
| title | VARCHAR(2000) | Post title |
| slug | VARCHAR(191) | URL-friendly version of title |
| mobiledoc | TEXT | Content in Mobiledoc rich-text format |
| html | TEXT | Rendered HTML content |
| plaintext | TEXT | Plain text version of content |
| featured_image | VARCHAR(2000) | URL of the featured image |
| featured | TINYINT(1) | Whether the post is featured (0/1) |
| type | VARCHAR(50) | post or page |
| status | VARCHAR(50) | draft, published, scheduled |
| visibility | VARCHAR(50) | public, members, paid |
| locale | VARCHAR(6) | Language code: en, zh-CN |
| author_id | VARCHAR(24) | Foreign key to users.id |
| published_at | DATETIME | When the post was published |

80+ columns across 12 tables. Every single one gets an AI-inferred description, type explanation, and enum values where applicable.

This isn't Ghost's problem. It's yours.

Ghost is a well-designed project. But even the best projects have database documentation that slowly rots.

Your project is no different. Every release adds more .sql files to migrations/. Three months later:

  • What's the difference between user_status and account_state?
  • Is legacy_score still used anywhere?
  • Who ran that DROP TABLE and did anyone notice?

This isn't about lacking someone to write documentation. Humans shouldn't be doing this job.

SQL migrations are already machine-readable. Translating them into human-readable docs is exactly what AI should be doing.

Schemalog — your database docs stop lying 

  npm install -g schemalog

  cd your-project
  schemalog init --dir migrations
  schemalog generate       # → SCHEMA.md
  schemalog dict           # → DATA_DICTIONARY.md
Enter fullscreen mode Exit fullscreen mode

Supported formats: YYYYMMDD_description.sql or YYYYMMDDHHMMSS_description.sql
Works with Supabase, Drizzle, Flyway, raw SQL — whatever produces timestamped migration files.

AI Providers: DeepSeek or OpenAI (GPT-4o-mini). A typical analysis costs a fraction of a cent.

Incremental caching: Second run costs zero — only new or changed migrations hit the AI.

Danger detection: DROP TABLE, DROP COLUMN, ALTER TYPE flagged locally with zero latency.

GitHub Action: PR contains a .sql migration → auto-comments the changelog. No more digging through migration folders during code review.

Try it out:

  npm install -g schemalog
Enter fullscreen mode Exit fullscreen mode

npm: npmjs.com/package/schemalog

Questions or feedback? Drop a comment below.

First release. All feedback welcome.

Top comments (0)