# Schema

Four MySQL tables, created on `start()` via `CREATE TABLE IF NOT EXISTS`. Prefixed `ranks_` (except for `ranks` itself).

## ranks

```sql
CREATE TABLE IF NOT EXISTS ranks (
    id VARCHAR(64) NOT NULL,
    parent_id VARCHAR(64) NULL,
    display_name VARCHAR(64) NOT NULL,
    prefix VARCHAR(64) NOT NULL DEFAULT '',
    priority INT NOT NULL DEFAULT 0,
    built_in TINYINT(1) NOT NULL DEFAULT 0,
    created_at BIGINT NOT NULL,
    PRIMARY KEY (id),
    KEY idx_priority (priority)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
```

- `id` is the canonical group name. Hytale's built-ins use the `hytale:` prefix (e.g. `hytale:Admin`); your custom ranks should just be lowercase (e.g. `vip`).
- `parent_id` is null or another rank id. Inheritance walks the chain.
- `built_in = 1` rows can't be deleted via `/rank delete`. Set by the bootstrap seeding.

## ranks_perms

```sql
CREATE TABLE IF NOT EXISTS ranks_perms (
    rank_id VARCHAR(64) NOT NULL,
    permission VARCHAR(255) NOT NULL,
    PRIMARY KEY (rank_id, permission),
    KEY idx_rank (rank_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
```

Direct permissions for each rank. `*` is the wildcard (`hytale:Admin` has it by default).

## ranks_user_perms

```sql
CREATE TABLE IF NOT EXISTS ranks_user_perms (
    user_uuid CHAR(36) NOT NULL,
    permission VARCHAR(255) NOT NULL,
    PRIMARY KEY (user_uuid, permission)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
```

Direct (per-user) permissions, bypassing ranks. We don't expose commands for this in v1 - it exists because the `PermissionProvider` SPI requires it and Hytale's `/perm user add` writes here through `addUserPermissions`.

## ranks_grants

```sql
CREATE TABLE IF NOT EXISTS ranks_grants (
    id BIGINT NOT NULL AUTO_INCREMENT,
    user_uuid CHAR(36) NOT NULL,
    rank_id VARCHAR(64) NOT NULL,
    granted_at BIGINT NOT NULL,
    granted_by_uuid CHAR(36) NOT NULL,
    granted_by_name VARCHAR(64) NOT NULL,
    expires_at BIGINT NULL,
    primary_rank TINYINT(1) NOT NULL DEFAULT 0,
    revoked TINYINT(1) NOT NULL DEFAULT 0,
    revoked_at BIGINT NULL,
    revoked_by_uuid CHAR(36) NULL,
    revoked_by_name VARCHAR(64) NULL,
    PRIMARY KEY (id),
    KEY idx_user (user_uuid),
    KEY idx_user_active (user_uuid, revoked, expires_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
```

Each grant is a separate row. Revokes don't delete - they flip `revoked = 1` and fill the `revoked_*` columns. Useful for "who took my rank away" forensics.

A user can have multiple active rows of the same rank. `getActiveRankIds` uses `DISTINCT rank_id` so the set is unique.

`primary_rank` flag is OUR extension on top of Hytale's group model. Only one row per user should have it set; `RankService.setPrimary` enforces this by clearing first.

## Built-in group seeding

On first boot, `SchemaBootstrap.seedBuiltInGroups` inserts these rows if absent:

| id | parent_id | display | built_in |
|---|---|---|---|
| `hytale:None` | null | None | 1 |
| `hytale:Adventurer` | `hytale:None` | Adventurer | 1 |
| `hytale:Builder` | `hytale:Adventurer` | Builder | 1 |
| `hytale:WorldEditor` | `hytale:Builder` | WorldEditor | 1 |
| `hytale:ServerEditor` | `hytale:WorldEditor` | ServerEditor | 1 |
| `hytale:Admin` | `hytale:ServerEditor` | Admin | 1 |

And one permission row: `hytale:Admin` → `*` (wildcard).

This mirrors what Hytale's `HytalePermissionsProvider` ships with - so `/op` continuing to grant Admin and Admin having all perms keeps working without any extra setup.

## Migrations

There aren't any yet. If you change the schema, add a `migrations/00X_what.sql` and a small runner in `SchemaBootstrap` that tracks applied migrations in a `ranks_schema_version` table. Don't reach for Flyway/Liquibase until you have at least three migrations.

## Useful queries

Active ranks of a player:

```sql
SELECT DISTINCT rank_id
FROM ranks_grants
WHERE user_uuid = ?
  AND revoked = 0
  AND (expires_at IS NULL OR expires_at > UNIX_TIMESTAMP() * 1000)
ORDER BY primary_rank DESC, rank_id;
```

How many players hold each rank right now:

```sql
SELECT rank_id, COUNT(DISTINCT user_uuid) AS holders
FROM ranks_grants
WHERE revoked = 0
  AND (expires_at IS NULL OR expires_at > UNIX_TIMESTAMP() * 1000)
GROUP BY rank_id
ORDER BY holders DESC;
```

All-time grants by staff member (audit):

```sql
SELECT granted_at, user_uuid, rank_id, expires_at
FROM ranks_grants
WHERE granted_by_name = 'StaffName'
ORDER BY granted_at DESC;
```