# Schema Horizontal Instructions
## Purpose
The Schema horizontal defines the data structures, types, and relationships that form the foundation of the system. It serves as the single source of truth for data models, validation rules, and constraints across all verticals.
## Content Requirements
### Required Sections
1. **Data Models**
- Entity definitions and properties
- Primary and foreign key relationships
- Data types and constraints
- Indexes and performance optimizations
2. **Type Definitions**
- Primitive types and enumerations
- Complex types and interfaces
- Generic types and templates
- Type unions and intersections
3. **Validation Rules**
- Field-level validation constraints
- Cross-field validation logic
- Business rule validations
- Custom validation functions
4. **Relationships**
- One-to-one relationships
- One-to-many relationships
- Many-to-many relationships
- Hierarchical relationships
5. **Migration Strategy**
- Schema version control
- Forward and backward compatibility
- Data transformation rules
- Rollback procedures
### Documentation Format
```yaml
entities:
User:
properties:
id:
type: "uuid"
primary_key: true
generated: true
email:
type: "string"
max_length: 255
unique: true
validation: "email"
created_at:
type: "timestamp"
default: "now()"
relationships:
profile:
type: "one_to_one"
entity: "UserProfile"
cascade: "delete"
indexes:
- fields: ["email"]
unique: true
- fields: ["created_at"]
type: "btree"
```
## Validation Rules
### Quality Control
1. **Data Integrity**
- All entities must have primary keys
- Foreign key relationships must be valid
- Referential integrity must be maintained
- Constraint violations must be prevented
2. **Performance**
- Appropriate indexes must be defined
- Query patterns must be optimized
- Large tables must have partitioning strategy
- N+1 query problems must be avoided
3. **Security**
- Sensitive data must be identified
- Encryption requirements must be specified
- Access control patterns must be defined
- Audit trail requirements must be documented
4. **Maintainability**
- Schema changes must be versioned
- Migration scripts must be provided
- Documentation must be up-to-date
- Deprecation timeline must be clear
### Automated Checks
- Schema validation against defined rules
- Relationship consistency verification
- Performance impact analysis
- Breaking change detection
- Security compliance scanning
## Evolution Rules
### Across Verticals (Left to Right)
1. **Foundation → Core**
- Basic entities evolve to include business logic constraints
- Simple relationships become complex domain models
- Generic types become domain-specific abstractions
2. **Core → Integration**
- Internal schemas expose external interfaces
- Single-system models become cross-system compatible
- Synchronous constraints become eventually consistent
3. **Integration → Extension**
- Fixed schemas become extensible with custom fields
- Monolithic models become modular and pluggable
- Static definitions become dynamic and configurable
### Schema Versioning
- **v1.0**: Core entities with basic relationships
- **v2.0**: Business logic constraints and validations
- **v3.0**: Performance optimizations and denormalization
- **v4.0**: Extension points and customization support
## Dependencies
### Upstream Dependencies
1. **Specs Horizontal**
- Business requirements and rules
- Data flow specifications
- Performance requirements
- Compliance requirements
### Downstream Dependencies
1. **API Horizontal**
- Request/response schemas
- Validation rule enforcement
- Type definitions for interfaces
2. **Backend Horizontal**
- Database implementation
- ORM/ODM configurations
- Query optimization strategies
3. **Frontend Horizontal**
- Type definitions for client code
- Form validation rules
- Data transformation logic
4. **Tests Horizontal**
- Test data generation
- Schema validation tests
- Performance benchmarks
## Best Practices
### Design Principles
1. **Normalization**
- Eliminate data redundancy
- Ensure data consistency
- Minimize update anomalies
- Balance with performance needs
2. **Domain-Driven Design**
- Model real-world entities and relationships
- Use ubiquitous language
- Maintain bounded contexts
- Implement aggregate roots
3. **Future-Proofing**
- Design for extensibility
- Plan for schema evolution
- Consider scalability requirements
- Implement versioning strategy
4. **Performance Optimization**
- Create appropriate indexes
- Consider denormalization where needed
- Plan for partitioning large tables
- Optimize query patterns
### Common Patterns
```yaml
# Audit Pattern
audit_fields:
created_at:
type: "timestamp"
default: "now()"
immutable: true
updated_at:
type: "timestamp"
default: "now()"
on_update: "now()"
created_by:
type: "uuid"
foreign_key: "users.id"
version:
type: "integer"
default: 1
optimistic_locking: true
# Soft Delete Pattern
soft_delete:
deleted_at:
type: "timestamp"
nullable: true
deleted_by:
type: "uuid"
foreign_key: "users.id"
nullable: true
# Hierarchical Pattern
hierarchy:
parent_id:
type: "uuid"
foreign_key: "same_table.id"
nullable: true
path:
type: "string"
materialized_path: true
level:
type: "integer"
computed: true
```
## Data Types and Standards
### Primitive Types
- **String Types**: `varchar`, `text`, `char`
- **Numeric Types**: `integer`, `bigint`, `decimal`, `float`
- **Date/Time Types**: `date`, `time`, `timestamp`, `interval`
- **Boolean Type**: `boolean`
- **Binary Types**: `bytea`, `blob`
- **JSON Types**: `json`, `jsonb`
- **UUID Type**: `uuid`
- **Array Types**: `array[type]`
### Complex Types
```yaml
# Enum Definition
status_enum:
values: ["active", "inactive", "pending", "suspended"]
default: "pending"
# JSON Schema Definition
metadata_schema:
type: "object"
properties:
tags:
type: "array"
items:
type: "string"
settings:
type: "object"
additional_properties: true
# Custom Type Definition
address_type:
properties:
street: { type: "string", max_length: 255 }
city: { type: "string", max_length: 100 }
postal_code: { type: "string", pattern: "^[0-9]{5}(-[0-9]{4})?$" }
country: { type: "string", length: 2 }
```
## Migration Management
### Migration Scripts
```sql
-- Migration: 001_create_users_table.sql
CREATE TABLE users (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
email VARCHAR(255) NOT NULL UNIQUE,
password_hash VARCHAR(255) NOT NULL,
created_at TIMESTAMP DEFAULT NOW(),
updated_at TIMESTAMP DEFAULT NOW()
);
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_users_created_at ON users(created_at);
```
### Rollback Strategy
```sql
-- Rollback: 001_create_users_table_rollback.sql
DROP INDEX IF EXISTS idx_users_created_at;
DROP INDEX IF EXISTS idx_users_email;
DROP TABLE IF EXISTS users;
```
## Integration Guidelines
### With Other Horizontals
1. **API Integration**
- Generate API schemas from database schemas
- Maintain consistency between internal and external models
- Implement proper serialization/deserialization
2. **Backend Integration**
- Configure ORM/ODM mappings
- Implement repository patterns
- Define query optimization strategies
3. **Test Integration**
- Generate test fixtures from schema definitions
- Implement schema validation tests
- Create performance benchmarks
### Tools and Standards
- **JSON Schema** for validation rules
- **SQL DDL** for relational databases
- **MongoDB Schema** for document stores
- **GraphQL Schema** for API definitions
- **Avro/Protocol Buffers** for serialization
- **Liquibase/Flyway** for migration management
