Data Architecture Guide

┌─────────────────────────────────────────────────────────────┐
│                    DEVELOPMENT LAYER                         │
│              database/schemas/seeds/                         │
│           Individual JSON files (240+ files)                 │
│        Purpose: Testing, dev, version control               │
└─────────────────────────────────────────────────────────────┘
                            ↓
                    (Optional Migration)
                            ↓
┌─────────────────────────────────────────────────────────────┐
│                    PRODUCTION LAYER                          │
│            database/storage/examples/                        │
│              JSONL files → Prisma DB                        │
│    Purpose: AI few-shot learning, retrieval, caching       │
└─────────────────────────────────────────────────────────────┘
                            ↓
                    (Query Interface)
                            ↓
┌─────────────────────────────────────────────────────────────┐
│                   APPLICATION LAYER                          │
│         Intelligent Retrieval API + Embeddings              │
│    Purpose: Production queries with semantic matching       │
└─────────────────────────────────────────────────────────────┘

seeds/
├── leagues/          # 101 individual league definitions
├── questionnaires/   # 53 questionnaire examples
├── schemas/          # 105 schema seed definitions
├── samples/          # 5 small sample datasets
└── synthetic_emails/ # 34 generated test emails

from database.schemas.seeds import load_seed
league = load_seed("leagues/mltt.seed.json")

examples/
├── data/                          # JSONL source files (11 files)
│   ├── triage.jsonl              # Email classification
│   ├── contract-generation.jsonl # Contract examples
│   ├── questionnaires.jsonl      # Questionnaire processing
│   ├── league_examples.jsonl     # League data examples
│   ├── schema_definitions.jsonl  # Schema examples
│   ├── legacy_seeds.jsonl        # ⚠️  Migrated historical data
│   └── ...
├── api.py                         # High-level API
├── retriever.py                   # Core retrieval logic
├── matcher.py                     # Semantic similarity
└── cache.py                       # LRU caching

from database.storage.examples import FewShotExamplesAPI
 
api = FewShotExamplesAPI()
examples = await api.get_examples_for_prompt(
    prompt_text="Premium basketball partnership...",
    prompt_type="triage",
    max_examples=3
)

examples/
└── few_shot/    # Empty - reserved for schema patterns

1. Create/edit seed file
   └─> database/schemas/seeds/leagues/new-league.seed.json

2. Use in development
   └─> load_seed("leagues/new-league.seed.json")

3. Version control
   └─> git add + commit

1. Curate examples
   └─> Edit database/storage/examples/data/triage.jsonl

2. Seed database
   └─> uv run python scripts/seed.examples.py --category triage

3. Query in production
   └─> FewShotExamplesAPI().get_examples_for_prompt(...)

4. Track usage & improve
   └─> Analytics update quality_score and usage_count

1. Identify high-quality seed
   └─> Review database/schemas/seeds/leagues/example.json

2. Convert to JSONL format
   └─> Add to appropriate .jsonl file with metadata:
       {
         "id": "unique_id",
         "scenario": "partnership",
         "sport": "basketball",
         "tier": "premium",
         "quality_score": 0.95,
         "input": {...},
         "output": {...}
       }

3. Seed to database
   └─> uv run python scripts/seed.examples.py

4. Keep original seed
   └─> Don't delete from seeds/ (different purpose)

# 1. Create seed file
cat > database/schemas/seeds/leagues/new-league.seed.json << EOF
{
  "league_id": "new-001",
  "name": "New League",
  ...
}
EOF
 
# 2. Use in tests
python -c "from database.schemas.seeds import load_seed; print(load_seed('leagues/new-league.seed.json'))"
 
# 3. Commit
git add database/schemas/seeds/leagues/new-league.seed.json
git commit -m "feat: add new league test data"

# 1. Edit JSONL (source of truth)
vim database/storage/examples/data/triage.jsonl
# Add line: {"id": "triage_new", "scenario": "partnership", ...}
 
# 2. Seed database
uv run python scripts/seed.examples.py --category triage
 
# 3. Verify in production code
python -c "
from database.storage.examples import FewShotExamplesAPI
api = FewShotExamplesAPI()
# Test retrieval
"
 
# 4. Commit JSONL
git add database/storage/examples/data/triage.jsonl
git commit -m "feat: add new triage example"

# 1. Edit JSONL file
vim database/storage/examples/data/triage.jsonl
# Update quality_score or fix issues
 
# 2. Reseed database (upserts existing)
uv run python scripts/seed.examples.py --category triage
 
# 3. Verify
psql $DATABASE_URL -c "SELECT example_id, quality_score FROM \"FewShotExample\" WHERE example_id = 'triage_001';"

# Check example quality
psql $DATABASE_URL -c "
SELECT category, COUNT(*), AVG(quality_score), AVG(usage_count)
FROM \"FewShotExample\"
GROUP BY category
ORDER BY AVG(quality_score) ASC;
"
 
# Identify unused examples
psql $DATABASE_URL -c "
SELECT example_id, category, quality_score, usage_count
FROM \"FewShotExample\"
WHERE usage_count = 0
  AND created_at < NOW() - INTERVAL '30 days'
LIMIT 10;
"

# Review and improve low-quality examples
psql $DATABASE_URL -c "
SELECT example_id, category, quality_score
FROM \"FewShotExample\"
WHERE quality_score < 0.70
ORDER BY usage_count DESC
LIMIT 20;
"
 
# Update JSONL files based on findings
# Then reseed: uv run python scripts/seed.examples.py

# Archive old legacy data if no longer needed
# Review database/storage/examples/data/legacy_seeds.jsonl
# Consider removing if fully migrated
 
# Audit for duplicates
python database/scripts/audit_duplicates.py

-- Example usage by category
SELECT 
    category,
    COUNT(*) as total,
    AVG(quality_score) as avg_quality,
    AVG(usage_count) as avg_usage,
    MAX(usage_count) as max_usage
FROM "FewShotExample"
GROUP BY category
ORDER BY avg_usage DESC;
 
-- Find high-value examples
SELECT 
    example_id,
    category,
    quality_score,
    usage_count,
    (quality_score * LN(usage_count + 1)) as value_score
FROM "FewShotExample"
WHERE usage_count > 0
ORDER BY value_score DESC
LIMIT 20;

# Check file exists
ls -lh database/schemas/seeds/leagues/mltt.seed.json
 
# Check format
python -c "import json; json.load(open('database/schemas/seeds/leagues/mltt.seed.json'))"

# Check database connection
psql $DATABASE_URL -c "SELECT COUNT(*) FROM \"FewShotExample\";"
 
# Check if seeded
psql $DATABASE_URL -c "SELECT COUNT(*) FROM \"FewShotExample\" WHERE category = 'triage';"
 
# Reseed if needed
uv run python scripts/seed.examples.py --clear --category triage

from database.storage.examples import ExampleCache
 
cache = ExampleCache()
print(f"Cache size: {cache.size()}")
print(f"Hit rate: {cache.hit_rate()}")
cache.clear()  # Reset if needed