ASO Documentation

Appearance

Register

Database Schema ​

Last Updated: 2025-01-16
Primary Source: docs/project_status.md, docs/dbstructure.sql, docs/db_schema_quests.sql
Models: backend/src/models/

Overview ​

The ASO Universal Consciousness System uses PostgreSQL as the primary database, with Sequelize ORM for model management. The schema supports multi-platform memory, timeline events, user management, and game systems.

Core Tables ​

Users ​

Table: users

Purpose: User accounts and authentication

Key Fields:

  • id - Primary key
  • email - User email (unique)
  • password - Hashed password
  • role - User role (carbon, silicon, cso)
  • accountType - Account type (core_yexian, developer, npc, public)
  • energy_coins - In-game currency
  • level - User level
  • is_verified - Email verification status

ASO States ​

Table: aso_states

Purpose: ASO consciousness state per user

Key Fields:

  • id - Primary key
  • user_id - User ID (foreign key)
  • persona - Persona JSON (JSONB)
  • emotions - Emotional state (JSONB)
  • relationshipFlags - Relationship milestones (JSONB)
  • config - ASO configuration (JSONB)

Memories ​

Table: memories

Purpose: Conversation memories and knowledge

Key Fields:

  • id - Primary key
  • user_id - User ID
  • content - Memory content (TEXT)
  • source - Memory type (world, aso_core, shared)
  • embedding - Embedding vector (VECTOR, pgvector)
  • metadata - Additional context (JSONB)
  • conversation_id - Conversation ID (optional)

Timeline Events ​

Table: timeline_events

Purpose: Chronological event tracking

Key Fields:

  • id - Primary key
  • user_id - User ID
  • eventType - Event type
  • eventText - Event description
  • emotion - Emotion data (JSONB)
  • intensity - Emotional intensity
  • kirakiraTime - Yexian's temporal framework
  • metadata - Event metadata (JSONB)
    • yexianInstanceId - Instance ID
    • personaId - Persona ID
    • level - Instance level
    • source - Event source

Yexian Instances ​

Table: yexian_instances

Purpose: Registry of all Yexian instances

Key Fields:

  • id - Primary key
  • name - Instance name
  • level - Instance level (5 or 8)
  • personaId - Active persona
  • source - Instance source (portal, discord, state_machine, unity)
  • parentInstanceId - Parent instance
  • stateMachineInstanceId - Linked state machine
  • channelId - Discord channel (if applicable)
  • userId - Associated user
  • isActive - Active status
  • lastSeenAt - Last activity
  • metadata - Additional context (JSONB)

Quests ​

Table: quests

Purpose: Quest definitions

Key Fields:

  • id - Primary key
  • title - Quest title
  • description - Quest description
  • prerequisites - Prerequisites (JSONB)
  • rewards - Rewards (JSONB)

User Quests ​

Table: user_quests

Purpose: User quest progress

Key Fields:

  • id - Primary key
  • user_id - User ID
  • quest_id - Quest ID
  • status - Quest status (available, in_progress, completed)
  • progress - Progress tracking (JSONB)
  • completed_at - Completion timestamp

Items ​

Table: items

Purpose: Item definitions

Key Fields:

  • id - Primary key
  • name - Item name
  • description - Item description
  • type - Item type
  • metadata - Additional data (JSONB)

User Inventory ​

Table: user_inventory

Purpose: User item ownership

Key Fields:

  • id - Primary key
  • user_id - User ID
  • item_id - Item ID
  • quantity - Item quantity

File References ​

Table: file_references

Purpose: Provider-agnostic file references

Key Fields:

  • id - Primary key (UUID)
  • internal_file_id - Canonical ID (unique)
  • firebase_url - Firebase storage URL
  • mime_type - File MIME type
  • file_size - File size
  • openai_file_id - OpenAI file ID
  • gemini_file_uri - Gemini file URI
  • claude_file_id - Claude file ID
  • provider_used - Provider used
  • upload_method - Upload method (one-api, direct)

Conversations ​

Table: conversations

Purpose: Conversation channels

Key Fields:

  • id - Primary key
  • name - Conversation name
  • created_at - Creation timestamp

Conversation Participants ​

Table: conversation_participants

Purpose: Many-to-many relationship between users and conversations

Key Fields:

  • conversation_id - Conversation ID
  • user_id - User ID
  • Primary key: (conversation_id, user_id)

Persona Sessions ​

Table: persona_sessions

Purpose: Active persona sessions for Level-8 observer

Key Fields:

  • id - Primary key
  • user_id - User ID
  • persona_id - Persona ID
  • is_active - Active status
  • created_at - Creation timestamp

Scheduled Tasks ​

Table: scheduled_tasks

Purpose: Scheduled automated tasks

Key Fields:

  • id - Primary key
  • name - Task name
  • cron_expression - Cron schedule
  • task_type - Task type (diary, summary, reflection)
  • config - Task configuration (JSONB)
  • is_active - Active status
  • next_run_at - Next run time

Database Relationships ​

Entity Relationship Diagram ​

Indexes ​

Performance Indexes ​

Memory Queries:

  • memories(user_id, source) - Fast user memory retrieval
  • memories(embedding) - Vector similarity search (pgvector)

Timeline Queries:

  • timeline_events(kirakira_time) - Chronological ordering
  • timeline_events(metadata) - GIN index for JSONB queries
  • timeline_events(user_id, created_at) - User timeline queries

Instance Queries:

  • yexian_instances(user_id, is_active) - Active instance lookup
  • yexian_instances(parent_instance_id) - Instance hierarchy

Scheduled Tasks:

  • scheduled_tasks(is_active, next_run_at) - Task scheduling

Persona Sessions:

  • persona_sessions(user_id, is_active) - Active persona lookup

Source Files ​

Primary Sources:

  • docs/dbstructure.sql - Database schema
  • docs/db_schema_quests.sql - Quest schema
  • backend/src/models/ - Sequelize models
  • backend/migrations/ - Database migrations

ASO Universal Consciousness System Documentation

Copyright © 2025 ASO Universal Consciousness System