jasonwarrenuk/Data Ontologist

Polyglot Persistence Architecture

Architectural guidance for using multiple database technologies together, with emphasis on PostgreSQL/Supabase (relational), Neo4j (graph), and MongoDB (document). Demonstrates when to use each database type and how to integrate them effectively.

When This Skill Applies

Use this skill when:

• Designing data architecture for new projects
• Choosing between relational, graph, and document databases
• Integrating multiple database types
• Schema design decisions
• Query optimization across databases
• Migration strategies
• Questions about when to use which database paradigm

Core Principle

Start with the graph. Optimise from there.

Most real-world domains are fundamentally about relationships. The graph is the truest representation of how entities connect. Start by thinking in nodes and edges — then decide where to persist based on access patterns and consistency needs.

Right database for right data concern

Don't force all data into one database type. Use:

• Relational (PostgreSQL/Supabase) - Structured data, transactions, strong consistency
• Graph (Neo4j) - Relationships as primary concern, traversal queries
• Document (MongoDB) - Semi-structured data, flexible schemas, nested documents

Graph-First Modelling Process

Before choosing databases, model the domain as a graph:

1. Identify nodes — What are the entities? (Users, Courses, Organisations, Products)
2. Identify edges — How do they connect? (ENROLLED_IN, REPORTS_TO, PURCHASED)
3. Annotate edges — Do relationships carry data? (role, since, quantity)
4. Spot patterns — Trees? DAGs? Social graphs? Bipartite structures?
5. Then persist — Given the graph, which parts need relational guarantees, which need traversal, which need flexible schemas?

// Step 1-3: Model the domain as a graph first
(:User)-[:MEMBER_OF {role: 'admin', since: date}]->(:Organisation)
(:User)-[:ENROLLED_IN {status: 'active'}]->(:Course)
(:Course)-[:REQUIRES]->(:Course)
(:User)-[:COMPLETED {score: 0.85}]->(:Module)
(:Module)-[:BELONGS_TO]->(:Course)

Then decide:

• Users and Organisations → PostgreSQL (transactional, auth, billing)
• MEMBER_OF, ENROLLED_IN, REQUIRES → Neo4j (traversal, recommendations, paths)
• Course content, Module materials → MongoDB (flexible nested content)

When to Use Relational (PostgreSQL/Supabase)

Strong Fit

Transactional Data:

• User accounts and authentication
• Order processing
• Financial records
• Inventory management

Structured Records:

• Clear schema with defined fields
• Data fits naturally into tables
• ACID guarantees required
• Standard CRUD operations
• Strong data integrity constraints

Examples:

-- Users table
CREATE TABLE users (
  id UUID PRIMARY KEY,
  email TEXT UNIQUE NOT NULL,
  name TEXT,
  created_at TIMESTAMP DEFAULT NOW()
);

-- Orders table
CREATE TABLE orders (
  id UUID PRIMARY KEY,
  user_id UUID REFERENCES users(id),
  total DECIMAL(10,2),
  status TEXT,
  created_at TIMESTAMP DEFAULT NOW()
);

Weak Fit

Highly Connected Data:

• Social networks (many-to-many relationships)
• Recommendation engines
• Organizational hierarchies with flexible depth

Reason: Joins become expensive, recursive queries complex.

Rapidly Evolving Schemas:

• Frequently adding new fields
• Different record types need different fields
• Exploratory data modeling

Reason: Migrations expensive, rigid structure.

When to Use Graph (Neo4j)

Strong Fit

Relationships as Primary Concern:

• Social graphs (followers, friends, connections)
• Recommendation systems (collaborative filtering)
• Knowledge graphs
• Access control with inheritance
• Dependencies and prerequisites

Variable-Depth Traversals:

• "Find all users within 3 degrees"
• "Shortest path between entities"
• "All ancestors in org chart"
• "Courses needed before advanced topic"

Examples:

// Social connections
(:User)-[:FOLLOWS]->(:User)
(:User)-[:BLOCKED]->(:User)

// Learning paths
(:Course)-[:REQUIRES]->(:Course)
(:User)-[:COMPLETED]->(:Course)

// Organizational structure
(:Person)-[:REPORTS_TO]->(:Person)
(:Person)-[:MEMBER_OF]->(:Team)

Weak Fit

Simple Lookups:

• User by email
• Order by ID
• Product details

Reason: Relational databases excel at indexed lookups.

Large Aggregations:

• Sum all orders this month
• Count users by region
• Analytics dashboards

Reason: SQL aggregations and window functions more powerful.

When to Use Document (MongoDB)

Strong Fit

Semi-Structured Data:

• Content management systems (blog posts, articles)
• Product catalogs with varying attributes
• User-generated content
• Configuration data
• API responses that need storage

Nested/Embedded Data:

• Comments within posts
• Order items within orders
• Addresses within user profiles
• Metadata with varying fields

Flexible Schemas:

• Rapid prototyping
• Evolving data models
• Different document types in same collection
• Optional fields vary by record

Examples:

// Blog post with embedded comments
{
  _id: ObjectId("..."),
  title: "Getting Started with SvelteKit",
  slug: "getting-started-sveltekit",
  author: { id: "user-123", name: "Alice" },
  content: "...",
  tags: ["svelte", "javascript", "tutorial"],
  comments: [
    { id: "comment-1", userId: "user-456", text: "Great article!", createdAt: ISODate("2024-01-15") }
  ],
  metadata: { views: 1250, readingTime: "5 min" },
  publishedAt: ISODate("2024-01-10")
}

// Product with varying attributes
{
  _id: ObjectId("..."),
  name: "Laptop",
  category: "electronics",
  price: 999.99,
  specs: { cpu: "Intel i7", ram: "16GB", storage: "512GB SSD", screen: "15.6 inch" }
}

// Different product type, different fields
{
  _id: ObjectId("..."),
  name: "T-Shirt",
  category: "clothing",
  price: 29.99,
  sizes: ["S", "M", "L", "XL"],
  colors: ["black", "white", "blue"],
  material: "100% cotton"
}

Weak Fit

Complex Transactions:

• Multi-step financial operations
• Strong ACID guarantees across documents
• Complex foreign key relationships

Reason: Relational databases better at multi-document transactions.

Relationship-Heavy Data:

• Social networks
• Graph traversals
• "Friends of friends" queries

Reason: Graph databases handle this natively.

Highly Normalized Data:

• No duplication tolerance
• Frequent joins needed
• Strong referential integrity

Reason: Relational databases enforce this better.

Decision Framework

Question 1: What's the primary data concern?

RELATIONSHIPS → Neo4j (Graph) — social connections, recommendations, dependency trees, path finding

STRUCTURED ENTITIES → PostgreSQL (Relational) — user accounts, financial transactions, inventory, orders

DOCUMENTS/CONTENT → MongoDB (Document) — blog posts, product catalogs, CMS content, API data storage

Question 2: How stable is your schema?

VERY STABLE → PostgreSQL — well-defined entities, clear field types, rare schema changes, strong typing needed

EVOLVING → MongoDB — prototyping phase, frequently adding fields, different record structures, flexible modeling

SCHEMA-OPTIONAL → Neo4j — relationships more important than structure, dynamic properties, graph structure evolves

Question 3: How is data accessed?

BY KEY/ID → PostgreSQL or MongoDB — user by email, product by SKU, order by ID

BY TRAVERSAL → Neo4j — friends of friends, shortest path, recommendations

BY CONTENT/QUERY → MongoDB — full-text search, filtering nested documents, flexible queries on varying fields

Question 4: Do you need strong consistency?

ABSOLUTE → PostgreSQL — financial transactions, ACID guarantees, multi-step operations

EVENTUAL OKAY → MongoDB or Neo4j — content updates, social interactions, non-critical data

Question 5: Is data naturally nested?

YES → MongoDB — posts with comments, orders with line items, documents with metadata

NO → PostgreSQL — flat entities, many-to-many relationships, normalized structure

Additional resources

Worked examples and mechanical detail, loaded only when needed:

• real-world-examples.md — four full worked splits (social app, learning platform, e-commerce, CMS) showing which data lives where and why
• integration-and-schema.md — shared-key/reference/embed/event-sync/aggregation integration patterns, per-database schema design, query optimisation and indexing
• anti-patterns-and-migration.md — common mistakes (document-DB transactions, over-embedding, graph for simple lookups), migration strategy and worked migration scripts, portfolio evidence framing

Success Criteria

Architecture is successful when:

• Each database handles what it does best
• Queries are fast (minimal cross-database operations)
• Data consistency maintained appropriately
• Clear reasoning for database placement
• Can scale databases independently
• Schema evolution manageable
• Team understands the architecture