Warehouse
Key
warehouse_id    
uuid
NN
Key
web_app_customer    
uuid
NN
warehouse_dba    
varchar
warehouse_name_listed    
varchar
NN
warehouse_location    
varchar
NN
online_inventory_website    
varchar
online_inventory_username    
varchar
online_inventory_password    
varchar
qbs_warehouse_location_id    
varchar
address1    
varchar
NN
address2    
varchar
city    
varchar
NN
state    
varchar
country    
integer
NN
zipcode    
varchar
NN
billing_address1    
varchar
billing_address2    
varchar
billing_address_city    
varchar
billing_address_state    
varchar
billing_address_country    
integer
billing_address_zipcode    
varchar
orders_email1    
character varying
orders_email2    
character varying
orders_fax1    
integer
orders_wechat1    
character varying
orders_whatsapp1    
character varying
orders_other1    
character varying
receiving_contact1    
varchar
receiving_contact2    
varchar
receiving_contact3    
varchar
receiving_email1    
varchar
receiving_email2    
varchar
receiving_email3    
varchar
receiving_fax1    
varchar
receiving_messaging1    
varchar
receiving_messaging2    
varchar
main_contact    
varchar
main_contact_phone    
integer
main_contact_phone_ext    
integer
main_contact_cell    
integer
main_contact_sms    
boolean
main_contact_email    
varchar
customer_service_phone    
integer
customer_service_phone_ext    
integer
allow_same_day_orders    
boolean
billable    
boolean
timezone    
integer(2)
NN
warehouse_open_time    
time
warehouse_close_time    
time
shipping_open_time    
time
shipping_close_time    
time
receiving_open_time    
time
receiving_close_time    
time
friday_shipping_open_time    
time
friday_shipping_closed_time    
time
friday_open_time    
time
friday_close_time    
time
open_sunday    
time
open_monday    
time
open_tuesday    
time
open_thursday    
time
open_friday    
time
open_saturday    
time
repack_capabilities    
varchar
dry_ice    
varchar
pallet_charge_a    
integer
pallet_charge_b    
integer
lumping_fee    
integer
ctr_unloading_fee    
integer
damage_control    
boolean
own_pallet_with_product    
varchar
pallet_exchange    
boolean
rate_sheet    
varchar
warehouse_holidays    
varchar
List of warehouses and Cold Storages
Customers
Key
customer_id    
uuid
NN
Key
web_app_customer    
uuid
NN
customer_name    
varchar(41)
NN
customer_dba    
varchar(41)
customer_name_listed_as    
varchar(41)
used_to_be_called    
varchar(41)
used_to_be_called_date_change    
date
salesman    
integer
quickbooks_cust_id    
varchar
erp_custom_id    
varchar
Key
customer_type_id    
integer
NN
order_frequency    
integer
order_frequency_days    
integer
avg_order_day    
varchar
buyer1_position    
varchar(50)
buyer1_firstname    
varchar(50)
buyer1_lastname    
varchar(50)
buyer1_email    
varchar(100)
buyer1_phone    
varchar(21)
buyer1_phone_ext    
varchar(10)
buyer1_phone_direct    
varchar(21)
buyer1_mobile    
varchar(21)
buyer1_mobile_sms    
boolean
buyer1_wechat    
varchar(50)
buyer1_whatsapp    
varchar(50)
buyer2_position    
varchar(50)
buyer2_firstname    
varchar(50)
buyer2_lastname    
varchar(50)
buyer2_email    
varchar(100)
buyer2_phone    
varchar(21)
buyer2_phone_ext    
varchar(10)
buyer2_phone_direct    
varchar(21)
buyer2_mobile    
varchar(21)
buyer2_mobile_sms    
boolean
buyer2_wechat    
varchar(50)
buyer2_whatsapp    
varchar(50)
buyer3_position    
varchar(50)
buyer3_firstname    
varchar(50)
buyer3_lastname    
varchar(50)
buyer3_email    
varchar(100)
buyer3_phone    
varchar(21)
buyer3_phone_ext    
varchar(10)
buyer3_phone_direct    
varchar(21)
buyer3_mobile    
varchar(21)
buyer3_mobile_sms    
boolean
buyer3_wechat    
varchar(50)
buyer3_whatsapp    
varchar(50)
buyer4_position    
varchar(50)
buyer4_firstname    
varchar(50)
buyer4_lastname    
varchar(50)
buyer4_email    
varchar(100)
buyer4_phone    
varchar(21)
buyer4_phone_ext    
varchar(10)
buyer4_phone_direct    
varchar(21)
buyer4_mobile    
varchar(21)
buyer4_mobile_sms    
boolean
buyer4_wechat    
varchar(50)
buyer4_whatsapp    
varchar(50)
buyer5_position    
varchar(50)
buyer5_firstname    
varchar(50)
buyer5_lastname    
varchar(50)
buyer5_email    
varchar(100)
buyer5_phone    
varchar(21)
buyer5_phone_ext    
varchar(10)
buyer5_phone_direct    
varchar(21)
buyer5_mobile    
varchar(21)
buyer5_mobile_sms    
boolean
buyer5_wechat    
varchar(50)
buyer5_whatsapp    
varchar(50)
address1    
varchar(60)
address2    
varchar(60)
city    
varchar(57)
state    
varchar(50)
country_id    
integer(3)
zipcode    
varchar(10)
billing_company_name    
varchar(60)
billing_address1    
varchar(60)
billing_address2    
varchar(60)
billing_city    
varchar(57)
billing_state    
varchar(50)
billing_country_id    
integer(3)
billing_zipcode    
varchar(8)
delivery1_label    
varchar(40)
delivery1_attn    
varchar(40)
delivery1_name    
varchar(40)
delivery1_address1    
varchar(60)
delivery1_address2    
varchar(60)
delivery1_address_city    
varchar(57)
delivery1_address_state    
varchar(50)
delivery1_address_country_id    
integer(3)
delivery1_address_zipcode    
varchar(8)
delivery1_address_phone    
varchar(21)
delivery1_address_email    
varchar(100)
delivery1_address_attach    
varchar
delivery1_address_website    
text
delivery1_address_instructions    
varchar(300)
delivery2_label    
varchar(40)
delivery2_attn    
varchar(40)
delivery2_name    
varchar(40)
delivery2_address1    
varchar(60)
delivery2_address2    
varchar(60)
delivery2_address_city    
varchar(57)
delivery2_address_state    
varchar(50)
delivery2_address_country_id    
integer(3)
delivery2_address_zipcode    
varchar(8)
delivery2_address_phone    
varchar(21)
delivery2_address_email    
varchar(100)
delivery2_address_attach    
varchar
delivery2_address_website    
text
delivery2_address_instructions    
varchar(300)
delivery3_label    
varchar(40)
delivery3_attn    
varchar(40)
delivery3_name    
varchar(40)
deliveru3_address1    
varchar(60)
delivery3_address2    
varchar(60)
delivery3_address_city    
varchar(57)
delivery3_address_state    
varchar(50)
delivery3_address_country_id    
integer(3)
delivery3_address_zipcode    
varchar(8)
delivery3_address_phone    
varchar(21)
delivery3_address_email    
varchar(100)
delivery3_address_attach    
varchar
delivery3_address_website    
text
delivery3_address_instructions    
varchar(300)
delivery4_label    
varchar(40)
delivery4_attn    
varchar(40)
delivery4_name    
varchar(40)
delivery4_address1    
varchar(60)
delivery4_address2    
varchar(60)
delivery4_address_city    
varchar(57)
delivery4_address_state    
varchar(50)
delivery4_address_country_id    
integer(3)
delivery4_address_zipcode    
integer(8)
delivery4_address_phone    
varchar(21)
delivery4_address_email    
varchar(100)
delivery4_address_attach    
varchar
delivery4_address_website    
text
delivery4_address_instructions    
varchar(300)
accounting1_contact_name    
varchar(50)
accounting1_contact_phone    
varchar(21)
accounting1_contact_phone_ext    
varchar(10)
accounting1_contact_email    
varchar(100)
accounting2_contact_name    
varchar(50)
accounting2_contact_phone    
varchar(21)
accounting2_contact_phone_ext    
varchar(10)
accounting2_contact_email    
varchar(100)
accounting3_contact_name    
varchar(50)
accounting3_contact_phone    
varchar(21)
accounting3_contact_phone_ext    
varchar(10)
accounting3_contact_email    
varchar(100)
send_invoice_mail_id    
boolean
send_invoice_email_id    
boolean
send_order_confirm    
boolean
order_confirm_by_email    
boolean
order_confirm_by_sms    
boolean
order_confirm_by_wechat    
boolean
order_confirm_by_whatsapp    
boolean
order_confirm_buyer    
boolean
bill_by_dba    
boolean
vendor_number    
varchar(25)
credit_insured    
boolean
credit_limit    
integer(15)
Key
payment_terms    
integer(2)
Key
payment_type    
integer(2)
average_pay_time    
integer(3)
current_balance_owed    
integer(18)
sales_order_default    
integer(1)
order_shipping_vendor_default_id    
integer(1)
order_shipping_collect_default    
integer(1)
email_sending_category_id    
integer(3)
Since ANY and ALL Sales data is listed under the Sales table, it is accessable via the Customer UUID and UUID of the Sale. Therefore, anything that is associated on a separate table will be JOINed together on the frontend. Routing seems hectic, but it makes sense database wise.
Orders - COME BACK TO! NEED MORE WORK!
Key
order_id    
uuid
NN
Key
web_app_customer    
uuid
NN
Key
customer_uuid    
uuid
NN
customer_billing_city    
uuid
NN
customer_po_number    
varchar(25)
NN
Key
salesman_entered_order    
integer
NN
Key
warehouse_id    
uuid
NN
Key
order_type_id    
integer(2)
NN
order_rush    
integer(1)
order_number    
varchar
NN
order_sent_on    
timestamptz
NN
Key
trucker_id    
uuid
Key
ship_term    
integer
order_first_created_date    
timestamptz
NN
customer_order_confirmation    
timestamptz
special_instructions_note    
varchar(300)
Key
inventory_product_id    
uuid
NN
inventory_lot_number    
varchar
NN
product_quantity    
integer
NN
This is the barebones table. It will utilize IDs to make it quick! ANYTHING that is listed in another table can be pulled up from just using the ID from another table and joining the details. Crazy! This means I don't add the following: customer billing city and state,
Salesman
Key
salesman_id    
integer
NN
Key
web_app_customer    
uuid
NN
salesman_username    
varchar
NN
salesman_password    
varchar
NN
Key
salesman_role_id    
integer
NN
salesman_abbrev    
varchar(3)
NN
salesman_work_phone    
varchar(21)
salesman_work_phone_ext    
varchar(10)
salesman_mobile    
varchar(21)
salesman_fax    
varchar(21)
salesman_email    
varchar(100)
NN
salesman_wechat    
varchar(50)
salesman_whatsapp    
varchar(50)
Web_App_Customer
Key
web_app_customer_id    
uuid
NN
This is the master list of the web app customers. Company IDs are assigned in here and those are the UUIDs
Order_Type
Key
order_type_id    
integer
NN
order_type_name    
varchar(30)
Vendors
Key
vendor_id    
uuid
NN
Key
web_app_customer    
uuid
NN
Roles
Key
role_id    
integer
NN
role_name    
varchar(30)
NN
role_permission    
integer
NN
Customer_Type
Key
customer_type_id    
integer
NN
customer_type_name    
varchar
Key
sellable    
varchar
Think this will be a table, but not fully sure. Still processing and thinking things through. Same as Sellable table...
Sellable
Key
sellable_id    
integer
NN
sellable_type    
varchar(50)
More thought needs to go into this! Currently added, but more thought out is needed!
Payment_terms
Key
payment_terms_id    
integer
NN
payment_days    
integer
NN
payment_name    
integer
NN
Need to think this one through more as well!!
Payment_type
Key
payment_type_id    
integer
NN
payment_type_name    
varchar(50)
NN
Ship_terms
Key
ship_terms_id    
integer
NN
ship_terms_name    
varchar(20)
NN
simply lists it on the order. Not for us, but for them. But, if Pre-Paid, it automatically makes it a custom Trucking PO we make.
Languages**
Key
languages_id    
uuid
NN
language_name    
varchar(30)
NN
abbrev    
varchar(3)
NN
abbrev_alias    
varchar(3)
NN
language_orientation    
varchar(3)
NN
Still need to figure out how to build this table to be able to use it. Might have to figure out how to list the area and the association. Will need to ask ChatGPT about this I feel... It will be for relabeling titles and stuff in the foreign language selected.
Inventory***
Key
inventory_id    
uuid
NN
Key
web_app_customer    
uuid
NN
Key
product_id    
uuid
NN
Key
purchase_order_id    
uuid
NN
Key
warehouse_id    
uuid
NN
warehouse_lot    
varchar
NN
product_quantity    
integer
on_special    
boolean
case_per pallet    
integer
case_ti    
integer
case_hi    
integer
list_price    
integer
NN
cost_price    
integer
NN
anniversary_date    
date
NN
comments    
text
Need Products made before actually adding this!
Products***
Key
products_id    
uuid
NN
Key
web_app_customer    
uuid
NN
product_sku    
varchar(40)
NN
quickbooks_product_id    
integer
product_size    
varchar
product_family    
varchar
NN
product_short_description    
varchar
NN
product_full_description    
varchar
pack_size    
varchar
product_origin_id    
integer
NN
upc_outer    
integer
upc_inner    
integer
partials_per_case    
integer
NN
case_net_weight    
integer
NN
case_gross_weight    
integer
NN
unit_of_measure    
varchar(4)
NN
Key
bill_by    
integer(2)
NN
Key
catch_method    
integer
NN
latin_name    
varchar
special_import_requirements    
varchar
dimensions    
varchar
average_lead_time    
integer
reorder_alert    
integer
special_import_requirements    
varchar
harvest_season_start1    
integer
harvest_season_end1    
integer
harvest_season_start2    
integer
harvest_season_end2    
integer
harvest_season_start3    
integer
harvest_season_end3    
integer
harvest_season_start4    
integer
harvest_season_end4    
integer
sell_season_start1    
integer
sell_season_end1    
integer
sell_season_start2    
integer
sell_season_end2    
integer
sell_season_holiday    
varchar
sell_season_weather1    
varchar
sell_season_weather2    
varchar
sell_season_weather3    
varchar
sell_season_weather4    
varchar
upc_outercase    
varchar
upc_innercase    
varchar
dimensions    
varchar
product_pic1    
text
product_pic2    
text
product_pic3    
text
product_pic4    
text
product_pic5    
text
product_pic6    
text
product_pic7    
text
product_pic8    
text
product_pic9    
text
product_pic10    
text
product_pic11    
text
product_pic12    
text
product_pic13    
text
product_pic14    
text
product_pic15    
text
product_spec_sheet1    
text
product_spec_sheet2    
text
product_spec_sheet3    
text
product_spec_sheet4    
text
product_spec_sheet5    
text
cooking_instructions1    
text
cooking_instructions2    
text
An idea with this is to be able to build a spec sheet if need be for web app customers instead of doing them by hand. A template will need to be made.
Company**
Key
company_id    
uuid
NN
company_name    
varchar(41)
NN
Company table, this is where users of a company dwell and thier roles. More thought is needed on this, but I am throwing this out there to spec out. Not sure if I need this table or not. Might need to combine it with "Web_App_Customer". Still need to understand the Salesman table. Issue I have is that there are "Salesmen, Managers, Admins, and Accountants." Salesman is easy, but what about the rest?? Do I combine 2 or 3 tables into 1??
Catch_method
Key
catch_method_id    
integer
NN
catch_method_name    
varchar
NN
Advanced_settings**
Key
advanced_settings_id    
uuid
NN
Key
web_app_customer    
uuid
NN
will be a work in progress...
Global_settings**
Key
id    
integer
NN
I think I need a global settings table to house customer settings. Advanced settings is for advanced settings that don't belong in standard settings like theme, langauage, time zone, ltr, etc. Global is the DEFAULT settings.
User_settings**
Key
id    
integer
NN
I do want settings for a per user basis. For example, some like a specific theme whereas some like darkmode. I want to be able to offer that to them. Not sure if languages would be for global for the customer or on a per user basis. The issue is how would it be a specific language? Like, global is English, but the user is Spanish. Would an admin or manager be able to add another language description to the product? More things to consider and think out!!
Bill_by
Key
bill_by_id    
integer
NN
bill_by_name    
varchar
NN
Purchase_orders
Key
purchase_order_id    
uuid
NN
Key
web_app_customer    
uuid
NN
vendor_id    
uuid
NN
pack_date    
date
NN
expiration_date    
date
tlc_code    
varchar(30)
NN
product_quantity    
integer
NN
Vendors
Key
vendor_id    
uuid
NN
Key
web_app_csutomer    
uuid
NN

Documentation for project Inventory 2
Inventory 2
Project
Name
Inventory 2
Description
Version 2 since version 1 was locked...
Diagram
Name
Main Diagram
Report
Generated
5/24/2024 | 4:48:46 PM
tables
Name
Warehouse
Description
List of warehouses and Cold Storages
Columns
Name
Data type
Param
Key
Not Null
warehouse_id
uuid
Yes
Yes
Description
*Thought is to use this for the Customer's ID in the system.
web_app_customer
uuid
Yes
warehouse_dba
varchar
warehouse_name_listed
varchar
Yes
warehouse_location
varchar
Yes
online_inventory_website
varchar
online_inventory_username
varchar
online_inventory_password
varchar
qbs_warehouse_location_id
varchar
address1
varchar
Yes
address2
varchar
city
varchar
Yes
state
varchar
country
integer
Yes
zipcode
varchar
Yes
billing_address1
varchar
billing_address2
varchar
billing_address_city
varchar
billing_address_state
varchar
billing_address_country
integer
billing_address_zipcode
varchar
orders_email1
character varying
orders_email2
character varying
orders_fax1
integer
orders_wechat1
character varying
orders_whatsapp1
character varying
orders_other1
character varying
receiving_contact1
varchar
receiving_contact2
varchar
receiving_contact3
varchar
receiving_email1
varchar
receiving_email2
varchar
receiving_email3
varchar
receiving_fax1
varchar
receiving_messaging1
varchar
receiving_messaging2
varchar
main_contact
varchar
main_contact_phone
integer
main_contact_phone_ext
integer
main_contact_cell
integer
main_contact_sms
boolean
main_contact_email
varchar
customer_service_phone
integer
customer_service_phone_ext
integer
allow_same_day_orders
boolean
Description
false = no, true = yes
billable
boolean
timezone
integer
2
Yes
warehouse_open_time
time
warehouse_close_time
time
shipping_open_time
time
shipping_close_time
time
receiving_open_time
time
receiving_close_time
time
friday_shipping_open_time
time
friday_shipping_closed_time
time
friday_open_time
time
friday_close_time
time
open_sunday
time
open_monday
time
open_tuesday
time
open_thursday
time
open_friday
time
open_saturday
time
repack_capabilities
varchar
dry_ice
varchar
pallet_charge_a
integer
pallet_charge_b
integer
lumping_fee
integer
ctr_unloading_fee
integer
damage_control
boolean
own_pallet_with_product
varchar
pallet_exchange
boolean
rate_sheet
varchar
warehouse_holidays
varchar
Keys
Warehouse_pkey
Column in Warehouse_pkey
warehouse_id
Name
Customers
Description
Since ANY and ALL Sales data is listed under the Sales table, it is accessable via the Customer UUID and UUID of the Sale. Therefore, anything that is associated on a separate table will be JOINed together on the frontend. Routing seems hectic, but it makes sense database wise.
Columns
Name
Data type
Param
Key
Not Null
customer_id
uuid
Yes
Yes
web_app_customer
uuid
Yes
Description
Might need to change the label of this, but for now it is listed as this.
customer_name
varchar
41
Yes
customer_dba
varchar
41
customer_name_listed_as
varchar
41
used_to_be_called
varchar
41
used_to_be_called_date_change
date
salesman
integer
quickbooks_cust_id
varchar
erp_custom_id
varchar
customer_type_id
integer
Yes
order_frequency
integer
order_frequency_days
integer
avg_order_day
varchar
buyer1_position
varchar
50
buyer1_firstname
varchar
50
buyer1_lastname
varchar
50
buyer1_email
varchar
100
buyer1_phone
varchar
21
buyer1_phone_ext
varchar
10
buyer1_phone_direct
varchar
21
buyer1_mobile
varchar
21
buyer1_mobile_sms
boolean
buyer1_wechat
varchar
50
buyer1_whatsapp
varchar
50
buyer2_position
varchar
50
buyer2_firstname
varchar
50
buyer2_lastname
varchar
50
buyer2_email
varchar
100
buyer2_phone
varchar
21
buyer2_phone_ext
varchar
10
buyer2_phone_direct
varchar
21
buyer2_mobile
varchar
21
buyer2_mobile_sms
boolean
buyer2_wechat
varchar
50
buyer2_whatsapp
varchar
50
buyer3_position
varchar
50
buyer3_firstname
varchar
50
buyer3_lastname
varchar
50
buyer3_email
varchar
100
buyer3_phone
varchar
21
buyer3_phone_ext
varchar
10
buyer3_phone_direct
varchar
21
buyer3_mobile
varchar
21
buyer3_mobile_sms
boolean
buyer3_wechat
varchar
50
buyer3_whatsapp
varchar
50
buyer4_position
varchar
50
buyer4_firstname
varchar
50
buyer4_lastname
varchar
50
buyer4_email
varchar
100
buyer4_phone
varchar
21
buyer4_phone_ext
varchar
10
buyer4_phone_direct
varchar
21
buyer4_mobile
varchar
21
buyer4_mobile_sms
boolean
Description
"true "or "false"
buyer4_wechat
varchar
50
buyer4_whatsapp
varchar
50
buyer5_position
varchar
50
buyer5_firstname
varchar
50
buyer5_lastname
varchar
50
buyer5_email
varchar
100
buyer5_phone
varchar
21
buyer5_phone_ext
varchar
10
buyer5_phone_direct
varchar
21
buyer5_mobile
varchar
21
buyer5_mobile_sms
boolean
buyer5_wechat
varchar
50
buyer5_whatsapp
varchar
50
address1
varchar
60
address2
varchar
60
city
varchar
57
state
varchar
50
country_id
integer
3
zipcode
varchar
10
billing_company_name
varchar
60
billing_address1
varchar
60
billing_address2
varchar
60
billing_city
varchar
57
billing_state
varchar
50
billing_country_id
integer
3
billing_zipcode
varchar
8
delivery1_label
varchar
40
delivery1_attn
varchar
40
delivery1_name
varchar
40
delivery1_address1
varchar
60
delivery1_address2
varchar
60
delivery1_address_city
varchar
57
delivery1_address_state
varchar
50
delivery1_address_country_id
integer
3
delivery1_address_zipcode
varchar
8
delivery1_address_phone
varchar
21
delivery1_address_email
varchar
100
delivery1_address_attach
varchar
delivery1_address_website
text
delivery1_address_instructions
varchar
300
delivery2_label
varchar
40
delivery2_attn
varchar
40
delivery2_name
varchar
40
delivery2_address1
varchar
60
delivery2_address2
varchar
60
delivery2_address_city
varchar
57
delivery2_address_state
varchar
50
delivery2_address_country_id
integer
3
delivery2_address_zipcode
varchar
8
delivery2_address_phone
varchar
21
delivery2_address_email
varchar
100
delivery2_address_attach
varchar
delivery2_address_website
text
delivery2_address_instructions
varchar
300
delivery3_label
varchar
40
delivery3_attn
varchar
40
delivery3_name
varchar
40
deliveru3_address1
varchar
60
delivery3_address2
varchar
60
delivery3_address_city
varchar
57
delivery3_address_state
varchar
50
delivery3_address_country_id
integer
3
delivery3_address_zipcode
varchar
8
delivery3_address_phone
varchar
21
delivery3_address_email
varchar
100
delivery3_address_attach
varchar
delivery3_address_website
text
delivery3_address_instructions
varchar
300
delivery4_label
varchar
40
delivery4_attn
varchar
40
delivery4_name
varchar
40
delivery4_address1
varchar
60
delivery4_address2
varchar
60
delivery4_address_city
varchar
57
delivery4_address_state
varchar
50
delivery4_address_country_id
integer
3
delivery4_address_zipcode
integer
8
delivery4_address_phone
varchar
21
delivery4_address_email
varchar
100
delivery4_address_attach
varchar
delivery4_address_website
text
delivery4_address_instructions
varchar
300
accounting1_contact_name
varchar
50
accounting1_contact_phone
varchar
21
accounting1_contact_phone_ext
varchar
10
accounting1_contact_email
varchar
100
accounting2_contact_name
varchar
50
accounting2_contact_phone
varchar
21
accounting2_contact_phone_ext
varchar
10
accounting2_contact_email
varchar
100
accounting3_contact_name
varchar
50
accounting3_contact_phone
varchar
21
accounting3_contact_phone_ext
varchar
10
accounting3_contact_email
varchar
100
send_invoice_mail_id
boolean
send_invoice_email_id
boolean
send_order_confirm
boolean
order_confirm_by_email
boolean
order_confirm_by_sms
boolean
order_confirm_by_wechat
boolean
order_confirm_by_whatsapp
boolean
order_confirm_buyer
boolean
bill_by_dba
boolean
vendor_number
varchar
25
credit_insured
boolean
credit_limit
integer
15
payment_terms
integer
2
payment_type
integer
2
average_pay_time
integer
3
current_balance_owed
integer
18
sales_order_default
integer
1
order_shipping_vendor_default_id
integer
1
order_shipping_collect_default
integer
1
email_sending_category_id
integer
3
Keys
Customers_pkey
Column in Customers_pkey
customer_id
Customers_key
Name
Orders - COME BACK TO! NEED MORE WORK!
Description
This is the barebones table. It will utilize IDs to make it quick! ANYTHING that is listed in another table can be pulled up from just using the ID from another table and joining the details. Crazy! This means I don't add the following: customer billing city and state,
Columns
Name
Data type
Param
Key
Not Null
order_id
uuid
Yes
Yes
web_app_customer
uuid
Yes
customer_uuid
uuid
Yes
customer_billing_city
uuid
Yes
customer_po_number
varchar
25
Yes
salesman_entered_order
integer
Yes
warehouse_id
uuid
Yes
order_type_id
integer
2
Yes
order_rush
integer
1
order_number
varchar
Yes
order_sent_on
timestamptz
Yes
Description
same as order date. This is when it was actually sent in.
trucker_id
uuid
Description
this is the vendor ID, but specifically vendors that are SHIPPERS
ship_term
integer
Description
need to see how to grab defaults from a specific customer and add it into here...
order_first_created_date
timestamptz
Yes
customer_order_confirmation
timestamptz
special_instructions_note
varchar
300
inventory_product_id
uuid
Yes
Description
Utilizing JOIN to pull in the Lot#'s for this to be shown. Inventory Products do not ever get deleted*. This is for traceability and record keeping. Maybe we can have a calculated "vault" storage plan for storing records after a certain period of time. Company policy is delete after (need to set a period of time). The initial is difficult since we are currently BUILDING storage and we don't know what the projected calculated storage will be. *Maybe utilizing Citrus Extension for SHARDING the db will allow us to horizontally expand the db since we will be storing lots of data. **Just some thoughts..
inventory_lot_number
varchar
Yes
product_quantity
integer
Yes
Keys
customers_uuid
Column in customers_uuid
order_id
Name
Salesman
Columns
Name
Data type
Param
Key
Not Null
salesman_id
integer
Yes
Yes
web_app_customer
uuid
Yes
salesman_username
varchar
Yes
salesman_password
varchar
Yes
salesman_role_id
integer
Yes
salesman_abbrev
varchar
3
Yes
salesman_work_phone
varchar
21
salesman_work_phone_ext
varchar
10
salesman_mobile
varchar
21
salesman_fax
varchar
21
salesman_email
varchar
100
Yes
salesman_wechat
varchar
50
salesman_whatsapp
varchar
50
Keys
Salesman_pkey
Column in Salesman_pkey
salesman_id
Name
Order_Type
Columns
Name
Data type
Param
Key
Not Null
order_type_id
integer
Yes
Yes
order_type_name
varchar
30
Keys
Order_Type_pkey
Column in Order_Type_pkey
order_type_id
Name
Vendors
Columns
Name
Data type
Param
Key
Not Null
vendor_id
uuid
Yes
Yes
web_app_customer
uuid
Yes
Keys
Vendors_pkey
Column in Vendors_pkey
vendor_id
Name
Roles
Columns
Name
Data type
Param
Key
Not Null
role_id
integer
Yes
Yes
role_name
varchar
30
Yes
role_permission
integer
Yes
Description
need to see how to give or add more permissions to a specific role.... Or if integer is the right value
Keys
Roles_pkey
Column in Roles_pkey
role_id
Name
Customer_Type
Description
Think this will be a table, but not fully sure. Still processing and thinking things through. Same as Sellable table...
Columns
Name
Data type
Param
Key
Not Null
customer_type_id
integer
Yes
Yes
customer_type_name
varchar
sellable
varchar
Keys
Customer_Type_pkey
Column in Customer_Type_pkey
customer_type_id
Name
Sellable
Description
More thought needs to go into this! Currently added, but more thought out is needed!
Columns
Name
Data type
Param
Key
Not Null
sellable_id
integer
Yes
Yes
sellable_type
varchar
50
Keys
Sellable_pkey
Column in Sellable_pkey
sellable_id
Name
Payment_terms
Description
Need to think this one through more as well!!
Columns
Name
Data type
Param
Key
Not Null
payment_terms_id
integer
Yes
Yes
payment_days
integer
Yes
payment_name
integer
Yes
Keys
Payment_terms_pkey
Column in Payment_terms_pkey
payment_terms_id
Name
Payment_type
Columns
Name
Data type
Param
Key
Not Null
payment_type_id
integer
Yes
Yes
payment_type_name
varchar
50
Yes
Keys
Payment_type_pkey
Column in Payment_type_pkey
payment_type_id
Name
Ship_terms
Description
simply lists it on the order. Not for us, but for them. But, if Pre-Paid, it automatically makes it a custom Trucking PO we make.
Columns
Name
Data type
Param
Key
Not Null
ship_terms_id
integer
Yes
Yes
ship_terms_name
varchar
20
Yes
Keys
Ship_terms_pkey
Column in Ship_terms_pkey
ship_terms_id
Name
Languages**
Description
Still need to figure out how to build this table to be able to use it. Might have to figure out how to list the area and the association. Will need to ask ChatGPT about this I feel... It will be for relabeling titles and stuff in the foreign language selected.
Columns
Name
Data type
Param
Key
Not Null
languages_id
uuid
Yes
Yes
language_name
varchar
30
Yes
abbrev
varchar
3
Yes
abbrev_alias
varchar
3
Yes
language_orientation
varchar
3
Yes
Keys
Languages**_pkey
Column in Languages**_pkey
languages_id
Name
Inventory***
Description
Need Products made before actually adding this!
Columns
Name
Data type
Param
Key
Not Null
inventory_id
uuid
Yes
Yes
web_app_customer
uuid
Yes
product_id
uuid
Yes
purchase_order_id
uuid
Yes
warehouse_id
uuid
Yes
warehouse_lot
varchar
Yes
product_quantity
integer
Description
This gets copied over from the PO, since you can do this in the DB. The PO has a set quantity, but you have to be able to deduct it and build orders by pulling the qty off the "inventory"
on_special
boolean
case_per pallet
integer
case_ti
integer
case_hi
integer
list_price
integer
Yes
cost_price
integer
Yes
anniversary_date
date
Yes
comments
text
Keys
Inventory***_pkey
Column in Inventory***_pkey
inventory_id
Name
Products***
Description
An idea with this is to be able to build a spec sheet if need be for web app customers instead of doing them by hand. A template will need to be made.
Columns
Name
Data type
Param
Key
Not Null
products_id
uuid
Yes
Yes
web_app_customer
uuid
Yes
product_sku
varchar
40
Yes
Description
This is an internal product id. We have a shortened version for our pricelist program, others use UPC codes, etc.
quickbooks_product_id
integer
product_size
varchar
product_family
varchar
Yes
product_short_description
varchar
Yes
Description
This is what will be on the header of the pricelist sections. Probably having an example for reference like a photo would be helpful.
product_full_description
varchar
Description
This field will be used in invoicing customers. Similar to qbs.
pack_size
varchar
product_origin_id
integer
Yes
upc_outer
integer
upc_inner
integer
partials_per_case
integer
Yes
case_net_weight
integer
Yes
case_gross_weight
integer
Yes
unit_of_measure
varchar
4
Yes
Description
bill by and unit of measure?? keep or get rid of?
bill_by
integer
2
Yes
Description
might need to make a separate table for this and use integers as IDs - ???
catch_method
integer
Yes
latin_name
varchar
special_import_requirements
varchar
dimensions
varchar
average_lead_time
integer
Description
how many days (divide by 7 to get weeks) are needed to produce the order
reorder_alert
integer
Description
expressed in days (weeks is divide by 7)
special_import_requirements
varchar
harvest_season_start1
integer
harvest_season_end1
integer
harvest_season_start2
integer
harvest_season_end2
integer
harvest_season_start3
integer
harvest_season_end3
integer
harvest_season_start4
integer
harvest_season_end4
integer
sell_season_start1
integer
sell_season_end1
integer
sell_season_start2
integer
sell_season_end2
integer
sell_season_holiday
varchar
sell_season_weather1
varchar
sell_season_weather2
varchar
sell_season_weather3
varchar
sell_season_weather4
varchar
upc_outercase
varchar
upc_innercase
varchar
dimensions
varchar
product_pic1
text
product_pic2
text
product_pic3
text
product_pic4
text
product_pic5
text
product_pic6
text
product_pic7
text
product_pic8
text
product_pic9
text
product_pic10
text
product_pic11
text
product_pic12
text
product_pic13
text
product_pic14
text
product_pic15
text
product_spec_sheet1
text
product_spec_sheet2
text
product_spec_sheet3
text
product_spec_sheet4
text
product_spec_sheet5
text
cooking_instructions1
text
cooking_instructions2
text
Keys
Products***_pkey
Column in Products***_pkey
products_id
Name
Company**
Description
Company table, this is where users of a company dwell and thier roles. More thought is needed on this, but I am throwing this out there to spec out. Not sure if I need this table or not. Might need to combine it with "Web_App_Customer". Still need to understand the Salesman table. Issue I have is that there are "Salesmen, Managers, Admins, and Accountants." Salesman is easy, but what about the rest?? Do I combine 2 or 3 tables into 1??
Columns
Name
Data type
Param
Key
Not Null
company_id
uuid
Yes
Yes
company_name
varchar
41
Yes
Keys
Company**_pkey
Column in Company**_pkey
company_id
Name
Catch_method
Columns
Name
Data type
Param
Key
Not Null
catch_method_id
integer
Yes
Yes
catch_method_name
varchar
Yes
Keys
Catch_method_pkey
Column in Catch_method_pkey
catch_method_id
Name
Advanced_settings**
Description
will be a work in progress...
Columns
Name
Data type
Param
Key
Not Null
advanced_settings_id
uuid
Yes
Yes
web_app_customer
uuid
Yes
Keys
Advanced_settings**_pkey
Column in Advanced_settings**_pkey
advanced_settings_id
Name
Global_settings**
Description
I think I need a global settings table to house customer settings. Advanced settings is for advanced settings that don't belong in standard settings like theme, langauage, time zone, ltr, etc. Global is the DEFAULT settings.
Columns
Name
Data type
Param
Key
Not Null
id
integer
Yes
Yes
Keys
Global_settings**_pkey
Column in Global_settings**_pkey
id
Name
User_settings**
Description
I do want settings for a per user basis. For example, some like a specific theme whereas some like darkmode. I want to be able to offer that to them. Not sure if languages would be for global for the customer or on a per user basis. The issue is how would it be a specific language? Like, global is English, but the user is Spanish. Would an admin or manager be able to add another language description to the product? More things to consider and think out!!
Columns
Name
Data type
Param
Key
Not Null
id
integer
Yes
Yes
Keys
User_settings**_pkey
Column in User_settings**_pkey
id
Name
Bill_by
Columns
Name
Data type
Param
Key
Not Null
bill_by_id
integer
Yes
Yes
bill_by_name
varchar
Yes
Keys
Bill_by_pkey
Column in Bill_by_pkey
bill_by_id
Name
Purchase_orders
Columns
Name
Data type
Param
Key
Not Null
purchase_order_id
uuid
Yes
Yes
web_app_customer
uuid
Yes
vendor_id
uuid
Yes
pack_date
date
Yes
expiration_date
date
tlc_code
varchar
30
Yes
Description
"traceability lot code" - FDA Requirement 2026/27
product_quantity
integer
Yes
Keys
Purchase_orders_pkey
Column in Purchase_orders_pkey
purchase_order_id
Name
Vendors
Columns
Name
Data type
Param
Key
Not Null
vendor_id
uuid
Yes
Yes
web_app_csutomer
uuid
Yes
Keys
Vendors_pkey1
Column in Vendors_pkey1
vendor_id
relationships
Name
Orders - COME BACK TO! NEED MORE WORK!_customer_uuid_fkey
Name
Source
Target
Cardinality type:
One to
many
Ordinality:
Mandatory
Mandatory
Key and columns
Key name
Column in Customers
Column in Orders - COME BACK TO! NEED MORE WORK!
Customers_pkey
customer_id
customer_uuid
Name
Customers_web_app_customer_fkey
Name
Source
Target
Cardinality type:
One to
many
Ordinality:
Mandatory
Mandatory
Key and columns
Key name
Column in Web_App_Customer
Column in Customers
Web_App_Customer_pkey
web_app_customer_id
web_app_customer
Name
Orders - COME BACK TO! NEED MORE WORK!_web_app_customer_fkey
Key and columns
Key name
Column in Web_App_Customer
Column in Orders - COME BACK TO! NEED MORE WORK!
Web_App_Customer_pkey
web_app_customer_id
web_app_customer
Name
Orders - COME BACK TO! NEED MORE W_salesman_entered_order_fkey
Name
Source
Target
Cardinality type:
One to
many
Ordinality:
Mandatory
Mandatory
Key and columns
Key name
Column in Salesman
Column in Orders - COME BACK TO! NEED MORE WORK!
Salesman_pkey
salesman_id
salesman_entered_order
Name
Warehouse_web_app_customer_fkey
Name
Source
Target
Cardinality type:
One to
many
Ordinality:
Mandatory
Mandatory
Key and columns
Key name
Column in Web_App_Customer
Column in Warehouse
Web_App_Customer_pkey
web_app_customer_id
web_app_customer
Name
Orders - COME BACK TO! NEED MORE WORK!_warehouse_id_fkey
Name
Source
Target
Cardinality type:
One to
many
Ordinality:
Mandatory
Mandatory
Key and columns
Key name
Column in Warehouse
Column in Orders - COME BACK TO! NEED MORE WORK!
Warehouse_pkey
warehouse_id
warehouse_id
Name
Orders - COME BACK TO! NEED MORE WORK!_order_type_id_fkey
Name
Source
Target
Cardinality type:
One to
many
Ordinality:
Mandatory
Mandatory
Key and columns
Key name
Column in Order_Type
Column in Orders - COME BACK TO! NEED MORE WORK!
Order_Type_pkey
order_type_id
order_type_id
Name
Vendors_web_app_customer_fkey
Name
Source
Target
Cardinality type:
One to
many
Ordinality:
Mandatory
Mandatory
Key and columns
Key name
Column in Web_App_Customer
Column in Vendors
Web_App_Customer_pkey
web_app_customer_id
web_app_customer
Name
Orders - COME BACK TO! NEED MORE WORK!_trucker_id_fkey
Name
Source
Target
Cardinality type:
One to
many
Ordinality:
Mandatory
Mandatory
Key and columns
Key name
Column in Vendors
Column in Orders - COME BACK TO! NEED MORE WORK!
Vendors_pkey
vendor_id
trucker_id
Name
Salesman_web_app_customer_fkey
Name
Source
Target
Cardinality type:
One to
many
Ordinality:
Mandatory
Mandatory
Key and columns
Key name
Column in Web_App_Customer
Column in Salesman
Web_App_Customer_pkey
web_app_customer_id
web_app_customer
Name
Salesman_salesman_role_id_fkey
Name
Source
Target
Cardinality type:
One to
many
Ordinality:
Mandatory
Mandatory
Key and columns
Key name
Column in Roles
Column in Salesman
Roles_pkey
role_id
salesman_role_id
Name
Customers_customer_type_id_fkey
Name
Source
Target
Cardinality type:
One to
many
Ordinality:
Mandatory
Mandatory
Key and columns
Key name
Column in Customer_Type
Column in Customers
Customer_Type_pkey
customer_type_id
customer_type_id
Name
Customer_Type_sellable_fkey
Name
Source
Target
Cardinality type:
One to
many
Ordinality:
Mandatory
Mandatory
Key and columns
Key name
Column in Sellable
Column in Customer_Type
Sellable_pkey
sellable_id
sellable
Name
Customers_payment_terms_fkey
Name
Source
Target
Cardinality type:
One to
many
Ordinality:
Mandatory
Mandatory
Key and columns
Key name
Column in Payment_terms
Column in Customers
Payment_terms_pkey
payment_terms_id
payment_terms
Name
Customers_payment_type_fkey
Name
Source
Target
Cardinality type:
One to
many
Ordinality:
Mandatory
Mandatory
Key and columns
Key name
Column in Payment_type
Column in Customers
Payment_type_pkey
payment_type_id
payment_type
Name
Orders - COME BACK TO! NEED MORE WORK!_ship_term_fkey
Name
Source
Target
Cardinality type:
One to
many
Ordinality:
Mandatory
Mandatory
Key and columns
Key name
Column in Ship_terms
Column in Orders - COME BACK TO! NEED MORE WORK!
Ship_terms_pkey
ship_terms_id
ship_term
Name
Orders - COME BACK TO! NEED MORE WOR_inventory_product_id_fkey
Name
Source
Target
Cardinality type:
One to
many
Ordinality:
Mandatory
Mandatory
Key and columns
Key name
Column in Products***
Column in Orders - COME BACK TO! NEED MORE WORK!
Products***_pkey
products_id
inventory_product_id
Name
Inventory***_warehouse_id_fkey
Name
Source
Target
Cardinality type:
One to
many
Ordinality:
Mandatory
Mandatory
Key and columns
Key name
Column in Warehouse
Column in Inventory***
Warehouse_pkey
warehouse_id
warehouse_id
Name
Products***_catch_method_fkey
Name
Source
Target
Cardinality type:
One to
many
Ordinality:
Mandatory
Mandatory
Key and columns
Key name
Column in Catch_method
Column in Products***
Catch_method_pkey
catch_method_id
catch_method
Name
Advanced_settings**_web_app_customer_fkey
Name
Source
Target
Cardinality type:
One to
many
Ordinality:
Mandatory
Mandatory
Key and columns
Key name
Column in Web_App_Customer
Column in Advanced_settings**
Web_App_Customer_pkey
web_app_customer_id
web_app_customer
Name
Products***_bill_by_fkey
Name
Source
Target
Cardinality type:
One to
many
Ordinality:
Mandatory
Mandatory
Key and columns
Key name
Column in Bill_by
Column in Products***
Bill_by_pkey
bill_by_id
bill_by
Name
Inventory***_product_id_fkey
Name
Source
Target
Cardinality type:
One to
many
Ordinality:
Mandatory
Mandatory
Key and columns
Key name
Column in Products***
Column in Inventory***
Products***_pkey
products_id
product_id
Name
Inventory***_web_app_customer_fkey
Name
Source
Target
Cardinality type:
One to
many
Ordinality:
Mandatory
Mandatory
Key and columns
Key name
Column in Web_App_Customer
Column in Inventory***
Web_App_Customer_pkey
web_app_customer_id
web_app_customer
Name
Inventory***_purchase_order_id_fkey
Name
Source
Target
Cardinality type:
One to
many
Ordinality:
Mandatory
Mandatory
Key and columns
Key name
Column in Purchase_orders
Column in Inventory***
Purchase_orders_pkey
purchase_order_id
purchase_order_id
Name
Purchase_orders_web_app_customer_fkey
Name
Source
Target
Cardinality type:
One to
many
Ordinality:
Mandatory
Mandatory
Key and columns
Key name
Column in Web_App_Customer
Column in Purchase_orders
Web_App_Customer_pkey
web_app_customer_id
web_app_customer
Name
Vendors_web_app_csutomer_fkey
Name
Source
Target
Cardinality type:
One to
many
Ordinality:
Mandatory
Mandatory
Key and columns
Key name
Column in Web_App_Customer
Column in Vendors
Web_App_Customer_pkey
web_app_customer_id
web_app_csutomer
Name
Products***_web_app_customer_fkey
Name
Source
Target
Cardinality type:
One to
many
Ordinality:
Mandatory
Mandatory
Key and columns
Key name
Column in Web_App_Customer
Column in Products***
Web_App_Customer_pkey
web_app_customer_id
web_app_customer