# Aurora DB Schema Management

This guide covers how to define and manage collection schemas in Aurora DB using the Aurora Query Language (AQL).

## Collections Overview

Collections in Aurora DB are similar to tables in relational databases but with the flexibility of document stores. Each collection has a defined schema that enforces data integrity while allowing for rich types.

## Creating Collections

To create a new collection, use the `define collection` statement:

```graphql
mutation {
    schema {
        define collection users {
            id: Uuid! @primary
            name: String!
            email: Email! @unique
            age: Int
            active: Boolean = true
            preferences: JSON
            metadata: Any
        }
    }
}
```

The syntax includes:
1. Collection name (`users`)
2. Field definitions (`name: type`)
3. Type modifiers (`!` for required fields)
4. Default values (`= true`)
5. Directives (`@primary`, `@unique`)

## System ID & Data Purity

Aurora maintains a decoupled internal tracking system to prevent primary key collisions and ensure application data remains "pure."

### The Internal `_sid`
Every document in Aurora has a private internal field called `_sid` (System ID). This is a **UUIDv7**, which provides optimized time-ordered storage and high-performance lookups.

*   **Invisible by Default**: `_sid` is automatically omitted from all `QueryResult` and `MutationResult` JSON objects.
*   **Audit Mode**: To expose the internal `_sid` for debugging or logging, enable the `debug_audit` flag in `ExecutionOptions`.

### Application `id` vs. Internal `_sid`
When you query for `{ id }` in AQL, Aurora pulls the value strictly from your JSON data object. It never maps the internal system ID onto your `id` field. The `_sid` field is always a tracking key generated by UUIDv7 so Aurora knows how to paginate data and track its state properly. Your provided `id` remains exactly as you saved it in the document data.

## Field Types

Aurora DB supports a rich set of field types:

| Type          | Description                   | Example            |
| ------------- | ----------------------------- | ------------------ |
| `String`      | UTF-8 text data               | `"John Doe"`       |
| `Int`         | 64-bit integer                | `42`               |
| `Float`       | 64-bit floating point         | `3.14159`          |
| `Boolean`     | True/false value              | `true`             |
| `ID`          | Internal identifier           | `"user:123"`       |
| `Uuid`        | UUID v4 or v7                 | `550e8400...`      |
| `DateTime`    | ISO 8601 date-time            | `"2023-01-01T..."` |
| `Date`        | Calendar date                 | `"2023-01-01"`     |
| `Time`        | Time of day                   | `"12:00:00"`       |
| `JSON`        | Structured JSON object        | `{"x": 1}`         |
| `Email`       | Validated email address       | `"user@ex.com"`    |
| `URL`         | Validated URL                 | `"https://..."`    |
| `PhoneNumber` | Validated phone number        | `"+15550123"`      |
| `Any`         | Dynamic/Schemaless field      | *Anything*         |
| `[Type]`      | Array of values               | `["tag1", "tag2"]` |

### The `Any` Type
The `Any` type allows you to store mixed-type data or unstructured content within a specific field, effectively bypassing schema validation for that field only.

## Modifying Schemas

You can modify existing collections using the `alter collection` statement.

### Adding Fields

```graphql
mutation {
    schema {
        alter collection users {
            add last_login: DateTime
            add tags: [String]
        }
    }
}
```

### Removing Fields

```graphql
mutation {
    schema {
        alter collection users {
            drop temporary_field
        }
    }
}
```

### Renaming Fields

```graphql
mutation {
    schema {
        alter collection users {
            rename full_name to name
        }
    }
}
```

## Schema Directives

Directives allow you to add special behavior to fields:

*   `@primary`: Marks the field as the primary key.
*   `@unique`: Enforces a unique constraint across the collection.
*   `@indexed`: Creates an index for faster querying.
*   `@validate(min: 0, max: 100)`: Adds custom validation rules.

Example with validation:

```graphql
define collection products {
    sku: String! @primary
    price: Float! @validate(min: 0.0)
    stock: Int @validate(min: 0)
}
```

## Migrations

For complex schema changes that involve data transformation, use the `migrate` block:

```graphql
migration {
    "v1.0.1": {
        alter collection users {
            add status: String
        }
        migrate data in users {
            set status = "active" where { active: { eq: true } }
        }
    }
}
```