Database Wiki — GFS System Guide

Tables

~120

Columns

Indexes

29.6 MB

DB Size

Entity Tables

customers 283 rows

Customer master records — companies GFS sells to

Column	Type	Nullable	Default	Description
`id`	INTEGER	No	—	NetSuite internal ID (PK)
`companyname`	TEXT	No	—	Company display name
`entityid`	TEXT	Yes	—	NS entity ID string
`email`	TEXT	Yes	—	Primary email
`phone`	TEXT	Yes	—	Primary phone
`fax`	TEXT	Yes	—	Fax number
`terms`	INTEGER	Yes	—	FK → ref_terms.id
`salesrep`	INTEGER	Yes	—	FK → employees.id
`category`	INTEGER	Yes	—	FK → ref_categories.id
`creditlimit`	REAL	Yes	—	Credit limit ($)
`isperson`	TEXT	Yes	'F'	T=individual, F=company
`datecreated`	TEXT	Yes	—	M/D/YYYY format
`lastmodifieddate`	TEXT	Yes	—	M/D/YYYY format

Indexes: idx_customers_name (companyname) · idx_customers_salesrep (salesrep)

vendors 484 rows

Vendor/supplier master records

Column	Type	Nullable	Default	Description
`id`	INTEGER	No	—	NetSuite internal ID (PK)
`companyname`	TEXT	No	—	Company display name
`entityid`	TEXT	Yes	—	NS entity ID string
`email`	TEXT	Yes	—	Primary email
`phone`	TEXT	Yes	—	Primary phone
`terms`	INTEGER	Yes	—	FK → ref_terms.id
`creditlimit`	REAL	Yes	—	Credit limit ($)
`is1099eligible`	TEXT	Yes	'F'	T=1099 eligible
`taxidnum`	TEXT	Yes	—	Tax ID / EIN
`datecreated`	TEXT	Yes	—	M/D/YYYY
`lastmodifieddate`	TEXT	Yes	—	M/D/YYYY
`isinactive`	TEXT	Yes	'F'	T=inactive

Index: idx_vendors_name (companyname)

items 1,265 rows 114 inactive

Product/item master — includes 14 allergen flags

Column	Type	Nullable	Default	Description
`id`	INTEGER	No	—	NetSuite internal ID (PK)
`itemid`	TEXT	No	—	Item name/number (e.g. PUB6000)
`displayname`	TEXT	Yes	—	Full display name
`itemtype`	TEXT	Yes	—	InvtPart, Assembly, Kit, NonInvtPart
`class`	INTEGER	Yes	—	FK → ref_classes.id
`department`	INTEGER	Yes	—	FK → ref_departments.id (= brand)
`upccode`	TEXT	Yes	—	UPC barcode
`averagecost`	REAL	Yes	—	Weighted avg cost ($)
`cost`	REAL	Yes	—	Last purchase cost ($)
`isinactive`	TEXT	Yes	'F'	T=inactive
`custitem1`	INTEGER	Yes	—	Custom field 1
`custitem3`	TEXT	Yes	—	Custom field 3
`custitem4`	TEXT	Yes	—	Custom field 4
`custitem6`	TEXT	Yes	—	Custom field 6
14 ALLERGEN FLAGS (all TEXT, default 'F', T=contains):
`custitem_as_milk` · `custitem_as_eggs` · `custitem_as_peanuts` · `custitem_as_soybeans` · `custitem_as_wheat` · `custitem_as_fish` · `custitem_as_tree_nuts` · `custitem_as_crustacean` · `custitem_as_celery` · `custitem_as_lupin` · `custitem_as_molluscs` · `custitem_as_mustard` · `custitem_as_sesame` · `custitem_as_sulphur_dioxide`

Indexes: idx_items_itemid · idx_items_type (itemtype) · idx_items_dept (department)

contacts 490 rows

People at customer/vendor companies

Column	Type	Description
`id`	INTEGER PK	NS internal ID
`firstname`	TEXT	First name
`lastname`	TEXT	Last name
`email`	TEXT	Email
`phone`	TEXT	Phone
`title`	TEXT	Job title
`company`	INTEGER	FK → customers.id or vendors.id
`company_name`	TEXT	Denormalized company name
`isinactive`	TEXT	T=inactive (default F)

Index: idx_contacts_company

employees 116 rows

GFS staff — sales reps, managers, operations

Column	Type	Description
`id`	INTEGER PK	NS internal ID
`entityid`	TEXT	Employee ID string
`firstname, lastname`	TEXT	Name
`email`	TEXT	Email
`title`	TEXT	Job title
`phone`	TEXT	Phone
`department`	INTEGER	FK → ref_departments.id
`class`	INTEGER	FK → ref_classes.id
`location`	INTEGER	FK → ref_locations.id
`supervisor`	INTEGER	FK → employees.id
`isinactive`	TEXT	T=inactive (default F)
`issalesrep`	TEXT	T=sales rep (default F)
`issupportrep`	TEXT	T=support rep (default F)
`hiredate, releasedate`	TEXT	M/D/YYYY
`giveaccess`	TEXT	T=has NS login (default F)

Transaction Tables

transactions 102,367 rows

All transaction headers — 20 types spanning 2018-2027

Column	Type	Nullable	Default	Description
`id`	INTEGER	No	—	NetSuite internal ID (PK)
`tranid`	TEXT	Yes	—	Document number (e.g. "12425")
`trandate`	TEXT	No	—	Date in M/D/YYYY format
`type`	TEXT	No	—	Transaction type code (see below)
`entity`	INTEGER	Yes	—	FK → customers.id or vendors.id (backfilled)
`entity_name`	TEXT	Yes	—	Denormalized entity name
`foreigntotal`	REAL	Yes	—	Total amount ($). Invoices positive, bills positive.
`status`	TEXT	Yes	—	Status code (varies by type)
`duedate`	TEXT	Yes	—	Payment due date M/D/YYYY
`shipdate`	TEXT	Yes	—	Ship date M/D/YYYY
`memo`	TEXT	Yes	—	Transaction memo
`year`	INTEGER	—	GENERATED	STORED: extracted from trandate (last 4 chars)

Indexes: idx_txn_type · idx_txn_date · idx_txn_entity · idx_txn_type_date (composite) · idx_txn_year

Transaction Type Codes (20)

Code	Name	Count	$ Value	Entity?	Statuses
`CustInvc`	Customer Invoice	12,542	$169.8M	Yes	A=Open, B=Paid
`SalesOrd`	Sales Order	12,862	$177.4M	Yes	B=Pending, D=Partial, F=PendBill, G=Billed, H=Closed
`VendBill`	Vendor Bill	22,916	$145.0M	Yes	A=Open, B=Paid, C=Cancelled
`PurchOrd`	Purchase Order	11,566	$131.0M	Yes	B=PendRcpt, D=Partial, E=PendBill/Partial, F=PendBill, G=Billed, H=Closed
`CustPymt`	Customer Payment	8,195	$163.2M	Yes	B=NotDeposited, C=Deposited
`VendPymt`	Vendor Payment	9,603	$144.0M	Yes	—
`ItemShip`	Item Fulfillment	12,375	—	No	—
`Check`	Check	5,493	$29.1M	Yes	—
`ItemRcpt`	Item Receipt	3,491	$51.7M	No	—
`CustCred`	Credit Memo	932	$2.7M	Yes	—
`InvAdjst`	Inventory Adjustment	848	$5.95M	No*	—
`WorkOrd`	Work Order	547	—	75%	—
`InvWksht`	Inventory Worksheet	457	$43.5M	No	—
`Deposit`	Deposit	220	$9.25M	No	—
`VendCred`	Vendor Credit	116	$254K	Yes	—
`Build`	Assembly Build	98	$107K	No	—
`Journal`	Journal Entry	47	—	No	—
`InvTrnfr`	Inventory Transfer	44	—	No	—
`Unbuild`	Assembly Unbuild	13	$4.2K	No	—
`CustRfnd`	Customer Refund	2	$43.4K	Yes	—

Line Item Tables

invoice_lines 28,528 rows

CustInvc line items · 100% coverage

Column	Type	Description
`id`	INTEGER PK	Auto-increment
`txn_id`	INTEGER	FK → transactions.id
`tranid`	TEXT	Invoice number
`trandate`	TEXT	M/D/YYYY
`customer`	TEXT	Customer name
`item`	INTEGER	FK → items.id
`item_name`	TEXT	Denormalized item name
`quantity`	REAL	Qty (negative = credit to rev)
`rate`	REAL	Unit price ($)
`netamount`	REAL	Line total (negative = revenue)
`year`	INTEGER	Year extracted

Use ABS() for revenue queries. Avg 2.27 lines/invoice.

so_lines 29,098 rows

SalesOrd line items · 100% coverage · avg 2.26 lines/order

Column	Type	Description
`id`	INTEGER PK	Auto-increment
`txn_id`	INTEGER	FK → transactions.id
`tranid`	TEXT	SO number
`trandate`	TEXT	M/D/YYYY
`customer`	TEXT	Customer name (denormalized)
`item`	INTEGER	FK → items.id
`item_name`	TEXT	Item display name
`quantity`	REAL	Qty ordered
`rate`	REAL	Unit price ($)
`netamount`	REAL	Line total
`year`	INTEGER	Year extracted

Indexes: idx_solines_txn · idx_solines_item

vb_lines 21,317 rows 100%

VendBill line items · 11,631 bills with items · 11,285 expense-only bills have no lines by design

Column	Type	Description
`id`	INTEGER PK	Auto-increment
`txn_id`	INTEGER	FK → transactions.id
`tranid`	TEXT	Bill number
`trandate`	TEXT	M/D/YYYY
`vendor`	TEXT	Vendor name (NOT "customer" — different from invoice/so_lines)
`item`	INTEGER	FK → items.id
`item_name`	TEXT	Item display name
`quantity`	REAL	Qty received/billed
`rate`	REAL	Unit cost ($)
`netamount`	REAL	Line total
`year`	INTEGER	Year extracted

Indexes: idx_vblines_txn · idx_vblines_item. Expense-only vendors (Tc's Trucking, Linecor, GoGreen, Daya, Cintas, AmEx, Uline) have VendBill headers but no item lines.

Financial Tables

gl_accounts 152 rows

Complete Chart of Accounts

Column	Type	Description
`id`	INTEGER PK	NS internal ID
`acctnumber`	TEXT	Account number (1101, 4000, etc.)
`fullname`	TEXT	Full account name
`accttype`	TEXT	Bank, AcctRec, AcctPay, OthCurrAsset, FixedAsset, OthCurrLiab, Equity, Income, COGS, Expense, OthIncome, OthExpense, NonPosting
`balance`	REAL	Current balance ($). NULL for NonPosting.
`isinactive`	TEXT	T=inactive (default F)

102 P&L accounts (Income/COGS/Expense/OthIncome/OthExpense). Loaded from NS live data.

customer_pricing 1,264 rows

Customer-specific item pricing

Column	Type	Description
`id`	INTEGER PK	Auto-increment
`customer_id`	INTEGER	FK → customers.id
`customer`	TEXT	Customer name
`item_id`	INTEGER	FK → items.id
`item_name`	TEXT	Item name
`price`	REAL	Customer price ($)
`level`	INTEGER	Price level
`currency`	INTEGER	Currency ID

Indexes: idx_pricing_cust · idx_pricing_item

Reference & Meta Tables

ref_terms 18

Payment terms

Due on receipt · Net 7 · Net 10 · Net 14 · Net 15 · Net 21 · Net 30 · Net 45 · Net 60 · Net 90 · Net 120 · 1% 10 Net 30 · 2% 10 Net 14 · 2% 10 Net 21 · 2% 10 Net 30 · 2% 14 Net 30 · 2% 15 Net 30 · Wire Pre Payment Required

ref_departments 14

Brands (= department in NS)

Alfresco Italian Specialties · Bentley's Bake Shope · Branson's Roadhouse BBQ · Brownstones Pizza · Components · Cornerstone Bakery · Food Broker · Global Food Solutions · Harvest Promise · Other · Power Up Meals · Right Start Foods · Smart Starts NY · Tiajuana Tortilla

ref_classes 6

Product classification

Frozen · Dry Grocery · Refrigerated · Packaging · Shipping · Other

ref_locations 17

Warehouses & facilities

Fairfield Warehouse · Linden Warehouse · Wyckoff Warehouse · Corporate HQ · NJ Satellite · LineCor · FOB Pickup · Superior Pack · Stellar Pak · Harvest Bakery · Meal Packing · Arias Mountain · Hayes Distribution · Mivila Calverton · Driscoll Foods · GFS Delta Pak · Quaker Sugar

ref_categories 14

Customer categories

Distributor · School District Direct · General Food Service · Food Manufacturer · Managment Company · Food Broker · School District · Healthcare · Co-Packing · Internal · E-Commerce · Migrant Feeding · Commissary · School

ref_shipping 7

Shipping methods

Airborne · Fedex Next Day · UPS Ground · Common Carrier · LineCor Distribution · FOB Customer Pickup · Vendor Delivery

Example Queries (D1 SQLite)

Revenue & Sales

-- Top 10 customers by 2026 revenue
SELECT entity_name, COUNT(*) as invs,
  ROUND(SUM(foreigntotal),2) as revenue
FROM transactions
WHERE type='CustInvc' AND year=2026
GROUP BY entity_name
ORDER BY revenue DESC LIMIT 10

-- Monthly revenue trend
SELECT year,
  CAST(SUBSTR(trandate,1,INSTR(trandate,'/')-1) AS INT) as month,
  COUNT(*) as inv_count,
  ROUND(SUM(foreigntotal),2) as revenue
FROM transactions
WHERE type='CustInvc' AND year>=2024
GROUP BY year, month ORDER BY year, month

-- Open sales orders
SELECT tranid, entity_name, foreigntotal, status
FROM transactions
WHERE type='SalesOrd'
  AND status IN ('A','B','D','E','F')
ORDER BY foreigntotal DESC

AR & Collections

-- Open AR by customer
SELECT entity_name, COUNT(*) as inv_count,
  ROUND(SUM(foreigntotal),2) as open_ar
FROM transactions
WHERE type='CustInvc' AND status='A'
GROUP BY entity_name
ORDER BY open_ar DESC

-- Top items by revenue (2026)
SELECT item_name,
  ROUND(SUM(ABS(netamount)),2) as revenue,
  ROUND(SUM(ABS(quantity)),2) as qty
FROM invoice_lines WHERE year=2026
GROUP BY item_name
ORDER BY revenue DESC LIMIT 20

-- Vendor spend analysis
SELECT entity_name,
  COUNT(*) as bills,
  ROUND(SUM(ABS(foreigntotal)),2) as spend
FROM transactions
WHERE type='VendBill' AND year=2026
GROUP BY entity_name
ORDER BY spend DESC LIMIT 10

sync_log 49+ rows

ETL sync tracking — one row per Worker cron execution

Column	Type	Description
`id`	INTEGER PK	Auto-increment
`sync_type`	TEXT	'scheduled' or 'manual'
`started_at`	TEXT	ISO 8601 timestamp
`completed_at`	TEXT	ISO 8601 timestamp
`records_synced`	INTEGER	Records updated (default 0)
`status`	TEXT	'running', 'success', 'error'
`error`	TEXT	Error message if failed

Foreign Key Relationships

customers.terms          → ref_terms.id
customers.salesrep       → employees.id
customers.category       → ref_categories.id
vendors.terms            → ref_terms.id
items.class              → ref_classes.id
items.department         → ref_departments.id       (= brand)
contacts.company         → customers.id | vendors.id
employees.department     → ref_departments.id
employees.class          → ref_classes.id
employees.location       → ref_locations.id
employees.supervisor     → employees.id             (self-ref)
transactions.entity      → customers.id | vendors.id (backfilled 78,616 rows)
invoice_lines.txn_id     → transactions.id
invoice_lines.item       → items.id
so_lines.txn_id          → transactions.id
so_lines.item            → items.id
vb_lines.txn_id          → transactions.id
vb_lines.item            → items.id
customer_pricing.customer_id → customers.id
customer_pricing.item_id     → items.id

Date Format Reference

All dates in D1 are stored as TEXT in M/D/YYYY format (e.g., "5/19/2026"). The year column on transactions is a GENERATED STORED column: CAST(SUBSTR(trandate, -4) AS INTEGER).

SQLite date parsingSUBSTR(trandate,-4) || '-' || SUBSTR('0'||SUBSTR(trandate,1,INSTR(trandate,'/')-1),-2) || '-' || ...

julianday() compatibleYes, after reformatting to YYYY-MM-DD

Month extractionCAST(SUBSTR(trandate, 1, INSTR(trandate, '/') - 1) AS INTEGER)