CREATE TABLE roles (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
name VARCHAR(50) UNIQUE NOT NULL,
description TEXT,
created_at TIMESTAMPTZ DEFAULT NOW()
);
permissions
CREATE TABLE permissions (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
name VARCHAR(100) UNIQUE NOT NULL,
description TEXT,
resource VARCHAR(50),
action VARCHAR(50),
created_at TIMESTAMPTZ DEFAULT NOW()
);
user_roles
CREATE TABLE user_roles (
user_id UUID NOT NULL,
role_id UUID NOT NULL,
business_id UUID NOT NULL,
PRIMARY KEY (user_id, role_id, business_id),
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
FOREIGN KEY (role_id) REFERENCES roles(id),
FOREIGN KEY (business_id) REFERENCES businesses(id)
);
Sales & Purchases (4 tables)
partners
CREATE TABLE partners (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
business_id UUID NOT NULL,
name VARCHAR NOT NULL,
nipt VARCHAR(10),
type VARCHAR(20), -- 'customer', 'supplier', 'both'
email VARCHAR(255),
phone VARCHAR(20),
address TEXT,
city VARCHAR,
country VARCHAR,
bank_account VARCHAR,
bank_name VARCHAR,
created_at TIMESTAMPTZ DEFAULT NOW(),
updated_at TIMESTAMPTZ DEFAULT NOW(),
deleted_at TIMESTAMPTZ,
synced BOOLEAN DEFAULT false,
FOREIGN KEY (business_id) REFERENCES businesses(id) ON DELETE CASCADE
);
CREATE INDEX idx_partners_business ON partners(business_id) WHERE deleted_at IS NULL;
CREATE INDEX idx_partners_nipt ON partners(nipt) WHERE deleted_at IS NULL;
articles
CREATE TABLE articles (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
business_id UUID NOT NULL,
name VARCHAR NOT NULL,
code VARCHAR(50),
unit VARCHAR(20), -- 'piece', 'kg', 'liter', 'm', etc.
tvsh_rate DECIMAL(5,2) DEFAULT 20.00, -- 20% standard VAT in Albania
sale_price DECIMAL(15,2),
purchase_price DECIMAL(15,2),
category VARCHAR(100),
created_at TIMESTAMPTZ DEFAULT NOW(),
updated_at TIMESTAMPTZ DEFAULT NOW(),
deleted_at TIMESTAMPTZ,
synced BOOLEAN DEFAULT false,
FOREIGN KEY (business_id) REFERENCES businesses(id) ON DELETE CASCADE
);
CREATE INDEX idx_articles_business ON articles(business_id) WHERE deleted_at IS NULL;
invoices
CREATE TABLE invoices (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
business_id UUID NOT NULL,
partner_id UUID NOT NULL,
type VARCHAR(10), -- 'sale', 'purchase'
number VARCHAR(50) NOT NULL,
date DATE NOT NULL,
subtotal DECIMAL(15,2) NOT NULL,
tvsh_amount DECIMAL(15,2) NOT NULL,
total DECIMAL(15,2) NOT NULL,
status VARCHAR(20) DEFAULT 'draft', -- 'draft', 'issued', 'paid', 'overdue', 'cancelled'
fiscal_number VARCHAR(50),
fiscal_status VARCHAR(20) DEFAULT 'pending', -- 'pending', 'submitted', 'approved', 'rejected'
due_date DATE,
payment_date DATE,
notes TEXT,
created_at TIMESTAMPTZ DEFAULT NOW(),
updated_at TIMESTAMPTZ DEFAULT NOW(),
deleted_at TIMESTAMPTZ,
synced BOOLEAN DEFAULT false,
FOREIGN KEY (business_id) REFERENCES businesses(id) ON DELETE CASCADE,
FOREIGN KEY (partner_id) REFERENCES partners(id)
);
CREATE INDEX idx_invoices_business ON invoices(business_id) WHERE deleted_at IS NULL;
CREATE INDEX idx_invoices_status ON invoices(status) WHERE deleted_at IS NULL;
CREATE INDEX idx_invoices_date ON invoices(date) WHERE deleted_at IS NULL;
invoice_items
CREATE TABLE invoice_items (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
invoice_id UUID NOT NULL,
article_id UUID NOT NULL,
quantity DECIMAL(15,4) NOT NULL,
unit_price DECIMAL(15,2) NOT NULL,
tvsh_rate DECIMAL(5,2) DEFAULT 20.00,
subtotal DECIMAL(15,2) NOT NULL,
created_at TIMESTAMPTZ DEFAULT NOW(),
updated_at TIMESTAMPTZ DEFAULT NOW(),
FOREIGN KEY (invoice_id) REFERENCES invoices(id) ON DELETE CASCADE,
FOREIGN KEY (article_id) REFERENCES articles(id)
);
CREATE INDEX idx_invoice_items_invoice ON invoice_items(invoice_id);
Fiscal (3 tables)
fiscal_queue
CREATE TABLE fiscal_queue (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
business_id UUID NOT NULL,
invoice_id UUID NOT NULL,
status VARCHAR(20) DEFAULT 'pending', -- 'pending', 'submitted', 'approved', 'rejected', 'error'
xml_content TEXT NOT NULL,
submitted_at TIMESTAMPTZ,
response_data JSONB,
retry_count INTEGER DEFAULT 0,
last_error VARCHAR,
created_at TIMESTAMPTZ DEFAULT NOW(),
updated_at TIMESTAMPTZ DEFAULT NOW(),
FOREIGN KEY (business_id) REFERENCES businesses(id) ON DELETE CASCADE,
FOREIGN KEY (invoice_id) REFERENCES invoices(id)
);
CREATE INDEX idx_fiscal_queue_status ON fiscal_queue(status, created_at) WHERE status IN ('pending', 'error');
idempotency_keys
CREATE TABLE idempotency_keys (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
key VARCHAR(255) UNIQUE NOT NULL,
created_at TIMESTAMPTZ DEFAULT NOW(),
expires_at TIMESTAMPTZ NOT NULL -- TTL index for cleanup
);
CREATE INDEX idx_idempotency_keys_expires ON idempotency_keys(expires_at);
tvsh_returns
CREATE TABLE tvsh_returns (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
business_id UUID NOT NULL,
period_month INTEGER NOT NULL,
period_year INTEGER NOT NULL,
tvsh_collected DECIMAL(15,2) NOT NULL,
tvsh_paid DECIMAL(15,2) NOT NULL,
tvsh_balance DECIMAL(15,2) NOT NULL,
status VARCHAR(20) DEFAULT 'draft', -- 'draft', 'submitted', 'approved'
submitted_at TIMESTAMPTZ,
created_at TIMESTAMPTZ DEFAULT NOW(),
updated_at TIMESTAMPTZ DEFAULT NOW(),
FOREIGN KEY (business_id) REFERENCES businesses(id) ON DELETE CASCADE
);
CREATE INDEX idx_tvsh_returns_period ON tvsh_returns(business_id, period_year, period_month);
Inventory (2 tables)
inventory_items
CREATE TABLE inventory_items (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
business_id UUID NOT NULL,
article_id UUID NOT NULL,
quantity DECIMAL(15,4) NOT NULL DEFAULT 0,
location VARCHAR(255),
warehouse_id UUID,
created_at TIMESTAMPTZ DEFAULT NOW(),
updated_at TIMESTAMPTZ DEFAULT NOW(),
deleted_at TIMESTAMPTZ,
synced BOOLEAN DEFAULT false,
FOREIGN KEY (business_id) REFERENCES businesses(id) ON DELETE CASCADE,
FOREIGN KEY (article_id) REFERENCES articles(id)
);
CREATE INDEX idx_inventory_business ON inventory_items(business_id) WHERE deleted_at IS NULL;
CREATE UNIQUE INDEX idx_inventory_article_warehouse ON inventory_items(article_id, warehouse_id) WHERE deleted_at IS NULL;
warehouse_movements
CREATE TABLE warehouse_movements (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
business_id UUID NOT NULL,
article_id UUID NOT NULL,
type VARCHAR(20), -- 'in', 'out', 'transfer', 'adjustment'
quantity DECIMAL(15,4) NOT NULL,
from_warehouse_id UUID,
to_warehouse_id UUID,
reference_id VARCHAR(50), -- Links to invoice_id or purchase_order_id
notes TEXT,
created_at TIMESTAMPTZ DEFAULT NOW(),
updated_at TIMESTAMPTZ DEFAULT NOW(),
FOREIGN KEY (business_id) REFERENCES businesses(id) ON DELETE CASCADE,
FOREIGN KEY (article_id) REFERENCES articles(id)
);
CREATE INDEX idx_warehouse_movements_business ON warehouse_movements(business_id, created_at);
Accounting / SKK (3 tables)
accounts
CREATE TABLE accounts (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
business_id UUID NOT NULL,
code VARCHAR(20) UNIQUE NOT NULL,
name VARCHAR(255) NOT NULL,
type VARCHAR(50), -- 'asset', 'liability', 'equity', 'revenue', 'expense'
parent_account_id UUID,
created_at TIMESTAMPTZ DEFAULT NOW(),
updated_at TIMESTAMPTZ DEFAULT NOW(),
deleted_at TIMESTAMPTZ,
FOREIGN KEY (business_id) REFERENCES businesses(id) ON DELETE CASCADE,
FOREIGN KEY (parent_account_id) REFERENCES accounts(id)
);
CREATE INDEX idx_accounts_business ON accounts(business_id) WHERE deleted_at IS NULL;
CREATE INDEX idx_accounts_type ON accounts(type) WHERE deleted_at IS NULL;
journal_entries
CREATE TABLE journal_entries (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
business_id UUID NOT NULL,
date DATE NOT NULL,
description TEXT NOT NULL,
reference VARCHAR(100),
total_debit DECIMAL(15,2) NOT NULL,
total_credit DECIMAL(15,2) NOT NULL,
created_at TIMESTAMPTZ DEFAULT NOW(),
updated_at TIMESTAMPTZ DEFAULT NOW(),
deleted_at TIMESTAMPTZ,
synced BOOLEAN DEFAULT false,
FOREIGN KEY (business_id) REFERENCES businesses(id) ON DELETE CASCADE
);
CREATE INDEX idx_journal_entries_business ON journal_entries(business_id, date) WHERE deleted_at IS NULL;
CREATE TABLE employees (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
business_id UUID NOT NULL,
name VARCHAR NOT NULL,
nipt_personal VARCHAR(10) UNIQUE,
hire_date DATE NOT NULL,
gross_salary DECIMAL(15,2) NOT NULL,
role VARCHAR(100),
email VARCHAR(255),
phone VARCHAR(20),
created_at TIMESTAMPTZ DEFAULT NOW(),
updated_at TIMESTAMPTZ DEFAULT NOW(),
deleted_at TIMESTAMPTZ,
synced BOOLEAN DEFAULT false,
FOREIGN KEY (business_id) REFERENCES businesses(id) ON DELETE CASCADE
);
CREATE INDEX idx_employees_business ON employees(business_id) WHERE deleted_at IS NULL;
payroll_runs
CREATE TABLE payroll_runs (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
business_id UUID NOT NULL,
period_month INTEGER NOT NULL,
period_year INTEGER NOT NULL,
status VARCHAR(20) DEFAULT 'draft', -- 'draft', 'processed', 'paid'
total_gross DECIMAL(15,2) NOT NULL DEFAULT 0,
total_tap DECIMAL(15,2) NOT NULL DEFAULT 0,
total_net DECIMAL(15,2) NOT NULL DEFAULT 0,
created_at TIMESTAMPTZ DEFAULT NOW(),
updated_at TIMESTAMPTZ DEFAULT NOW(),
FOREIGN KEY (business_id) REFERENCES businesses(id) ON DELETE CASCADE
);
CREATE INDEX idx_payroll_runs_period ON payroll_runs(business_id, period_year, period_month);
payroll_details
CREATE TABLE payroll_details (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
payroll_run_id UUID NOT NULL,
employee_id UUID NOT NULL,
gross_salary DECIMAL(15,2) NOT NULL,
tap_amount DECIMAL(15,2) NOT NULL DEFAULT 0, -- Personal Income Tax
pension_employee DECIMAL(15,2) NOT NULL DEFAULT 0,
pension_employer DECIMAL(15,2) NOT NULL DEFAULT 0,
health_employee DECIMAL(15,2) NOT NULL DEFAULT 0,
health_employer DECIMAL(15,2) NOT NULL DEFAULT 0,
net_salary DECIMAL(15,2) NOT NULL,
created_at TIMESTAMPTZ DEFAULT NOW(),
FOREIGN KEY (payroll_run_id) REFERENCES payroll_runs(id) ON DELETE CASCADE,
FOREIGN KEY (employee_id) REFERENCES employees(id)
);
AI Agents & Learning (6 tables)
agents
CREATE TABLE agents (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
business_id UUID NOT NULL,
type VARCHAR(50), -- 'finance', 'legal', 'document', 'company'
config JSONB,
created_at TIMESTAMPTZ DEFAULT NOW(),
updated_at TIMESTAMPTZ DEFAULT NOW(),
FOREIGN KEY (business_id) REFERENCES businesses(id) ON DELETE CASCADE
);
agent_examples (Few-Shot Learning)
CREATE TABLE agent_examples (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
business_id UUID NOT NULL,
agent_type VARCHAR(50),
query_text TEXT NOT NULL,
response_text TEXT NOT NULL,
embedding VECTOR(768) NOT NULL,
stars INTEGER DEFAULT 1, -- 1-5, set to 5 on positive feedback
created_at TIMESTAMPTZ DEFAULT NOW(),
updated_at TIMESTAMPTZ DEFAULT NOW(),
FOREIGN KEY (business_id) REFERENCES businesses(id) ON DELETE CASCADE
);
CREATE INDEX idx_agent_examples_hnsw
ON agent_examples USING hnsw (embedding vector_cosine_ops);
CREATE INDEX idx_agent_examples_stars ON agent_examples(stars) WHERE stars >= 4;
business_knowledge (Auto-Distilled Principles)
CREATE TABLE business_knowledge (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
business_id UUID NOT NULL,
principle_text TEXT NOT NULL,
embedding VECTOR(768) NOT NULL,
source VARCHAR, -- 'user_feedback', 'correction', 'manual_entry'
created_at TIMESTAMPTZ DEFAULT NOW(),
FOREIGN KEY (business_id) REFERENCES businesses(id) ON DELETE CASCADE
);
CREATE INDEX idx_business_knowledge_hnsw
ON business_knowledge USING hnsw (embedding vector_cosine_ops);
semantic_corrections (User Feedback for Avoiding Mistakes)
CREATE TABLE semantic_corrections (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
business_id UUID NOT NULL,
original_query TEXT NOT NULL,
correction TEXT NOT NULL,
embedding VECTOR(768) NOT NULL,
created_at TIMESTAMPTZ DEFAULT NOW(),
updated_at TIMESTAMPTZ DEFAULT NOW(),
FOREIGN KEY (business_id) REFERENCES businesses(id) ON DELETE CASCADE
);
CREATE INDEX idx_semantic_corrections_hnsw
ON semantic_corrections USING hnsw (embedding vector_cosine_ops);
feedback (User Ratings & Corrections)
CREATE TABLE feedback (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
business_id UUID NOT NULL,
message_id UUID NOT NULL,
agent_type VARCHAR(50),
rating INTEGER, -- 1-5 stars
correction_text TEXT,
reasoning_text TEXT, -- [ARSYETIM] blocks extracted from LLM
anomaly_type VARCHAR, -- if applicable
created_at TIMESTAMPTZ DEFAULT NOW(),
FOREIGN KEY (business_id) REFERENCES businesses(id) ON DELETE CASCADE
);
CREATE INDEX idx_feedback_rating ON feedback(rating) WHERE rating IS NOT NULL;
idx_know_builds_business_created ON know_builds(business_id, created_at DESC)
idx_know_builds_business_status ON know_builds(business_id, status, created_at DESC)
idx_know_document_index_state_... ON know_document_index_state(business_id, index_status, updated_at DESC)
idx_know_query_audit_business_created ON know_query_audit(business_id, created_at DESC)
pgvector Usage Patterns
Extension Setup
CREATE EXTENSION IF NOT EXISTS vector;
Embedding Column Definition
-- Standard 768-dim (Gemini / Ollama nomic-embed-text):
embedding VECTOR(768) NOT NULL
-- halfvec for memory-efficient HNSW (migration 087 — document chunks):
embedding halfvec(768) NOT NULL
Provider dimensions (active = 768):
Provider
Model
Dims
Column type
Status
Gemini
gemini-embedding-001
768
vector(768)
Active
Ollama
nomic-embed-text
768
vector(768)
Active
OpenAI
text-embedding-3-small
1536
vector(1536)
Legacy (migrated in 020, 098)
Ollama
mxbai-embed-large
1024
vector(1024)
Available
HNSW Index for Cosine Similarity
-- Standard vector:
CREATE INDEX idx_table_embedding_hnsw
ON table_name USING hnsw (embedding vector_cosine_ops);
-- halfvec (document chunks — migration 087):
CREATE INDEX idx_document_chunks_embedding_hnsw
ON document_chunks USING hnsw (embedding halfvec_cosine_ops);
Similarity Search Query
-- Find top 5 most similar documents
SELECT id, filename, embedding <=> $1 AS similarity
FROM documents
WHERE business_id = $2
ORDER BY embedding <=> $1
LIMIT 5;
-- $1 = query embedding (1536-dim vector)
-- <=> = cosine distance operator
Similarity Threshold Filtering
-- Standard matching (> 0.7)
SELECT id, query_text FROM agent_examples
WHERE business_id = $1
AND (embedding <=> $2) > 0.7
LIMIT 3;
-- Deduplication (> 0.92)
SELECT id FROM agent_examples
WHERE business_id = $1
AND (embedding <=> $2) > 0.92;
Computing Embeddings
// Use EmbeddingService (provider-agnostic) from AppState:
let embedding = state.embedding_service.embed(text).await?;
// Returns Vec<f32> — dimension depends on configured provider
// For retrieval-optimized models (adds search_document: prefix):
let indexed = state.embedding_service.embed_for_indexing(text).await?;
// For query optimization (adds search_query: prefix):
let query_vec = state.embedding_service.embed_for_query(query).await?;
// Batch processing (4 concurrent for Ollama):
let vectors = state.embedding_service.embed_batch_for_indexing(chunks).await?;
Document Assets (migration 106)
CREATE TABLE document_assets (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
business_id UUID NOT NULL REFERENCES businesses(id) ON DELETE CASCADE,
uploaded_by UUID NOT NULL REFERENCES users(id),
asset_type VARCHAR(20) NOT NULL, -- 'logo', 'letterhead', 'signature', 'stamp', 'image'
label TEXT NOT NULL,
filename TEXT NOT NULL,
file_path TEXT NOT NULL,
mime_type TEXT NOT NULL,
file_size BIGINT,
width_px INTEGER,
height_px INTEGER,
is_default BOOLEAN NOT NULL DEFAULT false,
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
deleted_at TIMESTAMPTZ
);
CREATE INDEX idx_document_assets_business ON document_assets(business_id) WHERE deleted_at IS NULL;
CREATE INDEX idx_document_assets_type ON document_assets(business_id, asset_type) WHERE deleted_at IS NULL;
Document Templates Extension (migration 107)
Migration 027 created document_templates with basic columns (name, template_path, parameters_schema). Migration 107 extends it with AI template features:
ALTER TABLE document_templates
ADD COLUMN IF NOT EXISTS created_by UUID REFERENCES users(id),
ADD COLUMN IF NOT EXISTS title TEXT,
ADD COLUMN IF NOT EXISTS language VARCHAR(5) NOT NULL DEFAULT 'sq',
ADD COLUMN IF NOT EXISTS content_markdown TEXT,
ADD COLUMN IF NOT EXISTS template_file_path TEXT,
ADD COLUMN IF NOT EXISTS section_schema JSONB NOT NULL DEFAULT '[]',
ADD COLUMN IF NOT EXISTS placeholder_schema JSONB NOT NULL DEFAULT '{}',
ADD COLUMN IF NOT EXISTS tags TEXT[] NOT NULL DEFAULT '{}',
ADD COLUMN IF NOT EXISTS usage_count INTEGER NOT NULL DEFAULT 0,
ADD COLUMN IF NOT EXISTS last_used_at TIMESTAMPTZ,
ADD COLUMN IF NOT EXISTS deleted_at TIMESTAMPTZ;
-- Relaxes NOT NULL on legacy columns (name, template_path)
-- Backfills title from name for existing rows
-- Indexes: idx_doc_templates_deleted, idx_doc_templates_type_v2, idx_doc_templates_tags (GIN)
Seed Document Templates (migration 108)
6 professional Albanian business templates (system-wide, business_id=NULL):
Same migration filenames as PostgreSQL (for sync coordination)
Data Type Mapping
PostgreSQL
SQLite
Reason
UUID
TEXT
SQLite has no UUID type
TIMESTAMPTZ
TEXT (ISO 8601)
Store as ISO strings, parse in Rust
DECIMAL(15,2)
REAL or TEXT
Use REAL for calculations, TEXT for audit trail
JSONB
TEXT (JSON)
SQLite 3.38+ supports JSON functions
VECTOR(768)
TEXT (Base64)
Serialize vector as base64 string
BOOLEAN
INTEGER (0/1)
SQLite boolean convention
Sync Pattern (Tauri Desktop)
// On local change:
1. Update SQLite row
2. Set synced = false
3. On background sync:
- Find all rows WHERE synced = false
- POST/PUT to backend API
- On success: set synced = true
- On failure: retry with exponential backoff
Conflict Resolution
Last-write-wins: Compare updated_at timestamps
Priority: Server always wins (authoritative)
Local-only: Check server during sync, if not present locally, keep local copy
UUID stability: All UUIDs generated client-side remain stable across sync
Example: Invoice Sync
-- SQLite local table
CREATE TABLE invoices (
id TEXT PRIMARY KEY,
business_id TEXT NOT NULL,
partner_id TEXT NOT NULL,
number VARCHAR(50),
date TEXT, -- ISO 8601 date
subtotal REAL,
tvsh_amount REAL,
total REAL,
status VARCHAR(20),
created_at TEXT, -- ISO 8601 timestamp
updated_at TEXT,
synced INTEGER DEFAULT 0 -- 0 = unsynced, 1 = synced
);
-- Sync check query
SELECT id FROM invoices WHERE synced = 0;
-- After successful POST to backend, update:
UPDATE invoices SET synced = 1 WHERE id = ?;
Query Patterns for Common Workflows
Financial Queries
Aging Report:
SELECT
SUM(CASE WHEN CURRENT_DATE - date <= 30 THEN total ELSE 0 END) AS days_0_30,
SUM(CASE WHEN CURRENT_DATE - date > 30 AND CURRENT_DATE - date <= 60 THEN total ELSE 0 END) AS days_30_60,
SUM(CASE WHEN CURRENT_DATE - date > 60 THEN total ELSE 0 END) AS days_60_plus
FROM invoices
WHERE business_id = $1
AND type = 'sale'
AND status IN ('issued', 'overdue')
AND deleted_at IS NULL;
Cash Flow (Monthly):
SELECT
DATE_TRUNC('month', date)::DATE AS month,
SUM(total) AS total_revenue
FROM invoices
WHERE business_id = $1
AND type = 'sale'
AND status IN ('issued', 'paid')
AND deleted_at IS NULL
GROUP BY DATE_TRUNC('month', date)
ORDER BY month;
AI Agent Queries
Few-Shot Example Selection:
SELECT id, query_text, response_text
FROM agent_examples
WHERE business_id = $1
AND agent_type = $2
AND (embedding <=> $3) > 0.7
ORDER BY stars DESC, (embedding <=> $3) ASC
LIMIT 3;
-- $1 = business_id
-- $2 = agent type (finance, legal, etc.)
-- $3 = current query embedding
Business Knowledge Injection:
SELECT principle_text
FROM business_knowledge
WHERE business_id = $1
ORDER BY created_at DESC;
Semantic Correction Lookup:
SELECT correction
FROM semantic_corrections
WHERE business_id = $1
AND (embedding <=> $2) > 0.7;
-- Prevents repeating the same mistake
Index Strategy
Standard Indexes
business_id on all tenant tables: Tenant isolation
status columns: State machine queries (invoices.status, fiscal_queue.status)
-- migrations/YYYYMMDDHHMMSS_feature_name.sql
-- Up
CREATE TABLE new_table (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
business_id UUID NOT NULL,
-- columns here
created_at TIMESTAMPTZ DEFAULT NOW(),
deleted_at TIMESTAMPTZ,
FOREIGN KEY (business_id) REFERENCES businesses(id) ON DELETE CASCADE
);
CREATE INDEX idx_new_table_business ON new_table(business_id)
WHERE deleted_at IS NULL;
-- Down
DROP INDEX IF EXISTS idx_new_table_business;
DROP TABLE IF EXISTS new_table;
Applying Migrations
# Local development
sqlx migrate run
# Production (via CI/CD)
cargo sqlx migrate run --database-url $DATABASE_URL
Testing Migrations
# Fresh database
sqlx database create
sqlx migrate run
# Verify schema
sqlx database describe
# Rollback test
sqlx migrate revert
sqlx migrate run
Schema Evolution Best Practices
Add before remove: Add new column, migrate data, then drop old column (across separate migrations)
Backward compatibility: Old versions of app must work during deployment
Indexes early: Index frequently-filtered columns immediately after creating them
Test locally first: Run migration cycle (up → down → up) before committing
Document decisions: Comments in migration files explain "why", not just "what"
Vector maintenance: Periodically rebuild HNSW indexes for query performance: REINDEX INDEX idx_name;