QuoteForge/bible-local/03-database.md

03 - Database

SQLite

SQLite is the local runtime database.

Main tables:

Table	Purpose
local_components	synced component metadata
local_pricelists	local pricelist headers
local_pricelist_items	local pricelist rows, the only runtime price source
local_projects	user projects
local_configurations	user configurations
local_configuration_versions	immutable revision snapshots
local_partnumber_books	partnumber book headers
local_partnumber_book_items	PN -> LOT catalog payload
pending_changes	sync queue
connection_settings	encrypted MariaDB connection settings
app_settings	local app state
local_schema_migrations	applied local migration markers

Rules:

• cache tables may be rebuilt if local migration recovery requires it;
• user-authored tables must not be dropped as a recovery shortcut;
• local_pricelist_items is the only valid runtime source of prices;
• configuration items and vendor_spec are stored as JSON payloads inside configuration rows.

MariaDB

MariaDB is the central sync database (RFQ_LOG). Final schema as of 2026-03-21.

QuoteForge tables (qt_* and stock_*)

Runtime read:

• qt_categories — pricelist categories
• qt_lot_metadata — component metadata, price settings
• qt_pricelists — pricelist headers (source: estimate / warehouse / competitor)
• qt_pricelist_items — pricelist rows
• stock_log — raw supplier price log, source for pricelist generation
• stock_ignore_rules — patterns to skip during stock import
• qt_partnumber_books — partnumber book headers
• qt_partnumber_book_items — PN→LOT catalog payload

Runtime read/write:

• qt_projects — projects
• qt_configurations — configurations
• qt_client_schema_state — per-client sync status and version tracking
• qt_pricelist_sync_status — pricelist sync timestamps per user

Insert-only tracking:

• qt_vendor_partnumber_seen — vendor partnumbers encountered during sync

Server-side only (not queried by client runtime):

• qt_component_usage_stats — aggregated component popularity stats (written by server jobs)
• qt_pricing_alerts — price anomaly alerts (models exist in Go; feature disabled in runtime)
• qt_schema_migrations — server migration history (applied via go run ./cmd/qfs -migrate)
• qt_scheduler_runs — server background job tracking (no Go code references it in this repo)

Competitor subsystem (server-side only, not used by QuoteForge Go code)

• qt_competitors — competitor registry
• partnumber_log_competitors — competitor price log (FK → qt_competitors)

These tables exist in the schema and are maintained by another tool or workflow. QuoteForge references competitor pricelists only via qt_pricelists (source='competitor').

Legacy RFQ tables (pre-QuoteForge, no Go code references)

• lot — original component registry (data preserved; superseded by qt_lot_metadata)
• lot_log — original supplier price log (superseded by stock_log)
• supplier — supplier registry (FK target for lot_log and machine_log)
• machine — device model registry
• machine_log — device price/quote log

These tables are retained for historical data. QuoteForge does not read or write them at runtime.

Rules:

• QuoteForge runtime must not depend on any legacy RFQ tables;
• stock enrichment happens during sync and is persisted into SQLite;
• normal UI requests must not query MariaDB tables directly;
• qt_client_local_migrations was removed from the schema on 2026-03-21 (was in earlier drafts).

MariaDB Table Structures

Full column reference as of 2026-03-21 (RFQ_LOG final schema).

qt_categories

Column	Type	Notes
id	bigint UNSIGNED PK AUTO_INCREMENT
code	varchar(20) UNIQUE NOT NULL
name	varchar(100) NOT NULL
name_ru	varchar(100)
display_order	bigint DEFAULT 0
is_required	tinyint(1) DEFAULT 0

qt_client_schema_state

PK: (username, hostname)

Column	Type	Notes
username	varchar(100)
hostname	varchar(255) DEFAULT ''
last_applied_migration_id	varchar(128)
app_version	varchar(64)
last_sync_at	datetime
last_sync_status	varchar(32)
pending_changes_count	int DEFAULT 0
pending_errors_count	int DEFAULT 0
configurations_count	int DEFAULT 0
projects_count	int DEFAULT 0
estimate_pricelist_version	varchar(128)
warehouse_pricelist_version	varchar(128)
competitor_pricelist_version	varchar(128)
last_sync_error_code	varchar(128)
last_sync_error_text	text
last_checked_at	datetime NOT NULL
updated_at	datetime NOT NULL

qt_component_usage_stats

PK: lot_name

Column	Type	Notes
lot_name	varchar(255)
quotes_total	bigint DEFAULT 0
quotes_last30d	bigint DEFAULT 0
quotes_last7d	bigint DEFAULT 0
total_quantity	bigint DEFAULT 0
total_revenue	decimal(14,2) DEFAULT 0
trend_direction	enum('up','stable','down') DEFAULT 'stable'
trend_percent	decimal(5,2) DEFAULT 0
last_used_at	datetime(3)

qt_competitors

Column	Type	Notes
id	bigint UNSIGNED PK AUTO_INCREMENT
name	varchar(255) NOT NULL
code	varchar(100) UNIQUE NOT NULL
delivery_basis	varchar(50) DEFAULT 'DDP'
currency	varchar(10) DEFAULT 'USD'
column_mapping	longtext JSON
is_active	tinyint(1) DEFAULT 1
created_at	timestamp
updated_at	timestamp ON UPDATE
price_uplift	decimal(8,4) DEFAULT 1.3	effective_price = price / price_uplift

qt_configurations

Column	Type	Notes
id	bigint UNSIGNED PK AUTO_INCREMENT
uuid	varchar(36) UNIQUE NOT NULL
user_id	bigint UNSIGNED
owner_username	varchar(100) NOT NULL
app_version	varchar(64)
project_uuid	char(36)	FK → qt_projects.uuid ON DELETE SET NULL
name	varchar(200) NOT NULL
items	longtext JSON NOT NULL	component list
total_price	decimal(12,2)
notes	text
is_template	tinyint(1) DEFAULT 0
created_at	datetime(3)
custom_price	decimal(12,2)
server_count	bigint DEFAULT 1
server_model	varchar(100)
support_code	varchar(20)
article	varchar(80)
pricelist_id	bigint UNSIGNED	FK → qt_pricelists.id
warehouse_pricelist_id	bigint UNSIGNED	FK → qt_pricelists.id
competitor_pricelist_id	bigint UNSIGNED	FK → qt_pricelists.id
disable_price_refresh	tinyint(1) DEFAULT 0
only_in_stock	tinyint(1) DEFAULT 0
line_no	int	position within project
price_updated_at	timestamp
vendor_spec	longtext JSON

qt_lot_metadata

PK: lot_name

Column	Type	Notes
lot_name	varchar(255)
category_id	bigint UNSIGNED	FK → qt_categories.id
vendor	varchar(50)
model	varchar(100)
specs	longtext JSON
current_price	decimal(12,2)	cached computed price
price_method	enum('manual','median','average','weighted_median') DEFAULT 'median'
price_period_days	bigint DEFAULT 90
price_updated_at	datetime(3)
request_count	bigint DEFAULT 0
last_request_date	date
popularity_score	decimal(10,4) DEFAULT 0
price_coefficient	decimal(5,2) DEFAULT 0	markup %
manual_price	decimal(12,2)
meta_prices	varchar(1000)	raw price samples JSON
meta_method	varchar(20)	method used for last compute
meta_period_days	bigint DEFAULT 90
is_hidden	tinyint(1) DEFAULT 0

qt_partnumber_books

Column	Type	Notes
id	bigint UNSIGNED PK AUTO_INCREMENT
version	varchar(30) UNIQUE NOT NULL
created_at	timestamp
created_by	varchar(100)
is_active	tinyint(1) DEFAULT 0	only one active at a time
partnumbers_json	longtext DEFAULT '[]'	flat list of partnumbers

qt_partnumber_book_items

Column	Type	Notes
id	bigint UNSIGNED PK AUTO_INCREMENT
partnumber	varchar(255) UNIQUE NOT NULL
lots_json	longtext NOT NULL	JSON array of lot_names
description	varchar(10000)

qt_pricelists

Column	Type	Notes
id	bigint UNSIGNED PK AUTO_INCREMENT
source	varchar(20) DEFAULT 'estimate'	'estimate' / 'warehouse' / 'competitor'
version	varchar(20) NOT NULL	UNIQUE with source
created_at	datetime(3)
created_by	varchar(100)
is_active	tinyint(1) DEFAULT 1
usage_count	bigint DEFAULT 0
expires_at	datetime(3)
notification	varchar(500)	shown to clients on sync

qt_pricelist_items

Column	Type	Notes
id	bigint UNSIGNED PK AUTO_INCREMENT
pricelist_id	bigint UNSIGNED NOT NULL	FK → qt_pricelists.id
lot_name	varchar(255) NOT NULL	INDEX with pricelist_id
lot_category	varchar(50)
price	decimal(12,2) NOT NULL
price_method	varchar(20)
price_period_days	bigint DEFAULT 90
price_coefficient	decimal(5,2) DEFAULT 0
manual_price	decimal(12,2)
meta_prices	varchar(1000)

qt_pricelist_sync_status

PK: username

Column	Type	Notes
username	varchar(100)
last_sync_at	datetime NOT NULL
updated_at	datetime NOT NULL
app_version	varchar(64)

qt_pricing_alerts

Column	Type	Notes
id	bigint UNSIGNED PK AUTO_INCREMENT
lot_name	varchar(255) NOT NULL
alert_type	enum('high_demand_stale_price','price_spike','price_drop','no_recent_quotes','trending_no_price')
severity	enum('low','medium','high','critical') DEFAULT 'medium'
message	text NOT NULL
details	longtext JSON
status	enum('new','acknowledged','resolved','ignored') DEFAULT 'new'
created_at	datetime(3)

qt_projects

Column	Type	Notes
id	bigint UNSIGNED PK AUTO_INCREMENT
uuid	char(36) UNIQUE NOT NULL
owner_username	varchar(100) NOT NULL
code	varchar(100) NOT NULL	UNIQUE with variant
variant	varchar(100) DEFAULT ''	UNIQUE with code
name	varchar(200)
tracker_url	varchar(500)
is_active	tinyint(1) DEFAULT 1
is_system	tinyint(1) DEFAULT 0
created_at	timestamp
updated_at	timestamp ON UPDATE

qt_schema_migrations

Column	Type	Notes
id	bigint UNSIGNED PK AUTO_INCREMENT
filename	varchar(255) UNIQUE NOT NULL
applied_at	datetime(3)

qt_scheduler_runs

PK: job_name

Column	Type	Notes
job_name	varchar(100)
last_started_at	datetime
last_finished_at	datetime
last_status	varchar(20) DEFAULT 'idle'
last_error	text
updated_at	timestamp ON UPDATE

qt_vendor_partnumber_seen

Column	Type	Notes
id	bigint UNSIGNED PK AUTO_INCREMENT
source_type	varchar(32) NOT NULL
vendor	varchar(255) DEFAULT ''
partnumber	varchar(255) UNIQUE NOT NULL
description	varchar(10000)
last_seen_at	datetime(3) NOT NULL
is_ignored	tinyint(1) DEFAULT 0
is_pattern	tinyint(1) DEFAULT 0
ignored_at	datetime(3)
ignored_by	varchar(100)
created_at	datetime(3)
updated_at	datetime(3)

stock_ignore_rules

Column	Type	Notes
id	bigint UNSIGNED PK AUTO_INCREMENT
target	varchar(20) NOT NULL	UNIQUE with match_type+pattern
match_type	varchar(20) NOT NULL
pattern	varchar(500) NOT NULL
created_at	timestamp

stock_log

Column	Type	Notes
stock_log_id	bigint UNSIGNED PK AUTO_INCREMENT
partnumber	varchar(255) NOT NULL	INDEX with date
supplier	varchar(255)
date	date NOT NULL
price	decimal(12,2) NOT NULL
quality	varchar(255)
comments	text
vendor	varchar(255)	INDEX
qty	decimal(14,3)

partnumber_log_competitors

Column	Type	Notes
id	bigint UNSIGNED PK AUTO_INCREMENT
competitor_id	bigint UNSIGNED NOT NULL	FK → qt_competitors.id
partnumber	varchar(255) NOT NULL
description	varchar(500)
vendor	varchar(255)
price	decimal(12,2) NOT NULL
price_loccur	decimal(12,2)	local currency price
currency	varchar(10)
qty	decimal(12,4) DEFAULT 1
date	date NOT NULL
created_at	timestamp

Legacy tables (lot / lot_log / machine / machine_log / supplier)

Retained for historical data only. Not queried by QuoteForge.

lot: lot_name (PK, char 255), lot_category, lot_description lot_log: lot_log_id AUTO_INCREMENT, lot (FK→lot), supplier (FK→supplier), date, price double, quality, comments supplier: supplier_name (PK, char 255), supplier_comment machine: machine_name (PK, char 255), machine_description machine_log: machine_log_id AUTO_INCREMENT, date, supplier (FK→supplier), country, opty, type, machine (FK→machine), customer_requirement, variant, price_gpl, price_estimate, qty, quality, carepack, lead_time_weeks, prepayment_percent, price_got, Comment

Migrations

SQLite:

• schema creation and additive changes go through GORM AutoMigrate;
• data fixes, index repair, and one-off rewrites go through runLocalMigrations;
• local migration state is tracked in local_schema_migrations.

MariaDB:

• SQL files live in migrations/;
• they are applied by go run ./cmd/qfs -migrate.