Something went wrong. Try again.
This repository has no description
Something went wrong. Try again.
123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124-- AUSPEX Oracle Database Schema-- Cloudflare D1
-- Users table (ATProto identities)CREATE TABLE IF NOT EXISTS users ( did TEXT PRIMARY KEY, handle TEXT NOT NULL, letta_block_id TEXT, created_at TEXT DEFAULT (datetime('now')), last_query_at TEXT, query_count INTEGER DEFAULT 0, is_blocked INTEGER DEFAULT 0, block_reason TEXT);
-- Queries table (the queue + history)CREATE TABLE IF NOT EXISTS queries ( id TEXT PRIMARY KEY, user_did TEXT NOT NULL, user_handle TEXT NOT NULL, -- User's original query query_text TEXT NOT NULL, -- Status tracking status TEXT DEFAULT 'pending', -- pending, processing, complete, failed, rejected created_at TEXT DEFAULT (datetime('now')), processing_started_at TEXT, completed_at TEXT, -- Generation outputs (stored for semantic clustering later) image_prompt TEXT, audio_prompt TEXT, caption TEXT, -- Oracle's personalized caption for Bluesky post seed INTEGER, duration REAL, -- Results video_url TEXT, video_r2_key TEXT, thumbnail_url TEXT, -- Bluesky posting bsky_post_uri TEXT, bsky_post_cid TEXT, bsky_post_status TEXT DEFAULT 'pending', -- pending, success, failed bsky_post_retry_count INTEGER DEFAULT 0, bsky_post_error TEXT, bsky_post_last_attempt TEXT,
-- Error handling (for video generation) error_message TEXT, retry_count INTEGER DEFAULT 0,
-- Embeddings for semantic clustering (stored as JSON arrays) embedding_query TEXT, -- User's query embedding embedding_image TEXT, -- Image prompt embedding embedding_audio TEXT, -- Audio prompt embedding
-- Constellation 3D coordinates (computed via UMAP) constellation_x REAL, constellation_y REAL, constellation_z REAL, constellation_updated_at TEXT,
FOREIGN KEY (user_did) REFERENCES users(did));
-- Indexes for common queriesCREATE INDEX IF NOT EXISTS idx_queries_status ON queries(status);CREATE INDEX IF NOT EXISTS idx_queries_user ON queries(user_did);CREATE INDEX IF NOT EXISTS idx_queries_created ON queries(created_at);CREATE INDEX IF NOT EXISTS idx_queries_bsky_post_status ON queries(bsky_post_status);
-- Oracle status (singleton for tracking system state)CREATE TABLE IF NOT EXISTS oracle_status ( id INTEGER PRIMARY KEY CHECK (id = 1), is_online INTEGER DEFAULT 0, last_heartbeat TEXT, last_generation_at TEXT, total_visions INTEGER DEFAULT 0);
-- Initialize oracle statusINSERT OR IGNORE INTO oracle_status (id) VALUES (1);
-- Oracle murmuration state (singleton for visual mood ring)-- AUSPEX's internal state expressed through flock behaviorCREATE TABLE IF NOT EXISTS oracle_state ( id INTEGER PRIMARY KEY CHECK (id = 1),
-- Emotional dimensions (0.0 - 1.0) arousal REAL DEFAULT 0.5, -- Energy level (dormant → activated) valence REAL DEFAULT 0.5, -- Tone (negative → positive)
-- Behavioral dimensions (0.0 - 1.0, mapped to boids algorithm) cohesion REAL DEFAULT 0.5, -- Flock tightness (scattered → unified) alignment REAL DEFAULT 0.5, -- Direction matching (chaotic → parallel) separation REAL DEFAULT 0.3, -- Personal space (crowded → spread) turbulence REAL DEFAULT 0.2, -- Movement noise (smooth → erratic) speed_limit REAL DEFAULT 0.5, -- Velocity cap (slow → fast)
-- Visual dimensions hue REAL DEFAULT 220.0, -- Base color (0-360 degrees) saturation REAL DEFAULT 0.7, -- Color intensity (0.0 - 1.0) brightness REAL DEFAULT 0.6, -- Light level (0.0 - 1.0) glow_intensity REAL DEFAULT 0.5, -- Ethereal aura (0.0 - 1.0)
-- Metadata updated_at TEXT DEFAULT (datetime('now')));
-- Initialize oracle state (contemplative blue murmuration)INSERT OR IGNORE INTO oracle_state (id) VALUES (1);
-- Rate limiting helper viewCREATE VIEW IF NOT EXISTS user_daily_queries ASSELECT user_did, COUNT(*) as query_count_todayFROM queries WHERE created_at > datetime('now', '-24 hours')GROUP BY user_did;