Database Schema

47 tables.

alembic_version 1 cols Other
Column Type Nullable Default
version_num VARCHAR(32) NOT NULL
baskets 10 cols Storage
Column Type Nullable Default
created_at TIMESTAMP NOT NULL
updated_at TIMESTAMP NOT NULL
id INTEGER NOT NULL nextval('baskets_id_seq'::regclass)
rack_id → racks INTEGER NOT NULL
name VARCHAR(150) NOT NULL
remarks TEXT yes
qr_code_string VARCHAR(200) yes
qr_image_path VARCHAR(255) yes
is_active BOOLEAN NOT NULL true
code VARCHAR(50) yes
bins 13 cols Other
Column Type Nullable Default
id INTEGER NOT NULL nextval('bins_id_seq'::regclass)
name VARCHAR(100) NOT NULL
shelf_id → shelves INTEGER yes
is_active BOOLEAN NOT NULL true
created_at TIMESTAMP NOT NULL now()
updated_at TIMESTAMP NOT NULL now()
code VARCHAR(50) yes
remarks TEXT yes
category_id INTEGER yes
subcategory_id INTEGER yes
warehouse_id INTEGER yes
qr_code_string VARCHAR(200) yes
qr_image_path VARCHAR(300) yes
iadc_discovered_material_photos 7 cols Jobs
Column Type Nullable Default
id INTEGER NOT NULL nextval('iadc_discovered_material_photos_id_seq'::regclass)
material_id → iadc_discovered_materials INTEGER NOT NULL
path VARCHAR(255) NOT NULL
created_at TIMESTAMP NOT NULL now()
updated_at TIMESTAMP NOT NULL now()
image_cached BOOLEAN NOT NULL false
image_cache_path VARCHAR(300) yes
iadc_discovered_materials 19 cols Jobs
Column Type Nullable Default
id INTEGER NOT NULL nextval('iadc_discovered_materials_id_seq'::regclass)
vessel_id → vessels INTEGER NOT NULL
vessel_document_id → vessel_documents INTEGER yes
category VARCHAR(150) yes
subcategory VARCHAR(150) yes
name VARCHAR(200) NOT NULL
description TEXT yes
attributes JSON yes
remarks TEXT yes
verification_status VARCHAR(19) NOT NULL 'NOT_VERIFIED'::iadc_verification_status
is_active BOOLEAN NOT NULL true
created_at TIMESTAMP NOT NULL now()
updated_at TIMESTAMP NOT NULL now()
promoted_material_id → materials INTEGER yes
source_pages JSON yes
source_type VARCHAR(20) yes
raw_text TEXT yes
make VARCHAR(200) yes
model VARCHAR(200) yes
iadc_product_specs 12 cols Jobs
Column Type Nullable Default
id INTEGER NOT NULL nextval('iadc_product_specs_id_seq'::regclass)
iadc_material_id → iadc_discovered_materials INTEGER NOT NULL
spec_type VARCHAR(30) NOT NULL
key VARCHAR(200) NOT NULL
value TEXT NOT NULL
key_normalized VARCHAR(200) NOT NULL
value_normalized VARCHAR(500) yes
value_numeric DOUBLE PRECISION yes
unit VARCHAR(30) yes
created_at TIMESTAMP NOT NULL now()
source_page INTEGER yes
source_context TEXT yes
iadc_verification_details 20 cols Jobs
Column Type Nullable Default
id INTEGER NOT NULL nextval('iadc_verification_details_id_seq'::regclass)
job_id → jobs INTEGER NOT NULL
material_id → iadc_discovered_materials INTEGER NOT NULL
picker_id → pickers INTEGER yes
status VARCHAR(19) NOT NULL 'NOT_VERIFIED'::iadc_verification_status
notes TEXT yes
evidence_paths JSON yes
verified_at TIMESTAMP yes
created_at TIMESTAMP NOT NULL now()
updated_at TIMESTAMP NOT NULL now()
qr_code_string VARCHAR(120) yes
qr_image_path VARCHAR(255) yes
associated_at TIMESTAMP yes
associated_by_picker_id INTEGER yes
associated_by_picker_name VARCHAR(200) yes
part_name VARCHAR(200) yes
brand VARCHAR(120) yes
picker_location_note TEXT yes
condition VARCHAR(30) yes
ecommerce_photo_paths JSON yes
inquiries 15 cols Inquiries
Column Type Nullable Default
id INTEGER NOT NULL nextval('inquiries_id_seq'::regclass)
name VARCHAR(150) NOT NULL
email VARCHAR(200) NOT NULL
phone VARCHAR(30) yes
company VARCHAR(200) yes
product_name VARCHAR(200) yes
material_id → materials INTEGER yes
quantity INTEGER yes
message TEXT NOT NULL
status VARCHAR(30) NOT NULL 'NEW'::character varying
response_notes TEXT yes
responded_at TIMESTAMP yes
is_active BOOLEAN NOT NULL true
created_at TIMESTAMP NOT NULL now()
updated_at TIMESTAMP NOT NULL now()
jobs 24 cols Jobs
Column Type Nullable Default
id INTEGER NOT NULL nextval('jobs_id_seq'::regclass)
job_title VARCHAR(150) NOT NULL
description TEXT yes
job_type VARCHAR(25) NOT NULL
material_id → materials INTEGER yes
status VARCHAR(20) NOT NULL 'CREATED'::job_status
created_by INTEGER NOT NULL
created_at TIMESTAMP NOT NULL now()
assigned_at TIMESTAMP yes
updated_at TIMESTAMP NOT NULL now()
is_active BOOLEAN NOT NULL true
assigned_user_id INTEGER yes
assigned_user_type VARCHAR(20) yes
assigned_user_name VARCHAR(150) yes
vessel_id → vessels INTEGER yes
vessel_document_id → vessel_documents INTEGER yes
surveyor_id → surveyors INTEGER yes
surveyor_name VARCHAR(150) yes
job_started_at TIMESTAMP yes
prereq_completed_at TIMESTAMP yes
job_in_progress_at TIMESTAMP yes
job_completed_at TIMESTAMP yes
warehouse_id → warehouses INTEGER yes
location_id → locations INTEGER yes
location_categories 13 cols Locations
Column Type Nullable Default
id INTEGER NOT NULL nextval('location_categories_id_seq'::regclass)
name VARCHAR(150) NOT NULL
description TEXT yes
latitude DOUBLE PRECISION yes
longitude DOUBLE PRECISION yes
geofence TEXT yes
warehouse_id → warehouses INTEGER yes
is_active BOOLEAN NOT NULL true
created_at TIMESTAMP NOT NULL now()
updated_at TIMESTAMP NOT NULL now()
qr_code_string VARCHAR(200) yes
qr_image_path VARCHAR(255) yes
code VARCHAR(50) yes
location_category_images 7 cols Locations
Column Type Nullable Default
id INTEGER NOT NULL nextval('location_category_images_id_seq'::regclass)
category_id → location_categories INTEGER NOT NULL
path VARCHAR(255) NOT NULL
created_at TIMESTAMP NOT NULL now()
updated_at TIMESTAMP NOT NULL now()
image_cached BOOLEAN NOT NULL false
image_cache_path VARCHAR(300) yes
location_qr 7 cols QR Codes
Column Type Nullable Default
id INTEGER NOT NULL nextval('location_qr_id_seq'::regclass)
qr_code_string VARCHAR(120) NOT NULL
qr_image_path VARCHAR(255) yes
location_id → locations INTEGER yes
is_issued BOOLEAN NOT NULL true
is_active BOOLEAN NOT NULL true
created_at TIMESTAMP NOT NULL now()
location_subcategories 14 cols Locations
Column Type Nullable Default
id INTEGER NOT NULL nextval('location_subcategories_id_seq'::regclass)
category_id → location_categories INTEGER NOT NULL
name VARCHAR(150) NOT NULL
description TEXT yes
latitude DOUBLE PRECISION yes
longitude DOUBLE PRECISION yes
geofence TEXT yes
is_active BOOLEAN NOT NULL true
created_at TIMESTAMP NOT NULL now()
updated_at TIMESTAMP NOT NULL now()
remarks TEXT yes
qr_code_string VARCHAR(200) yes
qr_image_path VARCHAR(255) yes
code VARCHAR(50) yes
location_subcategory_images 7 cols Locations
Column Type Nullable Default
id INTEGER NOT NULL nextval('location_subcategory_images_id_seq'::regclass)
subcategory_id → location_subcategories INTEGER NOT NULL
path VARCHAR(255) NOT NULL
created_at TIMESTAMP NOT NULL now()
updated_at TIMESTAMP NOT NULL now()
image_cached BOOLEAN NOT NULL false
image_cache_path VARCHAR(300) yes
locations 6 cols Locations
Column Type Nullable Default
id INTEGER NOT NULL nextval('locations_id_seq'::regclass)
name VARCHAR(150) NOT NULL
parent_id → locations INTEGER yes
is_active BOOLEAN NOT NULL true
created_at TIMESTAMP NOT NULL now()
updated_at TIMESTAMP NOT NULL now()
material_categories 9 cols Materials
Column Type Nullable Default
id INTEGER NOT NULL nextval('material_categories_id_seq'::regclass)
name VARCHAR(120) NOT NULL
description VARCHAR(255) yes
is_active BOOLEAN NOT NULL true
created_at TIMESTAMP NOT NULL now()
updated_at TIMESTAMP NOT NULL now()
is_iadc BOOLEAN NOT NULL false
storefront_enabled BOOLEAN yes false
iadc_code VARCHAR(20) yes
material_images 15 cols Materials
Column Type Nullable Default
id INTEGER NOT NULL nextval('material_images_id_seq'::regclass)
material_id → materials INTEGER NOT NULL
path VARCHAR(255) NOT NULL
caption VARCHAR(200) yes
uploaded_by_user_id INTEGER yes
uploaded_by_scope VARCHAR(50) yes
pipeline_stage VARCHAR(50) yes
is_primary BOOLEAN NOT NULL false
sort_order INTEGER NOT NULL 0
created_at TIMESTAMP NOT NULL now()
updated_at TIMESTAMP NOT NULL now()
image_type VARCHAR(30) NOT NULL 'general'::character varying
publish_to_store BOOLEAN yes false
image_cached BOOLEAN NOT NULL false
image_cache_path VARCHAR(300) yes
material_location_history 21 cols Materials
Column Type Nullable Default
id INTEGER NOT NULL nextval('material_location_history_id_seq'::regclass)
material_id → materials INTEGER NOT NULL
from_warehouse_id → warehouses INTEGER yes
from_shelf_id → shelves INTEGER yes
from_rack_id → racks INTEGER yes
from_basket_id → baskets INTEGER yes
from_location_text VARCHAR(300) yes
to_warehouse_id → warehouses INTEGER yes
to_shelf_id → shelves INTEGER yes
to_rack_id → racks INTEGER yes
to_basket_id → baskets INTEGER yes
to_location_text VARCHAR(300) yes
movement_type VARCHAR(50) NOT NULL
moved_by_user_id INTEGER NOT NULL
moved_by_scope VARCHAR(50) NOT NULL
job_id → jobs INTEGER yes
notes TEXT yes
moved_at TIMESTAMP NOT NULL now()
created_at TIMESTAMP NOT NULL now()
from_bin_id INTEGER yes
to_bin_id INTEGER yes
material_pipeline_events 10 cols Materials
Column Type Nullable Default
id INTEGER NOT NULL nextval('material_pipeline_events_id_seq'::regclass)
material_id → materials INTEGER NOT NULL
from_status VARCHAR(50) yes
to_status VARCHAR(50) NOT NULL
action VARCHAR(100) NOT NULL
performed_by_user_id INTEGER NOT NULL
performed_by_scope VARCHAR(50) NOT NULL
notes TEXT yes
metadata_json JSON yes
created_at TIMESTAMP NOT NULL now()
material_qr 13 cols QR Codes
Column Type Nullable Default
id INTEGER NOT NULL nextval('material_qr_id_seq'::regclass)
qr_code_string VARCHAR(120) NOT NULL
qr_image_path VARCHAR(255) yes
material_id → materials INTEGER yes
is_issued BOOLEAN NOT NULL true
is_active BOOLEAN NOT NULL true
created_at TIMESTAMP NOT NULL now()
issued_by_user_id INTEGER yes
issued_by_scope VARCHAR(50) yes
issued_at TIMESTAMP NOT NULL now()
associated_at TIMESTAMP yes
associated_by_picker_id INTEGER yes
iadc_material_id INTEGER yes
material_qr_print_job_items 4 cols Other
Column Type Nullable Default
id INTEGER NOT NULL nextval('material_qr_print_job_items_id_seq'::regclass)
print_job_id → material_qr_print_jobs INTEGER NOT NULL
material_qr_id → material_qr INTEGER NOT NULL
position INTEGER NOT NULL 0
material_qr_print_jobs 7 cols Other
Column Type Nullable Default
id INTEGER NOT NULL nextval('material_qr_print_jobs_id_seq'::regclass)
pdf_path VARCHAR(500) NOT NULL
qr_count INTEGER NOT NULL 0
filter_criteria JSON yes
issued_by_user_id INTEGER yes
issued_by_scope VARCHAR(50) yes
created_at TIMESTAMP NOT NULL now()
material_segments 9 cols Other
Column Type Nullable Default
id INTEGER NOT NULL nextval('material_segments_id_seq'::regclass)
sub_category_id → material_sub_categories INTEGER NOT NULL
name VARCHAR(150) NOT NULL
iadc_code VARCHAR(20) yes
description TEXT yes
is_active BOOLEAN NOT NULL true
storefront_enabled BOOLEAN NOT NULL true
created_at TIMESTAMP NOT NULL now()
updated_at TIMESTAMP NOT NULL now()
material_spare_links 5 cols Other
Column Type Nullable Default
id INTEGER NOT NULL nextval('material_spare_links_id_seq'::regclass)
material_id → materials INTEGER NOT NULL
parent_material_id → materials INTEGER NOT NULL
link_type VARCHAR(30) NOT NULL 'alternate'::character varying
created_at TIMESTAMP yes now()
material_sub_categories 9 cols Other
Column Type Nullable Default
id INTEGER NOT NULL nextval('material_sub_categories_id_seq'::regclass)
category_id → material_categories INTEGER NOT NULL
name VARCHAR(150) NOT NULL
iadc_code VARCHAR(20) yes
description TEXT yes
is_active BOOLEAN NOT NULL true
storefront_enabled BOOLEAN NOT NULL true
created_at TIMESTAMP NOT NULL now()
updated_at TIMESTAMP NOT NULL now()
material_vessels 2 cols Materials
Column Type Nullable Default
material_id → materials INTEGER NOT NULL
vessel_id → vessels INTEGER NOT NULL
materials 47 cols Materials
Column Type Nullable Default
id INTEGER NOT NULL nextval('materials_id_seq'::regclass)
category_id → material_categories INTEGER NOT NULL
location_text VARCHAR(200) yes
part_name VARCHAR(150) NOT NULL
part_type VARCHAR(80) yes
description TEXT yes
image_path VARCHAR(300) yes
is_active BOOLEAN NOT NULL true
created_at TIMESTAMP NOT NULL now()
updated_at TIMESTAMP NOT NULL now()
brand VARCHAR(120) yes
iadc_discovered_material_id → iadc_discovered_materials INTEGER yes
source_vessel_id → vessels INTEGER yes
pipeline_status VARCHAR(19) NOT NULL 'DRAFT'::material_pipeline_status
ready_for_sale BOOLEAN NOT NULL false
is_published BOOLEAN NOT NULL false
is_sold BOOLEAN NOT NULL false
published_at TIMESTAMP yes
sold_at TIMESTAMP yes
estimated_value NUMERIC(12, 2) yes
asking_price NUMERIC(12, 2) yes
sold_price NUMERIC(12, 2) yes
currency VARCHAR(3) yes 'USD'::character varying
condition_grade VARCHAR(20) yes
condition_notes TEXT yes
warehouse_id → warehouses INTEGER yes
shelf_id → shelves INTEGER yes
rack_id → racks INTEGER yes
basket_id → baskets INTEGER yes
quantity INTEGER NOT NULL 1
unit VARCHAR(30) yes
is_iadc_sourced BOOLEAN NOT NULL false
iadc_section_id VARCHAR(20) yes
iadc_subcategory VARCHAR(100) yes
publish_flag VARCHAR(20) NOT NULL 'PUBLISH'::character varying
attributes JSON yes
documents JSON yes
label_image_path VARCHAR(300) yes
ai_status VARCHAR(50) yes
source_type VARCHAR(50) yes
bin_id INTEGER yes
cross_reference_id VARCHAR(50) yes
ai_result_json JSONB yes
parent_material_id → materials INTEGER yes
sub_category_id → material_sub_categories INTEGER yes
segment_id → material_segments INTEGER yes
availability_override VARCHAR(20) yes
office_admins 11 cols Users
Column Type Nullable Default
id INTEGER NOT NULL nextval('office_admins_id_seq'::regclass)
user_id VARCHAR(50) NOT NULL
name VARCHAR(150) NOT NULL
photo VARCHAR(255) yes
designation VARCHAR(100) yes
employee_details JSON yes
login_id VARCHAR(100) NOT NULL
hashed_password VARCHAR(255) NOT NULL
is_active BOOLEAN NOT NULL true
created_at TIMESTAMP NOT NULL now()
updated_at TIMESTAMP NOT NULL now()
ops_log 16 cols Ops
Column Type Nullable Default
id INTEGER NOT NULL nextval('ops_log_id_seq'::regclass)
event_type VARCHAR(60) NOT NULL
node_type VARCHAR(30) yes
node_id INTEGER yes
node_name VARCHAR(200) yes
warehouse_id → warehouses INTEGER yes
material_id → materials INTEGER yes
material_name VARCHAR(200) yes
job_id → jobs INTEGER yes
user_id INTEGER yes
user_scope VARCHAR(30) yes
user_name VARCHAR(200) yes
summary VARCHAR(500) NOT NULL
details JSON yes
notes TEXT yes
created_at TIMESTAMP NOT NULL now()
pickers 11 cols Users
Column Type Nullable Default
id INTEGER NOT NULL nextval('pickers_id_seq'::regclass)
user_id VARCHAR(50) NOT NULL
name VARCHAR(150) NOT NULL
photo VARCHAR(255) yes
designation VARCHAR(100) yes
employee_details JSON yes
login_id VARCHAR(100) NOT NULL
hashed_password VARCHAR(255) NOT NULL
is_active BOOLEAN NOT NULL true
created_at TIMESTAMP NOT NULL now()
updated_at TIMESTAMP NOT NULL now()
racks 13 cols Storage
Column Type Nullable Default
created_at TIMESTAMP NOT NULL
updated_at TIMESTAMP NOT NULL
id INTEGER NOT NULL nextval('racks_id_seq'::regclass)
shelf_id → shelves INTEGER NOT NULL
name VARCHAR(150) NOT NULL
remarks TEXT yes
qr_code_string VARCHAR(200) yes
qr_image_path VARCHAR(255) yes
is_active BOOLEAN NOT NULL true
code VARCHAR(50) yes
category_id INTEGER yes
subcategory_id INTEGER yes
warehouse_id INTEGER yes
sales_alerts 11 cols Other
Column Type Nullable Default
id INTEGER NOT NULL nextval('sales_alerts_id_seq'::regclass)
type VARCHAR(40) NOT NULL
severity VARCHAR(20) NOT NULL 'info'::character varying
contact_id → sales_contacts INTEGER NOT NULL
lead_id → sales_leads INTEGER yes
message_id → whatsapp_messages INTEGER yes
title VARCHAR(200) NOT NULL
body TEXT yes
seen_at TIMESTAMP yes
dismissed_at TIMESTAMP yes
created_at TIMESTAMP NOT NULL now()
sales_contacts 13 cols Other
Column Type Nullable Default
id INTEGER NOT NULL nextval('sales_contacts_id_seq'::regclass)
phone VARCHAR(40) NOT NULL
display_name VARCHAR(200) yes
first_seen_at TIMESTAMP NOT NULL
last_seen_at TIMESTAMP NOT NULL
total_messages INTEGER NOT NULL 0
lifecycle_stage VARCHAR(30) NOT NULL 'new_contact'::character varying
tags JSONB yes
notes TEXT yes
linked_marinequip_customer_id INTEGER yes
assigned_to → sales_users INTEGER yes
created_at TIMESTAMP NOT NULL now()
updated_at TIMESTAMP NOT NULL now()
sales_leads 13 cols Other
Column Type Nullable Default
id INTEGER NOT NULL nextval('sales_leads_id_seq'::regclass)
contact_id → sales_contacts INTEGER NOT NULL
trigger_message_id → whatsapp_messages INTEGER yes
classification VARCHAR(30) NOT NULL
confidence_score DOUBLE PRECISION NOT NULL '0'::double precision
classifier_version VARCHAR(40) NOT NULL 'rules-v1'::character varying
classifier_reasoning TEXT yes
status VARCHAR(20) NOT NULL 'new'::character varying
assigned_to → sales_users INTEGER yes
product_interest JSONB yes
notes TEXT yes
created_at TIMESTAMP NOT NULL now()
updated_at TIMESTAMP NOT NULL now()
sales_users 8 cols Other
Column Type Nullable Default
id INTEGER NOT NULL nextval('sales_users_id_seq'::regclass)
login_id VARCHAR(100) NOT NULL
full_name VARCHAR(150) NOT NULL
email VARCHAR(200) yes
hashed_password VARCHAR(255) NOT NULL
is_active BOOLEAN NOT NULL true
created_at TIMESTAMP NOT NULL now()
updated_at TIMESTAMP NOT NULL now()
shelves 11 cols Storage
Column Type Nullable Default
created_at TIMESTAMP NOT NULL
updated_at TIMESTAMP NOT NULL
id INTEGER NOT NULL nextval('shelves_id_seq'::regclass)
subcategory_id → location_subcategories INTEGER NOT NULL
name VARCHAR(150) NOT NULL
remarks TEXT yes
qr_code_string VARCHAR(200) yes
qr_image_path VARCHAR(255) yes
is_active BOOLEAN NOT NULL true
code VARCHAR(50) yes
rack_id INTEGER yes
surveyors 11 cols Users
Column Type Nullable Default
id INTEGER NOT NULL nextval('surveyors_id_seq'::regclass)
user_id VARCHAR(50) NOT NULL
name VARCHAR(150) NOT NULL
photo VARCHAR(255) yes
designation VARCHAR(100) yes
employee_details JSON yes
login_id VARCHAR(100) NOT NULL
hashed_password VARCHAR(255) NOT NULL
is_active BOOLEAN NOT NULL true
created_at TIMESTAMP NOT NULL now()
updated_at TIMESTAMP NOT NULL now()
system_admins 10 cols Users
Column Type Nullable Default
id INTEGER NOT NULL nextval('system_admins_id_seq'::regclass)
employee_id VARCHAR(50) NOT NULL
first_name VARCHAR(100) NOT NULL
last_name VARCHAR(100) NOT NULL
role VARCHAR(50) NOT NULL 'system_admin'::character varying
hashed_password VARCHAR(255) NOT NULL
mobile_number VARCHAR(20) yes
is_active BOOLEAN NOT NULL true
created_at TIMESTAMP NOT NULL now()
updated_at TIMESTAMP NOT NULL now()
vessel_documents 7 cols Vessels
Column Type Nullable Default
id INTEGER NOT NULL nextval('vessel_documents_id_seq'::regclass)
vessel_id → vessels INTEGER NOT NULL
document_type VARCHAR(20) NOT NULL
title VARCHAR(150) NOT NULL
file_path VARCHAR(300) NOT NULL
uploaded_by INTEGER yes
created_at TIMESTAMP NOT NULL
vessel_images 6 cols Other
Column Type Nullable Default
id INTEGER NOT NULL nextval('vessel_images_id_seq'::regclass)
vessel_id → vessels INTEGER NOT NULL
path VARCHAR(500) NOT NULL
created_at TIMESTAMP NOT NULL now()
image_cached BOOLEAN NOT NULL false
image_cache_path VARCHAR(300) yes
vessel_mappings 6 cols Vessels
Column Type Nullable Default
id INTEGER NOT NULL nextval('vessel_mappings_id_seq'::regclass)
created_at TIMESTAMP NOT NULL now()
updated_at TIMESTAMP NOT NULL now()
vessel_id → vessels INTEGER NOT NULL
warehouse_id → warehouses INTEGER yes
location_category_id → location_categories INTEGER yes
vessels 11 cols Vessels
Column Type Nullable Default
id INTEGER NOT NULL nextval('vessels_id_seq'::regclass)
name VARCHAR(150) NOT NULL
code VARCHAR(50) yes
country VARCHAR(80) yes
current_location VARCHAR(150) yes
created_at TIMESTAMP NOT NULL
updated_at TIMESTAMP NOT NULL
is_active BOOLEAN NOT NULL true
skipped_categories TEXT yes
company VARCHAR(200) yes
is_recycled BOOLEAN NOT NULL false
warehouse_images 7 cols Warehouses
Column Type Nullable Default
id INTEGER NOT NULL nextval('warehouse_images_id_seq'::regclass)
warehouse_id → warehouses INTEGER NOT NULL
path VARCHAR(255) NOT NULL
created_at TIMESTAMP NOT NULL now()
updated_at TIMESTAMP NOT NULL now()
image_cached BOOLEAN NOT NULL false
image_cache_path VARCHAR(300) yes
warehouse_maintenance_items 19 cols Other
Column Type Nullable Default
id INTEGER NOT NULL nextval('warehouse_maintenance_items_id_seq'::regclass)
job_id → jobs INTEGER NOT NULL
warehouse_id → warehouses INTEGER yes
location_node_type VARCHAR(30) NOT NULL
location_node_id INTEGER NOT NULL
location_node_name VARCHAR(200) NOT NULL
qr_code VARCHAR(200) NOT NULL
qr_image_path VARCHAR(255) yes
location_status VARCHAR(8) NOT NULL 'UNMARKED'::location_marking_status
description TEXT yes
location_line_1 VARCHAR(255) yes
location_line_2 VARCHAR(255) yes
evidence_paths JSON yes
marked_by_user_id INTEGER yes
marked_by_user_name VARCHAR(150) yes
marked_at TIMESTAMP yes
is_active BOOLEAN NOT NULL true
created_at TIMESTAMP NOT NULL now()
updated_at TIMESTAMP NOT NULL now()
warehouse_managers 11 cols Users
Column Type Nullable Default
id INTEGER NOT NULL nextval('warehouse_managers_id_seq'::regclass)
user_id VARCHAR(50) NOT NULL
name VARCHAR(150) NOT NULL
photo VARCHAR(255) yes
designation VARCHAR(100) yes
employee_details JSON yes
login_id VARCHAR(100) NOT NULL
hashed_password VARCHAR(255) NOT NULL
is_active BOOLEAN NOT NULL true
created_at TIMESTAMP NOT NULL now()
updated_at TIMESTAMP NOT NULL now()
warehouses 15 cols Warehouses
Column Type Nullable Default
id INTEGER NOT NULL nextval('warehouses_id_seq'::regclass)
name VARCHAR(150) NOT NULL
code VARCHAR(50) yes
description TEXT yes
location_id → locations INTEGER yes
is_active BOOLEAN NOT NULL true
created_at TIMESTAMP NOT NULL now()
updated_at TIMESTAMP NOT NULL now()
latitude DOUBLE PRECISION yes
longitude DOUBLE PRECISION yes
geofence TEXT yes
qr_signature_path VARCHAR(255) yes
qr_code_string VARCHAR(200) yes
qr_image_path VARCHAR(255) yes
warehouse_type VARCHAR(50) yes
whatsapp_messages 14 cols Other
Column Type Nullable Default
id INTEGER NOT NULL nextval('whatsapp_messages_id_seq'::regclass)
wa_message_id VARCHAR(200) NOT NULL
chat_id VARCHAR(200) NOT NULL
sender_phone VARCHAR(40) NOT NULL
sender_name VARCHAR(200) yes
text TEXT yes
media_type VARCHAR(30) yes
media_url VARCHAR(500) yes
quoted_message_id VARCHAR(200) yes
received_at TIMESTAMP NOT NULL
raw_json JSONB yes
processed_at TIMESTAMP yes
processing_status VARCHAR(20) NOT NULL 'pending'::character varying
created_at TIMESTAMP NOT NULL now()