Database
This document defines the database architecture, data modeling principles, naming conventions, relationships, lifecycle management, and governance for MHMD Studio. The database should remain scalable, secure, maintainable, and independent from application-specific implementation details.
Document Metadata
| Property | Value |
|---|---|
| Document | Database |
| Version | 1.0 |
| Status | Active |
| Owner | Mohammed El Maachi |
| Last Updated | July 2026 |
Purpose
The database is the single source of truth for all persistent application data.
Its objectives are to:
- Store data reliably.
- Preserve data integrity.
- Support scalability.
- Enable efficient querying.
- Minimize duplication.
- Support future products.
Application code should adapt to the database, not the reverse.
Database Philosophy
The database should be:
- Normalized where practical.
- Explicit.
- Predictable.
- Secure.
- Extensible.
- Well documented.
Every table should represent a real business entity.
Primary Database
Primary Database:
PostgreSQL
Managed through:
Supabase
Reasons:
- ACID compliance
- Mature ecosystem
- Strong relational capabilities
- Full-text search
- Row Level Security
- JSON support
- Excellent tooling
Core Principles
Every table should have:
- One responsibility.
- A primary key.
- Created timestamp.
- Updated timestamp.
Optional:
- Soft delete timestamp.
- Created by.
- Updated by.
Naming Conventions
Tables:
Plural
Examples:
text
users
projects
articles
categories
servicesColumns:
snake_case
Examples:
text
created_at
updated_at
published_at
project_slug
reading_timePrimary Keys
Use UUIDs.
Example:
text
id UUID PRIMARY KEYAvoid sequential IDs for public entities.
Foreign Keys
Every relationship should use explicit foreign keys.
Never rely on implicit relationships.
Database constraints should enforce integrity.
Standard Columns
Every primary table should contain:
text
id
created_at
updated_atOptional:
text
deleted_at
created_by
updated_bySoft Deletes
Prefer soft deletes for user-generated content.
Use:
text
deleted_atinstead of permanent deletion where recovery is valuable.
Timestamps
Store timestamps in UTC.
Convert to local time only within the presentation layer.
Core Entities
Initial entities include:
- Users
- Projects
- Articles
- Categories
- Tags
- Services
- Resources
- Contact Requests
- Media Assets
- AI Conversations (future)
The schema should remain extensible.
Users
Purpose:
Authentication and ownership.
Potential fields:
- id
- name
- avatar_url
- role
- created_at
- updated_at
Projects
Fields may include:
- id
- title
- slug
- summary
- content
- status
- year
- featured
- cover_image
- created_at
- updated_at
Articles
Fields include:
- id
- title
- slug
- summary
- content
- reading_time
- published_at
- featured_image
- author_id
Categories
Purpose:
Organize articles, resources, and projects.
Should remain reusable across multiple entities.
Tags
Tags provide flexible classification.
Avoid excessive tagging.
Each tag should have a clear purpose.
Services
Store:
- Name
- Slug
- Description
- Order
- Visibility
Resources
May include:
- Guides
- Templates
- Downloads
- Tools
Store metadata separately from files.
Contact Requests
Fields:
- Name
- Company
- Budget
- Service
- Message
- Status
- Submitted At
Messages should never be publicly accessible.
Media Assets
Metadata should include:
- File name
- MIME type
- Dimensions
- Size
- Alt text
- Storage location
Do not duplicate binary files inside the database.
Relationships
Examples:
text
User
↓
Articles
↓
Categories
↓
Tagstext
Project
↓
Services
↓
TechnologiesRelationships should remain explicit.
Many-to-Many Relationships
Use junction tables.
Example:
text
article_tags
project_services
project_technologiesAvoid storing arrays of IDs.
Indexing
Create indexes for:
- Primary keys
- Foreign keys
- Slugs
- Published dates
- Search fields
Avoid unnecessary indexes.
Full-Text Search
Use PostgreSQL full-text search where appropriate.
Search should support:
- Articles
- Projects
- Resources
Semantic search may be added later.
Transactions
Use transactions whenever multiple related operations occur.
Atomic operations prevent inconsistent data.
Constraints
Use database constraints to enforce:
- Uniqueness
- Foreign keys
- Required values
- Valid ranges
Business rules should not rely solely on application code.
Row Level Security
Enable Row Level Security (RLS).
Policies should follow the principle of least privilege.
Public content should remain explicitly public.
Private content should remain inaccessible by default.
Migrations
All schema changes must use version-controlled migrations.
Never modify production schemas manually.
Every migration should be reversible whenever practical.
Backup Strategy
Backups should be:
- Automated
- Encrypted
- Verified
- Regularly tested
Recovery procedures should be documented.
Data Retention
Retention policies should define:
- Contact requests
- Logs
- Analytics
- Uploaded files
- AI conversations
Retention should balance privacy, compliance, and operational needs.
Database Monitoring
Monitor:
- Query performance
- Storage usage
- Slow queries
- Connection counts
- Replication health
- Backup status
Problems should be detected proactively.
AI Compatibility
The schema should support future AI capabilities such as:
- Embeddings
- Vector search
- Conversation history
- Knowledge retrieval
- Prompt library
- Agent memory
AI-related data should remain isolated from core business entities where appropriate.
Database Quality Checklist
Before deploying schema changes verify:
- Naming conventions followed.
- Relationships documented.
- Constraints implemented.
- Indexes reviewed.
- RLS configured.
- Migrations tested.
- Documentation updated.
- Performance evaluated.
Final Principle
The database is a long-term asset.
Schema decisions should optimize for clarity, integrity, and future evolution rather than short-term convenience.