+ ֕hIRt^RIt^RIHt^RIHt]!]4P R, tRt]3Rlt ]3Rlt ] R8Xd] !4] !4t ] P4t ] PR 4] P4t]!R 4]FKt] PR ]^, 24] P'4^,t]!R ]^, R ] R24KM ] P+4R#R#)z8 Database schema for FarmMatch - Single source of truth N)datetime)Pathz farmmatch.dba -- Properties table (main data) CREATE TABLE IF NOT EXISTS properties ( id INTEGER PRIMARY KEY AUTOINCREMENT, url TEXT UNIQUE NOT NULL, title TEXT, summary TEXT, description TEXT, price TEXT, location TEXT, breadcrumb TEXT, status TEXT DEFAULT 'Active', -- Geocoding lat REAL, lon REAL, geocoding_confidence TEXT, geocoded_from TEXT, geocoded_at TIMESTAMP, -- Analysis gpt_score REAL, gpt_reasoning TEXT, custom_score REAL, custom_reasoning TEXT, final_score REAL, risk_profile TEXT, -- Metadata scraped_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP, last_checked TIMESTAMP, -- Constraints CHECK (lat IS NULL OR (lat >= -90 AND lat <= 90)), CHECK (lon IS NULL OR (lon >= -180 AND lon <= 180)), CHECK (gpt_score IS NULL OR (gpt_score >= 0 AND gpt_score <= 5)), CHECK (custom_score IS NULL OR (custom_score >= 0 AND custom_score <= 5)), CHECK (final_score IS NULL OR (final_score >= 0 AND final_score <= 5)), CHECK (status IN ('Active', 'Removed', 'Pending', 'Sold')) ); -- Criteria scores (detailed breakdown) CREATE TABLE IF NOT EXISTS criteria_scores ( id INTEGER PRIMARY KEY AUTOINCREMENT, property_id INTEGER NOT NULL, criterion TEXT NOT NULL, score REAL NOT NULL, weight REAL DEFAULT 1.0, reasoning TEXT, evaluated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (property_id) REFERENCES properties(id) ON DELETE CASCADE, CHECK (score >= 1 AND score <= 5), UNIQUE(property_id, criterion) ); -- Geocoding history (audit trail) CREATE TABLE IF NOT EXISTS geocoding_history ( id INTEGER PRIMARY KEY AUTOINCREMENT, property_id INTEGER NOT NULL, lat REAL NOT NULL, lon REAL NOT NULL, confidence TEXT, source TEXT, geocoded_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (property_id) REFERENCES properties(id) ON DELETE CASCADE ); -- Analysis history (audit trail) CREATE TABLE IF NOT EXISTS analysis_history ( id INTEGER PRIMARY KEY AUTOINCREMENT, property_id INTEGER NOT NULL, analysis_type TEXT NOT NULL, score REAL, reasoning TEXT, analyzed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (property_id) REFERENCES properties(id) ON DELETE CASCADE, CHECK (analysis_type IN ('gpt', 'custom', 'final')) ); -- System metadata (pipeline state) CREATE TABLE IF NOT EXISTS system_metadata ( key TEXT PRIMARY KEY, value TEXT, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ); -- Indexes for performance CREATE INDEX IF NOT EXISTS idx_properties_status ON properties(status); CREATE INDEX IF NOT EXISTS idx_properties_url ON properties(url); CREATE INDEX IF NOT EXISTS idx_properties_location ON properties(lat, lon); CREATE INDEX IF NOT EXISTS idx_criteria_property ON criteria_scores(property_id); CREATE INDEX IF NOT EXISTS idx_geocoding_property ON geocoding_history(property_id); CREATE INDEX IF NOT EXISTS idx_analysis_property ON analysis_history(property_id); cx\RV 24\P!V4pVP4pVP \ 4VP R4VP R\P!4P434VP4VP4\R4R#)zCreate the database with schemazCreating database at: z INSERT OR REPLACE INTO system_metadata (key, value, updated_at) VALUES ('schema_version', '1.0', CURRENT_TIMESTAMP) z INSERT OR REPLACE INTO system_metadata (key, value, updated_at) VALUES ('created_at', ?, CURRENT_TIMESTAMP) u!✅ Database created successfullyN) printsqlite3connectcursor executescriptSCHEMAexecuternow isoformatcommitclose)db_pathconnrs& X/Users/jonathan/SynologyDrive/Since Today/PROJECTEN/farmmatch/scraper/database_schema.pycreate_databaseros "7) ,- ??7 #D [[]F   NN  NN ,,. " " $ &(  KKMJJL -/c\\P!V4p\PVnV#)zGet a database connection)rrRow row_factory)rrs& rget_connectionrs! ??7 #D{{D Kr__main__z1SELECT name FROM sqlite_master WHERE type='table'u 📊 Created tables:zSELECT COUNT(*) as count FROM z - z: z rows)__doc__rrpathlibr__file__parent DATABASE_PATHr rr__name__rrr fetchalltablesrtablefetchonecountrrrr&sX%%6 a F*04)  z  D [[]F NNFG __ F "$7azBC!!$ U1XJbu-.  JJLr