metabuilder/dbal/shared/api/schema/SPEC.md

# Complete YAML Entity Schema Specification
**Version**: 2.0
**Date**: 2026-02-04
**Status**: Comprehensive - All Relationship Types + All Features

---

## Entity Declaration

```yaml
entity: EntityName
version: "1.0"
description: "Entity purpose"
package: package_name         # Optional - which package owns this entity
table: custom_table_name      # Optional - override default table name
```

---

## Field Types

### Primitive Types

| Type | Description | Prisma | SQL | MongoDB |
|------|-------------|--------|-----|---------|
| `uuid` | UUID v4 | String | UUID | String |
| `cuid` | CUID (Collision-resistant UID) | String | VARCHAR | String |
| `string` | Short text (< 255 chars) | String | VARCHAR | String |
| `text` | Long text (unlimited) | String | TEXT | String |
| `email` | Email address | String | VARCHAR | String |
| `url` | URL | String | VARCHAR | String |
| `integer` | 32-bit integer | Int | INTEGER | Number |
| `bigint` | 64-bit integer | BigInt | BIGINT | Long |
| `float` | Floating point | Float | FLOAT | Double |
| `decimal` | Fixed-point decimal | Decimal | DECIMAL | Decimal128 |
| `boolean` | True/false | Boolean | BOOLEAN | Boolean |
| `timestamp` | Unix timestamp (milliseconds) | BigInt | BIGINT | Date |
| `date` | Date only (YYYY-MM-DD) | DateTime | DATE | Date |
| `datetime` | Date + time | DateTime | TIMESTAMP | Date |
| `json` | JSON object | Json | JSONB | Object |
| `enum` | Enum with fixed values | String | ENUM | String |
| `bytes` | Binary data | Bytes | BYTEA | Binary |

### Special Types

| Type | Description | Example |
|------|-------------|---------|
| `slug` | URL-friendly identifier | `my-article-title` |
| `phone` | Phone number | `+1-555-0123` |
| `currency` | Money amount (uses Decimal) | `19.99` |
| `color` | Hex color code | `#FF5733` |
| `coordinates` | Lat/long (uses Json) | `{lat: 37.7749, lng: -122.4194}` |

---

## Field Attributes

### Required Attributes

```yaml
fields:
  id:
    type: uuid
    primary: true              # Is this the primary key?
    required: true             # Can this field be null?
    unique: true               # Must values be unique?
    generated: true            # Auto-generated by database?
    nullable: false            # Explicit nullable (opposite of required)
```

### Constraints

```yaml
fields:
  username:
    type: string
    min_length: 3              # Minimum string length
    max_length: 50             # Maximum string length
    pattern: "^[a-zA-Z0-9_]+$" # Regex validation
    min: 0                     # Minimum number value
    max: 100                   # Maximum number value
```

### Enum Values

```yaml
fields:
  role:
    type: enum
    values: [user, admin, moderator]   # Fixed set of allowed values
    default: user
```

### Default Values

```yaml
fields:
  status:
    type: string
    default: draft             # Default value for new records
  isActive:
    type: boolean
    default: true
  viewCount:
    type: integer
    default: 0
  metadata:
    type: json
    default: {}
```

### Index Hints

```yaml
fields:
  tenantId:
    type: uuid
    index: true                # Create index on this field
    unique_index: true         # Create unique index
```

### Description

```yaml
fields:
  email:
    type: email
    description: "User's email address for login and notifications"
```

---

## Indexes

### Simple Index

```yaml
indexes:
  - fields: [email]            # Single-field index
```

### Composite Index

```yaml
indexes:
  - fields: [tenantId, slug]   # Multi-field index
    unique: true               # Composite unique constraint
    name: tenant_slug          # Optional index name
```

### Named Index

```yaml
indexes:
  - fields: [createdAt, status]
    name: recent_active
```

### Index Types (Database-Specific)

```yaml
indexes:
  - fields: [content]
    type: fulltext             # Full-text search index
    name: content_search
  - fields: [location]
    type: spatial              # Spatial/GIS index
```

---

## Relationships

See [RELATIONSHIPS.md](./RELATIONSHIPS.md) for complete relationship specification.

### Quick Reference

```yaml
relations:
  # Belongs to (N:1)
  author:
    type: belongs-to
    entity: User
    foreign_key: authorId
    on_delete: cascade         # cascade | set_null | restrict | no_action
    on_update: cascade

  # Has many (1:N)
  posts:
    type: has-many
    entity: Post
    foreign_key: authorId
    cascade_delete: true

  # Many-to-many (M:N)
  roles:
    type: many-to-many
    entity: Role
    through: UserRole
    foreign_key: userId
    target_key: roleId

  # Self-referential
  parent:
    type: belongs-to
    entity: Category
    foreign_key: parentId
    nullable: true

  # Polymorphic
  commentable:
    type: polymorphic
    foreign_key: commentableId
    type_key: commentableType
    entities: [Post, Video, Product]
```

---

## Access Control (ACL)

### Simple ACL

```yaml
acl:
  create:
    public: true               # Anyone can create
  read:
    user: true                 # Authenticated users can read
  update:
    admin: true                # Only admins can update
  delete:
    admin: true
```

### Row-Level Security

```yaml
acl:
  read:
    self: true                 # User can read own records
    row_level: "userId = $user.id"
  update:
    self: true
    row_level: "userId = $user.id OR role = 'admin'"
  delete:
    admin: true
    moderator: true
    row_level: "authorId = $user.id"
```

### Role Hierarchy

```yaml
acl:
  create:
    user: true                 # user, moderator, admin, god can create
  read:
    public: true               # Everyone (including unauthenticated)
  update:
    moderator: true            # moderator, admin, god can update
  delete:
    admin: true                # admin, god can delete
```

### Custom Permissions

```yaml
acl:
  publish:                     # Custom operation
    admin: true
  archive:
    moderator: true
  restore:
    admin: true
```

---

## Validation Rules

### Field-Level Validation

```yaml
fields:
  email:
    type: email
    validation:
      - rule: email            # Built-in email validation
        message: "Invalid email address"
      - rule: regex
        pattern: "^[^@]+@[^@]+\\.[^@]+$"
        message: "Must be a valid email format"

  age:
    type: integer
    validation:
      - rule: range
        min: 18
        max: 120
        message: "Age must be between 18 and 120"

  username:
    type: string
    validation:
      - rule: length
        min: 3
        max: 20
      - rule: regex
        pattern: "^[a-zA-Z0-9_]+$"
        message: "Only letters, numbers, and underscores allowed"
      - rule: blacklist
        values: [admin, root, system]
        message: "Username is reserved"
```

### Built-in Validation Rules

| Rule | Parameters | Description |
|------|------------|-------------|
| `required` | - | Field must have a value |
| `email` | - | Valid email format |
| `url` | - | Valid URL format |
| `length` | min, max | String length range |
| `range` | min, max | Number range |
| `regex` | pattern | Regular expression match |
| `enum` | values | Must be one of values |
| `unique` | - | Value must be unique |
| `blacklist` | values | Value cannot be in list |
| `whitelist` | values | Value must be in list |

---

## Hooks (Lifecycle Events)

```yaml
hooks:
  beforeCreate:
    - validate_email           # Function name in runtime
    - generate_slug
  afterCreate:
    - send_welcome_email
    - create_profile
  beforeUpdate:
    - validate_changes
  afterUpdate:
    - invalidate_cache
  beforeDelete:
    - check_dependencies
  afterDelete:
    - cleanup_files
```

---

## Soft Deletes

```yaml
entity: Post
soft_delete: true              # Enable soft deletes
deleted_at_field: deletedAt    # Field name (default: deletedAt)

fields:
  deletedAt:
    type: bigint
    nullable: true
    description: "Timestamp when record was soft-deleted"
```

When `soft_delete: true`:
- `delete()` sets `deletedAt` to current timestamp
- `list()` automatically filters out deleted records
- `restore()` operation clears `deletedAt`

---

## Timestamps

### Auto Timestamps

```yaml
entity: Post
timestamps: true               # Auto-add createdAt/updatedAt

# Equivalent to:
fields:
  createdAt:
    type: bigint
    generated: true
  updatedAt:
    type: bigint
    generated: true
```

### Custom Timestamp Fields

```yaml
entity: Post
timestamps:
  created: created_at          # Custom field name
  updated: modified_at
  deleted: deleted_at          # For soft deletes
```

---

## Tenancy

### Multi-Tenant

```yaml
entity: Post
multi_tenant: true             # Auto-add tenantId field

# Equivalent to:
fields:
  tenantId:
    type: uuid
    required: true
    index: true
```

### Tenant Isolation

```yaml
entity: Post
multi_tenant: true
tenant_field: organizationId   # Custom tenant field name
tenant_isolation: strict       # strict | soft | none
```

**Isolation Levels**:
- `strict`: All queries automatically filter by tenantId, cannot be disabled
- `soft`: Default filter by tenantId, but can be overridden
- `none`: tenantId field exists but no automatic filtering

---

## Versioning

### Optimistic Locking

```yaml
entity: Post
versioning: true               # Add version field for optimistic locking

# Equivalent to:
fields:
  version:
    type: integer
    required: true
    default: 1
```

### Audit Trail

```yaml
entity: Post
audit_trail: true              # Track all changes

# Creates PostHistory table with:
# - historyId (primary key)
# - postId (FK to Post)
# - changes (JSON of field changes)
# - changedBy (userId)
# - changedAt (timestamp)
# - operation (create/update/delete)
```

---

## Caching

```yaml
entity: User
cache:
  enabled: true
  ttl: 3600                    # Cache lifetime in seconds
  strategy: write-through      # write-through | write-behind | cache-aside
  key_pattern: "user:{id}"     # Redis key pattern
```

---

## Computed Fields

```yaml
entity: User
computed:
  fullName:
    type: string
    expression: "firstName || ' ' || lastName"
    description: "Computed from firstName + lastName"

  age:
    type: integer
    expression: "EXTRACT(YEAR FROM AGE(CURRENT_DATE, birthDate))"
```

---

## Polymorphic Types

```yaml
entity: Media
polymorphic_type: true         # This entity can be referenced polymorphically

fields:
  id: {type: uuid, primary: true}
  type: {type: enum, values: [image, video, audio]}
  url: {type: string, required: true}
```

---

## Complete Example

```yaml
entity: BlogPost
version: "2.0"
description: "Blog post with comments and tags"
package: blog_forge
table: blog_posts              # Custom table name
timestamps: true               # Auto createdAt/updatedAt
soft_delete: true              # Enable soft deletes
multi_tenant: true             # Add tenantId
versioning: true               # Optimistic locking
audit_trail: true              # Track changes

fields:
  id:
    type: cuid
    primary: true
    generated: true

  title:
    type: string
    required: true
    max_length: 200
    description: "Post title"
    validation:
      - rule: length
        min: 10
        max: 200

  slug:
    type: slug
    unique: true
    generated: true
    description: "URL-friendly identifier"

  content:
    type: text
    required: true
    validation:
      - rule: length
        min: 100
        message: "Content must be at least 100 characters"

  status:
    type: enum
    values: [draft, published, archived]
    default: draft
    index: true

  authorId:
    type: uuid
    required: true
    index: true

  categoryId:
    type: cuid
    nullable: true

  viewCount:
    type: integer
    default: 0
    min: 0

  publishedAt:
    type: bigint
    nullable: true

  metadata:
    type: json
    default: {}

indexes:
  - fields: [slug]
    unique: true
  - fields: [status, publishedAt]
    name: published_posts
  - fields: [authorId, createdAt]
    name: author_recent

relations:
  author:
    type: belongs-to
    entity: User
    foreign_key: authorId
    on_delete: restrict

  category:
    type: belongs-to
    entity: Category
    foreign_key: categoryId
    nullable: true
    on_delete: set_null

  comments:
    type: has-many
    entity: Comment
    foreign_key: postId
    cascade_delete: true

  tags:
    type: many-to-many
    entity: Tag
    through: PostTag
    foreign_key: postId
    target_key: tagId

acl:
  create:
    user: true
  read:
    public: true
  update:
    self: true
    row_level: "authorId = $user.id"
    moderator: true
  delete:
    self: true
    row_level: "authorId = $user.id"
    admin: true
  publish:
    moderator: true

hooks:
  beforeCreate:
    - generate_slug
    - set_published_date
  afterCreate:
    - notify_subscribers
  beforeUpdate:
    - validate_status_transition
  afterUpdate:
    - invalidate_cache
  beforeDelete:
    - archive_content

cache:
  enabled: true
  ttl: 1800
  strategy: write-through

computed:
  readTime:
    type: integer
    expression: "LENGTH(content) / 200"
    description: "Estimated read time in minutes"
```

---

## Migration from Prisma YAML

Since C++ DBAL can now generate Prisma schema from YAML, the existing `prisma/schema.yaml` is redundant.

**Before** (dual maintenance):
```
dbal/shared/api/schema/entities/*.yaml  ← Source of truth
prisma/schema.yaml                      ← Manually kept in sync
prisma/schema.prisma                    ← Generated from schema.yaml
```

**After** (single source):
```
dbal/shared/api/schema/entities/*.yaml  ← ONLY source of truth
/tmp/dbal-prisma/schema.prisma          ← Auto-generated by C++ DBAL
```

**Action**: Delete `prisma/schema.yaml` and `prisma/codegen/` scripts. Use C++ DBAL generator instead.

---

## Future Features (TODO)

1. **Database-Specific Extensions**:
   - PostgreSQL: Array types, JSONB operators, full-text search
   - MongoDB: Embedded documents, geospatial queries
   - MySQL: Full-text indexes, spatial types

2. **Advanced Validation**:
   - Cross-field validation (`endDate > startDate`)
   - Async validation (unique check via API)
   - Custom validation functions

3. **Triggers**:
   - Database-level triggers
   - Application-level event handlers

4. **Sharding**:
   - Horizontal partitioning by tenant
   - Range/hash partitioning strategies

5. **Graph Queries**:
   - Recursive CTEs for tree structures
   - Graph traversal operations