GFS Platform

Data model

Schema, relationships, D1 tables, KV/R2 usage

D1 database

gfs-netsuite

3818ecd5…

Tables

Indexes

FK relations

SuiteQL queries

Storage layers

DB — D1 SQLite gfs-netsuite (3818ecd5-995e-4694-a08b-a273c94291da). Mirror of NetSuite entities + transactions.
CACHE — KV namespace e880e40bc3674963bc37cf90e02f6369. Short-TTL caching.
STORAGE — R2 bucket gfs-files. Binary artifacts.

No schema-level FK or NOT NULL constraints (beyond PRIMARY KEY). Integrity is enforced upstream in NetSuite and during sync.

Entity relationship diagram

D1 tables and the FK relationships they encode. Tables shown with up to 10 columns each — open per-table detail below for the full column list.

Table inventory 18 tables

Table	Cols	Rows	Indexes
customers	13	283	idx_customers_name(companyname), idx_customers_salesrep(salesrep)
vendors	12	484	idx_vendors_name(companyname)
items	28	1,265	idx_items_itemid(itemid), idx_items_type(itemtype), idx_items_dept(department)
contacts	9	490	idx_contacts_company(company)
employees	17	116	—
transactions	12	102,367	idx_txn_type(type), idx_txn_date(trandate), idx_txn_entity(entity), idx_txn_type_date(type, trandate), idx_txn_year(year)
invoice_lines	11	28,528	idx_invlines_txn(txn_id), idx_invlines_item(item), idx_invlines_year(year)
so_lines	11	29,098	idx_solines_txn(txn_id), idx_solines_item(item)
vb_lines	11	21,315	idx_vblines_txn(txn_id), idx_vblines_item(item)
gl_accounts	6	152	—
customer_pricing	8	1,264	idx_pricing_cust(customer_id), idx_pricing_item(item_id)
sync_log	7	—	—
ref_terms	1	—	—
ref_departments	1	—	—
ref_classes	1	—	—
ref_locations	1	—	—
ref_categories	1	—	—
ref_shipping	1	—	—

Example D1 queries

Run via wrangler d1 execute gfs-netsuite --remote --command="…".

Revenue YTD

SELECT SUM(foreigntotal) FROM transactions WHERE type='CustInvc' AND year=2026 AND status='B'

Open AR

SELECT SUM(foreigntotal) AS open_ar, COUNT(*) FROM transactions WHERE type='CustInvc' AND status='A'

Top 10 customers (2026)

SELECT c.companyname, SUM(t.foreigntotal) AS rev FROM transactions t JOIN customers c ON c.id=t.entity WHERE t.type='CustInvc' AND t.year=2026 GROUP BY c.companyname ORDER BY rev DESC LIMIT 10

Customer pricing — stale

SELECT c.companyname, cp.last_modified FROM customer_pricing cp JOIN customers c ON c.id=cp.customer WHERE cp.last_modified < date('now','-180 day') ORDER BY cp.last_modified

Item velocity (units shipped 2026)

SELECT i.displayname, SUM(sol.quantity) AS units FROM so_lines sol JOIN items i ON i.id=sol.item JOIN transactions t ON t.id=sol.txn_id WHERE t.year=2026 GROUP BY i.displayname ORDER BY units DESC LIMIT 25

Vendor bill activity

SELECT v.companyname, COUNT(*) AS bills, SUM(vbl.amount) AS spend FROM vb_lines vbl JOIN transactions t ON t.id=vbl.txn_id JOIN vendors v ON v.id=t.entity WHERE t.year=2026 GROUP BY v.companyname ORDER BY spend DESC LIMIT 25

Sync log tail

SELECT * FROM sync_log ORDER BY id DESC LIMIT 25

Date format reference

Different date representations live in D1 and at the NS edge — common source of join/comparison bugs.

Column / Source	Format	Example
transactions.trandate	YYYY-MM-DD	2026-05-19
transactions.lastmodifieddate	ISO 8601 UTC	2026-05-19T13:33:03.674Z
transactions.year	YYYY (int)	2026
NetSuite NSDate via Chartstone	MM/DD/YYYY hh:mm AM/PM	05/19/2026 1:33 PM
D1 native	TEXT (no DATE type)	All dates stored as TEXT

SuiteQL admin query library 30 queries

Admin SuiteQL queries — copy/paste into Chartstone Pro or any SuiteQL runner. All read-only.

Custom records 1 queries

Custom record types with row counts

Lists each customrecordtype with how many records currently exist. Empty types are removal candidates.

SELECT crt.id, crt.name, crt.scriptid, (SELECT COUNT(*) FROM customrecord cr WHERE cr.recordtype = crt.id) AS record_count FROM customrecordtype crt ORDER BY record_count

Dunning 1 queries

Customers by current dunning level

Counts per dunning level — operational signal for the collections team.

SELECT c.custentity_3805_dunning_level AS level, COUNT(*) AS customer_count FROM customer c WHERE c.isinactive = 'F' AND c.custentity_3805_dunning_level IS NOT NULL GROUP BY c.custentity_3805_dunning_level ORDER BY level

Field audit 4 queries

Custom fields never populated

Custom body/entity/item fields that are NULL across every record — candidates for removal.

SELECT cf.scriptid, cf.label, cf.fieldtype, cf.appliestobody FROM customfield cf WHERE NOT EXISTS (SELECT 1 FROM transaction t WHERE t.{custbody_field} IS NOT NULL) -- replace {custbody_field} with the literal column name to test

Customer fields coverage

Per-field NULL counts on the customer record — identifies onboarding-data gaps.

SELECT COUNT(*) AS total_customers, COUNT(email) AS has_email, COUNT(phone) AS has_phone, COUNT(terms) AS has_terms, COUNT(creditlimit) AS has_creditlimit, COUNT(category) AS has_category FROM customer WHERE isinactive = 'F'

Vendor fields coverage

Per-field NULL counts on the vendor record — flags the documented 93% missing terms and 70% missing emails.

SELECT COUNT(*) AS total_vendors, COUNT(email) AS has_email, COUNT(phone) AS has_phone, COUNT(terms) AS has_terms, COUNT(taxidnum) AS has_taxid FROM vendor WHERE isinactive = 'F'

Item attributes coverage

GTIN, weight, storage temp coverage on inventory items — flags warehouse/BOL blockers.

SELECT COUNT(*) AS total_items, COUNT(upccode) AS has_upc, COUNT(weight) AS has_weight, COUNT(custitem_storage_temp) AS has_storage_temp, COUNT(custitem_cases_per_pallet) AS has_cases_per_pallet FROM item WHERE isinactive = 'F' AND itemtype IN ('InvtPart','Assembly')

Files 2 queries

Largest files in the File Cabinet

Top 50 files by size. Watch for runaway logs, screenshot bloat, or accidental uploads.

SELECT id, name, folder, filesize, datecreated, lastmodifieddate FROM file ORDER BY filesize DESC FETCH FIRST 50 ROWS ONLY

Files not accessed in 1 year

Stale files in the File Cabinet — candidates for archival to R2.

SELECT id, name, folder, filesize, datecreated FROM file WHERE lastmodifieddate < ADD_MONTHS(SYSDATE, -12) AND filesize > 1024 ORDER BY filesize DESC

Integration audit 2 queries

Active OAuth/TBA tokens

Inventory of active integration tokens with role and last-used (where available).

SELECT t.id, t.tokenname, t.application, r.name AS role_name, e.entityid AS owner, t.dateusedlast, t.tokenrevoked FROM oauthtoken t LEFT JOIN role r ON r.id = t.role LEFT JOIN employee e ON e.id = t.useruser WHERE t.tokenrevoked = 'F' ORDER BY t.dateusedlast DESC

RESTlet call volume (last 7 days)

Per-RESTlet execution counts — identifies hot integrations and ones that have gone silent.

SELECT s.name AS restlet, COUNT(*) AS calls_7d, MAX(sl.startdate) AS last_call FROM scriptexecutionlog sl JOIN script s ON s.id = sl.script WHERE s.scripttype = 'RESTLET' AND sl.startdate >= SYSDATE - 7 GROUP BY s.name ORDER BY calls_7d DESC

Inventory 2 queries

Items with zero stock across all locations

Items showing as stocked but with zero on-hand everywhere — pruning or restock signal.

SELECT i.id, i.itemid, i.displayname, i.itemtype FROM item i WHERE i.itemtype IN ('InvtPart','Assembly') AND i.isinactive = 'F' AND NOT EXISTS (SELECT 1 FROM inventorybalance b WHERE b.item = i.id AND b.quantityonhand > 0)

Inventory aging — items not received in 90 days

Items that have not had an item-receipt in the last 90 days. Slow-mover signal.

SELECT i.itemid, i.displayname, MAX(t.trandate) AS last_receipt FROM item i LEFT JOIN transactionline tl ON tl.item = i.id LEFT JOIN transaction t ON t.id = tl.transaction AND t.type = 'ItemRcpt' WHERE i.itemtype = 'InvtPart' AND i.isinactive = 'F' GROUP BY i.itemid, i.displayname HAVING MAX(t.trandate) < SYSDATE - 90 OR MAX(t.trandate) IS NULL ORDER BY last_receipt NULLS FIRST

Period close 2 queries

Open accounting periods

Every period currently open to posting — a Tier 1 gap until closed through FY2024.

SELECT id, periodname, startdate, enddate, isperiodclosed, isadjust, isyear, isquarter FROM accountingperiod WHERE isperiodclosed = 'F' ORDER BY startdate

Period close blockers — unposted transactions in old periods

Transactions sitting unposted in periods you would otherwise close.

SELECT ap.periodname, t.type, COUNT(*) AS unposted_count, SUM(t.foreigntotal) AS total_amount FROM transaction t JOIN accountingperiod ap ON ap.id = t.postingperiod WHERE t.posting = 'F' AND ap.startdate < SYSDATE - 90 GROUP BY ap.periodname, t.type ORDER BY ap.periodname

Role audit 3 queries

Roles with permission counts

Inventory of every role with the number of explicit record-type permissions assigned. Surfaces over-permissive roles.

SELECT r.id, r.name, r.centertype, COUNT(rp.permkey) AS permission_count FROM role r LEFT JOIN rolepermissions rp ON rp.role = r.id GROUP BY r.id, r.name, r.centertype ORDER BY permission_count DESC

Roles with full access on sensitive record types

Lists every role that has level=FULL on customer, vendor, employee, transaction, file, or company-information.

SELECT r.name AS role_name, rp.permkey, rp.permlevel FROM rolepermissions rp JOIN role r ON r.id = rp.role WHERE rp.permlevel = 'FULL' AND rp.permkey IN ('LIST_CUSTJOB','LIST_VENDOR','LIST_EMPLOYEE','TRAN_FIND','LIST_FILECABINET','ADMI_COMPANYPREF') ORDER BY r.name, rp.permkey

Users assigned to each role

Count of active employees per role. Highlights single-user roles (bus factor).

SELECT r.name AS role_name, COUNT(DISTINCT eml.entity) AS user_count FROM role r LEFT JOIN employeerolesmap eml ON eml.selectedrole = r.id LEFT JOIN employee e ON e.id = eml.entity AND e.isinactive = 'F' GROUP BY r.name ORDER BY user_count DESC

Saved search audit 1 queries

Saved searches not opened in 12 months

Stale saved searches — candidates to archive. Requires the LoginAudit / SavedSearchExecution table.

SELECT s.id, s.title, s.searchtype, s.owner FROM savedsearch s WHERE NOT EXISTS (SELECT 1 FROM savedsearchexecution se WHERE se.searchid = s.id AND se.executiondate >= ADD_MONTHS(SYSDATE, -12)) ORDER BY s.searchtype, s.title

Script audit 3 queries

Deployed but never executed

Scripts with deployment records but no entries in the scriptexecutionlog within the lookback window — candidates for deactivation.

SELECT s.id, s.name, s.scripttype, sd.status FROM script s JOIN scriptdeployment sd ON sd.script = s.id WHERE sd.isdeployed = 'T' AND NOT EXISTS (SELECT 1 FROM scriptexecutionlog sl WHERE sl.script = s.id AND sl.startdate >= ADD_MONTHS(SYSDATE, -3)) ORDER BY s.name

Top 20 scripts by execution time

Performance hot-spots — scripts consuming the most governance / wall-clock per execution.

SELECT s.name, s.scripttype, COUNT(*) AS executions, AVG(sl.duration) AS avg_ms, MAX(sl.duration) AS max_ms FROM scriptexecutionlog sl JOIN script s ON s.id = sl.script WHERE sl.startdate >= ADD_MONTHS(SYSDATE, -1) GROUP BY s.name, s.scripttype ORDER BY avg_ms DESC FETCH FIRST 20 ROWS ONLY

Scripts in TESTING status

TESTING-status scripts run only for the deployer — finds dormant/half-deployed automation.

SELECT s.id, s.name, s.scripttype, sd.deploymentid, sd.recordtype FROM script s JOIN scriptdeployment sd ON sd.script = s.id WHERE sd.status = 'TESTING' ORDER BY s.name

Suitelet audit 1 queries

Publicly accessible Suitelets (isonline=T)

Lists every Suitelet deployed with Available Without Login — the #1 security exposure in this account.

SELECT s.name, sd.deploymentid, sd.allroles, sd.allemployees, sd.isonline FROM script s JOIN scriptdeployment sd ON sd.script = s.id WHERE s.scripttype = 'SCRIPTLET' AND sd.isonline = 'T' ORDER BY s.name

System health 3 queries

Sync log entries (last 50)

Most-recent entries in the local sync log — paste into the D1 console after replacing transaction → sync_log.

SELECT id, sync_started, records_processed, error_count, notes FROM sync_log ORDER BY sync_started DESC LIMIT 50

D1 row counts (per table)

Counts every table in the D1 mirror. Run against D1 via wrangler d1 execute.

SELECT 'transactions' AS tbl, COUNT(*) AS rows FROM transactions UNION ALL SELECT 'so_lines', COUNT(*) FROM so_lines UNION ALL SELECT 'invoice_lines', COUNT(*) FROM invoice_lines UNION ALL SELECT 'vb_lines', COUNT(*) FROM vb_lines UNION ALL SELECT 'items', COUNT(*) FROM items UNION ALL SELECT 'customers', COUNT(*) FROM customers UNION ALL SELECT 'vendors', COUNT(*) FROM vendors UNION ALL SELECT 'employees', COUNT(*) FROM employees UNION ALL SELECT 'gl_accounts', COUNT(*) FROM gl_accounts UNION ALL SELECT 'contacts', COUNT(*) FROM contacts UNION ALL SELECT 'customer_pricing', COUNT(*) FROM customer_pricing ORDER BY rows DESC

Bundles installed (with version)

Inventory of every installed SuiteApp / bundle. Use to plan retirement of unused bundles (SII, Intrastat, etc.).

SELECT id, name, version, installdate, isinstalled FROM bundleinstallation ORDER BY installdate DESC

Transaction analysis 3 queries

Transactions posted to closed periods (should be zero)

Catches transactions that landed in periods that should have been closed — accounting integrity check.

SELECT t.id, t.tranid, t.type, t.trandate, t.postingperiod, ap.periodname, ap.isperiodclosed FROM transaction t JOIN accountingperiod ap ON ap.id = t.postingperiod WHERE ap.isperiodclosed = 'T' AND t.lastmodifieddate > ap.closedate

Open invoices by aging bucket

AR aging by bucket. Use as the source for an admin AR aging dashboard.

SELECT c.companyname, COUNT(t.id) AS invoice_count, SUM(CASE WHEN SYSDATE - t.duedate BETWEEN 0 AND 30 THEN t.foreigntotal ELSE 0 END) AS d_1_30, SUM(CASE WHEN SYSDATE - t.duedate BETWEEN 31 AND 60 THEN t.foreigntotal ELSE 0 END) AS d_31_60, SUM(CASE WHEN SYSDATE - t.duedate BETWEEN 61 AND 90 THEN t.foreigntotal ELSE 0 END) AS d_61_90, SUM(CASE WHEN SYSDATE - t.duedate > 90 THEN t.foreigntotal ELSE 0 END) AS d_90_plus FROM transaction t JOIN customer c ON c.id = t.entity WHERE t.type = 'CustInvc' AND t.status = 'A' GROUP BY c.companyname ORDER BY d_90_plus DESC

Transactions modified by user (audit trail)

Who touched what, when. Useful for security investigations.

SELECT t.id, t.tranid, t.type, t.lastmodifieddate, e.entityid AS modifier FROM transaction t LEFT JOIN employee e ON e.id = t.lastmodifiedby WHERE t.lastmodifieddate >= TRUNC(SYSDATE) ORDER BY t.lastmodifieddate DESC

Workflow audit 2 queries

Workflows in TESTING owned by individuals

Personal-owner workflows in TESTING affect production for the owner only — silent inconsistency.

SELECT w.name, w.recordtype, w.releasestatus, e.entityid AS owner FROM workflow w LEFT JOIN employee e ON e.id = w.owner WHERE w.releasestatus = 'TESTING' ORDER BY e.entityid, w.name

Active workflows by record type

Distribution of active workflows across record types — surfaces over-engineered processes.

SELECT w.recordtype, COUNT(*) AS active_workflows FROM workflow w WHERE w.releasestatus = 'RELEASED' GROUP BY w.recordtype ORDER BY active_workflows DESC

Per-table detail

Columns, types, and FK annotations per table. Indexes listed where they exist.

customers 13 columns

Column	Type	FK
id	INTEGER PRIMARY KEY
companyname	TEXT NOT NULL
entityid	TEXT
email	TEXT
phone	TEXT
fax	TEXT
terms	INTEGER	FK → ref_terms
salesrep	INTEGER	FK → employees
category	INTEGER	FK → ref_categories
creditlimit	REAL
isperson	TEXT DEFAULT 'F'
datecreated	TEXT
lastmodifieddate	TEXT

Indexes

idx_customers_name on (companyname)
idx_customers_salesrep on (salesrep)

vendors 12 columns

Column	Type	FK
id	INTEGER PRIMARY KEY
companyname	TEXT NOT NULL
entityid	TEXT
email	TEXT
phone	TEXT
terms	INTEGER	FK → ref_terms
creditlimit	REAL
is1099eligible	TEXT DEFAULT 'F'
taxidnum	TEXT
datecreated	TEXT
lastmodifieddate	TEXT
isinactive	TEXT DEFAULT 'F'

Indexes

idx_vendors_name on (companyname)

items 28 columns

Column	Type	FK
id	INTEGER PRIMARY KEY
itemid	TEXT NOT NULL
displayname	TEXT
itemtype	TEXT
class	INTEGER	FK → ref_classes
department	INTEGER	FK → ref_departments
upccode	TEXT
averagecost	REAL
cost	REAL
isinactive	TEXT DEFAULT 'F'
custitem1	INTEGER
custitem3	TEXT
custitem4	TEXT
custitem6	TEXT
custitem_as_milk	TEXT DEFAULT 'F'
custitem_as_eggs	TEXT DEFAULT 'F'
custitem_as_peanuts	TEXT DEFAULT 'F'
custitem_as_soybeans	TEXT DEFAULT 'F'
custitem_as_wheat	TEXT DEFAULT 'F'
custitem_as_fish	TEXT DEFAULT 'F'
custitem_as_tree_nuts	TEXT DEFAULT 'F'
custitem_as_crustacean	TEXT DEFAULT 'F'
custitem_as_celery	TEXT DEFAULT 'F'
custitem_as_lupin	TEXT DEFAULT 'F'
custitem_as_molluscs	TEXT DEFAULT 'F'
custitem_as_mustard	TEXT DEFAULT 'F'
custitem_as_sesame	TEXT DEFAULT 'F'
custitem_as_sulphur_dioxide	TEXT DEFAULT 'F'

Indexes

idx_items_itemid on (itemid)
idx_items_type on (itemtype)
idx_items_dept on (department)

contacts 9 columns

Column	Type	FK
id	INTEGER PRIMARY KEY
firstname	TEXT
lastname	TEXT
email	TEXT
phone	TEXT
title	TEXT
company	INTEGER	FK → vendors
company_name	TEXT
isinactive	TEXT DEFAULT 'F'

Indexes

idx_contacts_company on (company)

employees 17 columns

Column	Type	FK
id	INTEGER PRIMARY KEY
entityid	TEXT
firstname	TEXT
lastname	TEXT
email	TEXT
title	TEXT
phone	TEXT
department	INTEGER	FK → ref_departments
class	INTEGER	FK → ref_classes
location	INTEGER	FK → ref_locations
supervisor	INTEGER	FK → employees
isinactive	TEXT DEFAULT 'F'
issalesrep	TEXT DEFAULT 'F'
issupportrep	TEXT DEFAULT 'F'
hiredate	TEXT
releasedate	TEXT
giveaccess	TEXT DEFAULT 'F'

transactions 12 columns

Column	Type	FK
id	INTEGER PRIMARY KEY
tranid	TEXT
trandate	TEXT NOT NULL
type	TEXT NOT NULL
entity	INTEGER	FK → vendors
entity_name	TEXT
foreigntotal	REAL
status	TEXT
duedate	TEXT
shipdate	TEXT
memo	TEXT
year	INTEGER GENERATED ALWAYS AS (CAST(SUBSTR(trandate, -4) AS INTEGER)) STORED

Indexes

idx_txn_type on (type)
idx_txn_date on (trandate)
idx_txn_entity on (entity)
idx_txn_type_date on (type, trandate)
idx_txn_year on (year)

invoice_lines 11 columns

Column	Type	FK
id	INTEGER PRIMARY KEY AUTOINCREMENT
txn_id	INTEGER	FK → transactions
tranid	TEXT
trandate	TEXT
customer	TEXT
item	INTEGER	FK → items
item_name	TEXT
quantity	REAL
rate	REAL
netamount	REAL
year	INTEGER

Indexes

idx_invlines_txn on (txn_id)
idx_invlines_item on (item)
idx_invlines_year on (year)

so_lines 11 columns

Column	Type	FK
id	INTEGER PRIMARY KEY AUTOINCREMENT
txn_id	INTEGER	FK → transactions
tranid	TEXT
trandate	TEXT
customer	TEXT
item	INTEGER	FK → items
item_name	TEXT
quantity	REAL
rate	REAL
netamount	REAL
year	INTEGER

Indexes

idx_solines_txn on (txn_id)
idx_solines_item on (item)

vb_lines 11 columns

Column	Type	FK
id	INTEGER PRIMARY KEY AUTOINCREMENT
txn_id	INTEGER	FK → transactions
tranid	TEXT
trandate	TEXT
vendor	TEXT
item	INTEGER	FK → items
item_name	TEXT
quantity	REAL
rate	REAL
netamount	REAL
year	INTEGER

Indexes

idx_vblines_txn on (txn_id)
idx_vblines_item on (item)

gl_accounts 6 columns

Column	Type	FK
id	INTEGER PRIMARY KEY
acctnumber	TEXT
fullname	TEXT
accttype	TEXT
balance	REAL
isinactive	TEXT DEFAULT 'F'

customer_pricing 8 columns

Column	Type	FK
id	INTEGER PRIMARY KEY AUTOINCREMENT
customer_id	INTEGER
customer	TEXT	FK → customers
item_id	INTEGER
item_name	TEXT
price	REAL
level	INTEGER
currency	INTEGER

Indexes

idx_pricing_cust on (customer_id)
idx_pricing_item on (item_id)

sync_log 7 columns

Column	Type	FK
id	INTEGER PRIMARY KEY AUTOINCREMENT
sync_type	TEXT
started_at	TEXT
completed_at	TEXT
records_synced	INTEGER DEFAULT 0
status	TEXT DEFAULT 'running'
error	TEXT

ref_terms 1 columns

Column	Type	FK
id	INTEGER PRIMARY KEY, name TEXT

ref_departments 1 columns

Column	Type	FK
id	INTEGER PRIMARY KEY, name TEXT

ref_classes 1 columns

Column	Type	FK
id	INTEGER PRIMARY KEY, name TEXT

ref_locations 1 columns

Column	Type	FK
id	INTEGER PRIMARY KEY, name TEXT

ref_categories 1 columns

Column	Type	FK
id	INTEGER PRIMARY KEY, name TEXT

ref_shipping 1 columns

Column	Type	FK
id	INTEGER PRIMARY KEY, name TEXT

Source

schema.sql — canonical schema
sql/ — 45 one-time D1 loaders
wrangler.jsonc — binding declarations
data/suiteql-library.json — admin query library source