Ga naar hoofdinhoud

Assets Schema

Database tabellen voor de asset pipeline en ticket systeem.

Overzicht​

Het asset systeem beheert:

  • Asset categories - Organisatie van asset types
  • Spec templates - Technische specificaties
  • Tickets - Asset requests en status
  • Files - Geüploade bestanden met versies
  • Placeholders - Tijdelijke assets tracking
AssetCategory
  │
  └── AssetSpecTemplate
        │
        └── AssetTicket
            │
            ├── AssetTicketComment
            │
            └── AssetFile (versies)

Designer (beheerders)
AssetPlaceholder (tracking)

Tabellen​

asset_category​

Asset categorieën voor organisatie.

#[table(name = asset_category, public)]
pub struct AssetCategory {
    #[primary_key]
    pub category_id: String,      // "character", "item", "map"
    pub display_name: String,     // "Characters"
    pub description: String,
    pub icon: String,             // Emoji of icon
    pub sort_order: u8,
}

Categorieën:

IDNaamBeschrijving
characterCharactersSpeelbare karakters en NPCs
itemItemsInventory items en tools
uiUIInterface elementen
mapMapsAchtergronden en tilemaps
effectEffectsAnimaties en particles
audioAudioMuziek en geluiden

asset_spec_template​

Herbruikbare specificatie templates.

#[table(name = asset_spec_template, public)]
pub struct AssetSpecTemplate {
    #[primary_key]
    #[auto_inc]
    pub id: u64,
    pub category_id: String,
    pub template_name: String,    // "Character Sprite Sheet"
    pub width: u32,               // Pixels
    pub height: u32,
    pub format: String,           // "png", "svg", "aseprite"
    pub color_mode: String,       // "rgba", "indexed"
    pub max_file_size_kb: u32,
    pub frame_count: Option<u32>, // Voor animaties
    pub frame_columns: Option<u32>, // Sprite sheet columns
    pub notes: String,            // Extra vereisten
    pub example_path: String,     // Voorbeeld asset
}

Voorbeelden:

TemplateDimensiesFormat
Character Walk Cycle512x128PNG
Item Icon64x64PNG
UI Button200x60SVG
Background1920x1080PNG

asset_ticket​

Asset requests/tickets.

#[table(name = asset_ticket, public)]
pub struct AssetTicket {
    #[primary_key]
    #[auto_inc]
    pub id: u64,
    pub ticket_code: String,      // "CHAR-001", "ITEM-042"
    pub category_id: String,
    pub spec_template_id: Option<u64>,
    pub title: String,            // "Player Character - Girl V2"
    pub description: String,
    pub reference_urls: String,   // JSON array van URLs
    pub style_notes: String,      // Art style requirements
    pub priority: u8,             // 0=low, 1=normal, 2=high, 3=urgent
    pub status: String,           // Status enum
    pub created_by: Identity,
    pub assigned_to: Option<String>, // Designer email
    pub created_at: Timestamp,
    pub updated_at: Timestamp,
    pub due_date: Option<String>,
    pub tags: String,             // JSON: ["forest", "cute"]
    pub related_item_id: Option<String>,
    pub related_map_id: Option<String>,
    pub game_path: String,        // "res://assets/characters/girl_v2.png"
}

Status Flow:

    ┌─────────┐
    │  open   │ ← Nieuw ticket
    └────┬────┘
         │ assign
         ▼
   ┌───────────────┐
   │ in_progress   │ ← Designer werkt eraan
   └───────┬───────┘
           │ submit
           ▼
     ┌──────────┐
     │  review  │ ← Wacht op review
     └────┬─────┘
          │
    ┌─────┴─────┐
    │           │
    ▼           ▼
┌──────────┐ ┌──────────┐
│ approved │ │ revision │ → terug naar in_progress
└──────────┘ └──────────┘

asset_ticket_comment​

Commentaar en feedback op tickets.

#[table(name = asset_ticket_comment, public)]
pub struct AssetTicketComment {
    #[primary_key]
    #[auto_inc]
    pub id: u64,
    pub ticket_id: u64,
    pub author: Identity,
    pub author_name: String,
    pub content: String,
    pub comment_type: String,     // "comment", "revision_request", "approval"
    pub created_at: Timestamp,
}

asset_file​

Geüploade bestanden met versiebeheer.

#[table(name = asset_file, public)]
pub struct AssetFile {
    #[primary_key]
    #[auto_inc]
    pub id: u64,
    pub ticket_id: u64,
    pub version: u32,             // 1, 2, 3... voor revisies
    pub filename: String,         // Originele bestandsnaam
    pub file_path: String,        // Storage URL
    pub file_size_bytes: u64,
    pub width: u32,
    pub height: u32,
    pub mime_type: String,
    pub uploaded_by: Identity,
    pub uploaded_at: Timestamp,
    pub is_approved: bool,
    pub notes: String,
}

asset_placeholder​

Tracking van placeholder vs echte assets.

#[table(name = asset_placeholder, public)]
pub struct AssetPlaceholder {
    #[primary_key]
    pub game_path: String,        // "res://assets/items/honey_jar.png"
    pub ticket_id: Option<u64>,   // Linked ticket
    pub placeholder_type: String, // "color", "text", "simple_shape"
    pub placeholder_color: String, // "#FF5733"
    pub description: String,      // Wat dit moet worden
    pub is_replaced: bool,
    pub replaced_at: Option<Timestamp>,
}

designer​

Designer accounts (apart van spelers).

#[table(name = designer, public)]
pub struct Designer {
    #[primary_key]
    pub email: String,
    pub display_name: String,
    pub role: String,             // "artist", "animator", "lead"
    pub specialties: String,      // JSON: ["characters", "ui"]
    pub is_active: bool,
    pub created_at: Timestamp,
    pub last_active: Timestamp,
}

Workflow​

1. Ticket Aanmaken​

{
    "ticket_code": "CHAR-015",
    "category_id": "character",
    "title": "NPC - Bakker",
    "description": "Vriendelijke bakker NPC voor het dorp",
    "reference_urls": "[\"https://example.com/ref1.jpg\"]",
    "style_notes": "Warm, uitnodigend, draagt schort",
    "priority": 1,
    "status": "open",
    "game_path": "res://assets/characters/npc_baker.png"
}

2. Designer Toewijzen​

UPDATE asset_ticket
SET assigned_to = 'designer@example.com',
    status = 'in_progress'
WHERE id = 15

3. Asset Uploaden​

{
    "ticket_id": 15,
    "version": 1,
    "filename": "npc_baker_v1.png",
    "file_path": "https://r2.storage/assets/...",
    "file_size_bytes": 45600,
    "width": 256,
    "height": 512
}

4. Review & Approve​

-- Na goedkeuring
UPDATE asset_ticket SET status = 'approved' WHERE id = 15;
UPDATE asset_file SET is_approved = true WHERE ticket_id = 15 AND version = 2;
UPDATE asset_placeholder SET is_replaced = true WHERE game_path = 'res://...';

Queries​

Open tickets per categorie​

SELECT category_id, COUNT(*) as count
FROM asset_ticket
WHERE status IN ('open', 'in_progress')
GROUP BY category_id

Designer workload​

SELECT assigned_to, COUNT(*) as active_tickets
FROM asset_ticket
WHERE status = 'in_progress'
GROUP BY assigned_to

Volgende​