Schema Organization

Purpose: Understand database schema file structure
Last Updated: 2024-11-26

Overview

Database schema is organized in the db/ directory as numbered SQL files that load in order during container initialization. This structure provides clear organization by feature and predictable loading sequence.

Directory Structure

db/
├── 000_ids_schema.sql              # Core ID types
├── 000_prelude.sql                 # Utility functions
├── 001_cursors.sql                 # Cursor system
├── 100_audit_system.sql            # Audit logging
├── 200_auth_schema.sql             # Auth schema
├── 201_auth_utils.sql              # Auth utilities
├── 202_auth_session.sql            # Session management
├── ...
├── 300_graph.sql                   # Graph foundation
├── 301_graph_node_type.sql         # Node types
├── 302_graph_edge_type.sql         # Edge types
├── ...
├── 400_webhook_schema.sql          # Webhook system
├── 500_studio_schema.sql           # Studio schema
└── data/                           # Seed data
    ├── README.md
    ├── auth-algorithms.json
    ├── edge-types.json
    ├── jwt-audiences.json
    └── ...

Numbering Scheme

Range Allocation

Range	Feature	Purpose
000-099	Core	Foundation, utilities, IDs
100-199	Audit	Audit and logging system
200-299	Auth	Authentication and authorization
300-399	Graph	Graph data model
400-499	Webhook	Webhook system
500-599	Studio	Studio-specific features
900-999	Testing	Test utilities and fixtures

File Naming Convention

[number]_[feature]_[component].sql

Examples:
200_auth_schema.sql          # Main auth schema
201_auth_utils.sql           # Auth utility functions
301_graph_node_type.sql      # Graph node types
302_graph_edge_type.sql      # Graph edge types

Loading Order

Files load numerically:

1. 000_*.sql - Core foundations
2. 001_*.sql - Supporting systems
3. 100_*.sql - Feature: Audit
4. 200_*.sql - Feature: Auth
5. 300_*.sql - Feature: Graph
6. Continue sequentially...

Schema Modules

000-099: Core Foundation

000_ids_schema.sql - ID types and utilities

-- Core ID types
CREATE DOMAIN id_t AS BIGINT;

-- ID generation
CREATE FUNCTION generate_id() RETURNS id_t;

000_prelude.sql - Utility functions

-- JSON utilities
CREATE FUNCTION util.load_json_data(...);

-- Array utilities
CREATE FUNCTION util.array_distinct(...);

001_cursors.sql - Cursor system

-- Cursor types
CREATE TYPE cursor_t AS (...);

-- Cursor functions
CREATE FUNCTION cursors.encode(...);
CREATE FUNCTION cursors.decode(...);

100-199: Audit System

100_audit_system.sql - Audit logging

CREATE SCHEMA audit;

CREATE TABLE audit.log (
  id BIGSERIAL PRIMARY KEY,
  timestamp TIMESTAMPTZ DEFAULT NOW(),
  user_id BIGINT,
  action TEXT,
  details JSONB
);

CREATE FUNCTION audit.log_action(...);

200-299: Authentication

200_auth_schema.sql - Core auth schema

CREATE SCHEMA auth;

CREATE TABLE auth.session (
  id BIGSERIAL PRIMARY KEY,
  user_id BIGINT,
  token TEXT,
  expires_at TIMESTAMPTZ
);

201_auth_utils.sql - Auth utilities

CREATE FUNCTION auth.session_id() RETURNS BIGINT;
CREATE FUNCTION auth.subject_id() RETURNS BIGINT;

202-217 - Additional auth components:

• Session management
• JWT handling
• Key rotation
• Security functions

300-399: Graph System

300_graph.sql - Graph foundation

CREATE SCHEMA graph;

-- Base types
CREATE TYPE graph.node_type AS ENUM (...);
CREATE TYPE graph.edge_type AS ENUM (...);

301-315 - Graph components:

• Node types and tables
• Edge types and tables
• Resource-Statement model
• Traversal functions
• Search capabilities
• Access control
• Cursors

400-499: Webhook System

400_webhook_schema.sql - Webhook foundation

CREATE SCHEMA webhook;

CREATE TABLE webhook.event (
  id BIGSERIAL PRIMARY KEY,
  event_type TEXT,
  payload JSONB,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

401_webhook_functions.sql - Webhook utilities

CREATE FUNCTION webhook.trigger_event(...);
CREATE FUNCTION webhook.process_queue(...);

500-599: Studio Features

500_studio_schema.sql - Studio-specific

CREATE SCHEMA studio;

-- Studio functions
CREATE FUNCTION studio.login(...);
CREATE FUNCTION studio.logout(...);

900-999: Testing Utilities

998_debug_utils.sql - Debug functions

CREATE FUNCTION util.debug_info() RETURNS TABLE (...);

999_util_testing_functions.sql - Test utilities

CREATE FUNCTION util.create_test_roles();
CREATE FUNCTION util.cleanup_test_data();

Data Directory

Seed Data Files

Location: db/data/

Format: JSON files loaded via util.load_json_data()

Available data files:

data/
├── README.md                      # Data documentation
├── auth-algorithms.json           # Supported algorithms
├── edge-types.json                # Graph edge types
├── jwt-audiences.json             # JWT audiences
├── jwt-claim-mappings.json        # JWT claim mappings
├── jwt-issuers.json               # JWT issuers
├── jwt-role-mappings.json         # JWT role mappings
├── node-types.json                # Graph node types
├── reserved-namespaces.json       # Reserved namespaces
├── webhook-event-types.json       # Webhook event types
├── webhook-field-mappings.json    # Webhook field mappings
└── webhook-providers.json         # Webhook providers

Data Loading

In SQL files:

-- Load JSON seed data
SELECT util.load_json_data(
  'auth',
  'algorithm',
  'auth-algorithms.json'
);

Data format (auth-algorithms.json):

[
  {
    "name": "RS256",
    "description": "RSA Signature with SHA-256"
  },
  {
    "name": "RS384",
    "description": "RSA Signature with SHA-384"
  }
]

Dependencies and Order

Dependency Rules

Rule 1: Core before features

000_prelude.sql → 200_auth_schema.sql

Rule 2: Schema before functions

200_auth_schema.sql → 201_auth_utils.sql

Rule 3: Tables before constraints

300_graph.sql → 309_graph_node_check.sql

Rule 4: Types before usage

301_graph_node_type.sql → 303_graph_node.sql

Common Dependencies

000_ids_schema.sql
  ↓
200_auth_schema.sql
  ↓
201_auth_utils.sql
  ↓
300_graph.sql
  ↓
301_graph_node_type.sql
  ↓
303_graph_node.sql

Adding New Schema Files

Step 1: Choose Number

Criteria:

• Feature area (000, 100, 200, etc.)
• Load order requirements
• Related file numbers

Example: New graph feature → 3XX range

Step 2: Create File

-- db/305_graph_my_feature.sql

-- Purpose: My new graph feature
-- Dependencies: 303_graph_node.sql

CREATE TABLE graph.my_table (
  id BIGSERIAL PRIMARY KEY,
  node_id BIGINT REFERENCES graph.node(id),
  data JSONB DEFAULT '{}'
);

CREATE FUNCTION graph.my_function(
  p_node_id BIGINT
) RETURNS TABLE (...) AS $$
BEGIN
  -- Implementation
END;
$$ LANGUAGE plpgsql;

Step 3: Add Comments

-- Purpose: Brief description
-- Dependencies: List required files
-- Related: Link related files

COMMENT ON TABLE graph.my_table IS 
  'Detailed description of table purpose';

COMMENT ON FUNCTION graph.my_function IS 
  'Function description and usage';

Step 4: Test Loading

# Restart database to load new file
docker compose restart postgres

# Verify loaded
psql -h localhost -p 54320 -U savvi -d savvi_studio \
  -c "\d graph.my_table"

Step 5: Run Codegen

# Generate TypeScript wrappers
pnpm db:codegen

# Verify generated
cat src/__generated__/graph/index.ts

Best Practices

File Organization

✅ Do:

• Use descriptive names
• Follow numbering scheme
• Group related functionality
• Document dependencies
• Keep files focused (< 500 lines)

❌ Don't:

• Mix unrelated features
• Create circular dependencies
• Skip number ranges
• Use arbitrary numbering

SQL Style

Naming conventions:

-- Schemas: lowercase
CREATE SCHEMA graph;

-- Tables: snake_case
CREATE TABLE graph.node_type;

-- Functions: snake_case
CREATE FUNCTION graph.create_node();

-- Types: snake_case
CREATE TYPE graph.node_data;

-- Enums: snake_case with UPPER values
CREATE TYPE auth.algorithm AS ENUM ('RS256', 'RS384');

Formatting:

-- Use consistent indentation
CREATE TABLE graph.node (
  id BIGSERIAL PRIMARY KEY,
  type_namespace LTREE NOT NULL,
  data JSONB DEFAULT '{}',
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Add helpful comments
COMMENT ON TABLE graph.node IS 
  'Core node table for graph storage';

Dependencies

Document dependencies:

-- Dependencies:
--   - 000_ids_schema.sql (id_t type)
--   - 200_auth_schema.sql (auth.session)
--   - 301_graph_node_type.sql (node types)

Check dependencies exist:

-- Verify required schema exists
DO $$
BEGIN
  IF NOT EXISTS (
    SELECT 1 FROM pg_namespace WHERE nspname = 'auth'
  ) THEN
    RAISE EXCEPTION 'auth schema not found';
  END IF;
END $$;

Troubleshooting

File Not Loading

Check file name:

# Must match pattern: NNN_name.sql
ls db/ | grep -E '^[0-9]{3}_.*\.sql$'

Check dependencies:

# View loading order
ls -1 db/*.sql | sort

View logs:

docker compose logs postgres | grep ERROR

Order Issues

Symptom: "relation does not exist" errors

Solution: Renumber files to correct order

# Move file to correct position
mv db/350_feature.sql db/305_feature.sql

# Rebuild database
docker compose down -v
docker compose up -d postgres

Circular Dependencies

Detect:

-- Check for circular foreign keys
SELECT 
  tc.table_name,
  kcu.column_name,
  ccu.table_name AS foreign_table_name
FROM information_schema.table_constraints tc
JOIN information_schema.key_column_usage kcu
  ON tc.constraint_name = kcu.constraint_name
JOIN information_schema.constraint_column_usage ccu
  ON ccu.constraint_name = tc.constraint_name
WHERE tc.constraint_type = 'FOREIGN KEY';

Fix: Break cycle with deferred constraints or separate files

Related Documentation

• Local Development - How files load
• Migrations - Schema evolution
• Codegen System - Generate from schema

Quick Reference

Task	Command
View files	ls db/*.sql | sort
Check order	ls -1v db/*.sql
Test file	psql ... -f db/NNN_file.sql
Reload schema	docker compose restart postgres
View table	\d schema.table
View function	\df schema.function

---

Next steps: See Migrations for how to evolve schema over time, or Local Development for how schema loads in Docker.