---
id: DE-DATA_MODEL
title: "DSOL Data Models Documentation"
version: "1.0"
status: draft
created: 2026-02-27
lastUpdated: 2026-02-27
author: nguyetnta8
document_type: data-model
service: textiq-doc-extraction
---

# DSOL Data Models Documentation

**Project:** DSOL (Document processing service for Excel/Word extraction and semantic Q&A)
**Generated:** 2026-01-27
**Database Technologies:** PostgreSQL, Milvus Vector Database

---

## Table of Contents

1. [Overview](#overview)
2. [PostgreSQL Schema](#postgresql-schema)
   - [Entity Relationship Diagram](#entity-relationship-diagram)
   - [Tables](#tables)
3. [Milvus Vector Database Schema](#milvus-vector-database-schema)
4. [Data Flow](#data-flow)
5. [Migration History](#migration-history)

---

## Overview

DSOL uses a **hybrid database architecture**:

- **PostgreSQL**: Relational data storage for structured document metadata, extracted records, and cross-references
- **Milvus**: Vector database for semantic search over document embeddings (3072-dimensional vectors using Azure OpenAI text-embedding-3-large)

This architecture enables:
- **Structured Queries**: Fast lookups by document ID, sheet name, row number
- **Semantic Search**: Vector similarity search for natural language queries
- **Hybrid Retrieval**: Combining exact matches with semantic relevance

---

## PostgreSQL Schema

### Technology Stack

- **ORM**: SQLAlchemy 2.0+ with async support
- **Migrations**: Alembic
- **Database**: PostgreSQL with JSONB and ARRAY support
- **Key Features**:
  - UUID primary keys
  - JSONB for flexible content storage
  - GIN indexes for JSONB and ARRAY fields
  - Cascade deletes for referential integrity
  - Timestamps with timezone support

### Entity Relationship Diagram

```
┌─────────────────┐
│    ApiKey       │◄──────┐
│─────────────────│       │
│ id (PK)         │       │ Many
│ key_hash        │       │
│ name            │       │
│ rate_limit      │       │
│ is_active       │       │
└─────────────────┘       │
                          │
┌─────────────────────────┴───────┐
│         Document                │
│─────────────────────────────────│
│ id (PK)                         │◄──────┐
│ filename                        │       │
│ file_path                       │       │ Many
│ status                          │       │
│ sheet_count                     │       │
│ record_count                    │       │
│ api_key_id (FK)                 │       │
└─────────────────────────────────┘       │
          │                               │
          │ Many                          │
          ▼                               │
┌──────────────────────────────┐          │
│    ExtractedRecord           │          │
│──────────────────────────────│          │
│ id (PK)                      │◄─────┐   │
│ document_id (FK)             │      │   │
│ sheet_name                   │      │   │
│ table_title                  │      │   │
│ row_number                   │      │   │
│ content (JSONB)              │      │   │
│ resolved_content (JSONB)     │      │   │
│ tags (JSONB)                 │      │   │
└──────────────────────────────┘      │   │
          │                           │   │
          │ Many                      │   │
          ▼                           │   │
┌──────────────────────────────┐      │   │
│     CrossReference           │      │   │
│──────────────────────────────│      │   │
│ id (PK)                      │      │   │
│ source_record_id (FK) ───────┘      │   │
│ target_record_id (FK) ──────────────┘   │
│ reference_text               │          │
│ reference_type               │          │
│ resolved                     │          │
└──────────────────────────────┘          │
                                          │
┌─────────────────────────────┐           │
│   SymbolDictionary          │           │
│─────────────────────────────│           │
│ id (PK)                     │           │
│ document_id (FK) ───────────────────────┘
│ sheet_name                  │
│ symbol                      │
│ meaning                     │
│ context                     │
└─────────────────────────────┘

┌─────────────────────────────┐
│   DomainTermDictionary      │
│─────────────────────────────│
│ id (PK)                     │
│ term_original (UNIQUE)      │
│ term_ja                     │
│ term_en                     │
│ term_vi                     │
│ source_language             │
│ domain_category             │
│ source_document_id          │
│ translation_failed          │
└─────────────────────────────┘

┌─────────────────────────────┐
│       QueryLog              │
│─────────────────────────────│
│ id (PK)                     │
│ api_key_id (FK)             │
│ query_text                  │
│ answer_text                 │
│ confidence                  │
│ sources (JSONB)             │
│ processing_time_ms          │
└─────────────────────────────┘
```

### Tables

#### 1. **api_keys**

Authentication and rate limiting for API clients.

| Column | Type | Constraints | Description |
|--------|------|-------------|-------------|
| `id` | UUID | PRIMARY KEY, DEFAULT uuid4() | Unique API key identifier |
| `key_hash` | VARCHAR(255) | UNIQUE, NOT NULL | Hashed API key for secure storage |
| `name` | VARCHAR(255) | NULL | Human-readable name for the key |
| `rate_limit_per_minute` | INTEGER | DEFAULT 60 | Request limit per minute |
| `is_active` | BOOLEAN | DEFAULT TRUE | Whether the key is active |
| `created_at` | TIMESTAMP WITH TZ | DEFAULT now() | Creation timestamp |
| `last_used_at` | TIMESTAMP WITH TZ | NULL | Last usage timestamp |

**Relationships:**
- One-to-many with `documents`
- One-to-many with `query_logs`

**Indexes:**
- Primary key on `id`
- Unique index on `key_hash`

---

#### 2. **documents**

Uploaded Excel/Word documents and their processing status.

| Column | Type | Constraints | Description |
|--------|------|-------------|-------------|
| `id` | UUID | PRIMARY KEY, DEFAULT uuid4() | Unique document identifier |
| `filename` | VARCHAR(255) | NOT NULL | Original filename |
| `file_path` | VARCHAR(500) | NOT NULL | Storage path (S3/local) |
| `file_size_bytes` | INTEGER | NULL | File size in bytes |
| `status` | VARCHAR(50) | DEFAULT 'pending' | Processing status: pending, processing, completed, failed |
| `error_message` | TEXT | NULL | Error details if processing failed |
| `sheet_count` | INTEGER | NULL | Number of sheets (Excel only) |
| `record_count` | INTEGER | NULL | Total extracted records |
| `created_at` | TIMESTAMP WITH TZ | DEFAULT now() | Upload timestamp |
| `processed_at` | TIMESTAMP WITH TZ | NULL | Processing completion timestamp |
| `api_key_id` | UUID | FK → api_keys.id, NULL | Associated API key |

**Relationships:**
- Many-to-one with `api_keys`
- One-to-many with `extracted_records` (cascade delete)
- One-to-many with `symbol_dictionaries` (cascade delete)

**Indexes:**
- Primary key on `id`
- B-tree index on `status` (for status filtering)
- B-tree index on `api_key_id` (for API key lookups)

---

#### 3. **extracted_records**

Parsed table rows and content chunks from documents.

| Column | Type | Constraints | Description |
|--------|------|-------------|-------------|
| `id` | UUID | PRIMARY KEY, DEFAULT uuid4() | Unique record identifier |
| `document_id` | UUID | FK → documents.id (CASCADE), NOT NULL | Parent document |
| `sheet_name` | VARCHAR(255) | NOT NULL | Sheet name (Excel) or section (Word) |
| `table_title` | VARCHAR(255) | NULL | Table title if detected |
| `row_number` | INTEGER | NOT NULL | Row number in source table |
| `col_range` | VARCHAR(50) | NULL | Column range (e.g., "A:D") |
| `content` | JSONB | NOT NULL | Extracted key-value pairs from table row |
| `resolved_content` | JSONB | NULL | Content with symbols resolved to meanings |
| `headers` | JSONB | NULL | Table headers for this row |
| `tags` | JSONB | DEFAULT '[]' | Semantic tags (e.g., ["req:functional", "topic:auth"]) |
| `created_at` | TIMESTAMP WITH TZ | DEFAULT now() | Extraction timestamp |

**Relationships:**
- Many-to-one with `documents`
- One-to-many with `cross_references` as source (cascade delete)
- Many-to-one with `cross_references` as target

**Indexes:**
- Primary key on `id`
- B-tree index on `document_id` (for document lookups)
- B-tree index on `sheet_name` (for sheet filtering)
- GIN index on `content` (for JSONB queries)
- GIN index on `resolved_content` (for symbol-resolved queries)
- GIN index on `tags` (for tag filtering)

**JSONB Content Structure (Excel):**
```json
{
  "要件ID": "REQ-001",
  "要件名": "ユーザー認証",
  "優先度": "高",
  "ステータス": "実装中"
}
```

**JSONB Tags Structure:**
```json
[
  "req:functional",
  "topic:authentication",
  "priority:high",
  "リスク:セキュリティ"
]
```

---

#### 4. **symbol_dictionaries**

Symbol-to-meaning mappings extracted from documents.

| Column | Type | Constraints | Description |
|--------|------|-------------|-------------|
| `id` | UUID | PRIMARY KEY, DEFAULT uuid4() | Unique mapping identifier |
| `document_id` | UUID | FK → documents.id (CASCADE), NOT NULL | Source document |
| `sheet_name` | VARCHAR(255) | NULL | Sheet where symbol appears |
| `symbol` | VARCHAR(50) | NOT NULL | The symbol (e.g., "◯", "△", "×") |
| `meaning` | TEXT | NOT NULL | Symbol meaning/definition |
| `context` | VARCHAR(255) | NULL | Additional context |
| `created_at` | TIMESTAMP WITH TZ | DEFAULT now() | Creation timestamp |

**Relationships:**
- Many-to-one with `documents`

**Indexes:**
- Primary key on `id`
- B-tree index on `document_id`
- B-tree index on `symbol` (for symbol lookups)

**Example Data:**
| symbol | meaning | context |
|--------|---------|---------|
| ◯ | 完了 (Completed) | Status indicator |
| △ | 進行中 (In Progress) | Status indicator |
| × | 未対応 (Not Started) | Status indicator |

---

#### 5. **cross_references**

Links between records (e.g., "See REQ-001").

| Column | Type | Constraints | Description |
|--------|------|-------------|-------------|
| `id` | UUID | PRIMARY KEY, DEFAULT uuid4() | Unique reference identifier |
| `source_record_id` | UUID | FK → extracted_records.id (CASCADE), NOT NULL | Record containing the reference |
| `target_record_id` | UUID | FK → extracted_records.id (SET NULL), NULL | Referenced record (if resolved) |
| `reference_text` | TEXT | NOT NULL | Raw reference text (e.g., "REQ-001") |
| `reference_type` | VARCHAR(50) | NULL | Type of reference (req_id, row_ref, etc.) |
| `resolved` | BOOLEAN | DEFAULT FALSE | Whether target was successfully resolved |
| `created_at` | TIMESTAMP WITH TZ | DEFAULT now() | Creation timestamp |

**Relationships:**
- Many-to-one with `extracted_records` (as source, cascade delete)
- Many-to-one with `extracted_records` (as target, set null on delete)

**Indexes:**
- Primary key on `id`

---

#### 6. **domain_term_dictionary**

Multilingual domain-specific terminology with translations.

| Column | Type | Constraints | Description |
|--------|------|-------------|-------------|
| `id` | UUID | PRIMARY KEY, DEFAULT uuid4() | Unique term identifier |
| `term_original` | VARCHAR(255) | UNIQUE, NOT NULL | Original term as extracted |
| `term_ja` | VARCHAR(255) | NULL | Japanese translation |
| `term_en` | VARCHAR(255) | NULL | English translation |
| `term_vi` | VARCHAR(255) | NULL | Vietnamese translation |
| `source_language` | VARCHAR(10) | NOT NULL | Detected source language (ja/en/vi) |
| `domain_category` | VARCHAR(100) | DEFAULT 'general' | Domain category (finance, hr, engineering, etc.) |
| `source_document_id` | UUID | NULL | Document where term was first seen |
| `translation_failed` | BOOLEAN | DEFAULT FALSE | Flag if translation API failed |
| `created_at` | TIMESTAMP WITH TZ | DEFAULT now() | Creation timestamp |

**Indexes:**
- Primary key on `id`
- Unique index on `term_original`
- B-tree index on `term_ja` (for Japanese lookups)
- B-tree index on `term_en` (for English lookups)
- B-tree index on `term_vi` (for Vietnamese lookups)

**Purpose:** Enables cross-lingual search and query expansion for domain-specific terms.

---

#### 7. **query_logs**

API query history and analytics.

| Column | Type | Constraints | Description |
|--------|------|-------------|-------------|
| `id` | UUID | PRIMARY KEY, DEFAULT uuid4() | Unique log identifier |
| `api_key_id` | UUID | FK → api_keys.id, NULL | Associated API key |
| `query_text` | TEXT | NOT NULL | User's natural language query |
| `answer_text` | TEXT | NULL | Generated answer |
| `confidence` | FLOAT | NULL | Answer confidence score (0.0-1.0) |
| `sources` | JSONB | NULL | Retrieved source records with metadata |
| `processing_time_ms` | INTEGER | NULL | Query processing time in milliseconds |
| `created_at` | TIMESTAMP WITH TZ | DEFAULT now() | Query timestamp |

**Relationships:**
- Many-to-one with `api_keys`

**Indexes:**
- Primary key on `id`
- GIN index on `sources` (for JSONB queries)

**JSONB Sources Structure:**
```json
{
  "records": [
    {
      "record_id": "uuid",
      "document_id": "uuid",
      "sheet_name": "要件一覧",
      "score": 0.87,
      "excerpt": "ユーザー認証..."
    }
  ],
  "retrieval_method": "hybrid",
  "vector_search_results": 5,
  "keyword_search_results": 3
}
```

---

## Milvus Vector Database Schema

### Collection: `dsol_document_records`

**Purpose:** Store document embeddings for semantic similarity search.

### Schema Definition

| Field | Data Type | Dimension/Length | Description |
|-------|-----------|------------------|-------------|
| `id` | INT64 | - | Primary key (auto-generated) |
| `vector` | FLOAT_VECTOR | 3072 | Embedding vector from Azure OpenAI text-embedding-3-large |
| `record_id` | VARCHAR | 36 | Links to PostgreSQL extracted_records.id |
| `document_id` | VARCHAR | 36 | Links to PostgreSQL documents.id |
| `filename` | VARCHAR | 255 | Document filename for display |
| `document_type` | VARCHAR | 20 | "excel" or "word" |
| `text_content` | VARCHAR | 65535 | Full text for result display (truncated if needed) |
| `metadata` | JSON | - | Flexible type-specific metadata |
| `domain_category` | VARCHAR | 64 | Domain category: finance, hr, engineering, general, etc. |
| `domain_terms` | ARRAY<VARCHAR> | max_capacity: 50, max_length: 100 | Domain-specific terms for pre-filtering |
| `tags` | ARRAY<VARCHAR> | max_capacity: 20, max_length: 100 | Semantic tags (supports multilingual tags) |

### Indexes

1. **Vector Index (HNSW)**
   - **Field:** `vector`
   - **Metric:** COSINE similarity
   - **Parameters:**
     - M: 16 (bi-directional links)
     - efConstruction: 200 (candidate list size)
   - **Purpose:** Fast approximate nearest neighbor search

2. **Inverted Index (domain_category)**
   - **Field:** `domain_category`
   - **Purpose:** Efficient filtering by domain

3. **Inverted Index (domain_terms)**
   - **Field:** `domain_terms`
   - **Purpose:** Array filtering for domain-specific retrieval

4. **Inverted Index (tags)**
   - **Field:** `tags`
   - **Purpose:** Semantic tag filtering

### Metadata Structure

#### For Excel Documents:
```json
{
  "sheet_name": "要件一覧",
  "row_number": 42,
  "has_symbols": true,
  "cell_colors": {"A": "#FFFFFF", "B": "#FFFF00"},
  "record_json": {
    "要件ID": "REQ-001",
    "要件名": "ユーザー認証"
  },
  "record_json_truncated": false
}
```

#### For Excel Documents (DETAILED Pipeline - Images/Flowcharts):
```json
{
  "sheet_name": "システム構成図",
  "element_type": "Image",
  "image_number": 1,
  "position_key": "Sheet1_img_1",
  "image_format": "png",
  "width": 800,
  "height": 600,
  "cell_reference": "B5",
  "from_cell": "B5",
  "to_cell": "E12",
  "mime_type": "image/png"
}
```

#### For Word Documents:
```json
{
  "page_number": 3,
  "contains_table": true
}
```

### Vector Dimensions

- **Model:** Azure OpenAI `text-embedding-3-large`
- **Dimensions:** 3072
- **Distance Metric:** Cosine similarity
- **Search Method:** HNSW (Hierarchical Navigable Small World)

### Data Flow: PostgreSQL ↔ Milvus

```
┌─────────────────────────────────────────────────────────┐
│                   Indexing Pipeline                      │
└─────────────────────────────────────────────────────────┘
                            │
         1. Extract Record  │  2. Generate Embedding
                            ▼
          ┌──────────────────────────────┐
          │     ExtractedRecord          │
          │  (PostgreSQL)                │
          │  - id: UUID                  │
          │  - content: JSONB            │
          │  - tags: JSONB               │
          └──────────────┬───────────────┘
                         │
                         │ 3. Store in both DBs
        ┌────────────────┴─────────────────┐
        │                                  │
        ▼                                  ▼
┌─────────────────┐              ┌──────────────────┐
│   PostgreSQL    │              │     Milvus       │
│  Structured     │              │  Vector Store    │
│  Metadata       │              │  Embeddings      │
└─────────────────┘              └──────────────────┘
        │                                  │
        │                                  │
        └────────────────┬─────────────────┘
                         │
         4. Query combines both sources
                         │
                         ▼
          ┌──────────────────────────────┐
          │    Hybrid Retrieval          │
          │  - Vector Search (Milvus)    │
          │  - Metadata Filter (Postgres)│
          │  - Re-ranking                │
          └──────────────────────────────┘
```

**Key Points:**
- PostgreSQL `extracted_records.id` matches Milvus `record_id` for linking
- Each record has BOTH structured metadata (Postgres) AND semantic embedding (Milvus)
- Queries can filter by domain_terms before vector search for efficiency

---

## Data Flow

### 1. Document Upload & Processing

```
User Upload → FastAPI → S3/Local Storage → PostgreSQL (documents table)
                                        ↓
                            Celery/Airflow Processing
                                        ↓
                        ┌───────────────┴────────────────┐
                        │                                │
                        ▼                                ▼
              Extract Content                 Generate Embeddings
                        │                                │
                        ▼                                ▼
          PostgreSQL (extracted_records)    Milvus (vector store)
```

### 2. Query Processing

```
User Query → FastAPI → Query Analysis
                            │
            ┌───────────────┴────────────────┐
            │                                │
            ▼                                ▼
    Vector Search (Milvus)         Metadata Filter (PostgreSQL)
            │                                │
            └───────────────┬────────────────┘
                            │
                            ▼
                      Hybrid Re-ranking
                            │
                            ▼
                    LLM Answer Generation
                            │
                            ▼
                   PostgreSQL (query_logs)
```

---

## Migration History

### Alembic Migrations (PostgreSQL)

| Version | Date | Description |
|---------|------|-------------|
| `b88b5d952ac3` | 2024-11-26 | Initial schema: api_keys, documents, extracted_records, symbol_dictionaries, cross_references, query_logs |
| `04571689d204` | 2024-11-28 | Add `table_title` column to extracted_records |
| `6302e01e470e` | 2025-01-15 | Add `domain_term_dictionary` table for multilingual support |
| `2a19eed6a4b4` | 2025-01-20 | Add `tags` ARRAY column to extracted_records |
| `3e29c99b24ae` | 2025-01-22 | Migrate `tags` from ARRAY to JSONB for complex tag objects |

### Milvus Schema Migrations

| Date | Description |
|------|-------------|
| 2024-11 | Initial collection with basic fields (vector, record_id, document_id, text_content, metadata) |
| 2025-01 | Add domain-aware fields: `domain_category`, `domain_terms` (ARRAY) |
| 2025-01 | Add semantic tagging: `tags` (ARRAY with increased max_length=100 for multilingual support) |

**Migration Script:** `scripts/migrate_milvus_schema.py`
- Zero-downtime migration by creating new collection, copying data, and swapping

---

## Best Practices

### PostgreSQL

1. **JSONB Queries:** Use GIN indexes for fast JSONB searches
   ```sql
   SELECT * FROM extracted_records WHERE content @> '{"要件ID": "REQ-001"}'::jsonb;
   ```

2. **Cascade Deletes:** Deleting a document automatically cleans up all related records

3. **Timezone Handling:** All timestamps use `timezone=True` for consistency

### Milvus

1. **Vector Search with Filters:**
   ```python
   results = collection.search(
       data=[query_vector],
       anns_field="vector",
       param={"metric_type": "COSINE", "params": {"ef": 64}},
       limit=10,
       expr='domain_category == "finance" AND array_contains_any(tags, ["req:functional"])'
   )
   ```

2. **Domain Pre-filtering:** Use `domain_terms` to reduce search space before vector search

3. **Batch Inserts:** Insert in batches of 100 for optimal performance

---

## Database Maintenance

### PostgreSQL Maintenance

```bash
# Run pending migrations
alembic upgrade head

# Create new migration
alembic revision --autogenerate -m "description"

# Vacuum full database
psql -U user -d dsol -c "VACUUM ANALYZE;"
```

### Milvus Maintenance

```bash
# Verify schema
python scripts/verify_milvus_schema.py

# Rebuild index (if needed)
collection.drop_index("vector")
collection.create_index(field_name="vector", index_params={...})
collection.load()
```

---

## Performance Considerations

### PostgreSQL

- **JSONB GIN indexes** enable sub-second searches on 100K+ records
- **Partitioning:** Consider partitioning `extracted_records` by `document_id` for very large datasets (>1M records)
- **Connection pooling:** Use SQLAlchemy async pool with 10-20 connections

### Milvus

- **HNSW index** provides ~10ms search latency for 1M vectors
- **Memory usage:** ~4GB RAM per 1M vectors (3072 dims)
- **Search optimization:**
  - Use `ef` parameter (search list size) to trade speed vs. accuracy
  - Pre-filter with `domain_category` to reduce search space by 5-10x
  - Batch queries when possible (search multiple vectors at once)

---

**Document Version:** 1.0
**Last Updated:** 2026-01-27
**Maintainer:** DSOL Development Team

## Change History

| Version | Date | Author | Changes |
|---------|------|--------|---------|
| 1.0 | 2026-02-27 | nguyetnta8 | Initial creation |