Name: Auto Finanza Database Schema
Author: Saimirbaci

Auto Finanza Database Schema | Skills Pool

CREATE TABLE users (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    email VARCHAR(255) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL, -- argon2id
    business_id UUID NOT NULL,
    role VARCHAR DEFAULT 'member',
    ai_language VARCHAR(5) NOT NULL DEFAULT 'sq',        -- migration 086: ISO 639-1, controls agent response language
    frontend_language VARCHAR(5) NOT NULL DEFAULT 'sq',  -- migration 086: ISO 639-1, controls UI error messages
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW(),
    deleted_at TIMESTAMPTZ,
    FOREIGN KEY (business_id) REFERENCES businesses(id) ON DELETE CASCADE
);

CREATE TABLE businesses (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name VARCHAR NOT NULL,
    nipt VARCHAR(10) UNIQUE,
    address TEXT,
    akshi_certificate_path VARCHAR, -- Path to uploaded certificate
    model_tier_override INTEGER, -- 1=Flash, 2=Sonnet, 3=Opus (NULL = auto)
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW(),
    deleted_at TIMESTAMPTZ
);

CREATE TABLE roles (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name VARCHAR(50) UNIQUE NOT NULL,
    description TEXT,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

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()
);

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)
);

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;

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;

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;

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);

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');

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);

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);

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;

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);

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;

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 journal_entry_lines (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    journal_entry_id UUID NOT NULL,
    account_id UUID NOT NULL,
    debit DECIMAL(15,2) DEFAULT 0,
    credit DECIMAL(15,2) DEFAULT 0,
    description TEXT,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    FOREIGN KEY (journal_entry_id) REFERENCES journal_entries(id) ON DELETE CASCADE,
    FOREIGN KEY (account_id) REFERENCES accounts(id)
);

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;

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);

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)
);

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
);

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;

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);

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);

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;

CREATE TABLE agent_sessions (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    business_id UUID NOT NULL,
    user_id UUID NOT NULL,
    agent_type VARCHAR(50),
    intent VARCHAR(255),
    status VARCHAR(20), -- 'pending', 'processing', 'complete', 'error'
    token_usage INTEGER,
    model_used VARCHAR,
    quality_score DECIMAL(3,2),
    created_at TIMESTAMPTZ DEFAULT NOW(),
    FOREIGN KEY (business_id) REFERENCES businesses(id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

CREATE TABLE documents (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    business_id UUID NOT NULL,
    filename VARCHAR NOT NULL,
    file_path VARCHAR NOT NULL,
    doc_type VARCHAR(50), -- 'invoice', 'contract', 'receipt', 'report', etc.
    extracted_text TEXT,
    embedding VECTOR(768),
    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_documents_business ON documents(business_id) WHERE deleted_at IS NULL;
CREATE INDEX idx_documents_hnsw ON documents USING hnsw (embedding vector_cosine_ops);

CREATE TABLE file_sessions (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    business_id UUID NOT NULL,
    user_id UUID NOT NULL,
    status VARCHAR(20) DEFAULT 'processing', -- 'processing', 'complete', 'error'
    total_budget_tokens INTEGER DEFAULT 12000,
    used_tokens INTEGER DEFAULT 0,
    session_summary TEXT,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW(),
    FOREIGN KEY (business_id) REFERENCES businesses(id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

CREATE TABLE file_session_files (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    session_id UUID NOT NULL,
    filename VARCHAR NOT NULL,
    file_path VARCHAR NOT NULL,
    summary TEXT,
    extracted_json JSONB,
    embedding VECTOR(768),
    status VARCHAR(20) DEFAULT 'pending', -- 'pending', 'processing', 'complete', 'error'
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW(),
    FOREIGN KEY (session_id) REFERENCES file_sessions(id) ON DELETE CASCADE
);

CREATE INDEX idx_file_session_files_session ON file_session_files(session_id);

CREATE TABLE workflows (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    business_id UUID NOT NULL,
    type VARCHAR(50), -- 'monthly_report', 'invoice_batch', 'compliance_check'
    status VARCHAR(20) DEFAULT 'pending', -- 'pending', 'in_progress', 'complete', 'failed'
    context JSONB, -- Arbitrary workflow context
    result JSONB,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW(),
    FOREIGN KEY (business_id) REFERENCES businesses(id) ON DELETE CASCADE
);

CREATE TABLE goal_workspaces (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    business_id UUID NOT NULL,
    user_id UUID NOT NULL,
    goal_text TEXT NOT NULL,
    plan JSONB, -- Serialized Plan struct
    status VARCHAR(20) DEFAULT 'planning', -- 'planning', 'in_progress', 'complete', 'failed'
    result JSONB,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW(),
    FOREIGN KEY (business_id) REFERENCES businesses(id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

CREATE TABLE background_jobs (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    business_id UUID NOT NULL,
    job_type VARCHAR(50), -- 'fiscal_submit', 'tvsh_summary', 'report_generate'
    status VARCHAR(20) DEFAULT 'pending', -- 'pending', 'running', 'complete', 'failed'
    payload JSONB,
    result JSONB,
    last_error VARCHAR,
    retry_count INTEGER 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_background_jobs_status ON background_jobs(status, created_at);

Migration	Feature
001	Initial schema: users, businesses, roles, permissions
002	Sales & purchases: partners, articles, invoices, invoice_items
003	Fiscal: fiscal_queue, TVSH returns
004	Inventory: inventory_items, warehouse_movements
005	Accounting: accounts, journal_entries, journal_entry_lines
006	HR & Payroll: employees, payroll_runs, payroll_details
007	UUID extensions and indexes
008	pgvector extension and setup
009	Soft delete support on all tables
010	Multi-tenancy: business_id foreign keys, isolation indexes

Migration	Feature
011	agents table, agent_sessions
012	agent_examples table (few-shot learning) with HNSW index
013	business_knowledge table (distilled principles)
014	semantic_corrections table (feedback-driven learning)
015	feedback table (ratings, corrections, anomalies)
016	documents table with embeddings
017	JSONB config columns for flexible agent settings
018	Agent type enumerations (finance, legal, document, company)
019	Audit indexes for feedback queries
020	Agent session tracking and quality metrics

Migration	Feature
021	Open banking integration (partner bank data)
022	Diaspora support (foreign employee tracking)
023	VAT declaration pre-filling
024	Accountant portal (read-only access)
025	Multi-warehouse support
026	Invoice number gap detection
027	Partner credit limits and aging analysis
028	Expense categories and cost centers
029	Budget allocation and tracking
030	Payment terms (due dates, early payment discounts)
...	Additional features (031-040)

Migration	Feature
041	Quality gates (rule-based, semantic)
042	Workflow engine: workflows, workflow_steps
043	Goal engine: goal_workspaces
044	Background jobs: background_jobs table
045	Anomaly detection results storage
046	Agent streaming session metadata
047	Compliance calendar (legal deadlines)
048	Contract management (contract_documents, contract_clauses)
049	Business KPI tracking
050	Multi-file session processing
051	File session metadata and budget tracking
052	Document classification labels
053	Cross-file anomaly detection results
054	Agent confidence scoring
055	Model tier usage tracking (for cost reporting)
056	Knowledge graph edges (entity relationships)
057	Agent examples deduplication log
058	Learning loop audit trail
059	Semantic similarity search indexes
060	File sessions with file_session_files

Migration	Feature
061	File sessions (multi-file processing context)
062	Business memory tables
063	Proactive intelligence
064	Seed construction flag
065	Background jobs table
066	Idempotency keys (prevent double fiscal submission)

Migration	Feature
067	Construction phases 4-6: SAL assistant, RAG, HITL, triggers, frontend pages, offline sync
068	Add deleted_at to employees table (bulk import fix)
069	WhatsApp messages integration (generic messaging)
070	Telegram messages integration (generic messaging)
071	Construction type guards
072	EVM (Earned Value Management) fields on cost codes
073	Subcontract certifications
074	Project documents (merged with construction)
075	SAL rejection columns + SKK account seed for construction
076	Committed cost invoice tracking columns
077	Construction alerts + OCR indexed_at on documents
078	Transaction audit log
079	Tier 2: construction_photos, construction_notifications, construction_project_documents, client_portal_tokens
080	Tier 3: construction_milestones, compliance suite (templates/checklists/items), materials/deliveries/consumption, WhatsApp alerts/subscriptions, sync_watermarks. BOQ line date columns. 6 seeded Albanian compliance templates.

Migration	Feature
081	Agent loop checkpoints (HITL resume state, 48h expiry)
082	Agent session state (working_memory JSONB + compacted_context, 24h expiry)
083	Feedback synthesis tables
084	HR skills, projects, locations
085	Document processing robustness (indexed_at, processing_status, error columns)
086	Language preferences: `ai_language` + `frontend_language` on `users` table (default 'sq')
087	`halfvec` HNSW indexes on document chunks; `document_relationships` table (part_of_project, references)

Migration	Feature
088	Fiscal foundation: expands `journal_entries` `source_type` CHECK (+5 types); `fiscal_calendar`, `fiscal_year_state` (7 module flags), `exchange_rates`, `accounting_period_locks`, `vat_ledger_entries`; `enforce_period_lock()` trigger
089	Opening balance: `opening_balance_runs`, `opening_balance_entries` (Class 1-5 transfer)
090	Fixed assets (bug fix — table never existed before): `fixed_assets` (book_value GENERATED), `asset_group_pools` (closing_balance GENERATED), `depreciation_runs`, `depreciation_run_lines`, `asset_disposals`
091	FX revaluation: `exchange_rate_revaluations`, `revaluation_lines` (gain 7812 / loss 6812)
092	Tax instalments: `tax_loss_carryforwards` (5yr limit), `tax_instalment_plans`, `tax_instalment_payments` (quarter 1-4)
093	Audit checks: `audit_check_runs`, `audit_check_results` (8 preflight checks C1-C8, SSE streaming)
094	Performance snapshots: `performance_snapshots`, `non_deductible_adjustments` (CIT KPIs)
095	VAT management: `vat_positions` (refund_claim_deadline GENERATED = period_end + 60 days), `vat_offset_requests`

Migration	Feature
096	Company wiki tables
097	Extended user feedback annotations
098	Fix embedding dimensions to 768
099	Goal step feedback tables
100	Goal step feedback attachments
101	Fix template budgets
102	Document Generation v2: `content_markdown`, `content_sections` JSONB, `parent_document_id`, `version_number`, `revision_prompt` on `generated_documents`; `generated_document_id` on `agent_messages`
103	Agent messages `metadata` JSONB column

Migration	Feature
104	`session_documents` — links documents to agent sessions
105	`user_document_attachments` — user-uploaded doc attachments for agent messages
106	`document_assets` — business logos, signatures, stamps for document embedding
107	`document_templates` extended — AI template features: `section_schema`, `placeholder_schema`, `content_markdown`, `tags`
108	Seed 6 professional Albanian templates (tender, financial offer, construction, etc.)

Table	Key Columns	Purpose
`know_vertical_assignments`	`business_id PK`, `vertical TEXT`, `rollout_mode TEXT CHECK ('disabled','shadow','primary')`	Track which vertical + rollout mode per business
`know_workspaces`	`business_id PK`, `active_build_id UUID→know_builds`, `artifact_root TEXT NOT NULL`	Active build state per business
`know_builds`	`id UUID PK`, `business_id`, `status TEXT`, `trigger TEXT`, `changed/indexed/failed_documents INT`, `started_at/finished_at TIMESTAMPTZ`	Build history with progress tracking
`know_document_index_state`	`UNIQUE(business_id, document_id)`, `index_status CHECK ('pending','indexed','stale','deleted','failed')`, `last_indexed_at TIMESTAMPTZ`	Per-document indexing status
`know_query_audit`	`business_id`, `request_id TEXT`, `primary_engine TEXT`, `shadow_engine TEXT`, `mismatch_summary JSONB`, `primary/shadow_latency_ms INT`	Shadow-mode A/B query comparison audit

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)

CREATE EXTENSION IF NOT EXISTS vector;

-- 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	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

-- 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);

-- 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

-- 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;

// 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?;

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;

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)

CREATE TABLE document_relationships (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    business_id UUID NOT NULL REFERENCES businesses(id) ON DELETE CASCADE,
    source_document_id UUID NOT NULL REFERENCES documents(id) ON DELETE CASCADE,
    target_document_id UUID NOT NULL REFERENCES documents(id) ON DELETE CASCADE,
    relationship_type VARCHAR(50) NOT NULL, -- 'part_of_project' | 'references'
    confidence DECIMAL(4,3) NOT NULL,       -- 0.0 - 1.0
    detected_by VARCHAR(50),               -- 'directory' | 'filename' | 'content'
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

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

// 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

-- 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 = ?;

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;

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;

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

SELECT principle_text
FROM business_knowledge
WHERE business_id = $1
ORDER BY created_at DESC;

SELECT correction
FROM semantic_corrections
WHERE business_id = $1
  AND (embedding <=> $2) > 0.7;
-- Prevents repeating the same mistake

CREATE INDEX idx_table_active ON table_name(column)
  WHERE deleted_at IS NULL;

# Generate empty migration
sqlx migrate add -r create_new_table

# File created: migrations/YYYYMMDDHHMMSS_create_new_table.sql

-- 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;

# Local development
sqlx migrate run

# Production (via CI/CD)
cargo sqlx migrate run --database-url $DATABASE_URL

# Fresh database
sqlx database create
sqlx migrate run

# Verify schema
sqlx database describe

# Rollback test
sqlx migrate revert
sqlx migrate run

Auto Finanza Database Schema

Schema Conventions

Primary Keys

Timestamps

Soft Deletes

Auto Finanza Database Schema

Schema Conventions

Primary Keys

Timestamps

Soft Deletes

Multi-Tenancy

Data Types

Vector Embeddings (pgvector)

Sync Status (Tauri Desktop Offline)

Key Tables by Domain

Auth & Multi-Tenancy (5 tables)

users

businesses

roles

permissions

user_roles

Sales & Purchases (4 tables)

partners

articles

invoices

invoice_items

Fiscal (3 tables)

fiscal_queue

idempotency_keys

tvsh_returns

Inventory (2 tables)

inventory_items

warehouse_movements

Accounting / SKK (3 tables)

accounts

journal_entries

journal_entry_lines

HR & Payroll (3 tables)

employees

payroll_runs

payroll_details

AI Agents & Learning (6 tables)

agents

agent_examples (Few-Shot Learning)

business_knowledge (Auto-Distilled Principles)

semantic_corrections (User Feedback for Avoiding Mistakes)

feedback (User Ratings & Corrections)

agent_sessions

Documents (3 tables)

documents

file_sessions (Multi-File Processing)

file_session_files

Workflows & Goals (2 tables)

workflows

goal_workspaces

Background Jobs (1 table)

background_jobs

Migration History Summary

Core Infrastructure (001-010)

AI Agents Foundation (011-020)

Advanced Features (021-040)

Super CoWorker AI (041-060)

Recent Additions (061-066)

Construction Module (067-080)

Agent Infrastructure & Language (081-087)

Accounting Automation (088-095)

Agent & Document Features (096-103)

Document v2 Continuations (104-108)

Know Shadow-Mode Foundation (109)

pgvector Usage Patterns

Extension Setup

Embedding Column Definition

HNSW Index for Cosine Similarity

Similarity Search Query

Similarity Threshold Filtering

Computing Embeddings

Document Assets (migration 106)

Document Templates Extension (migration 107)

Seed Document Templates (migration 108)

Document Relationships Table (migration 087)

`users`

`businesses`

`roles`

`permissions`

`user_roles`

`partners`

`articles`

`invoices`

`invoice_items`

`fiscal_queue`

`idempotency_keys`

`tvsh_returns`

`inventory_items`

`warehouse_movements`

`accounts`

`journal_entries`

`journal_entry_lines`

`employees`

`payroll_runs`

`payroll_details`

`agents`

`agent_examples` (Few-Shot Learning)

`business_knowledge` (Auto-Distilled Principles)

`semantic_corrections` (User Feedback for Avoiding Mistakes)

`feedback` (User Ratings & Corrections)

`agent_sessions`

`documents`

`file_sessions` (Multi-File Processing)

`file_session_files`

`workflows`

`goal_workspaces`

`background_jobs`