Database Schema
📋 purchases
id - Primary Key (UUID)
qbo_purchase_id - QuickBooks purchase ID
purchase_order_number - PO number
vendor_name - Supplier name
purchase_date - Date of purchase
item_description - What was purchased
item_category - Material type/category
quantity_ordered - Total qty ordered
unit_cost - Cost per unit
total_cost - Total purchase cost
unit_of_measure - ea, ft, lb, etc.
status - pending/received/partial
import_source - knowify/qbo_direct
qbo_last_modified - QBO last modified timestamp
created_at - Timestamp
updated_at - Timestamp
🔍 pending_imports
id - Primary Key (UUID)
qbo_purchase_id - QuickBooks purchase ID
purchase_data - JSON blob of purchase details
confidence_score - 0-100 (how likely it's inventory)
flag_reasons - JSON array of why it was flagged
status - pending/approved/rejected
reviewed_by_user_id - Foreign Key → users(id)
reviewed_at - Timestamp
created_at - Timestamp
⚙️ sync_log
id - Primary Key (UUID)
sync_type - scheduled/manual
started_at - When sync began
completed_at - When sync finished
status - success/failed/partial
purchases_found - Count of purchases retrieved
purchases_flagged - Count auto-flagged for review
error_message - If failed, why
api_calls_used - QBO API call count
created_at - Timestamp
📦 inventory_items
id - Primary Key (UUID)
purchase_id - Foreign Key → purchases(id)
qr_code - Unique QR identifier
item_description - Material description
quantity_received - Qty received
quantity_remaining - Current stock
unit_cost - Cost per unit (from purchase)
received_date - When received
location - Yard location/bin
status - available/depleted/reserved
created_at - Timestamp
updated_at - Timestamp
🔄 pull_requests
id - Primary Key (UUID)
inventory_item_id - Foreign Key → inventory_items(id)
requested_by_user_id - Foreign Key → users(id)
job_name - Job identifier
job_code - Optional job code
quantity_requested - Qty to pull
status - pending/approved/rejected
request_date - When requested
notes - Additional info
reviewed_by_user_id - Foreign Key → users(id)
review_date - When reviewed
review_notes - Admin comments
created_at - Timestamp
updated_at - Timestamp
✅ approved_pulls
id - Primary Key (UUID)
pull_request_id - Foreign Key → pull_requests(id)
inventory_item_id - Foreign Key → inventory_items(id)
purchase_id - Foreign Key → purchases(id) (for FIFO)
job_name - Job name
quantity_pulled - Actual qty pulled
unit_cost - Cost allocated (FIFO)
total_cost - Total job cost
pull_date - When pulled
zapier_sent - Boolean (sent to Zapier?)
zapier_sent_date - When sent
knowify_status - success/pending/failed
created_at - Timestamp
updated_at - Timestamp
👥 users
id - Primary Key (UUID)
email - User email
password_hash - Encrypted password
first_name - First name
last_name - Last name
role - admin/worker/receiver
is_active - Boolean
created_at - Timestamp
updated_at - Timestamp
💼 jobs
id - Primary Key (UUID)
job_name - Job identifier
job_code - Optional code
client_name - Client
status - active/completed/on-hold
start_date - Job start
end_date - Job completion
created_at - Timestamp
updated_at - Timestamp
💡 FIFO Implementation Note
When a pull is approved, the system will:
- Query inventory_items for the scanned QR code
- Find the oldest purchase (earliest received_date) with available quantity
- Allocate cost from that purchase to the approved_pull
- If quantity needed exceeds oldest batch, move to next oldest
- Update quantity_remaining on each inventory_item used