# Database Design
## Overview
Zellij uses PostgreSQL as its primary data store, leveraging JSONB for flexible metadata storage and GORM as the ORM layer. The schema is designed to support version control, collaboration, and semantic pattern management.
## Core Design Decisions
### 1. Unified Pattern Storage
Initially, we considered separate tables for fields, models, and collections. However, we discovered significant duplication (82%) across projects due to Airtable's limitations. We now use:
- Separate tables for type safety and specific relationships
- Deduplication during import to reduce storage
- JSONB metadata fields for flexibility
### 2. Soft Deletes
All primary entities use soft deletes (`deleted_at` timestamps) to:
- Maintain referential integrity
- Support recovery of deleted items
- Enable audit trails
### 3. UUID Primary Keys
All tables use UUIDs for primary keys to:
- Support distributed systems
- Avoid ID conflicts during merges
- Enable client-side ID generation
## Entity Relationship Diagram
> **Note**: This document describes the **target architecture**. For the current implementation status, see [Current Database State](/docs/architecture/current-state)
### Future State - Complete System
```mermaid
erDiagram
actors ||--o{ projects : "owns"
actors ||--o{ fields : "authors"
actors ||--o{ models : "authors"
actors ||--o{ collections : "authors"
actors ||--o{ issues : "creates"
actors ||--o{ stars : "gives"
actors ||--o{ watches : "watches"
actors ||--o{ project_members : "member_of"
projects ||--o{ project_fields : "contains"
projects ||--o{ project_models : "contains"
projects ||--o{ project_collections : "contains"
projects ||--o{ branches : "has"
projects ||--o{ issues : "tracks"
projects ||--o{ stars : "receives"
projects ||--o{ watches : "watched_by"
projects ||--o{ project_members : "has_members"
fields ||--o{ project_fields : "used_in"
fields ||--o{ model_fields : "used_by"
fields ||--o{ collection_fields : "used_by"
fields ||--o{ field_semantics : "has"
fields ||--o{ field_history : "history"
fields }o--|| categories : "categorized_as"
models ||--o{ project_models : "used_in"
models ||--o{ model_fields : "contains"
models ||--o{ model_history : "history"
collections ||--o{ project_collections : "used_in"
collections ||--o{ collection_fields : "contains"
collections ||--o{ collection_history : "history"
branches ||--o{ commits : "contains"
commits ||--o{ changes : "includes"
branches ||--o{ releases : "released_as"
ontologies ||--o{ ontology_classes : "defines"
ontologies ||--o{ project_ontologies : "used_by"
vocabularies ||--o{ vocabulary_terms : "contains"
institutions ||--o{ actors : "affiliates"
```
## Core Tables
### actors
Primary user/organization table supporting both individual and institutional accounts.
| Column | Type | Description |
|--------|------|-------------|
| id | UUID | Primary key |
| name | VARCHAR(255) | Display name |
| username | VARCHAR(255) | Unique username |
| email | VARCHAR(255) | Unique email |
| password_hash | VARCHAR(255) | Bcrypt password hash |
| role | VARCHAR(50) | System role (admin, user) |
| actor_type | VARCHAR(50) | person/organization |
| orcid | VARCHAR(19) | ORCID identifier |
| institution_id | UUID | FK to institutions |
| metadata | JSONB | Additional profile data |
| created_at | TIMESTAMP | Registration date |
| updated_at | TIMESTAMP | Last modification |
| deleted_at | TIMESTAMP | Soft delete |
### projects
Central entity for organizing semantic patterns.
| Column | Type | Description |
|--------|------|-------------|
| id | UUID | Primary key |
| name | VARCHAR(255) | Project name |
| identifier | VARCHAR(255) | Unique identifier |
| description | TEXT | Project description |
| homepage | VARCHAR(255) | Project website |
| repository_url | VARCHAR(255) | Git repository |
| owner_id | UUID | FK to actors |
| visibility | VARCHAR(50) | public/private |
| default_branch | VARCHAR(255) | Main branch name |
| metadata | JSONB | Additional data |
| created_at | TIMESTAMP | Creation date |
| updated_at | TIMESTAMP | Last modification |
| deleted_at | TIMESTAMP | Soft delete |
### fields
Atomic semantic field definitions.
| Column | Type | Description |
|--------|------|-------------|
| id | UUID | Primary key |
| name | VARCHAR(255) | Field name |
| identifier | VARCHAR(255) | Unique identifier |
| description | TEXT | Field description |
| uri | VARCHAR(500) | Full URI |
| rdf_type | VARCHAR(255) | RDF type |
| ontology_path | VARCHAR(500) | Ontology location |
| category_id | UUID | FK to categories |
| author_id | UUID | FK to actors |
| project_id | UUID | FK to projects |
| metadata | JSONB | Import/custom data |
| original_data | JSONB | Preserved import data |
| created_at | TIMESTAMP | Creation date |
| updated_at | TIMESTAMP | Last modification |
| deleted_at | TIMESTAMP | Soft delete |
### models
Semantic model definitions (classes/shapes).
| Column | Type | Description |
|--------|------|-------------|
| id | UUID | Primary key |
| name | VARCHAR(255) | Model name |
| identifier | VARCHAR(255) | Unique identifier |
| description | TEXT | Model description |
| uri | VARCHAR(500) | Full URI |
| rdf_type | VARCHAR(255) | RDF type |
| author_id | UUID | FK to actors |
| project_id | UUID | FK to projects |
| metadata | JSONB | Additional data |
| original_data | JSONB | Preserved import data |
| created_at | TIMESTAMP | Creation date |
| updated_at | TIMESTAMP | Last modification |
| deleted_at | TIMESTAMP | Soft delete |
### collections
Named groups of patterns.
| Column | Type | Description |
|--------|------|-------------|
| id | UUID | Primary key |
| name | VARCHAR(255) | Collection name |
| identifier | VARCHAR(255) | Unique identifier |
| description | TEXT | Collection description |
| uri | VARCHAR(500) | Full URI |
| rdf_type | VARCHAR(255) | RDF type |
| author_id | UUID | FK to actors |
| project_id | UUID | FK to projects |
| metadata | JSONB | Additional data |
| original_data | JSONB | Preserved import data |
| created_at | TIMESTAMP | Creation date |
| updated_at | TIMESTAMP | Last modification |
| deleted_at | TIMESTAMP | Soft delete |
## Relationship Tables
### project_fields
Links fields to projects (many-to-many).
| Column | Type | Description |
|--------|------|-------------|
| project_id | UUID | FK to projects |
| field_id | UUID | FK to fields |
| added_at | TIMESTAMP | When added |
| added_by | UUID | FK to actors |
### model_fields
Links fields to models with cardinality.
| Column | Type | Description |
|--------|------|-------------|
| model_id | UUID | FK to models |
| field_id | UUID | FK to fields |
| cardinality | VARCHAR(50) | 1, *, +, ? |
| required | BOOLEAN | Is required |
| order_index | INTEGER | Display order |
### collection_fields
Links fields to collections.
| Column | Type | Description |
|--------|------|-------------|
| collection_id | UUID | FK to collections |
| field_id | UUID | FK to fields |
| order_index | INTEGER | Display order |
## Version Control Tables
### branches
Git-like branches for projects.
| Column | Type | Description |
|--------|------|-------------|
| id | UUID | Primary key |
| project_id | UUID | FK to projects |
| name | VARCHAR(255) | Branch name |
| description | TEXT | Branch purpose |
| base_branch | VARCHAR(255) | Parent branch |
| created_by | UUID | FK to actors |
| metadata | JSONB | Additional data |
| created_at | TIMESTAMP | Creation date |
| updated_at | TIMESTAMP | Last modification |
| deleted_at | TIMESTAMP | Soft delete |
### commits
Version control commits.
| Column | Type | Description |
|--------|------|-------------|
| id | UUID | Primary key |
| branch_id | UUID | FK to branches |
| author_id | UUID | FK to actors |
| message | TEXT | Commit message |
| parent_id | UUID | Parent commit |
| tree_hash | VARCHAR(64) | Content hash |
| metadata | JSONB | Additional data |
| created_at | TIMESTAMP | Commit date |
### changes
Individual changes within commits.
| Column | Type | Description |
|--------|------|-------------|
| id | UUID | Primary key |
| commit_id | UUID | FK to commits |
| entity_type | VARCHAR(50) | field/model/collection |
| entity_id | UUID | Entity affected |
| change_type | VARCHAR(50) | create/update/delete |
| old_value | JSONB | Previous state |
| new_value | JSONB | New state |
| metadata | JSONB | Additional data |
## Social Features Tables
### issues
GitHub-style issues for projects.
| Column | Type | Description |
|--------|------|-------------|
| id | UUID | Primary key |
| project_id | UUID | FK to projects |
| number | INTEGER | Issue number |
| title | VARCHAR(255) | Issue title |
| body | TEXT | Issue description |
| state | VARCHAR(50) | open/closed |
| author_id | UUID | FK to actors |
| assignee_id | UUID | FK to actors |
| labels | JSONB | Issue labels |
| metadata | JSONB | Additional data |
| created_at | TIMESTAMP | Creation date |
| updated_at | TIMESTAMP | Last modification |
| closed_at | TIMESTAMP | Close date |
### stars
User stars/favorites for projects.
| Column | Type | Description |
|--------|------|-------------|
| user_id | UUID | FK to actors |
| project_id | UUID | FK to projects |
| created_at | TIMESTAMP | Star date |
### watches
Project watch subscriptions.
| Column | Type | Description |
|--------|------|-------------|
| user_id | UUID | FK to actors |
| project_id | UUID | FK to projects |
| created_at | TIMESTAMP | Watch date |
## Authorization Tables
### casbin_rules
RBAC rules for fine-grained permissions.
| Column | Type | Description |
|--------|------|-------------|
| id | BIGSERIAL | Primary key |
| ptype | VARCHAR(100) | Policy type |
| v0 | VARCHAR(100) | Subject |
| v1 | VARCHAR(100) | Object |
| v2 | VARCHAR(100) | Action |
| v3-v5 | VARCHAR(100) | Additional fields |
## Indexes
### Performance Indexes
- `actors(username)` - User lookup
- `actors(email)` - Login
- `projects(identifier)` - URL routing
- `projects(owner_id)` - User projects
- `fields(identifier)` - Pattern lookup
- `fields(name, project_id)` - Search
- `models(identifier)` - Pattern lookup
- `collections(identifier)` - Pattern lookup
### Full-Text Search Indexes
- `fields(name, description)` - Pattern search
- `models(name, description)` - Pattern search
- `collections(name, description)` - Pattern search
- `projects(name, description)` - Project search
## Migration Strategy
### From Airtable
1. Export all Airtable bases to CSV
2. Run deduplication algorithm (MD5 hash of key fields)
3. Create "Shared Patterns" project for deduplicated items
4. Track original project associations in metadata
5. Result: 82% reduction in storage (26,518 → 4,769 patterns)
### Schema Evolution
- All migrations versioned and reversible
- Zero-downtime migrations using:
- Add new columns as nullable
- Backfill data
- Add constraints after backfill
- Remove old columns in separate migration
## Best Practices
### JSONB Usage
- Store structured but variable data
- Index specific JSONB paths for queries
- Keep core fields as columns for performance
### UUID Generation
- Generate UUIDs in application layer
- Use UUID v4 for randomness
- Consider UUID v7 for time-ordering
### Soft Deletes
- Always check deleted_at in queries
- Periodic cleanup of old soft-deleted records
- Consider archival strategy for compliance