# Database Taxonomy & Metadata Field Guide (V9.5)

This document provides a comprehensive schema reference for the **Esoteric Studies Database**. It details the core SQL tables and, crucially, the JSON metadata templates used for the heterogeneous "Lists" stored within the `entities` table.

---

## 🏗️ Core SQL Architecture

The database (`esoteric.db`) consists of strict relational tables for logs and a flexible "NoSQL-over-SQL" table for knowledge entities.

### 1. `chats`
*The registry of all research sessions.*
- **id** (TEXT): MD5 hash of the session filename. Stable ID.
- **filename** (TEXT): Original filename (e.g., `2025-10-15_hermetic_study.md`).
- **created_at** (TEXT): ISO timestamp.
- **topic_summary** (TEXT): High-level theme.

### 2. `chat_messages`
*The atomic units of conversation.*
- **id** (INTEGER): Auto-increment.
- **chat_id** (TEXT): Foreign key to `chats.id`.
- **role** (TEXT): 'user' or 'assistant'.
- **content** (TEXT): The raw text of the message.

### 3. `prompts`
*Extracted scholarly inquiries.*
- **id** (INTEGER): Auto-increment.
- **chat_id** (TEXT): Foreign key.
- **prompt_text** (TEXT): The raw question asked.
- **strategy_summary** (TEXT): Classification of the prompting technique (e.g., "Reflexive", "Direct").
- **prompt_topic** (TEXT): The extracted subject.

### 4. `entities`
*The "Lists" of the Mansion.*
- **id** (INTEGER): Auto-increment.
- **name** (TEXT): Unique name of the entity (e.g., "Hermes Trismegistus", "Calcination").
- **type** (TEXT): The category/list this entity belongs to.
- **attributes** (TEXT): A **JSON Blob** containing the rich metadata.

---

## 📜 Entity Lists & Metadata Templates

The `entities` table allows for "Rich Profiles" by storing flexible JSON in the `attributes` column. Below are the templates for each major `type`.

### 1. The Lessons List
**Type:** `Lesson`
**Source:** Ingested from `lessons_learned_v9_5.md`.
**Description:** Architectural, UX, and Digital Humanities insights gained during the project build.

**JSON Template:**
```json
{
  "category": "Database & Architecture", // or "Interface & UX", "Digital Humanities"
  "insight": "The use of WAL mode prevents database locks during multi-agent concurrent writes.",
  "designer": "Branch Manager", // The persona responsible for this insight
  "phase": "V9.5"
}
```

### 2. The Hermetic List
**Type:** `Hermetic Figure`, `Hermetic Text`, `Hermetic Topic`
**Source:** Ingested from `hermetic_tradition_v9_4.json`.
**Description:** Figures and texts related to the transmission of the *Prisca Theologia*.

**JSON Template (Figure):**
```json
{
  "period": "Renaissance", // "Late Antiquity", "Medieval", etc.
  "region": "Florence",
  "school": "Neoplatonism",
  "associated_texts": ["Corpus Hermeticum", "Asclepius"],
  "influence_score": 0.85
}
```

**JSON Template (Text):**
```json
{
  "author": "Hermes Trismegistus (attrib.)",
  "dating": "2nd Century AD",
  "language": "Greek",
  "manuscript_tradition": "Laurentian Library"
}
```

### 3. The Alchemy List
**Type:** `Alchemy Material`, `Alchemy Operation`, `Alchemy Symbol`
**Source:** Ingested from `alchemy_dictionary.json`.
**Description:** The materials, processes, and symbols of the Great Work.

**JSON Template:**
```json
{
  "symbol": "🜍", // Unicode alchemical symbol if available
  "phase": "Nigredo", // Associated phase of the work
  "nature": "Volatile", // "Fixed" or "Volatile"
  "correspondences": ["Saturn", "Lead", "Melancholy"],
  "definition": "The breakdown of matter into its prima materia."
}
```

### 4. The Scholar List
**Type:** `Scholar`, `Scholarly Topic`
**Source:** Extracted from PDF bibliographies and chat citations.
**Description:** Modern academics and their specific fields of study.

**JSON Template:**
```json
{
  "field": "History of Science",
  "institution": "Warburg Institute",
  "key_works": ["Giordano Bruno and the Hermetic Tradition"],
  "associated_primary_figures": ["Bruno", "Dee", "Kircher"]
}
```

### 5. The Design Team
**Type:** `Persona`
**Source:** System configuration.
**Description:** The meta-entities representing the agents working on the database.

**JSON Template:**
```json
{
  "role": "Narrative Designer",
  "archetype": "Storyteller",
  "focus_area": "Lineage & Transmission",
  "current_status": "Active"
}
```

---

## 🔒 System Invariants & Rules

1.  **MD5 ID Generation**: IDs for chats are *always* generated by hashing the filename `hashlib.md5(filename.encode()).hexdigest()`. This ensures idempotency; re-running ingestion does not create duplicate chat records.
2.  **Rich Profile Standard (V9.4)**: Any entity added to the `entities` table *must* have at least 3 keys in its `attributes` JSON. Entities with fewer than 3 keys are flagged as "Thin" by the `metadata_sweep_v9_5.py` and placed in the **Mend Queue**.
3.  **Unique Constraints**: The `name` field in `entities` is `UNIQUE`. We do not allow two entities named "Mercury". Instead, we use the `attributes` to distinguish context (e.g., `{"context": "Planet"}` vs `{"context": "Metal"}`).