Data Schema

The vectorization system uses a simple 4-table hierarchy that separates concerns and provides flexibility for document processing and retrieval.

Schema Overview

UML Entity-Relationship Diagram

Database Tables

Files Table

Stores raw file metadata and source information.

CREATE TABLE IF NOT EXISTS files (
    file_id TEXT PRIMARY KEY,
    filename TEXT NOT NULL,
    source_path TEXT,
    url TEXT,
    extension TEXT,
    mime_type TEXT,
    size_bytes INTEGER,
    uploaded_at TEXT NOT NULL DEFAULT (datetime('now')),
    file_hash TEXT NOT NULL,
    binary_data BLOB
);

Purpose: Track original files with optional binary content storage

• Flexible Sources: Support both local files (source_path) and remote files (url)
• File Metadata: Extension, MIME type, and size for processing decisions
• Binary Storage: Optional binary_data field stores the actual file content
• HTTP File Serving: Files with binary_data can be served directly via the built-in HTTP endpoint at /databases/:databaseId/files/:fileId. See File Serving for details.
• Deduplication: Use file_hash to prevent duplicate uploads
• Audit Trail: Track when files were uploaded

Documents Table

Stores processed content extracted from files.

CREATE TABLE IF NOT EXISTS documents (
    document_id TEXT PRIMARY KEY,
    file_id TEXT NOT NULL,
    title TEXT NOT NULL,
    markdown TEXT NOT NULL,
    metadata JSON DEFAULT '{}',
    processed_at TEXT NOT NULL DEFAULT (datetime('now')),
    content_hash TEXT NOT NULL,
    
    FOREIGN KEY (file_id) REFERENCES files(file_id) ON DELETE CASCADE
);

Purpose: Store processed, searchable content

• Markdown Format: Standardized content format for all document types
• One-to-Many: One file can produce multiple documents (e.g., PDF chapters)
• Flexible Metadata: JSON field for document-level information
• Processing Tracking: Know when content was extracted
• Content Integrity: Hash for change detection

Chunks Table

Stores text segments optimized for embedding and retrieval.

CREATE TABLE IF NOT EXISTS chunks (
    chunk_id TEXT PRIMARY KEY,
    document_id TEXT NOT NULL,
    chunk_index INTEGER NOT NULL,
    text TEXT NOT NULL,
    token_count INTEGER NOT NULL,
    metadata JSON DEFAULT '{}',
    
    FOREIGN KEY (document_id) REFERENCES documents(document_id) ON DELETE CASCADE
);

Purpose: Break documents into searchable segments

• Ordered Chunks: chunk_index maintains document structure
• Token Tracking: Monitor embedding costs and context limits
• Flexible Metadata: JSON field for strategy-specific data
• Configurable Size: Support different chunking strategies

Vectors Table

Stores embeddings for similarity search.

CREATE TABLE IF NOT EXISTS vectors (
    chunk_id TEXT PRIMARY KEY,
    vector F32_BLOB(1536) NOT NULL,
    embedding_model TEXT NOT NULL,
    
    FOREIGN KEY (chunk_id) REFERENCES chunks(chunk_id) ON DELETE CASCADE
);

-- LibSQL vector index for similarity search
CREATE INDEX IF NOT EXISTS vectors_idx ON vectors (libsql_vector_idx(vector));

Purpose: Enable fast similarity search

• LibSQL Vectors: Native F32_BLOB type for efficient storage
• Model Tracking: Know which embedding model was used
• Vector Index: Optimized similarity search with DiskANN
• Separate Storage: Allows re-embedding without re-chunking

Design Rationale

Why Separate Tables?

1. Processing Pipeline Flexibility

Each table represents a distinct processing stage:

File Upload → Content Extraction → Chunking → Embedding

You can:

• Upload files without immediate processing
• Extract content without chunking
• Chunk without embedding (useful for batch processing)
• Re-embed with new models without re-processing

2. Storage Optimization

• Files: Metadata only, no content duplication
• Documents: Text storage optimized for reading
• Chunks: Indexed for retrieval
• Vectors: Specialized storage with vector indexes

3. Multiple Embeddings

The separation allows:

• Testing different embedding models on same chunks
• A/B testing retrieval quality
• Gradual migration to new models
• Multi-modal embeddings (future)

4. Clear Data Ownership

• Files: Source tracking and audit
• Documents: Content ownership and licensing
• Chunks: Processing configuration
• Vectors: Model and cost tracking

Why This Structure?

Files → Documents Separation

• Multiple Formats: PDF, Word, HTML → unified Markdown
• Multiple Documents: One PDF → multiple chapters/sections
• Processing Status: Track failed extractions separately
• Source Preservation: Keep original metadata without storing files

Documents → Chunks Separation

• Flexible Chunking: Different strategies for different content
• Overlap Control: Chunks can share content at boundaries
• Size Optimization: Balance between context and efficiency
• Metadata Enrichment: Add chunk-specific information

Chunks → Vectors Separation

• Model Evolution: Upgrade embeddings without reprocessing
• Cost Management: Track embedding API usage
• Selective Embedding: Not all chunks need vectors
• Multiple Embeddings: Compare different models

TypeScript Types

Database Record Types (with Zod Validation)

import { z } from 'zod';

// File Record Schema
export const FileRecordSchema = z.object({
    file_id: z.string(),
    filename: z.string(),
    source_path: z.string().optional(),
    url: z.string().optional(),
    extension: z.string().optional(),
    mime_type: z.string().optional(),
    size_bytes: z.number().optional(),
    uploaded_at: z.string().optional(),
    file_hash: z.string(),
    binary_data: z.instanceof(Buffer).optional(),
});

export type FileRecord = z.infer<typeof FileRecordSchema>;

// Document Record Schema
export const DocumentRecordSchema = z.object({
    document_id: z.string(),
    file_id: z.string(),
    title: z.string(),
    markdown: z.string(),
    metadata: z.string(), // JSON string
    processed_at: z.string(),
    content_hash: z.string(),
});

export type DocumentRecord = z.infer<typeof DocumentRecordSchema>;

// Chunk Record Schema
export const ChunkRecordSchema = z.object({
    chunk_id: z.string(),
    document_id: z.string(),
    chunk_index: z.number(),
    text: z.string(),
    token_count: z.number(),
    metadata: z.string(), // JSON string
});

export type ChunkRecord = z.infer<typeof ChunkRecordSchema>;

// Vector Record Schema
export const VectorRecordSchema = z.object({
    chunk_id: z.string(),
    vector: z.instanceof(Float32Array), // LibSQL F32_BLOB
    embedding_model: z.string(),
});

export type VectorRecord = z.infer<typeof VectorRecordSchema>;

Pipeline Processing Types

// Document metadata interface
export interface DocumentMetadata {
    type: string;
    source: string;
    createdAt?: string;
    tags?: string[];
    [key: string]: any;
}

// Base document chunk interface
export interface DocumentChunk {
    id: string;
    documentId: string;
    content: string;
    index: number;
    metadata: DocumentMetadata;
}

// Enriched chunk with additional metadata
export interface EnrichedChunk extends DocumentChunk {
    enrichedMetadata?: Record<string, any>;
}

// Embedded chunk with vector embedding
export interface EmbeddedChunk extends EnrichedChunk {
    embedding: number[];
    embeddingModel: string;
    embeddedAt?: string;
}

// Stored chunk with database fields
export interface StoredChunk extends DocumentChunk {
    chunk_id: string;
    token_count: number;
    stored_at: string;
}

// Stored enriched chunk
export interface StoredEnrichedChunk extends EnrichedChunk {
    chunk_id: string;
    token_count: number;
    stored_at: string;
}

// Vector storage result
export interface StoredVector {
    chunkId: string;
    documentId: string;
    stored: boolean;
    error?: string;
}

// Vector with full data
export interface StoredVectorData extends StoredVector {
    vector: Float32Array;
    embedding_model: string;
    stored_at: string;
}

// Processing pipeline result
export interface ProcessingResult {
    documentsProcessed: number;
    chunksCreated: number;
    vectorsStored: number;
    errors: string[];
    processingTime: number;
}

Query Patterns

Search Query with Joins

SELECT 
    c.chunk_id,
    c.text as content,
    c.metadata,
    d.title as document_title,
    f.filename as source_file,
    vector_distance_cos(v.vector, vector32(?)) as distance
FROM vector_top_k('vectors_idx', vector32(?), ?) AS top_k
JOIN vectors v ON v.chunk_id = top_k.id
JOIN chunks c ON c.chunk_id = v.chunk_id
JOIN documents d ON d.document_id = c.document_id
JOIN files f ON f.file_id = d.file_id
ORDER BY distance ASC;

Document Chunk Count

SELECT 
    d.document_id,
    d.title,
    COUNT(c.chunk_id) as chunk_count,
    SUM(c.token_count) as total_tokens
FROM documents d
LEFT JOIN chunks c ON c.document_id = d.document_id
GROUP BY d.document_id;

Best Practices

ID Generation

• Use descriptive prefixes: file_, doc_, chunk_
• Include timestamps for debugging
• Ensure uniqueness across distributed systems

Metadata Usage

Document metadata JSON:

• Language (detected or specified)
• Tags and categories
• Summary or abstract
• Author information
• Processing method used
• Document version
• Custom application data

Chunk metadata JSON:

• Section titles and hierarchy
• Page numbers (for PDFs)
• Language detection results
• Quality scores
• Semantic coherence scores
• Entity mentions
• Custom strategy data

Data Integrity

• Use transactions for multi-table operations
• Cascade deletes to maintain consistency
• Hash verification for content changes
• Regular cleanup of orphaned records

Performance

• Index foreign keys for join performance
• Batch inserts for large documents
• Use appropriate chunk sizes (512 tokens recommended)
• Monitor vector index performance

Available CRUD Operations

File Operations

• aktorCreateFile - Create new file record
• aktorGetFileById - Retrieve file by ID
• aktorListFiles - List files with pagination
• aktorUpdateFile - Update file metadata
• aktorDeleteFile - Delete file and cascade to related records

Document Operations

• aktorCreateDocument - Create new document record
• aktorGetDocumentById - Retrieve document by ID
• aktorGetDocumentsByFileId - Get all documents for a file
• aktorListDocuments - List documents with pagination
• aktorUpdateDocument - Update document content or metadata
• aktorDeleteDocument - Delete document and cascade to chunks/vectors

Chunk Operations

• aktorCreateChunk - Create new chunk record
• aktorGetChunkById - Retrieve chunk by ID
• aktorGetChunksByDocumentId - Get all chunks for a document
• aktorListChunks - List chunks with pagination
• aktorUpdateChunk - Update chunk content or metadata
• aktorDeleteChunk - Delete chunk and cascade to vectors
• aktorCreateMultipleChunks - Batch create chunks

Vector Operations

• aktorCreateVector - Create new vector record with upsert logic
• aktorGetVectorByChunkId - Retrieve vector by chunk ID
• aktorGetVectorsByDocumentId - Get all vectors for a document
• aktorListVectors - List vectors with pagination
• aktorUpdateVector - Update vector embedding or model
• aktorDeleteVector - Delete single vector
• aktorDeleteVectorsByDocumentId - Delete all vectors for a document
• aktorCreateMultipleVectors - Batch create vectors
• aktorSearchSimilarVectors - Vector similarity search

Storage Operations

• aktorStoreChunk - Store document chunk with metadata
• aktorStoreMultipleChunks - Batch store chunks
• aktorStoreEnrichedChunk - Store enriched chunk with metadata
• aktorStoreMultipleEnrichedChunks - Batch store enriched chunks
• aktorStoreEmbeddedChunkVector - Store embedded chunk as vector
• aktorStoreMultipleEmbeddedChunkVectors - Batch store embedded chunks as vectors
• aktorGetStoredChunk - Retrieve stored chunk by ID
• aktorGetStoredChunksByDocumentId - Get stored chunks for document
• aktorGetStoredVectorData - Get vector with full metadata
• aktorSearchSimilarVectorsWithChunks - Search with chunk context

Future Considerations

The schema is designed to support:

• Multi-modal embeddings: Image/audio vectors in same structure
• Version control: Document revision tracking
• Access control: User/group permissions per document
• Hybrid search: Combined vector and full-text search
• Incremental updates: Chunk-level document updates

Next Steps

• Learn about Embedding Pipeline →
• Explore Retrieval Pipeline →
• Serve Stored Files via HTTP →
• Browse All Available Aktors →
• Develop Custom Strategies →