Headless Content Management Systems (CMS) like Strapi and Directus have transformed modern web architecture. By decoupling content authoring from frontend presentation, they allow engineering teams to power Next.js websites, mobile apps, and e-commerce platforms from a centralized API.

Under the hood, headless CMS platforms rely on relational databases - typically PostgreSQL, MySQL, or local SQLite. As content schemas grow to encompass multi-language localization, many-to-many tag relations, dynamic component zones, and nested media libraries, the underlying SQL database catalog becomes intricate.

When a content entry gets corrupted, an upload reference breaks, or a schema migration fails to apply via the CMS admin interface, developers need direct database visibility.

In this guide, we examine how Strapi and Directus structure relational tables and show how to inspect, debug, and optimize headless CMS databases on macOS.

1. How Strapi v4/v5 Maps Content to Relational Tables

Strapi translates your Content-Type Builder definitions into structured SQL tables:

  • Core Entities: A collection type named Article maps to articles.
  • Relational Link Tables: Many-to-many relationships (e.g., articles linked to tags) generate join tables named articles_tags_links.
  • Dynamic Zones & Components: Complex components generate nested tables like articles_components and components_page_blocks.
  • Media Assets: Uploaded files are cataloged in files and linked to content via files_related_morphs.

The Common Pitfall: Orphaned Uploads

When content entries are deleted via custom scripts or failed CMS transactions, rows in files_related_morphs or articles_tags_links can become orphaned. These orphaned references cause silent errors in GraphQL and REST API endpoints.

2. How Directus Manages Database State

Unlike Strapi, which wraps database schemas in its own internal configuration layer, Directus is a pure database mirror:

  • Every Directus collection is a literal SQL table.
  • Directus metadata is stored in dedicated system tables prefixed with directus_ (e.g., directus_fields, directus_collections, directus_relations).
  • Directus does not alter your relational architecture; it sits directly on top of your PostgreSQL or MySQL schema.

This direct mirror approach makes Directus exceptionally clean to manage with native SQL clients.

3. Managing Headless CMS Databases with SQLOZ (macOS Native)

While the web admin interfaces of Strapi and Directus are designed for content editors, developers need deep database control.

SQLOZ provides the ideal native Mac companion for headless CMS development.

SQLOZ Visual Schema Designer and Relational Mapping

Why CMS Developers Use SQLOZ:

  1. Visual Relationship Mapping: Strapi and Directus generate dozens of relational foreign keys and morph tables. The Visual Schema Designer maps these associations automatically on an interactive 120 FPS canvas, giving you immediate clarity on your content architecture.
  2. Instant Local SQLite Inspection: Strapi defaults to SQLite for quick local prototypes (.tmp/data.db). Drag this file directly into SQLOZ to inspect mock data or reset corrupted admin accounts in seconds.
  3. Cross-Dialect CMS Migration: Ready to promote your local Strapi/Directus SQLite prototype to a production cloud PostgreSQL instance on AWS RDS or DigitalOcean? Use SQLOZ’s Cross-Dialect Migration Tool to stream tables, foreign keys, and sequences directly without writing complex migration scripts.
  4. Hardware-Backed SSH Bastions: Securely inspect remote production CMS databases behind private cloud VPCs using encrypted SSH tunnels stored in the macOS Keychain.

4. Useful Maintenance Queries for Strapi & Directus

Finding Orphaned Media References in Strapi:

-- Identify media links pointing to non-existent upload files
SELECT 
    m.id, 
    m.file_id, 
    m.related_type 
FROM files_related_morphs m
LEFT JOIN files f ON m.file_id = f.id
WHERE f.id IS NULL;

Checking Active Directus Revision History:

-- Inspect recent revisions and audit trails
SELECT 
    id, 
    action, 
    collection, 
    item, 
    timestamp 
FROM directus_activity 
ORDER BY timestamp DESC 
LIMIT 25;

Summary

Headless CMS platforms offer tremendous content flexibility, but diagnosing data anomalies requires direct relational insight. By pairing Strapi and Directus with a native Swift database manager like SQLOZ, developers can troubleshoot content relationships, manage migrations, and maintain peak API performance.

To explore more about relational database design and schema visualization, read our guide on Understanding SQL Table Relationships and Foreign Keys and discover How to Connect to Remote Databases via SSH Tunnels on macOS.