-- ============================================================================ -- PFMIS — Poultry Farm Management Information System -- Database schema for MariaDB 10.4+ / MySQL 8.0+ -- Charset utf8mb4 throughout; InnoDB for FK support and transactions. -- -- This file creates TABLES only — it does NOT create or switch databases, so -- it imports cleanly into an already-created database (which is the only thing -- shared hosting like cPanel lets you do — the database is made in cPanel's -- MySQL wizard, with an account prefix such as `youracct_pfmis`). -- • cPanel / phpMyAdmin: select your database on the left, then Import. -- • Local dev: mysql -u root -p -e "CREATE DATABASE IF NOT EXISTS pfmis -- CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci" -- mysql -u root -p pfmis < sql/schema.sql -- ============================================================================ -- --------------------------------------------------------------------------- -- Farms — top of the hierarchy. Every record ties back to a farm. -- --------------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS farms ( id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, name VARCHAR(120) NOT NULL, -- a branch points at its head farm; NULL = a head or standalone farm. Each -- branch is still a full, independently-scoped farm with its own data. -- ON DELETE SET NULL: deleting a head farm turns its branches into standalone -- farms rather than orphaning them behind a dangling parent_id. parent_id INT UNSIGNED NULL, logo_path VARCHAR(255) NULL, -- suspended farms are blocked from logging in / making requests (e.g. for -- non-payment); a platform admin can still enter them for support. status ENUM('active','suspended') NOT NULL DEFAULT 'active', currency CHAR(3) NOT NULL DEFAULT 'UGX', -- comma-separated subset of layer/broiler/breeder — a farm can run more than -- one operation type at once, so this isn't a single-value ENUM kind VARCHAR(40) NOT NULL DEFAULT 'layer', -- JSON array of app modules this farm may use; overrides its plan's default. -- NULL = inherit the plan (or all). Effective set enforced in Auth. modules JSON NULL, live_birds INT UNSIGNED NOT NULL DEFAULT 0, capacity INT UNSIGNED NOT NULL DEFAULT 0, locale VARCHAR(40) NOT NULL DEFAULT 'English (UG)', timezone VARCHAR(60) NOT NULL DEFAULT 'Africa/Kampala', -- exact farm coordinates, entered by hand in Settings — powers the -- Dashboard's live temperature/humidity (fetched client-side straight from -- Open-Meteo's free API, no key needed, so nothing is stored server-side -- beyond the coordinates themselves). NULL until a farm sets them. latitude DECIMAL(9,6) NULL, longitude DECIMAL(9,6) NULL, phone VARCHAR(255) NULL, -- one or more numbers, comma-separated, where SMS alerts (mortality, feed reorder, vaccination due) are sent notify_sms TINYINT(1) NOT NULL DEFAULT 0, notify_email TINYINT(1) NOT NULL DEFAULT 0, notify_whatsapp TINYINT(1) NOT NULL DEFAULT 0, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, KEY idx_farms_parent (parent_id), CONSTRAINT fk_farms_parent FOREIGN KEY (parent_id) REFERENCES farms(id) ON DELETE SET NULL ) ENGINE=InnoDB; -- --------------------------------------------------------------------------- -- Flocks — a batch of birds placed in a house. -- The UI uses a string business code (e.g. FL-2401) as the flock identifier, -- so we keep `code` UNIQUE and reference it from child records for fidelity -- with the front end, while still having a numeric PK for joins. -- --------------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS flocks ( id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, farm_id INT UNSIGNED NOT NULL, code VARCHAR(32) NOT NULL, house VARCHAR(32) NOT NULL, breed VARCHAR(64) NOT NULL, status ENUM('growing','laying','molting','spent') NOT NULL DEFAULT 'growing', age_days INT UNSIGNED NOT NULL DEFAULT 0, qty INT UNSIGNED NOT NULL DEFAULT 0, placed_on DATE NULL, source VARCHAR(120) NULL, lay_rate DECIMAL(5,2) NOT NULL DEFAULT 0, -- % mortality_pct DECIMAL(5,3) NOT NULL DEFAULT 0, -- % daily fcr DECIMAL(5,2) NOT NULL DEFAULT 0, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, UNIQUE KEY uq_flock_code (code), KEY idx_flock_farm (farm_id), CONSTRAINT fk_flock_farm FOREIGN KEY (farm_id) REFERENCES farms(id) ON DELETE CASCADE ) ENGINE=InnoDB; -- --------------------------------------------------------------------------- -- Daily entries — production numbers per house per day (one row per house/day). -- Keyed by house rather than flock: houses are the stable, physical unit a -- worker records against, while flocks come and go (placed, transferred, -- split) — after a bird transfer, "whichever flock is in this house today" -- can change without the house itself changing. flock_code is kept purely as -- an informational reference to whichever flock occupied the house when the -- entry was recorded. Feed columns are the aggregate; the per-component -- breakdown lands in daily_entry_feed so we keep normalization AND the UI's -- "breakdown" string. -- --------------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS daily_entries ( id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, farm_id INT UNSIGNED NOT NULL, house VARCHAR(32) NOT NULL, flock_code VARCHAR(32) NULL, entry_date DATE NOT NULL, mortality INT UNSIGNED NOT NULL DEFAULT 0, culls INT UNSIGNED NOT NULL DEFAULT 0, water_l INT UNSIGNED NOT NULL DEFAULT 0, avg_weight_g INT UNSIGNED NOT NULL DEFAULT 0, -- no eggs column: egg_collections is the single source of truth for egg -- counts (with grade detail this table never had), not duplicated here feed_kg DECIMAL(10,2) NOT NULL DEFAULT 0, feed_cost DECIMAL(14,2) NOT NULL DEFAULT 0, loss_pct DECIMAL(6,3) NOT NULL DEFAULT 0, breakdown VARCHAR(255) NOT NULL DEFAULT '', created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, UNIQUE KEY uq_entry (farm_id, house, entry_date), -- one entry per house per day KEY idx_entry_farm_date (farm_id, entry_date), CONSTRAINT fk_entry_farm FOREIGN KEY (farm_id) REFERENCES farms(id) ON DELETE CASCADE ) ENGINE=InnoDB; -- per-component feed consumption for a daily entry. `feed_type` is a shared -- free-text label: for `source`='raw' rows (historical, from before feed -- mixing existed) it names a feed_stock type; for `source`='mix' rows -- (current) it names a feed_mixes mix — birds are fed the mix, not the raw -- ingredients directly, so Daily Entry now draws from feed_mixes, not -- feed_stock (see DailyEntryController::feed()). The two are kept in the same -- column/table for continuity with existing history, but every cascade that -- touches this table (delete a feed type, delete a mix, rename either from -- Configurations) filters by `source` so a mix and a raw type that happen to -- share a literal name can never cross-contaminate each other's records. CREATE TABLE IF NOT EXISTS daily_entry_feed ( id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, entry_id INT UNSIGNED NOT NULL, feed_type VARCHAR(64) NOT NULL, source ENUM('raw','mix') NOT NULL DEFAULT 'raw', qty_kg DECIMAL(10,2) NOT NULL DEFAULT 0, unit_cost DECIMAL(12,2) NOT NULL DEFAULT 0, line_cost DECIMAL(14,2) NOT NULL DEFAULT 0, KEY idx_def_entry (entry_id), CONSTRAINT fk_def_entry FOREIGN KEY (entry_id) REFERENCES daily_entries(id) ON DELETE CASCADE ) ENGINE=InnoDB; -- --------------------------------------------------------------------------- -- Feed stock — inventory of RAW feed types with unit cost. No longer drawn -- down by Daily Entry directly — it's the input to Feed Mixing (see -- feed_mixes below), which blends measured amounts of these into a stored, -- costed mix that Daily Entry then draws from. -- --------------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS feed_stock ( id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, farm_id INT UNSIGNED NOT NULL, feed_type VARCHAR(64) NOT NULL, qty_kg DECIMAL(12,2) NOT NULL DEFAULT 0, reorder_kg DECIMAL(12,2) NOT NULL DEFAULT 0, unit_cost DECIMAL(12,2) NOT NULL DEFAULT 0, -- UGX per kg -- most recent purchase's supplier/date, kept in sync by purchase() on every -- new delivery (same "blended/rolling" treatment as unit_cost above) and -- still directly correctable here, same as qty/cost/reorder — the full, -- immutable history of every individual delivery lives in feed_purchases supplier VARCHAR(160) NULL, purchased_on DATE NULL, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, UNIQUE KEY uq_feed (farm_id, feed_type), KEY idx_feed_farm (farm_id), CONSTRAINT fk_feed_farm FOREIGN KEY (farm_id) REFERENCES farms(id) ON DELETE CASCADE ) ENGINE=InnoDB; -- --------------------------------------------------------------------------- -- Feed purchases — one row per "Record purchase" event (feed_stock above only -- holds the current running total per type, not individual purchase history). -- Unioned into v_expenses_all below so a purchase shows up as a real expense -- automatically, the same way a payroll run does. `payment_status` covers how -- the delivery was actually settled — not every delivery is cash-in-hand: a -- supplier may extend credit (an unpaid liability until settled) or the feed -- may simply be donated (free, nothing ever owed). `invoice_no` is the -- supplier's own invoice/receipt reference, if the farm has one to record. -- --------------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS feed_purchases ( id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, farm_id INT UNSIGNED NOT NULL, feed_type VARCHAR(64) NOT NULL, qty_kg DECIMAL(12,2) NOT NULL DEFAULT 0, unit_cost DECIMAL(12,2) NOT NULL DEFAULT 0, line_cost DECIMAL(14,2) NOT NULL DEFAULT 0, payment_status ENUM('paid','credit','donated') NOT NULL DEFAULT 'paid', -- when a 'credit' purchase was later settled (paid off). NULL = still owed. -- payment_status keeps its original value so the ledger still credits -- Accounts Payable at purchase; the separate settlement entry clears it. settled_on DATE NULL, invoice_no VARCHAR(60) NULL, supplier VARCHAR(160) NULL, purchased_on DATE NOT NULL, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, KEY idx_feedpur_farm (farm_id, purchased_on), CONSTRAINT fk_feedpur_farm FOREIGN KEY (farm_id) REFERENCES farms(id) ON DELETE CASCADE ) ENGINE=InnoDB; -- --------------------------------------------------------------------------- -- Feed mixes — inventory of MIXED feed, blended from measured amounts of raw -- feed_stock components via a mixing batch (see feed_mix_batches below). -- Daily Entry draws feed from here, not from feed_stock directly — birds are -- fed the mix, not the raw ingredients. Mirrors feed_stock's own shape -- (qty/reorder/cost) so the rest of the app (Daily Entry's draw-down, the -- delete cascade) can reuse the exact same pattern. -- --------------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS feed_mixes ( id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, farm_id INT UNSIGNED NOT NULL, mix_name VARCHAR(64) NOT NULL, qty_kg DECIMAL(12,2) NOT NULL DEFAULT 0, reorder_kg DECIMAL(12,2) NOT NULL DEFAULT 0, unit_cost DECIMAL(12,2) NOT NULL DEFAULT 0, -- blended UGX/kg, weighted across every batch ever mixed into this name created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, UNIQUE KEY uq_feedmix (farm_id, mix_name), KEY idx_feedmix_farm (farm_id), CONSTRAINT fk_feedmix_farm FOREIGN KEY (farm_id) REFERENCES farms(id) ON DELETE CASCADE ) ENGINE=InnoDB; -- --------------------------------------------------------------------------- -- Feed mix batches — one row per "Mix a batch" event (feed_mixes above only -- holds the current running total per mix, not individual mixing history — -- same relationship feed_purchases has to feed_stock). No cash changes hands -- here (the raw feed was already expensed when it was bought — see -- feed_purchases), so batches are never unioned into Expenses; this is purely -- an internal stock movement + cost trace. -- --------------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS feed_mix_batches ( id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, farm_id INT UNSIGNED NOT NULL, mix_name VARCHAR(64) NOT NULL, qty_kg DECIMAL(12,2) NOT NULL DEFAULT 0, -- total output of this batch (sum of its raw components) unit_cost DECIMAL(12,2) NOT NULL DEFAULT 0, -- this batch's own blended cost/kg total_cost DECIMAL(14,2) NOT NULL DEFAULT 0, note VARCHAR(255) NULL, mixed_on DATE NOT NULL, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, KEY idx_feedmixbatch_farm (farm_id, mixed_on), CONSTRAINT fk_feedmixbatch_farm FOREIGN KEY (farm_id) REFERENCES farms(id) ON DELETE CASCADE ) ENGINE=InnoDB; -- per-component raw feed consumption for a single mixing batch — the recipe -- actually used, traceable after the fact (same normalization pattern as -- daily_entry_feed under daily_entries) CREATE TABLE IF NOT EXISTS feed_mix_batch_components ( id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, batch_id INT UNSIGNED NOT NULL, feed_type VARCHAR(64) NOT NULL, qty_kg DECIMAL(10,2) NOT NULL DEFAULT 0, unit_cost DECIMAL(12,2) NOT NULL DEFAULT 0, line_cost DECIMAL(14,2) NOT NULL DEFAULT 0, KEY idx_fmbc_batch (batch_id), CONSTRAINT fk_fmbc_batch FOREIGN KEY (batch_id) REFERENCES feed_mix_batches(id) ON DELETE CASCADE ) ENGINE=InnoDB; -- --------------------------------------------------------------------------- -- Egg collections — logged gradings; a running tally is derived via SUM(). -- `store` names which egg store the collected eggs went into (e.g. "Cold -- Room") — a managed list ('eggStores'), nullable for the same "imports -- cleanly onto pre-existing data" reason every other retrofitted column here -- is nullable. See sql/migration-2026-07-egg-stores.sql for the full design. -- --------------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS egg_collections ( id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, farm_id INT UNSIGNED NOT NULL, house VARCHAR(32) NOT NULL, flock_code VARCHAR(32) NOT NULL, collected_on DATE NOT NULL, store VARCHAR(64) NULL, total INT UNSIGNED NOT NULL DEFAULT 0, grade_twin INT UNSIGNED NOT NULL DEFAULT 0, -- twin-yolk eggs, graded ahead of grade_a grade_a INT UNSIGNED NOT NULL DEFAULT 0, grade_b INT UNSIGNED NOT NULL DEFAULT 0, cracked INT UNSIGNED NOT NULL DEFAULT 0, dirty INT UNSIGNED NOT NULL DEFAULT 0, rejected INT UNSIGNED NOT NULL DEFAULT 0, avg_weight_g DECIMAL(6,2) NOT NULL DEFAULT 0, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, KEY idx_egg_farm (farm_id, collected_on), KEY idx_egg_farm_store (farm_id, store), CONSTRAINT fk_egg_farm FOREIGN KEY (farm_id) REFERENCES farms(id) ON DELETE CASCADE ) ENGINE=InnoDB; -- --------------------------------------------------------------------------- -- Health events -- --------------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS health_events ( id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, farm_id INT UNSIGNED NOT NULL, flock_code VARCHAR(32) NOT NULL, disease VARCHAR(160) NOT NULL, affected INT UNSIGNED NOT NULL DEFAULT 0, onset_date DATE NULL, vet VARCHAR(120) NULL, symptoms VARCHAR(255) NULL, status ENUM('under treatment','monitoring','resolved') NOT NULL DEFAULT 'under treatment', created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, KEY idx_health_farm (farm_id), CONSTRAINT fk_health_farm FOREIGN KEY (farm_id) REFERENCES farms(id) ON DELETE CASCADE ) ENGINE=InnoDB; -- --------------------------------------------------------------------------- -- Vaccinations -- --------------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS vaccinations ( id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, farm_id INT UNSIGNED NOT NULL, flock_code VARCHAR(32) NOT NULL, vaccine VARCHAR(120) NOT NULL, route VARCHAR(60) NULL, dose VARCHAR(40) NULL, scheduled_on DATE NULL, status ENUM('scheduled','due','done') NOT NULL DEFAULT 'scheduled', -- open-ended recurrence (e.g. "ND/IB monthly during laying") — NULL means a -- one-time dose, same as every row before this existed. Marking a row with -- this set 'done' auto-creates the next occurrence this many days later -- (see VaccinationController::scheduleNext()), so an indefinite repeat never -- has to be re-added by hand each cycle. repeat_every_days INT UNSIGNED NULL, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, KEY idx_vacc_farm (farm_id), CONSTRAINT fk_vacc_farm FOREIGN KEY (farm_id) REFERENCES farms(id) ON DELETE CASCADE ) ENGINE=InnoDB; -- --------------------------------------------------------------------------- -- Vaccination programs — a reusable, named template listing which vaccine -- (with route/dose) is due at which age in days, so scheduling a flock's -- whole vaccination calendar is "apply this program" instead of adding every -- entry by hand. Applying one is done client-side (computes scheduled_on = -- flock.placed_on + age_days per item, then creates ordinary `vaccinations` -- rows through the existing endpoint) — these tables only hold the templates. -- --------------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS vaccination_programs ( id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, farm_id INT UNSIGNED NOT NULL, name VARCHAR(120) NOT NULL, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, KEY idx_vaxprog_farm (farm_id), CONSTRAINT fk_vaxprog_farm FOREIGN KEY (farm_id) REFERENCES farms(id) ON DELETE CASCADE ) ENGINE=InnoDB; CREATE TABLE IF NOT EXISTS vaccination_program_items ( id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, program_id INT UNSIGNED NOT NULL, vaccine VARCHAR(120) NOT NULL, route VARCHAR(60) NULL, dose VARCHAR(40) NULL, age_days INT UNSIGNED NOT NULL DEFAULT 0, -- carried onto the vaccinations row this item generates when the program is -- applied to a flock — see vaccinations.repeat_every_days above repeat_every_days INT UNSIGNED NULL, sort_order INT UNSIGNED NOT NULL DEFAULT 0, KEY idx_vaxprogitem_program (program_id), CONSTRAINT fk_vaxprogitem_program FOREIGN KEY (program_id) REFERENCES vaccination_programs(id) ON DELETE CASCADE ) ENGINE=InnoDB; -- --------------------------------------------------------------------------- -- Inventory (general stock — packaging, equipment, medication, etc.) -- `purchased_on` plus EITHER `useful_life_months` OR `depreciation_rate` are -- optional and only meaningful for depreciable equipment (not consumables) — -- when set, the frontend computes straight-line current value live (never -- cached, same philosophy as every other derived total in this app): -- useful_life_months set -> cost - (cost ÷ useful life months) × months elapsed -- depreciation_rate set -> cost - (cost × rate/100 ÷ 12) × months elapsed -- (depreciation_rate is a %/year straight-line rate — a second, equivalent way -- to express the same useful-life idea, not a different depreciation model; -- useful_life_months wins if both are somehow set). Leaving both blank means -- "not tracked", and the item's value is just its unit cost, unchanged. -- --------------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS inventory_items ( id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, farm_id INT UNSIGNED NOT NULL, sku VARCHAR(40) NOT NULL, name VARCHAR(160) NOT NULL, category VARCHAR(60) NOT NULL, qty DECIMAL(12,2) NOT NULL DEFAULT 0, reorder_level DECIMAL(12,2) NOT NULL DEFAULT 0, unit VARCHAR(20) NOT NULL DEFAULT 'pcs', -- opt-in batch/expiry tracking: when 1, qty is the SUM of inventory_batches -- and stock is drawn first-expiry-first-out. See migration-2026-07-inventory-batches.sql. track_batches TINYINT(1) NOT NULL DEFAULT 0, unit_cost DECIMAL(12,2) NOT NULL DEFAULT 0, purchased_on DATE NULL, useful_life_months INT UNSIGNED NULL, depreciation_rate DECIMAL(5,2) NULL, -- %/year, e.g. 20.00 created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, UNIQUE KEY uq_inv_sku (farm_id, sku), KEY idx_inv_farm (farm_id), CONSTRAINT fk_inv_farm FOREIGN KEY (farm_id) REFERENCES farms(id) ON DELETE CASCADE ) ENGINE=InnoDB; -- --------------------------------------------------------------------------- -- Inventory usage — losses/write-offs (damaged, expired, lost, stolen, other) -- that reduce a specific item's stock, the same "usage log that draws down a -- real stock" pattern as bird_usage/egg_usage/feed_stock, just keyed to one -- inventory item instead of a farm-wide pool (each item is distinct, not -- fungible like eggs across grades). -- --------------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS inventory_usage ( id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, farm_id INT UNSIGNED NOT NULL, item_id INT UNSIGNED NOT NULL, qty DECIMAL(12,2) NOT NULL, reason ENUM('damaged','expired','lost','stolen','other') NOT NULL, note VARCHAR(255) NULL, -- for a batched item, the per-lot draw this loss took (FEFO), so an edit/delete -- restores the same lots. NULL for a non-batched item. batch_breakdown JSON NULL, used_on DATE NOT NULL, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, KEY idx_invusage_farm (farm_id, used_on), KEY idx_invusage_item (item_id), CONSTRAINT fk_invusage_farm FOREIGN KEY (farm_id) REFERENCES farms(id) ON DELETE CASCADE, CONSTRAINT fk_invusage_item FOREIGN KEY (item_id) REFERENCES inventory_items(id) ON DELETE CASCADE ) ENGINE=InnoDB; -- Batches (lots) of a batch-tracked inventory item, each with its own expiry. -- A batched item's qty is the sum of these. See migration-2026-07-inventory-batches.sql. CREATE TABLE IF NOT EXISTS inventory_batches ( id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, farm_id INT UNSIGNED NOT NULL, item_id INT UNSIGNED NOT NULL, lot_no VARCHAR(60) NULL, qty DECIMAL(12,2) NOT NULL DEFAULT 0, expiry_date DATE NULL, received_on DATE NULL, supplier VARCHAR(120) NULL, grn_id INT UNSIGNED NULL, note VARCHAR(255) NULL, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, KEY idx_invb_item (item_id), KEY idx_invb_farm_expiry (farm_id, expiry_date), KEY idx_invb_grn (grn_id), CONSTRAINT fk_invb_item FOREIGN KEY (item_id) REFERENCES inventory_items(id) ON DELETE CASCADE, CONSTRAINT fk_invb_farm FOREIGN KEY (farm_id) REFERENCES farms(id) ON DELETE CASCADE ) ENGINE=InnoDB; -- --------------------------------------------------------------------------- -- Customers — an optional directory of regular/repeat customers a sale can -- pick from instead of retyping their details. Sales still store the -- customer's name/phone/address as plain text on the invoice itself (not a -- foreign key) so a one-off/walk-in customer never has to be added here -- first, and so an invoice's details stay frozen even if the customer record -- is later edited or removed. -- --------------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS customers ( id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, farm_id INT UNSIGNED NOT NULL, name VARCHAR(160) NOT NULL, phone VARCHAR(40) NULL, email VARCHAR(160) NULL, -- for CRM email campaigns address VARCHAR(255) NULL, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, KEY idx_customers_farm (farm_id), CONSTRAINT fk_customers_farm FOREIGN KEY (farm_id) REFERENCES farms(id) ON DELETE CASCADE ) ENGINE=InnoDB; -- --------------------------------------------------------------------------- -- Suppliers — the mirror image of customers: an optional directory of -- regular/repeat suppliers a feed purchase or expense can pick from instead -- of retyping their details every time. Same reasoning as customers: never -- referenced by a foreign key from feed_purchases/expenses (both keep the -- supplier/payee name as plain text), so a one-time supplier never has to be -- added here first, and a past record stays frozen even if this directory -- entry is later edited or removed. -- --------------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS suppliers ( id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, farm_id INT UNSIGNED NOT NULL, name VARCHAR(160) NOT NULL, phone VARCHAR(40) NULL, address VARCHAR(255) NULL, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, KEY idx_suppliers_farm (farm_id), CONSTRAINT fk_suppliers_farm FOREIGN KEY (farm_id) REFERENCES farms(id) ON DELETE CASCADE ) ENGINE=InnoDB; -- --------------------------------------------------------------------------- -- Sales (invoices) -- --------------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS sales ( id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, farm_id INT UNSIGNED NOT NULL, invoice_no VARCHAR(40) NOT NULL, customer VARCHAR(160) NOT NULL, customer_phone VARCHAR(40) NULL, customer_address VARCHAR(255) NULL, amount DECIMAL(14,2) NOT NULL DEFAULT 0, -- sum of this invoice's sale_items.line_total amount_paid DECIMAL(14,2) NOT NULL DEFAULT 0, status ENUM('paid','credit','partial') NOT NULL DEFAULT 'paid', sold_on DATE NOT NULL, -- unguessable public handle for the printed QR code's /verify link. The QR -- must NOT expose the sequential id, or anyone could walk /verify/sale/1,2,3… -- and harvest every farm's customers and amounts. Set once at create time. verify_token CHAR(32) NULL, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, UNIQUE KEY uq_invoice (farm_id, invoice_no), UNIQUE KEY uq_sales_verify_token (verify_token), KEY idx_sales_farm (farm_id, sold_on), CONSTRAINT fk_sales_farm FOREIGN KEY (farm_id) REFERENCES farms(id) ON DELETE CASCADE ) ENGINE=InnoDB; -- --------------------------------------------------------------------------- -- Sale line items — an invoice can list several items, each with its own -- qty/price, instead of the one-item-per-invoice model sales used to have. -- --------------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS sale_items ( id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, sale_id INT UNSIGNED NOT NULL, item VARCHAR(120) NOT NULL, qty INT UNSIGNED NOT NULL DEFAULT 0, unit_price DECIMAL(14,2) NOT NULL DEFAULT 0, line_total DECIMAL(14,2) NOT NULL DEFAULT 0, sort_order INT UNSIGNED NOT NULL DEFAULT 0, -- which egg grade this line was sold from (cracked/dirty often go at a -- discount) — meaningless for non-egg items, so NULL there. Mirrors -- egg_usage.grade; see that table's comment for the 'mixed' convention. grade ENUM('gradeTwin','gradeA','gradeB','cracked','dirty','rejected','mixed') NULL, -- which egg store this line was sold from — meaningless for non-egg items, -- so NULL there, same treatment as grade above store VARCHAR(64) NULL, KEY idx_sale_items_sale (sale_id), CONSTRAINT fk_sale_items_sale FOREIGN KEY (sale_id) REFERENCES sales(id) ON DELETE CASCADE ) ENGINE=InnoDB; -- --------------------------------------------------------------------------- -- Delivery notes — a document accompanying goods leaving the farm (items + -- quantities only, never prices). Raised against a sale (copies its customer + -- items) or standalone; optional Fleet vehicle/driver; pending -> delivered -- with a signature line. Under the Sales permission. See -- migration-2026-07-delivery-notes.sql. -- --------------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS delivery_notes ( id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, farm_id INT UNSIGNED NOT NULL, ref_no VARCHAR(40) NOT NULL, sale_id INT UNSIGNED NULL, customer VARCHAR(160) NOT NULL, customer_phone VARCHAR(40) NULL, delivery_address VARCHAR(255) NULL, delivery_date DATE NOT NULL, vehicle_id INT UNSIGNED NULL, driver_id INT UNSIGNED NULL, status ENUM('pending','delivered','cancelled') NOT NULL DEFAULT 'pending', received_by VARCHAR(120) NULL, delivered_at DATETIME NULL, notes VARCHAR(500) NULL, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, KEY idx_dn_farm (farm_id), KEY idx_dn_sale (sale_id), CONSTRAINT fk_dn_farm FOREIGN KEY (farm_id) REFERENCES farms(id) ON DELETE CASCADE, CONSTRAINT fk_dn_sale FOREIGN KEY (sale_id) REFERENCES sales(id) ON DELETE SET NULL, CONSTRAINT fk_dn_vehicle FOREIGN KEY (vehicle_id) REFERENCES vehicles(id) ON DELETE SET NULL, CONSTRAINT fk_dn_driver FOREIGN KEY (driver_id) REFERENCES fleet_drivers(id) ON DELETE SET NULL ) ENGINE=InnoDB; CREATE TABLE IF NOT EXISTS delivery_note_items ( id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, dn_id INT UNSIGNED NOT NULL, description VARCHAR(200) NOT NULL, qty DECIMAL(12,2) NOT NULL DEFAULT 0, unit VARCHAR(40) NULL, sort_order INT UNSIGNED NOT NULL DEFAULT 0, KEY idx_dni_dn (dn_id), CONSTRAINT fk_dni_dn FOREIGN KEY (dn_id) REFERENCES delivery_notes(id) ON DELETE CASCADE ) ENGINE=InnoDB; -- --------------------------------------------------------------------------- -- Egg bookings — a customer deposit taken in advance (often when there's a -- shortage at the time), before any eggs actually leave the farm. Deliberately -- NEVER touches egg_collections/egg_usage while 'pending' — the cash is real -- and tracked (as a liability, not Revenue — see Dashboard's "Customer -- Deposits" figure), but nothing has left stock yet, so nothing should be -- deducted. A customer often collects in several installments rather than -- all at once (e.g. 200 trays booked, taken as 50 + 50 + 100 over separate -- visits) — `qty_fulfilled`/`amount_paid_applied` are running totals across -- every such partial fulfillment (see egg_booking_fulfillments below), and -- the booking only flips to 'fulfilled' once qty_fulfilled reaches qty. -- Each fulfillment (BookingController::fulfill) converts that installment -- into a real `sales` + `sale_items` row at that moment — stock is validated -- and deducted THEN, applying whatever's left of the deposit as that sale's -- payment. Cancelling a booking (e.g. the customer never returns) just flips -- its status; refunding the cash, if any, is a real-world step outside this table. -- --------------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS egg_bookings ( id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, farm_id INT UNSIGNED NOT NULL, ref_no VARCHAR(40) NOT NULL, customer VARCHAR(160) NOT NULL, customer_phone VARCHAR(40) NULL, customer_address VARCHAR(255) NULL, item VARCHAR(120) NOT NULL, qty INT UNSIGNED NOT NULL DEFAULT 0, qty_fulfilled INT UNSIGNED NOT NULL DEFAULT 0, -- running total supplied so far, across every partial fulfillment unit_price DECIMAL(14,2) NOT NULL DEFAULT 0, amount DECIMAL(14,2) NOT NULL DEFAULT 0, -- qty * unit_price, server-computed like sales.amount amount_paid DECIMAL(14,2) NOT NULL DEFAULT 0, -- the deposit actually collected today amount_paid_applied DECIMAL(14,2) NOT NULL DEFAULT 0, -- how much of that deposit has been credited to a fulfillment sale so far grade ENUM('gradeTwin','gradeA','gradeB','cracked','dirty','rejected','mixed') NULL, store VARCHAR(64) NULL, status ENUM('pending','fulfilled','cancelled') NOT NULL DEFAULT 'pending', booked_on DATE NOT NULL, notes VARCHAR(255) NULL, sale_id INT UNSIGNED NULL, -- most recent fulfillment's sale — full history is in egg_booking_fulfillments created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, UNIQUE KEY uq_booking (farm_id, ref_no), KEY idx_bookings_farm (farm_id, status), CONSTRAINT fk_bookings_farm FOREIGN KEY (farm_id) REFERENCES farms(id) ON DELETE CASCADE, CONSTRAINT fk_bookings_sale FOREIGN KEY (sale_id) REFERENCES sales(id) ON DELETE SET NULL ) ENGINE=InnoDB; -- one row per partial (or full) fulfillment of a booking — the real history -- that qty_fulfilled/amount_paid_applied above are just running totals of CREATE TABLE IF NOT EXISTS egg_booking_fulfillments ( id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, booking_id INT UNSIGNED NOT NULL, sale_id INT UNSIGNED NULL, qty INT UNSIGNED NOT NULL DEFAULT 0, fulfilled_on DATE NOT NULL, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, KEY idx_bookingfulfillments_booking (booking_id), CONSTRAINT fk_bookingfulfillments_booking FOREIGN KEY (booking_id) REFERENCES egg_bookings(id) ON DELETE CASCADE, CONSTRAINT fk_bookingfulfillments_sale FOREIGN KEY (sale_id) REFERENCES sales(id) ON DELETE SET NULL ) ENGINE=InnoDB; -- --------------------------------------------------------------------------- -- Egg usage — every way eggs leave stock (sold, donated, consumed on-farm, or -- other). Current POOLED stock = SUM(egg_collections.total) - SUM(egg_usage.qty), -- computed on read rather than cached, same as every other derived total in -- this app. `grade` optionally attributes a usage row to a specific grade -- bucket (e.g. selling cracked eggs at a discount) — 'mixed' is the default/ -- legacy meaning "drawn from the pooled stock, not attributed to one grade", -- exactly how every row behaved before grades existed here, so old rows and -- anyone who doesn't care to specify keep working unchanged. Per-grade -- available stock = that grade's SUM(egg_collections.) minus -- SUM(egg_usage.qty) WHERE grade = that same grade — see -- EggUsageController::availableForGrade(). `sale_id` is set only for -- reason='sold' rows, which SalesController creates automatically (one row -- per egg line item, each carrying that line's own grade) — never written -- directly by the user. -- --------------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS egg_usage ( id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, farm_id INT UNSIGNED NOT NULL, qty INT UNSIGNED NOT NULL, reason ENUM('sold','donated','consumed','missing','hatching','other') NOT NULL, grade ENUM('gradeTwin','gradeA','gradeB','cracked','dirty','rejected','mixed') NOT NULL DEFAULT 'mixed', -- which egg store this usage was drawn from (managed list 'eggStores'), -- nullable for pre-existing rows — see egg_collections.store above store VARCHAR(64) NULL, note VARCHAR(255) NULL, used_on DATE NOT NULL, sale_id INT UNSIGNED NULL, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, KEY idx_egg_usage_farm (farm_id, used_on), KEY idx_eggusage_farm_store (farm_id, store), CONSTRAINT fk_egg_usage_farm FOREIGN KEY (farm_id) REFERENCES farms(id) ON DELETE CASCADE, CONSTRAINT fk_egg_usage_sale FOREIGN KEY (sale_id) REFERENCES sales(id) ON DELETE CASCADE ) ENGINE=InnoDB; -- --------------------------------------------------------------------------- -- Hatchery — incubation "sets" from egg-set to hatch, with candling events. -- An 'own' set draws eggs from stock (egg_usage, reason 'hatching', linked by -- egg_usage_id); a 'bought' set records supplier + per-egg cost. Hatching can -- place chicks as a new flock (flock_id). See migration-2026-07-hatchery.sql. -- --------------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS hatch_sets ( id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, farm_id INT UNSIGNED NOT NULL, ref_no VARCHAR(40) NOT NULL, source ENUM('own','bought') NOT NULL DEFAULT 'own', breed VARCHAR(64) NOT NULL, store VARCHAR(64) NULL, machine VARCHAR(80) NULL, eggs_set INT UNSIGNED NOT NULL DEFAULT 0, set_on DATE NOT NULL, incubation_days INT UNSIGNED NOT NULL DEFAULT 21, expected_hatch_on DATE NULL, supplier VARCHAR(120) NULL, egg_unit_cost DECIMAL(10,2) NULL, eggs_removed INT UNSIGNED NOT NULL DEFAULT 0, status ENUM('incubating','hatched','cancelled') NOT NULL DEFAULT 'incubating', hatched_on DATE NULL, chicks_hatched INT UNSIGNED NULL, chicks_culled INT UNSIGNED NOT NULL DEFAULT 0, dead_in_shell INT UNSIGNED NULL, flock_id INT UNSIGNED NULL, egg_usage_id INT UNSIGNED NULL, notes VARCHAR(500) NULL, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, KEY idx_hs_farm (farm_id), KEY idx_hs_status (farm_id, status), CONSTRAINT fk_hs_farm FOREIGN KEY (farm_id) REFERENCES farms(id) ON DELETE CASCADE, CONSTRAINT fk_hs_flock FOREIGN KEY (flock_id) REFERENCES flocks(id) ON DELETE SET NULL, CONSTRAINT fk_hs_usage FOREIGN KEY (egg_usage_id) REFERENCES egg_usage(id) ON DELETE SET NULL ) ENGINE=InnoDB; CREATE TABLE IF NOT EXISTS hatch_candlings ( id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, farm_id INT UNSIGNED NOT NULL, hatch_set_id INT UNSIGNED NOT NULL, candled_on DATE NOT NULL, stage VARCHAR(40) NULL, eggs_checked INT UNSIGNED NULL, removed INT UNSIGNED NOT NULL DEFAULT 0, note VARCHAR(255) NULL, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, KEY idx_hc_set (hatch_set_id), CONSTRAINT fk_hc_farm FOREIGN KEY (farm_id) REFERENCES farms(id) ON DELETE CASCADE, CONSTRAINT fk_hc_set FOREIGN KEY (hatch_set_id) REFERENCES hatch_sets(id) ON DELETE CASCADE ) ENGINE=InnoDB; -- --------------------------------------------------------------------------- -- Biosecurity — visitor/vehicle register, disinfection log, configurable -- checklist templates run as pass/fail inspections, and incidents. Standalone -- module. See migration-2026-07-biosecurity.sql. -- --------------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS biosecurity_visitors ( id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, farm_id INT UNSIGNED NOT NULL, visitor_name VARCHAR(120) NOT NULL, company VARCHAR(120) NULL, purpose VARCHAR(160) NULL, phone VARCHAR(40) NULL, from_location VARCHAR(160) NULL, vehicle_reg VARCHAR(40) NULL, visited_on DATE NOT NULL, time_in VARCHAR(5) NULL, time_out VARCHAR(5) NULL, disinfected TINYINT(1) NOT NULL DEFAULT 0, note VARCHAR(255) NULL, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, KEY idx_bvis_farm (farm_id, visited_on), CONSTRAINT fk_bvis_farm FOREIGN KEY (farm_id) REFERENCES farms(id) ON DELETE CASCADE ) ENGINE=InnoDB; CREATE TABLE IF NOT EXISTS biosecurity_disinfections ( id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, farm_id INT UNSIGNED NOT NULL, location VARCHAR(120) NOT NULL, chemical VARCHAR(120) NULL, concentration VARCHAR(60) NULL, done_by VARCHAR(120) NULL, done_on DATE NOT NULL, time VARCHAR(5) NULL, note VARCHAR(255) NULL, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, KEY idx_bdis_farm (farm_id, done_on), CONSTRAINT fk_bdis_farm FOREIGN KEY (farm_id) REFERENCES farms(id) ON DELETE CASCADE ) ENGINE=InnoDB; CREATE TABLE IF NOT EXISTS biosecurity_incidents ( id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, farm_id INT UNSIGNED NOT NULL, incident_type VARCHAR(120) NOT NULL, severity ENUM('low','medium','high') NOT NULL DEFAULT 'medium', occurred_on DATE NOT NULL, description VARCHAR(500) NULL, action_taken VARCHAR(500) NULL, status ENUM('open','resolved') NOT NULL DEFAULT 'open', resolved_on DATE NULL, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, KEY idx_binc_farm (farm_id, status), CONSTRAINT fk_binc_farm FOREIGN KEY (farm_id) REFERENCES farms(id) ON DELETE CASCADE ) ENGINE=InnoDB; CREATE TABLE IF NOT EXISTS biosecurity_checklists ( id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, farm_id INT UNSIGNED NOT NULL, name VARCHAR(120) NOT NULL, active TINYINT(1) NOT NULL DEFAULT 1, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, KEY idx_bchk_farm (farm_id), CONSTRAINT fk_bchk_farm FOREIGN KEY (farm_id) REFERENCES farms(id) ON DELETE CASCADE ) ENGINE=InnoDB; CREATE TABLE IF NOT EXISTS biosecurity_checklist_items ( id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, template_id INT UNSIGNED NOT NULL, farm_id INT UNSIGNED NOT NULL, label VARCHAR(200) NOT NULL, sort_order INT UNSIGNED NOT NULL DEFAULT 0, KEY idx_bchki_tpl (template_id), CONSTRAINT fk_bchki_tpl FOREIGN KEY (template_id) REFERENCES biosecurity_checklists(id) ON DELETE CASCADE, CONSTRAINT fk_bchki_farm FOREIGN KEY (farm_id) REFERENCES farms(id) ON DELETE CASCADE ) ENGINE=InnoDB; CREATE TABLE IF NOT EXISTS biosecurity_inspections ( id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, farm_id INT UNSIGNED NOT NULL, template_id INT UNSIGNED NULL, template_name VARCHAR(120) NULL, inspected_on DATE NOT NULL, inspector VARCHAR(120) NULL, passed INT UNSIGNED NOT NULL DEFAULT 0, failed INT UNSIGNED NOT NULL DEFAULT 0, na INT UNSIGNED NOT NULL DEFAULT 0, score_pct DECIMAL(5,1) NULL, note VARCHAR(255) NULL, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, KEY idx_bins_farm (farm_id, inspected_on), CONSTRAINT fk_bins_farm FOREIGN KEY (farm_id) REFERENCES farms(id) ON DELETE CASCADE, CONSTRAINT fk_bins_tpl FOREIGN KEY (template_id) REFERENCES biosecurity_checklists(id) ON DELETE SET NULL ) ENGINE=InnoDB; CREATE TABLE IF NOT EXISTS biosecurity_inspection_results ( id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, inspection_id INT UNSIGNED NOT NULL, farm_id INT UNSIGNED NOT NULL, label VARCHAR(200) NOT NULL, result ENUM('pass','fail','na') NOT NULL DEFAULT 'pass', note VARCHAR(255) NULL, KEY idx_binsr_ins (inspection_id), CONSTRAINT fk_binsr_ins FOREIGN KEY (inspection_id) REFERENCES biosecurity_inspections(id) ON DELETE CASCADE, CONSTRAINT fk_binsr_farm FOREIGN KEY (farm_id) REFERENCES farms(id) ON DELETE CASCADE ) ENGINE=InnoDB; -- --------------------------------------------------------------------------- -- Equipment & maintenance — a register of individual durable machines and -- keeping them running (separate from Inventory's consumable stock). A -- completed maintenance record with a cost posts a Maintenance expense to the -- ledger, exactly like Fleet. See migration-2026-07-equipment.sql. -- --------------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS equipment ( id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, farm_id INT UNSIGNED NOT NULL, name VARCHAR(120) NOT NULL, type VARCHAR(60) NULL, serial_no VARCHAR(80) NULL, location VARCHAR(120) NULL, manufacturer VARCHAR(120) NULL, model VARCHAR(120) NULL, purchased_on DATE NULL, purchase_cost DECIMAL(14,2) NULL, useful_life_months INT UNSIGNED NULL, depreciation_rate DECIMAL(5,2) NULL, status ENUM('running','maintenance','down','retired') NOT NULL DEFAULT 'running', notes VARCHAR(500) NULL, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, KEY idx_equip_farm (farm_id), CONSTRAINT fk_equip_farm FOREIGN KEY (farm_id) REFERENCES farms(id) ON DELETE CASCADE ) ENGINE=InnoDB; CREATE TABLE IF NOT EXISTS equipment_maintenance ( id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, farm_id INT UNSIGNED NOT NULL, equipment_id INT UNSIGNED NOT NULL, kind ENUM('preventive','repair','inspection','other') NOT NULL DEFAULT 'repair', service_date DATE NOT NULL, description VARCHAR(255) NULL, cost DECIMAL(14,2) NOT NULL DEFAULT 0, provider VARCHAR(120) NULL, downtime_hours DECIMAL(8,2) NULL, next_due_date DATE NULL, status ENUM('scheduled','done') NOT NULL DEFAULT 'done', notes VARCHAR(500) NULL, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, KEY idx_emaint_farm (farm_id, service_date), KEY idx_emaint_equip (equipment_id), CONSTRAINT fk_emaint_farm FOREIGN KEY (farm_id) REFERENCES farms(id) ON DELETE CASCADE, CONSTRAINT fk_emaint_equip FOREIGN KEY (equipment_id) REFERENCES equipment(id) ON DELETE CASCADE ) ENGINE=InnoDB; CREATE TABLE IF NOT EXISTS equipment_pm ( id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, farm_id INT UNSIGNED NOT NULL, equipment_id INT UNSIGNED NOT NULL, task VARCHAR(160) NOT NULL, interval_days INT UNSIGNED NOT NULL DEFAULT 30, last_done_on DATE NULL, next_due_on DATE NOT NULL, active TINYINT(1) NOT NULL DEFAULT 1, notes VARCHAR(255) NULL, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, KEY idx_epm_farm (farm_id, next_due_on), KEY idx_epm_equip (equipment_id), CONSTRAINT fk_epm_farm FOREIGN KEY (farm_id) REFERENCES farms(id) ON DELETE CASCADE, CONSTRAINT fk_epm_equip FOREIGN KEY (equipment_id) REFERENCES equipment(id) ON DELETE CASCADE ) ENGINE=InnoDB; -- --------------------------------------------------------------------------- -- Document management — uploaded farm documents (licences, permits, contracts, -- SOPs, receipts) with categories + expiry tracking. File bytes live on the -- filesystem outside the web root (backend/storage/documents); only this -- metadata is in the DB. See migration-2026-07-documents.sql. -- --------------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS documents ( id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, farm_id INT UNSIGNED NOT NULL, title VARCHAR(160) NOT NULL, category VARCHAR(60) NULL, doc_no VARCHAR(80) NULL, issuer VARCHAR(120) NULL, issued_on DATE NULL, expires_on DATE NULL, file_name VARCHAR(200) NOT NULL, stored_name VARCHAR(200) NOT NULL, mime VARCHAR(120) NULL, size_bytes INT UNSIGNED NOT NULL DEFAULT 0, uploaded_by VARCHAR(120) NULL, notes VARCHAR(500) NULL, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, KEY idx_doc_farm (farm_id), KEY idx_doc_expiry (farm_id, expires_on), CONSTRAINT fk_doc_farm FOREIGN KEY (farm_id) REFERENCES farms(id) ON DELETE CASCADE ) ENGINE=InnoDB; -- --------------------------------------------------------------------------- -- CRM / marketing — campaigns (SMS/email blasts to a customer segment, sent via -- the EgoSMS gateway / SMTP, with per-recipient status), follow-up tasks, and -- complaints. Builds on the Customers directory. See migration-2026-07-crm.sql. -- --------------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS crm_campaigns ( id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, farm_id INT UNSIGNED NOT NULL, name VARCHAR(120) NOT NULL, channel ENUM('sms','email') NOT NULL DEFAULT 'sms', audience VARCHAR(30) NOT NULL DEFAULT 'all', subject VARCHAR(160) NULL, message TEXT NOT NULL, status ENUM('draft','sent') NOT NULL DEFAULT 'draft', total INT UNSIGNED NOT NULL DEFAULT 0, sent_count INT UNSIGNED NOT NULL DEFAULT 0, failed_count INT UNSIGNED NOT NULL DEFAULT 0, sent_at DATETIME NULL, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, KEY idx_crmc_farm (farm_id), CONSTRAINT fk_crmc_farm FOREIGN KEY (farm_id) REFERENCES farms(id) ON DELETE CASCADE ) ENGINE=InnoDB; CREATE TABLE IF NOT EXISTS crm_campaign_recipients ( id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, campaign_id INT UNSIGNED NOT NULL, farm_id INT UNSIGNED NOT NULL, customer_id INT UNSIGNED NULL, name VARCHAR(160) NULL, contact VARCHAR(160) NULL, status ENUM('pending','sent','failed','skipped') NOT NULL DEFAULT 'pending', detail VARCHAR(255) NULL, sent_at DATETIME NULL, KEY idx_crmr_campaign (campaign_id), CONSTRAINT fk_crmr_campaign FOREIGN KEY (campaign_id) REFERENCES crm_campaigns(id) ON DELETE CASCADE, CONSTRAINT fk_crmr_farm FOREIGN KEY (farm_id) REFERENCES farms(id) ON DELETE CASCADE, CONSTRAINT fk_crmr_customer FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE SET NULL ) ENGINE=InnoDB; CREATE TABLE IF NOT EXISTS crm_followups ( id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, farm_id INT UNSIGNED NOT NULL, customer_id INT UNSIGNED NULL, customer_name VARCHAR(160) NULL, title VARCHAR(200) NOT NULL, due_on DATE NOT NULL, assigned_to VARCHAR(120) NULL, status ENUM('open','done') NOT NULL DEFAULT 'open', done_on DATE NULL, notes VARCHAR(500) NULL, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, KEY idx_crmf_farm (farm_id, status, due_on), KEY idx_crmf_customer (customer_id), CONSTRAINT fk_crmf_farm FOREIGN KEY (farm_id) REFERENCES farms(id) ON DELETE CASCADE, CONSTRAINT fk_crmf_customer FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE SET NULL ) ENGINE=InnoDB; CREATE TABLE IF NOT EXISTS crm_complaints ( id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, farm_id INT UNSIGNED NOT NULL, customer_id INT UNSIGNED NULL, customer_name VARCHAR(160) NULL, subject VARCHAR(200) NOT NULL, severity ENUM('low','medium','high') NOT NULL DEFAULT 'medium', description VARCHAR(500) NULL, action_taken VARCHAR(500) NULL, status ENUM('open','resolved') NOT NULL DEFAULT 'open', occurred_on DATE NOT NULL, resolved_on DATE NULL, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, KEY idx_crmx_farm (farm_id, status), KEY idx_crmx_customer (customer_id), CONSTRAINT fk_crmx_farm FOREIGN KEY (farm_id) REFERENCES farms(id) ON DELETE CASCADE, CONSTRAINT fk_crmx_customer FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE SET NULL ) ENGINE=InnoDB; -- --------------------------------------------------------------------------- -- Egg store transfers — moves pooled stock between two egg stores (e.g. Cold -- Room -> Shop Front). A transfer never changes the farm-wide total (it -- credits one store and debits another by the same qty), so it's a pure -- ledger, not a collection or usage event — see the comment atop -- sql/migration-2026-07-egg-stores.sql for the read-time qty-per-store formula. -- --------------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS egg_store_transfers ( id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, farm_id INT UNSIGNED NOT NULL, from_store VARCHAR(64) NOT NULL, to_store VARCHAR(64) NOT NULL, qty INT UNSIGNED NOT NULL, -- 'mixed' (default) moves pooled/ungraded stock, same as before this column -- existed; naming a real grade attributes the move to that grade's bucket -- in both stores — see sql/migration-2026-07-egg-store-transfer-grade.sql grade ENUM('gradeTwin','gradeA','gradeB','cracked','dirty','rejected','mixed') NOT NULL DEFAULT 'mixed', note VARCHAR(255) NULL, moved_on DATE NOT NULL, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, KEY idx_esxfer_farm (farm_id, moved_on), CONSTRAINT fk_esxfer_farm FOREIGN KEY (farm_id) REFERENCES farms(id) ON DELETE CASCADE ) ENGINE=InnoDB; -- --------------------------------------------------------------------------- -- Bird usage — manual ways LIVE birds leave the flock other than dying: -- donated or consumed on-farm (mirrors egg_usage's manual reasons). Unlike -- eggs, "sold" has no row here at all — the number of birds a sale represents -- is derived client-side from the sale's own line items (see -- lib/metrics.js#birdsSoldFrom), the same way it's already computed for the -- Dashboard/Topbar Live Birds figure, so there's nothing to sync. "Died" also -- has no row here — that's daily_entries.mortality + culls, already recorded -- through Daily Entry, not re-entered a second time through this table. -- --------------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS bird_usage ( id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, farm_id INT UNSIGNED NOT NULL, qty INT UNSIGNED NOT NULL, reason ENUM('donated','consumed','missing','other') NOT NULL, note VARCHAR(255) NULL, used_on DATE NOT NULL, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, KEY idx_bird_usage_farm (farm_id, used_on), CONSTRAINT fk_bird_usage_farm FOREIGN KEY (farm_id) REFERENCES farms(id) ON DELETE CASCADE ) ENGINE=InnoDB; -- --------------------------------------------------------------------------- -- Farm payment accounts — how customers can pay this farm (bank details, -- mobile money, ...), printed on every invoice/receipt. Free-text method + -- details rather than rigid bank-name/account-number columns, since the -- right fields vary a lot by payment method (a bank transfer needs an -- account number; mobile money just needs a phone number and a name). -- --------------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS farm_payment_accounts ( id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, farm_id INT UNSIGNED NOT NULL, method VARCHAR(80) NOT NULL, -- e.g. "Bank Transfer", "Mobile Money (MTN)" details VARCHAR(255) NOT NULL, -- e.g. "Stanbic Bank · Acc 9030012345 · HOLA Farm Ltd" sort_order INT NOT NULL DEFAULT 0, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, KEY idx_fpa_farm (farm_id, sort_order), CONSTRAINT fk_fpa_farm FOREIGN KEY (farm_id) REFERENCES farms(id) ON DELETE CASCADE ) ENGINE=InnoDB; -- --------------------------------------------------------------------------- -- Expenses (manual). Payroll runs surface as expenses at read time (see view). -- --------------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS expenses ( id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, farm_id INT UNSIGNED NOT NULL, ref_no VARCHAR(40) NOT NULL, category VARCHAR(60) NOT NULL, payee VARCHAR(160) NOT NULL, items VARCHAR(255) NULL, -- free-text description of what was paid for, e.g. "300 egg trays, wire mesh" amount DECIMAL(14,2) NOT NULL DEFAULT 0, method VARCHAR(40) NOT NULL DEFAULT 'Cash', status ENUM('paid','pending') NOT NULL DEFAULT 'paid', spent_on DATE NOT NULL, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, UNIQUE KEY uq_expense (farm_id, ref_no), KEY idx_exp_farm (farm_id, spent_on), CONSTRAINT fk_exp_farm FOREIGN KEY (farm_id) REFERENCES farms(id) ON DELETE CASCADE ) ENGINE=InnoDB; -- --------------------------------------------------------------------------- -- Employees + Payroll -- --------------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS employees ( id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, farm_id INT UNSIGNED NOT NULL, name VARCHAR(160) NOT NULL, role VARCHAR(80) NOT NULL, department VARCHAR(80) NOT NULL, phone VARCHAR(40) NULL, email VARCHAR(160) NULL, salary DECIMAL(14,2) NOT NULL DEFAULT 0, -- gross monthly status ENUM('present','absent','leave') NOT NULL DEFAULT 'present', created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, KEY idx_emp_farm (farm_id), CONSTRAINT fk_emp_farm FOREIGN KEY (farm_id) REFERENCES farms(id) ON DELETE CASCADE ) ENGINE=InnoDB; -- Deeper HR — attendance, leave (pending -> approved/rejected) and performance -- reviews, all against employees. See migration-2026-07-hr-plus.sql. CREATE TABLE IF NOT EXISTS hr_attendance ( id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, farm_id INT UNSIGNED NOT NULL, employee_id INT UNSIGNED NOT NULL, work_date DATE NOT NULL, status ENUM('present','absent','late','half_day','leave') NOT NULL DEFAULT 'present', hours DECIMAL(5,2) NULL, note VARCHAR(255) NULL, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, UNIQUE KEY uq_att (farm_id, employee_id, work_date), KEY idx_att_farm (farm_id, work_date), CONSTRAINT fk_att_farm FOREIGN KEY (farm_id) REFERENCES farms(id) ON DELETE CASCADE, CONSTRAINT fk_att_emp FOREIGN KEY (employee_id) REFERENCES employees(id) ON DELETE CASCADE ) ENGINE=InnoDB; CREATE TABLE IF NOT EXISTS hr_leave ( id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, farm_id INT UNSIGNED NOT NULL, employee_id INT UNSIGNED NOT NULL, leave_type VARCHAR(40) NOT NULL DEFAULT 'annual', from_date DATE NOT NULL, to_date DATE NOT NULL, days DECIMAL(4,1) NOT NULL DEFAULT 1, reason VARCHAR(255) NULL, status ENUM('pending','approved','rejected') NOT NULL DEFAULT 'pending', decided_by VARCHAR(120) NULL, decided_on DATE NULL, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, KEY idx_leave_farm (farm_id, status), KEY idx_leave_emp (employee_id), CONSTRAINT fk_leave_farm FOREIGN KEY (farm_id) REFERENCES farms(id) ON DELETE CASCADE, CONSTRAINT fk_leave_emp FOREIGN KEY (employee_id) REFERENCES employees(id) ON DELETE CASCADE ) ENGINE=InnoDB; CREATE TABLE IF NOT EXISTS hr_reviews ( id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, farm_id INT UNSIGNED NOT NULL, employee_id INT UNSIGNED NOT NULL, period VARCHAR(40) NOT NULL, review_date DATE NOT NULL, reviewer VARCHAR(120) NULL, rating TINYINT UNSIGNED NULL, strengths VARCHAR(500) NULL, improvements VARCHAR(500) NULL, notes VARCHAR(500) NULL, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, KEY idx_rev_farm (farm_id), KEY idx_rev_emp (employee_id), CONSTRAINT fk_rev_farm FOREIGN KEY (farm_id) REFERENCES farms(id) ON DELETE CASCADE, CONSTRAINT fk_rev_emp FOREIGN KEY (employee_id) REFERENCES employees(id) ON DELETE CASCADE ) ENGINE=InnoDB; CREATE TABLE IF NOT EXISTS payroll_runs ( id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, farm_id INT UNSIGNED NOT NULL, ref_no VARCHAR(40) NOT NULL, period VARCHAR(40) NOT NULL, -- e.g. "July 2026" item VARCHAR(120) NULL, -- optional description, e.g. "Monthly salaries" method VARCHAR(40) NULL, -- how staff were paid: Cash / Bank transfer / Mobile Money headcount INT UNSIGNED NOT NULL DEFAULT 0, gross DECIMAL(14,2) NOT NULL DEFAULT 0, deductions DECIMAL(14,2) NOT NULL DEFAULT 0, advance_recovered DECIMAL(14,2) NOT NULL DEFAULT 0, -- salary advances recovered in this run net DECIMAL(14,2) NOT NULL DEFAULT 0, -- cash actually paid = gross - deductions - advance_recovered run_on DATE NOT NULL, status VARCHAR(20) NOT NULL DEFAULT 'processed', created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, UNIQUE KEY uq_payroll (farm_id, ref_no), KEY idx_pay_farm (farm_id), CONSTRAINT fk_pay_farm FOREIGN KEY (farm_id) REFERENCES farms(id) ON DELETE CASCADE ) ENGINE=InnoDB; -- --------------------------------------------------------------------------- -- Payroll run lines — one row per employee actually paid in a run, so who's -- included and their individual additions/deductions are real, not just an -- aggregate percentage applied to everyone. employee_id is SET NULL (not -- cascaded) if that employee is later deleted, and employee_name is snapshotted -- at run time — a payroll run is a historical record of what was actually paid -- and must stay readable even after the roster changes. -- --------------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS payroll_run_lines ( id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, run_id INT UNSIGNED NOT NULL, employee_id INT UNSIGNED NULL, employee_name VARCHAR(160) NOT NULL, base_salary DECIMAL(14,2) NOT NULL DEFAULT 0, additions DECIMAL(14,2) NOT NULL DEFAULT 0, deductions DECIMAL(14,2) NOT NULL DEFAULT 0, advance_recovery DECIMAL(14,2) NOT NULL DEFAULT 0, -- salary advance recovered from this employee this run net DECIMAL(14,2) NOT NULL DEFAULT 0, note VARCHAR(255) NULL, KEY idx_prl_run (run_id), KEY idx_prl_employee (employee_id), CONSTRAINT fk_prl_run FOREIGN KEY (run_id) REFERENCES payroll_runs(id) ON DELETE CASCADE, CONSTRAINT fk_prl_employee FOREIGN KEY (employee_id) REFERENCES employees(id) ON DELETE SET NULL ) ENGINE=InnoDB; -- --------------------------------------------------------------------------- -- Payroll run line items — the itemized breakdown behind each line's flat -- `additions`/`deductions` totals above (e.g. "Overtime" + "Bonus" as two -- separate addition items, "NSSF" + "Loan repayment" as two deduction items). -- Those totals on payroll_run_lines stay as server-computed sums of these -- rows so existing reads (reports, the runs table) don't need to change. -- --------------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS payroll_run_line_items ( id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, run_line_id INT UNSIGNED NOT NULL, kind ENUM('addition','deduction') NOT NULL, label VARCHAR(120) NOT NULL, amount DECIMAL(14,2) NOT NULL, KEY idx_prli_line (run_line_id), CONSTRAINT fk_prli_line FOREIGN KEY (run_line_id) REFERENCES payroll_run_lines(id) ON DELETE CASCADE ) ENGINE=InnoDB; -- --------------------------------------------------------------------------- -- Salary advances — money paid to a staff member ahead of payday, recovered -- from later payroll. A receivable (asset), NOT an expense: `recovered` grows -- as payroll runs claw it back, and `status` flips to 'cleared' once fully -- repaid. Outstanding = amount - recovered. -- --------------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS salary_advances ( id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, farm_id INT UNSIGNED NOT NULL, employee_id INT UNSIGNED NULL, employee_name VARCHAR(160) NOT NULL, amount DECIMAL(14,2) NOT NULL, recovered DECIMAL(14,2) NOT NULL DEFAULT 0, method VARCHAR(40) NULL, -- how it was paid out: Cash / Bank transfer / Mobile Money advanced_on DATE NOT NULL, note VARCHAR(255) NULL, status VARCHAR(20) NOT NULL DEFAULT 'open', -- open | cleared | void created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, KEY idx_adv_farm (farm_id), KEY idx_adv_employee (employee_id), CONSTRAINT fk_adv_farm FOREIGN KEY (farm_id) REFERENCES farms(id) ON DELETE CASCADE, CONSTRAINT fk_adv_employee FOREIGN KEY (employee_id) REFERENCES employees(id) ON DELETE SET NULL ) ENGINE=InnoDB; -- Exact allocation of each payroll run's advance recovery to specific advances, -- so reversing a run restores precisely what it clawed back (FK-cascades on run -- delete; the destroy handler reads these to un-recover before they vanish). CREATE TABLE IF NOT EXISTS payroll_advance_recoveries ( id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, run_id INT UNSIGNED NOT NULL, advance_id INT UNSIGNED NOT NULL, amount DECIMAL(14,2) NOT NULL, KEY idx_par_run (run_id), KEY idx_par_advance (advance_id), CONSTRAINT fk_par_run FOREIGN KEY (run_id) REFERENCES payroll_runs(id) ON DELETE CASCADE, CONSTRAINT fk_par_advance FOREIGN KEY (advance_id) REFERENCES salary_advances(id) ON DELETE CASCADE ) ENGINE=InnoDB; -- --------------------------------------------------------------------------- -- Managed lists — the dropdown option sets edited in Settings. -- Stored as (list_key, value) rows; grouped by key on read. -- --------------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS managed_lists ( id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, farm_id INT UNSIGNED NOT NULL, list_key VARCHAR(48) NOT NULL, -- e.g. 'breeds','feedTypes','vets','eggStores' value VARCHAR(120) NOT NULL, sort_order INT NOT NULL DEFAULT 0, UNIQUE KEY uq_list_value (farm_id, list_key, value), KEY idx_list_key (farm_id, list_key), CONSTRAINT fk_managed_lists_farm FOREIGN KEY (farm_id) REFERENCES farms(id) ON DELETE CASCADE ) ENGINE=InnoDB; -- --------------------------------------------------------------------------- -- Roles — a named set of per-module permissions ('none'|'view'|'edit'), -- stored as JSON so the module list can grow without a schema change. -- is_system marks the built-in "Admin" role, which can't be edited or deleted -- (so a farm can never end up with nobody able to manage users/roles). -- --------------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS roles ( id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, farm_id INT UNSIGNED NOT NULL, name VARCHAR(60) NOT NULL, description VARCHAR(160) NULL, is_system TINYINT(1) NOT NULL DEFAULT 0, permissions JSON NOT NULL, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, UNIQUE KEY uq_role_name (farm_id, name), CONSTRAINT fk_roles_farm FOREIGN KEY (farm_id) REFERENCES farms(id) ON DELETE CASCADE ) ENGINE=InnoDB; -- --------------------------------------------------------------------------- -- Users — real login accounts. One farm each; a role governs what they can -- see/do within it. No ON DELETE CASCADE from roles → users on purpose: a -- role that still has users assigned can't be deleted (FK error surfaces as -- a friendly message via Http::friendly()). -- --------------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS users ( id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, farm_id INT UNSIGNED NOT NULL, role_id INT UNSIGNED NOT NULL, name VARCHAR(120) NOT NULL, email VARCHAR(160) NOT NULL, phone VARCHAR(40) NULL, -- for SMS OTP login password_hash VARCHAR(255) NOT NULL, status ENUM('active','suspended') NOT NULL DEFAULT 'active', -- a platform operator who manages ALL farms (create/suspend/delete, manage -- users, and impersonate). Above the per-farm role system; set by hand for -- the first operator, then via the admin dashboard. 0 for ordinary users. is_platform_admin TINYINT(1) NOT NULL DEFAULT 0, -- for a platform admin: JSON array of allowed Platform sections -- (farms/billing/support/system/migrate/admins). NULL = full (super admin). platform_perms JSON NULL, -- optional authenticator-app 2FA: base32 shared secret (NULL when off) and -- whether the second login step is enforced. See migration-2026-07-2fa.sql. totp_secret VARCHAR(64) NULL, totp_enabled TINYINT(1) NOT NULL DEFAULT 0, last_login_at TIMESTAMP NULL, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, UNIQUE KEY uq_user_email (email), KEY idx_users_farm (farm_id), CONSTRAINT fk_users_farm FOREIGN KEY (farm_id) REFERENCES farms(id) ON DELETE CASCADE, CONSTRAINT fk_users_role FOREIGN KEY (role_id) REFERENCES roles(id) ) ENGINE=InnoDB; -- --------------------------------------------------------------------------- -- User ↔ farm memberships — grants a user access to a specific farm WITH a -- role in that farm, so one person can be assigned to several farms (a head -- and its branches, or any set) and switch between them. users.farm_id stays -- the user's home/default farm; a matching row here is seeded for it. Switching -- into a farm is only allowed when a membership row exists (enforced in Auth), -- and the user's effective role for that farm is this row's role_id. -- --------------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS user_farms ( id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, user_id INT UNSIGNED NOT NULL, farm_id INT UNSIGNED NOT NULL, role_id INT UNSIGNED NOT NULL, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, UNIQUE KEY uq_user_farm (user_id, farm_id), KEY idx_uf_user (user_id), KEY idx_uf_farm (farm_id), CONSTRAINT fk_uf_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE, CONSTRAINT fk_uf_farm FOREIGN KEY (farm_id) REFERENCES farms(id) ON DELETE CASCADE, CONSTRAINT fk_uf_role FOREIGN KEY (role_id) REFERENCES roles(id) ON DELETE CASCADE ) ENGINE=InnoDB; -- --------------------------------------------------------------------------- -- Sessions — bearer tokens issued at login. A row per active login; deleting -- it (logout) or letting it pass expires_at ends that session immediately. -- --------------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS sessions ( token CHAR(64) PRIMARY KEY, user_id INT UNSIGNED NOT NULL, -- when a platform admin is "inside" a farm (impersonating for setup/support), -- this holds that farm's id so every farm-scoped request resolves to it; -- NULL the rest of the time. Cleared if the farm is deleted. acting_farm_id INT UNSIGNED NULL, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, expires_at TIMESTAMP NOT NULL, KEY idx_sessions_user (user_id), CONSTRAINT fk_sessions_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE, CONSTRAINT fk_sessions_acting_farm FOREIGN KEY (acting_farm_id) REFERENCES farms(id) ON DELETE SET NULL ) ENGINE=InnoDB; -- --------------------------------------------------------------------------- -- Admin audit log — a platform-global (NOT farm-scoped) trail of operator -- actions: provisioning, suspend/reactivate, delete, user changes, and -- impersonation start/stop. Kept out of per-farm backups on purpose. -- --------------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS admin_audit_log ( id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, admin_user_id INT UNSIGNED NULL, -- the operator; NULL-able so the log survives a user delete action VARCHAR(40) NOT NULL, farm_id INT UNSIGNED NULL, detail VARCHAR(255) NULL, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, KEY idx_audit_created (created_at) ) ENGINE=InnoDB; -- --------------------------------------------------------------------------- -- Password resets — a short-lived token for the "forgot password" flow. Until -- a mailer is configured the token is issued but NEVER returned in the API -- response (that would let anyone who knows an email take over the account); -- self-service reset therefore completes only via a real emailed link, and an -- admin can reset any farm user's password from Settings in the meantime. -- --------------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS password_resets ( token CHAR(64) PRIMARY KEY, user_id INT UNSIGNED NOT NULL, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, expires_at TIMESTAMP NOT NULL, KEY idx_pwreset_user (user_id), CONSTRAINT fk_pwreset_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE ) ENGINE=InnoDB; -- --------------------------------------------------------------------------- -- Rate limiting — a fixed-window counter per "bucket" (e.g. login::), -- used to throttle authentication endpoints against brute-force / reset-spam. -- A bucket resets itself once expires_at passes, so no separate sweep is needed. -- --------------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS rate_limits ( bucket VARCHAR(191) PRIMARY KEY, hits INT UNSIGNED NOT NULL DEFAULT 0, expires_at DATETIME NOT NULL ) ENGINE=InnoDB; -- --------------------------------------------------------------------------- -- SMS send log — every attempt (success or failure) from src/Core/Sms.php, -- for two reasons: an audit trail of what was actually texted, and the -- dedup check that stops the same underlying event (a specific vaccination, -- sale, or daily entry) from texting a farm more than once. `ref_id` is the -- triggering record's own id (nullable — a manual/ad-hoc send has none). -- --------------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS sms_log ( id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, farm_id INT UNSIGNED NOT NULL, kind VARCHAR(30) NOT NULL, -- 'mortality','feed_reorder','sale_receipt','vaccination_due' ref_id INT UNSIGNED NULL, phone VARCHAR(30) NOT NULL, message VARCHAR(400) NOT NULL, status VARCHAR(20) NOT NULL, -- 'OK' or 'Failed', straight from the gateway response VARCHAR(255) NULL, -- the gateway's own message, for troubleshooting sent_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, KEY idx_sms_farm (farm_id, sent_at), KEY idx_sms_dedupe (farm_id, kind, ref_id), CONSTRAINT fk_sms_farm FOREIGN KEY (farm_id) REFERENCES farms(id) ON DELETE CASCADE ) ENGINE=InnoDB; -- --------------------------------------------------------------------------- -- Per-farm, per-trigger SMS settings (Configurations page) — each of the 4 -- triggers (mortality, feed_reorder, sale_receipt, vaccination_due) can be -- independently turned off and given its own custom wording, layered under -- the farm's general "SMS alerts" toggle in Settings (which still gates all -- of them). A farm gets a row here only once it customizes a trigger — no -- row means "enabled, built-in default wording" (see Sms::triggerConfig()). -- --------------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS sms_triggers ( id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, farm_id INT UNSIGNED NOT NULL, kind VARCHAR(30) NOT NULL, -- 'mortality','feed_reorder','sale_receipt','vaccination_due' enabled TINYINT(1) NOT NULL DEFAULT 1, message VARCHAR(500) NULL, -- custom {token} template; NULL falls back to the built-in default created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, UNIQUE KEY uq_sms_trigger_farm_kind (farm_id, kind), CONSTRAINT fk_sms_trigger_farm FOREIGN KEY (farm_id) REFERENCES farms(id) ON DELETE CASCADE ) ENGINE=InnoDB; -- --------------------------------------------------------------------------- -- A read-time view that unions manual expenses with payroll runs and feed -- purchases, so both auto-appear in Expenses instead of needing to be -- re-entered by hand. `source` flags which rows are read-only (derived). -- --------------------------------------------------------------------------- CREATE OR REPLACE VIEW v_expenses_all AS SELECT id, farm_id, ref_no, category, payee, items, amount, method, status, spent_on, 'manual' AS source FROM expenses UNION ALL SELECT NULL AS id, farm_id, ref_no, 'Payroll' AS category, CONCAT('Staff wages - ', period) AS payee, item AS items, net AS amount, COALESCE(NULLIF(method, ''), 'Bank transfer') AS method, 'paid' AS status, run_on AS spent_on, 'payroll' AS source FROM payroll_runs UNION ALL SELECT id AS id, farm_id, COALESCE(NULLIF(invoice_no, ''), CONCAT('FP-', id)) AS ref_no, 'Feed' AS category, IF(supplier IS NOT NULL AND supplier <> '', supplier, CONCAT(feed_type, ' purchase')) AS payee, CONCAT(qty_kg, 'kg ', feed_type) AS items, line_cost AS amount, CASE WHEN payment_status = 'donated' THEN 'Donated' WHEN payment_status = 'credit' AND settled_on IS NULL THEN 'Credit' ELSE 'Cash' END AS method, CASE WHEN payment_status = 'credit' AND settled_on IS NULL THEN 'pending' ELSE 'paid' END AS status, purchased_on AS spent_on, 'feed' AS source FROM feed_purchases; -- =========================================================================== -- Mini accounting — a real double-entry ledger sitting UNDER the transactions -- users already record. Sales, Expenses, Payroll, Feed purchases and Booking -- deposits each auto-post one balanced journal entry the moment they're saved -- (see App\Core\Ledger, called from inside each source controller's own -- transaction, so a posting failure rolls the source write back too). The -- ledger is the single source of truth for the Trial Balance / Balance Sheet / -- Cashbook / General Ledger reports — nothing there is a re-sum of the source -- tables, it's all aggregated from journal_entry_lines, so the books always -- tie out (Σ debits = Σ credits by construction). -- =========================================================================== -- Chart of accounts — one per farm, seeded with a default poultry chart on -- first use (Ledger::seedChart). `role` is a stable semantic key the -- auto-poster resolves accounts by (e.g. 'cash', 'accounts_receivable', -- 'sales_income') so it never hard-codes account names; `map_key` links an -- expense account to the expense category it represents ('Feed', 'Utilities', -- 'Payroll', …), so a brand-new category auto-creates a matching account. CREATE TABLE IF NOT EXISTS chart_of_accounts ( id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, farm_id INT UNSIGNED NOT NULL, code VARCHAR(20) NOT NULL, name VARCHAR(120) NOT NULL, type ENUM('asset','liability','equity','income','expense') NOT NULL, role VARCHAR(48) NULL, -- fixed system accounts only (cash, accounts_receivable, …) map_key VARCHAR(80) NULL, -- expense-category link (Feed / Utilities / Payroll / …) is_system TINYINT(1) NOT NULL DEFAULT 0, -- seeded/auto-created accounts the user can't delete out from under the poster sort_order INT UNSIGNED NOT NULL DEFAULT 0, active TINYINT(1) NOT NULL DEFAULT 1, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, UNIQUE KEY uq_coa_code (farm_id, code), KEY idx_coa_farm (farm_id), KEY idx_coa_role (farm_id, role), KEY idx_coa_mapkey (farm_id, map_key), CONSTRAINT fk_coa_farm FOREIGN KEY (farm_id) REFERENCES farms(id) ON DELETE CASCADE ) ENGINE=InnoDB; -- Journal entry header. `source_type`+`source_id` link back to the transaction -- that generated it (sale/expense/payroll/feed/booking/sale_payment/…), so an -- edit re-posts idempotently (delete + reinsert this entry) and a delete -- reverses cleanly. A manual entry (opening balances, adjustments) leaves both -- NULL. MySQL allows many NULLs in a UNIQUE key, so several manual entries -- coexist while each source transaction still maps to at most one entry. CREATE TABLE IF NOT EXISTS journal_entries ( id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, farm_id INT UNSIGNED NOT NULL, entry_no VARCHAR(40) NOT NULL, entry_date DATE NOT NULL, memo VARCHAR(255) NULL, source_type VARCHAR(30) NULL, source_id INT UNSIGNED NULL, is_system TINYINT(1) NOT NULL DEFAULT 0, -- 1 = auto-posted (read-only in the UI); 0 = a hand-keyed manual entry created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, UNIQUE KEY uq_je_source (farm_id, source_type, source_id), KEY idx_je_farm (farm_id, entry_date), CONSTRAINT fk_je_farm FOREIGN KEY (farm_id) REFERENCES farms(id) ON DELETE CASCADE ) ENGINE=InnoDB; -- One debit-or-credit posting against one account. Every line carries exactly -- one non-zero side; the entry as a whole must balance (enforced in Ledger::post). CREATE TABLE IF NOT EXISTS journal_entry_lines ( id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, entry_id INT UNSIGNED NOT NULL, account_id INT UNSIGNED NOT NULL, debit DECIMAL(14,2) NOT NULL DEFAULT 0, credit DECIMAL(14,2) NOT NULL DEFAULT 0, memo VARCHAR(255) NULL, sort_order INT UNSIGNED NOT NULL DEFAULT 0, KEY idx_jel_entry (entry_id), KEY idx_jel_account (account_id), CONSTRAINT fk_jel_entry FOREIGN KEY (entry_id) REFERENCES journal_entries(id) ON DELETE CASCADE, CONSTRAINT fk_jel_account FOREIGN KEY (account_id) REFERENCES chart_of_accounts(id) ) ENGINE=InnoDB; -- Budgets — an annual planned amount per income/expense account, compared -- against ledger actuals in the Budgets screen. One row per (account, year). -- See migration-2026-07-budgets.sql. CREATE TABLE IF NOT EXISTS budgets ( id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, farm_id INT UNSIGNED NOT NULL, account_id INT UNSIGNED NOT NULL, fiscal_year SMALLINT UNSIGNED NOT NULL, amount DECIMAL(14,2) NOT NULL DEFAULT 0, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, UNIQUE KEY uq_budget (farm_id, account_id, fiscal_year), KEY idx_budget_farm_year (farm_id, fiscal_year), CONSTRAINT fk_budget_farm FOREIGN KEY (farm_id) REFERENCES farms(id) ON DELETE CASCADE, CONSTRAINT fk_budget_account FOREIGN KEY (account_id) REFERENCES chart_of_accounts(id) ON DELETE CASCADE ) ENGINE=InnoDB; -- Payments received against a credit/partial SALE after it was first invoiced — -- the receipt history that keeps Accounts Receivable honest over time. Each row -- also auto-posts Dr Cash/Bank, Cr Accounts Receivable. CREATE TABLE IF NOT EXISTS sale_payments ( id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, farm_id INT UNSIGNED NOT NULL, sale_id INT UNSIGNED NOT NULL, amount DECIMAL(14,2) NOT NULL DEFAULT 0, method VARCHAR(40) NOT NULL DEFAULT 'Cash', paid_on DATE NOT NULL, note VARCHAR(255) NULL, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, KEY idx_sp_farm (farm_id), KEY idx_sp_sale (sale_id), CONSTRAINT fk_sp_farm FOREIGN KEY (farm_id) REFERENCES farms(id) ON DELETE CASCADE, CONSTRAINT fk_sp_sale FOREIGN KEY (sale_id) REFERENCES sales(id) ON DELETE CASCADE ) ENGINE=InnoDB; -- Mirror of sale_payments for settling a PENDING expense later (Dr Accounts -- Payable, Cr Cash/Bank). CREATE TABLE IF NOT EXISTS expense_payments ( id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, farm_id INT UNSIGNED NOT NULL, expense_id INT UNSIGNED NOT NULL, amount DECIMAL(14,2) NOT NULL DEFAULT 0, method VARCHAR(40) NOT NULL DEFAULT 'Cash', paid_on DATE NOT NULL, note VARCHAR(255) NULL, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, KEY idx_ep_farm (farm_id), KEY idx_ep_expense (expense_id), CONSTRAINT fk_ep_farm FOREIGN KEY (farm_id) REFERENCES farms(id) ON DELETE CASCADE, CONSTRAINT fk_ep_expense FOREIGN KEY (expense_id) REFERENCES expenses(id) ON DELETE CASCADE ) ENGINE=InnoDB; -- =========================================================================== -- Billing & subscriptions (platform-level SaaS layer, managed by an operator -- from the Platform admin dashboard). A farm is put on a `plan` (the pricing -- catalogue), which seeds a `subscription` holding that farm's EFFECTIVE terms -- — every value overridable per farm. The subscription drives the trial → -- active → past_due → suspended lifecycle (a suspended subscription flips -- farms.status = 'suspended', which blocks login). SMS is billed either PREPAID -- (subscriptions.sms_balance credits, decremented per send in App\Core\Sms and -- blocked at zero) or POSTPAID (usage counted from sms_log, invoiced at each -- renewal). Payments are recorded by hand (no gateway). See App\Core\Billing. -- =========================================================================== CREATE TABLE IF NOT EXISTS plans ( id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, name VARCHAR(80) NOT NULL, description VARCHAR(255) NULL, price DECIMAL(12,2) NOT NULL DEFAULT 0, -- subscription price per cycle currency CHAR(3) NOT NULL DEFAULT 'UGX', billing_cycle ENUM('monthly','quarterly','annual') NOT NULL DEFAULT 'monthly', trial_days INT UNSIGNED NOT NULL DEFAULT 0, max_birds INT UNSIGNED NULL, -- NULL = unlimited max_users INT UNSIGNED NULL, -- NULL = unlimited sms_mode ENUM('prepaid','postpaid') NOT NULL DEFAULT 'postpaid', sms_rate DECIMAL(10,2) NOT NULL DEFAULT 0, -- price charged to the farm per SMS sms_included INT UNSIGNED NOT NULL DEFAULT 0, -- free SMS allowance per cycle (postpaid) modules JSON NULL, -- default app modules for farms on this plan; NULL = all is_active TINYINT(1) NOT NULL DEFAULT 1, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ) ENGINE=InnoDB; CREATE TABLE IF NOT EXISTS subscriptions ( id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, farm_id INT UNSIGNED NOT NULL, plan_id INT UNSIGNED NULL, -- NULL once fully custom / plan deleted status ENUM('trial','active','past_due','suspended','cancelled') NOT NULL DEFAULT 'trial', price DECIMAL(12,2) NOT NULL DEFAULT 0, -- effective price (override of plan) currency CHAR(3) NOT NULL DEFAULT 'UGX', billing_cycle ENUM('monthly','quarterly','annual') NOT NULL DEFAULT 'monthly', trial_ends_on DATE NULL, period_start DATE NULL, period_end DATE NULL, -- next due / renewal date max_birds INT UNSIGNED NULL, max_users INT UNSIGNED NULL, sms_mode ENUM('prepaid','postpaid') NOT NULL DEFAULT 'postpaid', sms_rate DECIMAL(10,2) NOT NULL DEFAULT 0, sms_included INT UNSIGNED NOT NULL DEFAULT 0, sms_balance INT NOT NULL DEFAULT 0, -- prepaid credit balance (SMS count); signed to tolerate a race notes VARCHAR(255) NULL, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, UNIQUE KEY uq_sub_farm (farm_id), KEY idx_sub_plan (plan_id), CONSTRAINT fk_sub_farm FOREIGN KEY (farm_id) REFERENCES farms(id) ON DELETE CASCADE, CONSTRAINT fk_sub_plan FOREIGN KEY (plan_id) REFERENCES plans(id) ON DELETE SET NULL ) ENGINE=InnoDB; CREATE TABLE IF NOT EXISTS billing_invoices ( id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, farm_id INT UNSIGNED NOT NULL, invoice_no VARCHAR(40) NOT NULL, type ENUM('subscription','sms','manual') NOT NULL DEFAULT 'subscription', description VARCHAR(255) NULL, period_start DATE NULL, period_end DATE NULL, amount DECIMAL(12,2) NOT NULL DEFAULT 0, currency CHAR(3) NOT NULL DEFAULT 'UGX', status ENUM('unpaid','paid','void') NOT NULL DEFAULT 'unpaid', issued_on DATE NOT NULL, due_on DATE NULL, paid_on DATE NULL, -- once paid, the id of the matching row created in the farm's `expenses` table -- (so the charge shows in Expenses + Trial Balance). No FK: a dangling id after -- a manual expense delete is fine and stops the sync resurrecting it. expense_id INT UNSIGNED NULL, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, UNIQUE KEY uq_binv (farm_id, invoice_no), KEY idx_binv_status (farm_id, status), CONSTRAINT fk_binv_farm FOREIGN KEY (farm_id) REFERENCES farms(id) ON DELETE CASCADE ) ENGINE=InnoDB; CREATE TABLE IF NOT EXISTS billing_payments ( id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, farm_id INT UNSIGNED NOT NULL, invoice_id INT UNSIGNED NULL, amount DECIMAL(12,2) NOT NULL DEFAULT 0, method VARCHAR(40) NULL, reference VARCHAR(80) NULL, paid_on DATE NOT NULL, note VARCHAR(255) NULL, recorded_by INT UNSIGNED NULL, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, KEY idx_bpay_farm (farm_id, paid_on), CONSTRAINT fk_bpay_farm FOREIGN KEY (farm_id) REFERENCES farms(id) ON DELETE CASCADE, CONSTRAINT fk_bpay_invoice FOREIGN KEY (invoice_id) REFERENCES billing_invoices(id) ON DELETE SET NULL ) ENGINE=InnoDB; CREATE TABLE IF NOT EXISTS sms_credit_ledger ( id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, farm_id INT UNSIGNED NOT NULL, delta INT NOT NULL, -- +top-up / -usage / +/- adjustment balance_after INT NOT NULL, reason VARCHAR(30) NOT NULL, -- 'topup' | 'usage' | 'adjustment' sms_log_id INT UNSIGNED NULL, note VARCHAR(255) NULL, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, KEY idx_smscredit_farm (farm_id, created_at), CONSTRAINT fk_smscredit_farm FOREIGN KEY (farm_id) REFERENCES farms(id) ON DELETE CASCADE ) ENGINE=InnoDB; -- =========================================================================== -- Support live chat — one ongoing conversation per farm. Farm users chat from a -- floating widget; the platform operator answers every farm's thread from the -- Platform → Support dashboard. "Live" = short-interval polling (no WebSockets -- on this stack). Unread is derived from each side's last-read message id. -- =========================================================================== CREATE TABLE IF NOT EXISTS support_threads ( id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, farm_id INT UNSIGNED NOT NULL, status ENUM('open','closed') NOT NULL DEFAULT 'open', last_message_at TIMESTAMP NULL, admin_last_read_id INT UNSIGNED NOT NULL DEFAULT 0, farm_last_read_id INT UNSIGNED NOT NULL DEFAULT 0, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, UNIQUE KEY uq_support_farm (farm_id), CONSTRAINT fk_support_thread_farm FOREIGN KEY (farm_id) REFERENCES farms(id) ON DELETE CASCADE ) ENGINE=InnoDB; CREATE TABLE IF NOT EXISTS support_messages ( id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, thread_id INT UNSIGNED NOT NULL, farm_id INT UNSIGNED NOT NULL, sender ENUM('farm','admin') NOT NULL, user_id INT UNSIGNED NULL, user_name VARCHAR(120) NULL, body VARCHAR(2000) NOT NULL, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, KEY idx_support_msg_thread (thread_id, id), KEY idx_support_msg_farm (farm_id, id), CONSTRAINT fk_support_msg_thread FOREIGN KEY (thread_id) REFERENCES support_threads(id) ON DELETE CASCADE, CONSTRAINT fk_support_msg_farm FOREIGN KEY (farm_id) REFERENCES farms(id) ON DELETE CASCADE ) ENGINE=InnoDB; -- =========================================================================== -- Online mobile-money payments (Yo! Uganda) — a farm pays its subscription -- invoices / buys prepaid SMS credit from its own dashboard. Each row is a -- payment intent (pending → succeeded/failed); `applied` guards the money -- effect from running twice. See App\Core\YoPayments + App\Controllers\PaymentController. -- =========================================================================== -- Scheduled-job run log — each cron/HTTP job records a row when it runs; the -- admin System page reads the latest per job to flag a stale/failed schedule. CREATE TABLE IF NOT EXISTS cron_runs ( id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, job VARCHAR(50) NOT NULL, status ENUM('ok','error') NOT NULL DEFAULT 'ok', detail VARCHAR(255) NULL, duration_ms INT UNSIGNED NULL, ran_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, KEY idx_cron_job (job, id) ) ENGINE=InnoDB; CREATE TABLE IF NOT EXISTS payments ( id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, farm_id INT UNSIGNED NOT NULL, kind ENUM('invoice','sms') NOT NULL, invoice_id INT UNSIGNED NULL, credits INT UNSIGNED NULL, amount DECIMAL(12,2) NOT NULL, currency CHAR(3) NOT NULL DEFAULT 'UGX', phone VARCHAR(30) NOT NULL, external_ref VARCHAR(40) NOT NULL, provider_ref VARCHAR(64) NULL, status ENUM('pending','succeeded','failed') NOT NULL DEFAULT 'pending', applied TINYINT(1) NOT NULL DEFAULT 0, message VARCHAR(255) NULL, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, UNIQUE KEY uq_payment_ref (external_ref), KEY idx_payment_farm (farm_id, created_at), KEY idx_payment_invoice (invoice_id), CONSTRAINT fk_payment_farm FOREIGN KEY (farm_id) REFERENCES farms(id) ON DELETE CASCADE, CONSTRAINT fk_payment_invoice FOREIGN KEY (invoice_id) REFERENCES billing_invoices(id) ON DELETE SET NULL ) ENGINE=InnoDB; -- --------------------------------------------------------------------------- -- WebAuthn / passkey sign-in — an optional biometric layer on top of the -- email+password login (see migration-2026-07-webauthn.sql). A device holds -- the private key; the server stores only the public key. Password login is -- unchanged and stays the fallback. -- --------------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS webauthn_credentials ( id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, user_id INT UNSIGNED NOT NULL, credential_id VARCHAR(512) NOT NULL, public_key TEXT NOT NULL, sign_count INT UNSIGNED NOT NULL DEFAULT 0, label VARCHAR(120) NULL, transports VARCHAR(191) NULL, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, last_used_at TIMESTAMP NULL, UNIQUE KEY uq_webauthn_cred (credential_id(191)), KEY idx_webauthn_user (user_id), CONSTRAINT fk_webauthn_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE ) ENGINE=InnoDB; CREATE TABLE IF NOT EXISTS webauthn_challenges ( id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, handle CHAR(64) NOT NULL, challenge VARCHAR(255) NOT NULL, purpose ENUM('register','login') NOT NULL, user_id INT UNSIGNED NULL, expires_at TIMESTAMP NOT NULL, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, UNIQUE KEY uq_webauthn_handle (handle), KEY idx_webauthn_expires (expires_at) ) ENGINE=InnoDB; -- --------------------------------------------------------------------------- -- Platform key/value settings the super admin edits from the dashboard (no -- redeploy). First use: the SMTP config for outgoing email, under key 'smtp'. -- See migration-2026-07-email.sql. -- --------------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS app_settings ( name VARCHAR(64) PRIMARY KEY, value TEXT NULL, updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ) ENGINE=InnoDB; -- --------------------------------------------------------------------------- -- Two-factor auth (authenticator app / TOTP) — see migration-2026-07-2fa.sql. -- Recovery codes are one-time bypass codes; the challenges table bridges the -- two steps of a 2FA login (password step -> code step). -- --------------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS totp_recovery_codes ( id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, user_id INT UNSIGNED NOT NULL, code_hash VARCHAR(255) NOT NULL, used_at TIMESTAMP NULL, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, KEY idx_totp_recovery_user (user_id), CONSTRAINT fk_totp_recovery_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE ) ENGINE=InnoDB; CREATE TABLE IF NOT EXISTS two_factor_challenges ( id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, token CHAR(64) NOT NULL, user_id INT UNSIGNED NOT NULL, attempts TINYINT UNSIGNED NOT NULL DEFAULT 0, expires_at TIMESTAMP NOT NULL, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, UNIQUE KEY uq_2fa_token (token), KEY idx_2fa_expires (expires_at), CONSTRAINT fk_2fa_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE ) ENGINE=InnoDB; -- Personal API tokens — long-lived bearer tokens (prefix 'pfmis_pat_') a user -- creates to call the API as themselves. Only the sha256 hash is stored. See -- migration-2026-07-api-tokens.sql. CREATE TABLE IF NOT EXISTS api_tokens ( id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, user_id INT UNSIGNED NOT NULL, name VARCHAR(120) NOT NULL, token_hash CHAR(64) NOT NULL, prefix VARCHAR(20) NOT NULL, last_used_at TIMESTAMP NULL, expires_at TIMESTAMP NULL, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, UNIQUE KEY uq_apitoken_hash (token_hash), KEY idx_apitoken_user (user_id), CONSTRAINT fk_apitoken_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE ) ENGINE=InnoDB; -- Access requests — public "request access" submissions a platform admin -- reviews and provisions. See migration-2026-07-access-requests.sql. CREATE TABLE IF NOT EXISTS access_requests ( id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, farm_name VARCHAR(160) NOT NULL, contact_name VARCHAR(120) NULL, email VARCHAR(160) NOT NULL, phone VARCHAR(40) NULL, note VARCHAR(500) NULL, status ENUM('pending','approved','rejected') NOT NULL DEFAULT 'pending', farm_id INT UNSIGNED NULL, reviewed_by VARCHAR(160) NULL, reviewed_at TIMESTAMP NULL, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, KEY idx_accreq_status (status, created_at), CONSTRAINT fk_accreq_farm FOREIGN KEY (farm_id) REFERENCES farms(id) ON DELETE SET NULL ) ENGINE=InnoDB; -- Short-lived anti-CSRF state values for the Google OAuth login flow. -- See migration-2026-07-google-sso.sql. CREATE TABLE IF NOT EXISTS oauth_states ( state CHAR(64) PRIMARY KEY, expires_at TIMESTAMP NOT NULL, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, KEY idx_oauth_expires (expires_at) ) ENGINE=InnoDB; -- One-time login codes (email/SMS OTP sign-in). See migration-2026-07-otp-login.sql. CREATE TABLE IF NOT EXISTS login_otps ( id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, user_id INT UNSIGNED NOT NULL, code_hash CHAR(64) NOT NULL, channel ENUM('email','sms') NOT NULL DEFAULT 'email', attempts TINYINT UNSIGNED NOT NULL DEFAULT 0, expires_at TIMESTAMP NOT NULL, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, KEY idx_otp_user (user_id), KEY idx_otp_expires (expires_at), CONSTRAINT fk_otp_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE ) ENGINE=InnoDB; -- --------------------------------------------------------------------------- -- Cameras — a farm's CCTV feeds viewable inside PFMIS (see -- migration-2026-07-cameras.sql). provider 'hikconnect' fetches a browser- -- playable HLS URL + snapshot server-side via the platform's Hik-Connect Open -- Platform account; 'manual' uses directly-pasted HLS/snapshot URLs. -- --------------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS cameras ( id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, farm_id INT UNSIGNED NOT NULL, name VARCHAR(120) NOT NULL, house VARCHAR(60) NULL, provider ENUM('hikconnect','manual') NOT NULL DEFAULT 'hikconnect', device_serial VARCHAR(64) NULL, channel_no INT UNSIGNED NOT NULL DEFAULT 1, verify_code VARCHAR(64) NULL, stream_url VARCHAR(500) NULL, snapshot_url VARCHAR(500) NULL, status ENUM('active','inactive') NOT NULL DEFAULT 'active', sort_order INT NOT NULL DEFAULT 0, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, KEY idx_cameras_farm (farm_id, sort_order), CONSTRAINT fk_cameras_farm FOREIGN KEY (farm_id) REFERENCES farms(id) ON DELETE CASCADE ) ENGINE=InnoDB; CREATE TABLE IF NOT EXISTS hik_token ( id TINYINT UNSIGNED PRIMARY KEY DEFAULT 1, access_token VARCHAR(255) NOT NULL, area_domain VARCHAR(191) NULL, expires_at TIMESTAMP NOT NULL, updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ) ENGINE=InnoDB; -- --------------------------------------------------------------------------- -- Fleet — a farm's vehicles and their running (see migration-2026-07-fleet.sql): -- the vehicle register, drivers, trips (mileage), fuel logs and maintenance. -- 'fleet' is a normal toggleable farm module. -- --------------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS vehicles ( id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, farm_id INT UNSIGNED NOT NULL, reg_no VARCHAR(30) NOT NULL, label VARCHAR(120) NULL, type VARCHAR(40) NULL, make VARCHAR(60) NULL, model VARCHAR(60) NULL, year_made SMALLINT UNSIGNED NULL, capacity VARCHAR(60) NULL, fuel_type VARCHAR(30) NULL, odometer INT UNSIGNED NOT NULL DEFAULT 0, gps_device_id VARCHAR(80) NULL, last_lat DECIMAL(10,6) NULL, last_lng DECIMAL(10,6) NULL, last_seen TIMESTAMP NULL, status ENUM('active','maintenance','inactive') NOT NULL DEFAULT 'active', acquired_on DATE NULL, notes VARCHAR(500) NULL, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, UNIQUE KEY uq_vehicle_reg (farm_id, reg_no), KEY idx_vehicle_farm (farm_id), CONSTRAINT fk_vehicle_farm FOREIGN KEY (farm_id) REFERENCES farms(id) ON DELETE CASCADE ) ENGINE=InnoDB; CREATE TABLE IF NOT EXISTS fleet_drivers ( id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, farm_id INT UNSIGNED NOT NULL, name VARCHAR(120) NOT NULL, phone VARCHAR(30) NULL, license_no VARCHAR(60) NULL, license_expiry DATE NULL, employee_id INT UNSIGNED NULL, status ENUM('active','inactive') NOT NULL DEFAULT 'active', notes VARCHAR(500) NULL, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, KEY idx_driver_farm (farm_id), CONSTRAINT fk_driver_farm FOREIGN KEY (farm_id) REFERENCES farms(id) ON DELETE CASCADE, CONSTRAINT fk_driver_emp FOREIGN KEY (employee_id) REFERENCES employees(id) ON DELETE SET NULL ) ENGINE=InnoDB; CREATE TABLE IF NOT EXISTS fleet_trips ( id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, farm_id INT UNSIGNED NOT NULL, vehicle_id INT UNSIGNED NOT NULL, driver_id INT UNSIGNED NULL, trip_date DATE NOT NULL, origin VARCHAR(120) NULL, destination VARCHAR(120) NULL, purpose VARCHAR(160) NULL, start_odometer INT UNSIGNED NULL, end_odometer INT UNSIGNED NULL, distance_km DECIMAL(10,1) NULL, cost DECIMAL(12,2) NOT NULL DEFAULT 0, customer VARCHAR(120) NULL, status ENUM('planned','completed','cancelled') NOT NULL DEFAULT 'completed', notes VARCHAR(500) NULL, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, KEY idx_trip_farm (farm_id, trip_date), KEY idx_trip_vehicle (vehicle_id), CONSTRAINT fk_trip_farm FOREIGN KEY (farm_id) REFERENCES farms(id) ON DELETE CASCADE, CONSTRAINT fk_trip_vehicle FOREIGN KEY (vehicle_id) REFERENCES vehicles(id) ON DELETE CASCADE, CONSTRAINT fk_trip_driver FOREIGN KEY (driver_id) REFERENCES fleet_drivers(id) ON DELETE SET NULL ) ENGINE=InnoDB; CREATE TABLE IF NOT EXISTS fleet_fuel ( id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, farm_id INT UNSIGNED NOT NULL, vehicle_id INT UNSIGNED NOT NULL, driver_id INT UNSIGNED NULL, fuel_date DATE NOT NULL, litres DECIMAL(10,2) NOT NULL DEFAULT 0, unit_cost DECIMAL(12,2) NOT NULL DEFAULT 0, total_cost DECIMAL(12,2) NOT NULL DEFAULT 0, odometer INT UNSIGNED NULL, station VARCHAR(120) NULL, notes VARCHAR(500) NULL, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, KEY idx_fuel_farm (farm_id, fuel_date), KEY idx_fuel_vehicle (vehicle_id), CONSTRAINT fk_fuel_farm FOREIGN KEY (farm_id) REFERENCES farms(id) ON DELETE CASCADE, CONSTRAINT fk_fuel_vehicle FOREIGN KEY (vehicle_id) REFERENCES vehicles(id) ON DELETE CASCADE, CONSTRAINT fk_fuel_driver FOREIGN KEY (driver_id) REFERENCES fleet_drivers(id) ON DELETE SET NULL ) ENGINE=InnoDB; CREATE TABLE IF NOT EXISTS fleet_maintenance ( id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, farm_id INT UNSIGNED NOT NULL, vehicle_id INT UNSIGNED NOT NULL, service_date DATE NOT NULL, service_type VARCHAR(40) NULL, description VARCHAR(300) NULL, cost DECIMAL(12,2) NOT NULL DEFAULT 0, odometer INT UNSIGNED NULL, next_service_date DATE NULL, provider VARCHAR(120) NULL, status ENUM('scheduled','done') NOT NULL DEFAULT 'done', notes VARCHAR(500) NULL, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, KEY idx_maint_farm (farm_id, service_date), KEY idx_maint_vehicle (vehicle_id), KEY idx_maint_next (farm_id, next_service_date), CONSTRAINT fk_maint_farm FOREIGN KEY (farm_id) REFERENCES farms(id) ON DELETE CASCADE, CONSTRAINT fk_maint_vehicle FOREIGN KEY (vehicle_id) REFERENCES vehicles(id) ON DELETE CASCADE ) ENGINE=InnoDB; -- --------------------------------------------------------------------------- -- Procurement — purchase requests, RFQs, purchase orders, goods received (GRN) -- and goods issued (GIN). See migration-2026-07-procurement.sql. Reuses the -- suppliers directory. Standalone (no stock/ledger posting yet). 'procurement' -- is a normal toggleable farm module. -- --------------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS purchase_requests ( id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, farm_id INT UNSIGNED NOT NULL, ref_no VARCHAR(20) NOT NULL, request_date DATE NOT NULL, needed_by DATE NULL, requested_by VARCHAR(120) NULL, department VARCHAR(80) NULL, priority ENUM('low','normal','high') NOT NULL DEFAULT 'normal', status ENUM('draft','submitted','approved','rejected','ordered') NOT NULL DEFAULT 'draft', approver VARCHAR(120) NULL, approved_on DATE NULL, decision_note VARCHAR(300) NULL, notes VARCHAR(500) NULL, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, UNIQUE KEY uq_pr_ref (farm_id, ref_no), KEY idx_pr_farm (farm_id, request_date), CONSTRAINT fk_pr_farm FOREIGN KEY (farm_id) REFERENCES farms(id) ON DELETE CASCADE ) ENGINE=InnoDB; CREATE TABLE IF NOT EXISTS purchase_request_items ( id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, pr_id INT UNSIGNED NOT NULL, description VARCHAR(200) NOT NULL, qty DECIMAL(12,2) NOT NULL DEFAULT 0, unit VARCHAR(30) NULL, est_unit_cost DECIMAL(12,2) NOT NULL DEFAULT 0, KEY idx_pri_pr (pr_id), CONSTRAINT fk_pri_pr FOREIGN KEY (pr_id) REFERENCES purchase_requests(id) ON DELETE CASCADE ) ENGINE=InnoDB; CREATE TABLE IF NOT EXISTS rfqs ( id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, farm_id INT UNSIGNED NOT NULL, ref_no VARCHAR(20) NOT NULL, rfq_date DATE NOT NULL, supplier_id INT UNSIGNED NULL, status ENUM('draft','sent','quoted','closed') NOT NULL DEFAULT 'draft', valid_until DATE NULL, notes VARCHAR(500) NULL, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, UNIQUE KEY uq_rfq_ref (farm_id, ref_no), KEY idx_rfq_farm (farm_id, rfq_date), CONSTRAINT fk_rfq_farm FOREIGN KEY (farm_id) REFERENCES farms(id) ON DELETE CASCADE, CONSTRAINT fk_rfq_supplier FOREIGN KEY (supplier_id) REFERENCES suppliers(id) ON DELETE SET NULL ) ENGINE=InnoDB; CREATE TABLE IF NOT EXISTS rfq_items ( id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, rfq_id INT UNSIGNED NOT NULL, description VARCHAR(200) NOT NULL, qty DECIMAL(12,2) NOT NULL DEFAULT 0, unit VARCHAR(30) NULL, quoted_unit_price DECIMAL(12,2) NULL, KEY idx_rfqi_rfq (rfq_id), CONSTRAINT fk_rfqi_rfq FOREIGN KEY (rfq_id) REFERENCES rfqs(id) ON DELETE CASCADE ) ENGINE=InnoDB; CREATE TABLE IF NOT EXISTS purchase_orders ( id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, farm_id INT UNSIGNED NOT NULL, ref_no VARCHAR(20) NOT NULL, supplier_id INT UNSIGNED NULL, pr_id INT UNSIGNED NULL, order_date DATE NOT NULL, expected_date DATE NULL, status ENUM('draft','approved','sent','partial','received','cancelled') NOT NULL DEFAULT 'draft', currency CHAR(3) NOT NULL DEFAULT 'UGX', amount DECIMAL(14,2) NOT NULL DEFAULT 0, approver VARCHAR(120) NULL, approved_on DATE NULL, received_on DATE NULL, notes VARCHAR(500) NULL, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, UNIQUE KEY uq_po_ref (farm_id, ref_no), KEY idx_po_farm (farm_id, order_date), KEY idx_po_supplier (supplier_id), CONSTRAINT fk_po_farm FOREIGN KEY (farm_id) REFERENCES farms(id) ON DELETE CASCADE, CONSTRAINT fk_po_supplier FOREIGN KEY (supplier_id) REFERENCES suppliers(id) ON DELETE SET NULL, CONSTRAINT fk_po_pr FOREIGN KEY (pr_id) REFERENCES purchase_requests(id) ON DELETE SET NULL ) ENGINE=InnoDB; CREATE TABLE IF NOT EXISTS purchase_order_items ( id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, po_id INT UNSIGNED NOT NULL, description VARCHAR(200) NOT NULL, qty DECIMAL(12,2) NOT NULL DEFAULT 0, unit VARCHAR(30) NULL, stock_type ENUM('feed','inventory') NULL, stock_ref VARCHAR(120) NULL, unit_price DECIMAL(12,2) NOT NULL DEFAULT 0, line_total DECIMAL(14,2) NOT NULL DEFAULT 0, qty_received DECIMAL(12,2) NOT NULL DEFAULT 0, KEY idx_poi_po (po_id), CONSTRAINT fk_poi_po FOREIGN KEY (po_id) REFERENCES purchase_orders(id) ON DELETE CASCADE ) ENGINE=InnoDB; CREATE TABLE IF NOT EXISTS goods_received ( id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, farm_id INT UNSIGNED NOT NULL, ref_no VARCHAR(20) NOT NULL, po_id INT UNSIGNED NULL, supplier_id INT UNSIGNED NULL, received_date DATE NOT NULL, received_by VARCHAR(120) NULL, rating TINYINT UNSIGNED NULL, status ENUM('partial','complete') NOT NULL DEFAULT 'complete', notes VARCHAR(500) NULL, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, UNIQUE KEY uq_grn_ref (farm_id, ref_no), KEY idx_grn_farm (farm_id, received_date), KEY idx_grn_supplier (supplier_id), CONSTRAINT fk_grn_farm FOREIGN KEY (farm_id) REFERENCES farms(id) ON DELETE CASCADE, CONSTRAINT fk_grn_po FOREIGN KEY (po_id) REFERENCES purchase_orders(id) ON DELETE SET NULL, CONSTRAINT fk_grn_supplier FOREIGN KEY (supplier_id) REFERENCES suppliers(id) ON DELETE SET NULL ) ENGINE=InnoDB; CREATE TABLE IF NOT EXISTS goods_received_items ( id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, grn_id INT UNSIGNED NOT NULL, po_item_id INT UNSIGNED NULL, description VARCHAR(200) NOT NULL, qty DECIMAL(12,2) NOT NULL DEFAULT 0, unit VARCHAR(30) NULL, KEY idx_grni_grn (grn_id), CONSTRAINT fk_grni_grn FOREIGN KEY (grn_id) REFERENCES goods_received(id) ON DELETE CASCADE, CONSTRAINT fk_grni_poi FOREIGN KEY (po_item_id) REFERENCES purchase_order_items(id) ON DELETE SET NULL ) ENGINE=InnoDB; CREATE TABLE IF NOT EXISTS goods_issued ( id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, farm_id INT UNSIGNED NOT NULL, ref_no VARCHAR(20) NOT NULL, issue_date DATE NOT NULL, issued_to VARCHAR(120) NULL, department VARCHAR(80) NULL, purpose VARCHAR(200) NULL, notes VARCHAR(500) NULL, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, UNIQUE KEY uq_gin_ref (farm_id, ref_no), KEY idx_gin_farm (farm_id, issue_date), CONSTRAINT fk_gin_farm FOREIGN KEY (farm_id) REFERENCES farms(id) ON DELETE CASCADE ) ENGINE=InnoDB; CREATE TABLE IF NOT EXISTS goods_issued_items ( id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, gin_id INT UNSIGNED NOT NULL, description VARCHAR(200) NOT NULL, qty DECIMAL(12,2) NOT NULL DEFAULT 0, unit VARCHAR(30) NULL, KEY idx_gini_gin (gin_id), CONSTRAINT fk_gini_gin FOREIGN KEY (gin_id) REFERENCES goods_issued(id) ON DELETE CASCADE ) ENGINE=InnoDB;