Document: 16-database-architecture.md
Version: 1.0.0

Purpose

This document defines the database architecture and data modelling standards used across all Rundown projects.

The database is responsible for storing structured business data in a consistent, reliable, and scalable manner. It serves as the foundation for application logic, content relationships, and transactional operations.

This document establishes how data should be modelled, organized, and maintained throughout the lifecycle of a project.

Database Philosophy

The database should represent the business domain rather than the user interface.

Tables, relationships, and schemas should model real-world entities and processes instead of pages, components, or frontend layouts.

A well-designed database should remain stable even if the frontend is completely redesigned.

Standard Database

Rundown standardizes on:

PostgreSQL

Reasons include:

• Reliability

• Mature ecosystem

• Strong relational modelling

• Excellent performance

• Transaction support

• Scalability

Responsibilities

The database owns:

• Structured business data

• Relationships

• Transactions

• User records

• Orders

• Products

• Content metadata

• Audit information

Non-Responsibilities

The database should not own:

• Images

• Videos

• Documents

• Frontend layouts

• Styling information

• Animation configuration

• Infrastructure settings

Binary assets belong in Cloudflare R2.

Data Modelling

Every table should represent a meaningful business entity.

Examples:

• Users

• Products

• Orders

• Categories

• Posts

• Services

• Team Members

Avoid creating tables that exist solely to support a specific page or UI component.

Relationships

Relationships should accurately reflect business rules.

Examples:

Category
    │
    ├── Product
    │
    ├── Product
    │
    └── Product

Author
    │
    ├── Post
    │
    ├── Post
    │
    └── Post

Relationships should minimize duplication while maintaining clarity.

Normalization

Data should be normalized where appropriate.

Benefits include:

• Reduced duplication

• Improved consistency

• Easier updates

• Better maintainability

Denormalization should only be introduced when supported by measurable performance requirements.

Naming Standards

Database objects should follow the naming conventions defined in:

09-naming-conventions.md

Examples:

Tables:

products

order_items

blog_posts

Columns:

created_at

updated_at

product_name

user_id

Primary Keys

Every table should include a primary key.

Primary keys should:

• Be unique

• Never change

• Be used consistently across relationships

Foreign Keys

Relationships between tables should use foreign keys to maintain referential integrity.

Avoid storing duplicate relationship data.

Timestamps

Every business table should include:

created_at

updated_at

Additional timestamps may be introduced when required.

Examples:

• published_at

• deleted_at

• completed_at

Soft Deletes

Soft deletes should be used only when business requirements justify retaining deleted records.

Examples:

• Orders

• Customer accounts

• Audit records

Temporary or non-critical data may be permanently deleted when appropriate.

Indexing

Indexes should be added for:

• Frequently searched columns

• Foreign keys

• Unique fields

• High-volume queries

Indexes improve performance but should be introduced deliberately to avoid unnecessary overhead.

Transactions

Database transactions should be used whenever multiple operations must succeed or fail together.

Examples:

• Checkout

• Order creation

• Inventory updates

• Payment confirmation

Partial writes should be avoided.

Migrations

All schema changes must be version-controlled through migrations.

Direct manual changes to production databases are prohibited.

Migrations should be:

• Reproducible

• Reviewed

• Tested before deployment

Backup Strategy

Every production database must be backed up regularly.

Backups should be:

• Automated

• Verified

• Securely stored

• Periodically tested for restoration

A backup that cannot be restored should not be considered valid.

Performance

Database performance should prioritize:

• Efficient queries

• Appropriate indexing

• Minimal duplication

• Controlled relationships

• Predictable execution plans

Application performance issues should not automatically be solved by adding more database complexity.

Security

The database should:

• Restrict direct access

• Use least-privilege credentials

• Encrypt connections

• Protect sensitive information

• Validate migrations before deployment

Production credentials must never be exposed within application code.

Engineering Decision

Business-Oriented Data Model

Status: Accepted

Decision

Database schemas should model business entities rather than frontend implementation details.

Reasoning

This approach:

• Supports multiple frontend experiences.

• Reduces coupling.

• Simplifies maintenance.

• Improves long-term scalability.

• Keeps the data model independent of presentation.

Database Checklist

Before introducing a new table or relationship, verify:

• Does this represent a business entity?

• Are relationships normalized?

• Are naming conventions followed?

• Have indexes been considered?

• Is referential integrity maintained?

• Is a migration required?

• Does this duplicate existing data?

Summary

The database serves as the structured foundation of every Rundown application.

By modelling business concepts rather than frontend implementation, the database remains stable, scalable, and maintainable as applications evolve.

Related Documents

• 09-naming-conventions.md

• 12-system-architecture.md

• 13-architecture-boundaries.md

• 15-payload-cms-architecture.md

Related ADRs

• ADR-001: Repository Structure