Database Schema Reference

The HeroBM application core database (herobm_core) is a fully typed, relational PostgreSQL database managed via Drizzle ORM. It enforces strict referential integrity, domain state machines, event sourcing audit logs, and transactional outbox queuing.

Architectural Principles & Standards

[!NOTE] 1. UUID Primary Keys: Every operational table uses a uuid primary key with gen_random_uuid() to prevent auto-increment enumeration vulnerabilities and facilitate safe distributed ingestion.

[!IMPORTANT] 2. Enforced Foreign Keys & Referential Integrity: Inter-entity relationships strictly enforce foreign keys. Cascading deletes are restricted to dependent line items (e.g., order lines, bin contents), while master dimension records use RESTRICT rules.

[!TIP] 3. Microsoft CDM & Schema.org Conventions: Column names follow snake_case naming aligned with common enterprise standards (account_number, currency_code, state_code, created_on, modified_on).

[!WARNING] 4. Explicit State & No Magic Defaults: Entity lifecycle states are strictly governed by application state machines and check constraints. No default values are used for critical financial states.

Core Lineage & Entity Relationships

The following entity-relationship diagram illustrates the operational dependencies between CRM, Catalog, Sales, Purchasing, Warehouse, and General Ledger domains:

flowchart TD
    subgraph CRM ["CRM & Stakeholders"]
        organizations["organizations"]
        contacts["contacts"]
        customers["customers"]
        suppliers["suppliers"]
        opportunities["opportunities"]
    end

    subgraph Catalog ["Catalog"]
        products["products"]
    end

    subgraph Sales ["Sales & Fulfilment"]
        sales_orders["sales_orders"]
        sales_order_line_items["sales_order_lines"]
        sales_order_shipments["sales_shipments"]
        sales_invoices["sales_invoices"]
    end

    subgraph Procurement ["Purchasing & Receiving"]
        purchase_orders["purchase_orders"]
        purchase_order_line_items["purchase_order_lines"]
        goods_received["goods_received"]
        purchase_invoices["purchase_invoices"]
    end

    subgraph Inventory ["Warehouse & Ledger"]
        locations["locations"]
        bins["bins"]
        inventory_entries["inventory_entries"]
        inventory_ledger["inventory_ledger"]
    end

    subgraph GL ["General Ledger"]
        gl_accounts["gl_accounts"]
        gl_journal_entries["gl_journal_entries"]
        gl_journal_lines["gl_journal_lines"]
        payment_entries["payment_entries"]
    end

    contacts -->|referred_by_contact_id| contacts
    organizations -->|referred_by_organization_id| contacts
    organizations -->|organization_id| customers
    gl_journal_entries -->|reversed_by| gl_journal_entries
    gl_accounts -->|gl_account_id| gl_journal_lines
    gl_journal_entries -->|journal_entry_id| gl_journal_lines
    locations -->|location_id| goods_received
    suppliers -->|vendor_id| goods_received
    bins -->|bin_id| inventory_ledger
    inventory_entries -->|entry_id| inventory_ledger
    locations -->|location_id| inventory_ledger
    products -->|product_id| inventory_ledger
    contacts -->|referred_by_contact_id| organizations
    organizations -->|referred_by_organization_id| organizations
    gl_accounts -->|gl_account_bank| payment_entries
    purchase_orders -->|purchase_order_id| purchase_invoices
    suppliers -->|vendor_id| purchase_invoices
    locations -->|delivery_location_id| purchase_orders
    suppliers -->|vendor_id| purchase_orders
    customers -->|customer_id| sales_invoices
    sales_orders -->|sales_order_id| sales_invoices
    locations -->|fulfillment_location_id| sales_order_shipments
    sales_orders -->|sales_order_id| sales_order_shipments
    customers -->|customer_id| sales_orders
    locations -->|fulfillment_location_id| sales_orders
    opportunities -->|opportunity_id| sales_orders
    organizations -->|organization_id| suppliers
    locations -->|location_id| work_orders
    bins -->|output_bin_id| work_orders
    products -->|product_id| work_orders
    bins -->|wip_bin_id| work_orders

Schema Summary & Table Directory

The herobm_core schema contains 133 tables, 1449 columns, and 280 foreign key relationships across 8 business domains:

Table	Domain	Primary Key	Columns	Foreign Keys	Live Rows
contacts	CRM & Stakeholders	`contact_id`	15	2	—
crm_activities	CRM & Stakeholders	`activity_id`	16	5	—
customer_delivery_addresses	CRM & Stakeholders	`id`	17	1	—
customer_groups	CRM & Stakeholders	`customer_group_id`	14	6	—
customers	CRM & Stakeholders	`customer_id`	25	4	—
opportunities	CRM & Stakeholders	`opportunity_id`	15	1	—
opportunity_contacts	CRM & Stakeholders	`opportunity_contact_id`	5	2	—
opportunity_notes	CRM & Stakeholders	`note_id`	5	2	—
opportunity_organizations	CRM & Stakeholders	`opportunity_organization_id`	5	2	—
organization_contact_links	CRM & Stakeholders	`link_id`	6	2	—
organization_notes	CRM & Stakeholders	`note_id`	5	2	—
organization_organization_links	CRM & Stakeholders	`link_id`	5	2	—
organizations	CRM & Stakeholders	`organization_id`	26	3	—
project_notes	CRM & Stakeholders	`note_id`	5	2	—
projects	CRM & Stakeholders	`project_id`	23	5	—
trading_terms	CRM & Stakeholders	`trading_terms_id`	10	0	—
discount_matrix	Products & Catalog	`discount_matrix_id`	7	3	—
product_components	Products & Catalog	`component_id`	7	2	—
product_default_bins	Products & Catalog	`product_default_bin_id`	9	3	—
product_groups	Products & Catalog	`product_group_id`	9	6	—
product_images	Products & Catalog	`image_id`	10	1	—
product_suppliers	Products & Catalog	`product_supplier_id`	18	2	—
product_uoms	Products & Catalog	`product_uom_id`	7	2	—
products	Products & Catalog	`product_id`	32	6	—
uom_dictionary	Products & Catalog	`uom_code`	4	0	—
backorders	Sales & Distribution	`backorder_id`	15	10	—
sales_credit_note_lines	Sales & Distribution	`credit_note_line_id`	13	4	—
sales_credit_notes	Sales & Distribution	`credit_note_id`	20	4	—
sales_events	Sales & Distribution	`event_id`	8	0	—
sales_invoice_lines	Sales & Distribution	`invoice_line_id`	6	2	—
sales_invoices	Sales & Distribution	`invoice_id`	24	2	—
sales_order_lines	Sales & Distribution	`sales_order_line_id`	19	5	—
sales_order_picks	Sales & Distribution	`pick_id`	10	4	—
sales_order_return_lines	Sales & Distribution	`return_line_id`	14	3	—
sales_order_returns	Sales & Distribution	`return_id`	9	2	—
sales_order_shipment_lines	Sales & Distribution	`shipment_line_id`	4	2	—
sales_order_shipments	Sales & Distribution	`shipment_id`	12	2	—
sales_orders	Sales & Distribution	`sales_order_id`	34	3	—
goods_received	Purchasing & Procurement	`goods_received_id`	10	2	—
goods_received_lines	Purchasing & Procurement	`goods_received_line_id`	9	4	—
procurement_events	Purchasing & Procurement	`event_id`	8	0	—
purchase_debit_note_lines	Purchasing & Procurement	`debit_note_line_id`	10	4	—
purchase_debit_note_shipments	Purchasing & Procurement	`debit_note_shipment_id`	4	2	—
purchase_debit_notes	Purchasing & Procurement	`debit_note_id`	20	3	—
purchase_invoice_lines	Purchasing & Procurement	`invoice_line_id`	10	4	—
purchase_invoice_receipts	Purchasing & Procurement	`invoice_receipt_id`	4	2	—
purchase_invoices	Purchasing & Procurement	`invoice_id`	23	2	—
purchase_order_lines	Purchasing & Procurement	`purchase_order_line_id`	15	3	—
purchase_order_return_lines	Purchasing & Procurement	`return_line_id`	7	3	—
purchase_order_return_shipment_lines	Purchasing & Procurement	`shipment_line_id`	4	2	—
purchase_order_return_shipments	Purchasing & Procurement	`shipment_id`	10	2	—
purchase_order_returns	Purchasing & Procurement	`return_id`	8	1	—
purchase_orders	Purchasing & Procurement	`purchase_order_id`	18	2	—
supplier_expiries	Purchasing & Procurement	`expiry_id`	8	1	—
supplier_groups	Purchasing & Procurement	`supplier_group_id`	17	6	—
suppliers	Purchasing & Procurement	`vendor_id`	27	4	—
bin_contents	Warehouse & Inventory	`bin_content_id`	5	2	—
bins	Warehouse & Inventory	`bin_id`	12	1	—
inventory_entries	Warehouse & Inventory	`entry_id`	10	0	—
inventory_events	Warehouse & Inventory	`event_id`	8	0	—
inventory_ledger	Warehouse & Inventory	`ledger_id`	7	5	—
locations	Warehouse & Inventory	`location_id`	14	0	—
transfer_order_lines	Warehouse & Inventory	`transfer_order_line_id`	7	3	—
transfer_order_picks	Warehouse & Inventory	`pick_id`	10	4	—
transfer_order_receipt_lines	Warehouse & Inventory	`receipt_line_id`	7	4	—
transfer_order_receipts	Warehouse & Inventory	`receipt_id`	7	1	—
transfer_order_shipment_lines	Warehouse & Inventory	`shipment_line_id`	6	4	—
transfer_order_shipments	Warehouse & Inventory	`shipment_id`	11	1	—
transfer_orders	Warehouse & Inventory	`transfer_order_id`	13	4	—
warehouse_events	Warehouse & Inventory	`event_id`	8	0	—
zones	Warehouse & Inventory	`zone_id`	9	1	—
activities	Financials & General Ledger	`activity_id`	7	0	—
bank_statement_lines	Financials & General Ledger	`line_id`	13	4	—
cost_centers	Financials & General Ledger	`cost_center_id`	7	0	—
csv_mapping_profiles	Financials & General Ledger	`profile_id`	12	0	—
exchange_rates	Financials & General Ledger	`exchange_rate_id`	7	0	—
financial_events	Financials & General Ledger	`event_id`	8	0	—
gl_accounts	Financials & General Ledger	`gl_account_id`	13	0	—
gl_fiscal_periods	Financials & General Ledger	`period_id`	14	0	—
gl_journal_entries	Financials & General Ledger	`journal_entry_id`	13	1	—
gl_journal_lines	Financials & General Ledger	`journal_line_id`	17	6	—
gl_match_groups	Financials & General Ledger	`match_group_id`	5	1	—
gl_reconciliations	Financials & General Ledger	`reconciliation_id`	8	1	—
gl_settings	Financials & General Ledger	`settings_id`	32	24	—
payment_allocations	Financials & General Ledger	`allocation_id`	7	1	—
payment_entries	Financials & General Ledger	`payment_id`	19	1	—
payment_lines	Financials & General Ledger	`payment_line_id`	5	2	—
reconciliation_events	Financials & General Ledger	`event_id`	8	0	—
reconciliation_rules	Financials & General Ledger	`rule_id`	17	3	—
tax_categories	Financials & General Ledger	`tax_category_id`	7	0	—
tax_position_mappings	Financials & General Ledger	`mapping_id`	4	3	—
tax_positions	Financials & General Ledger	`tax_position_id`	3	0	—
work_order_components	Manufacturing & Work Orders	`work_order_component_id`	5	2	—
work_order_picks	Manufacturing & Work Orders	`pick_id`	9	3	—
work_orders	Manufacturing & Work Orders	`work_order_id`	17	4	—
_pipeline_jobs	System, Security & Telemetry	`job_id`	8	0	—
api_keys	System, Security & Telemetry	`api_key_id`	8	0	—
app_settings	System, Security & Telemetry	`settings_id`	34	7	—
business_report_events	System, Security & Telemetry	`event_id`	8	0	—
business_reports	System, Security & Telemetry	`id`	8	0	—
casbin_rule	System, Security & Telemetry	`id`	8	0	—
email_events	System, Security & Telemetry	`event_id`	8	0	—
email_outbox	System, Security & Telemetry	`id`	14	0	—
group_events	System, Security & Telemetry	`event_id`	8	0	—
integration_events	System, Security & Telemetry	`event_id`	8	0	—
integrations	System, Security & Telemetry	`integration_id`	6	0	—
macros	System, Security & Telemetry	`macro_id`	6	0	—
master_data_events	System, Security & Telemetry	`event_id`	8	0	—
outbox	System, Security & Telemetry	`outbox_id`	10	0	—
pdf_template_contexts	System, Security & Telemetry	—	2	1	—
pdf_template_hooks	System, Security & Telemetry	`id`	5	1	—
pdf_templates	System, Security & Telemetry	`id`	9	0	—
system_events	System, Security & Telemetry	`event_id`	8	0	—
tenant_settings	System, Security & Telemetry	`tenant_settings_id`	20	0	—
user_events	System, Security & Telemetry	`event_id`	7	0	—
user_settings	System, Security & Telemetry	`user_id`	6	1	—
user_two_factor	System, Security & Telemetry	`user_id`	7	1	—
users	System, Security & Telemetry	`user_id`	8	0	—
webhooks	System, Security & Telemetry	`webhook_id`	6	0	—
crm_activity_contacts	System, Security & Telemetry	`activity_contact_id`	4	2	—
crm_settings	System, Security & Telemetry	`settings_id`	8	0	—
manufacturing_settings	System, Security & Telemetry	`manufacturing_settings_id`	3	0	—
product_settings	System, Security & Telemetry	`settings_id`	4	0	—
project_budget_lines	System, Security & Telemetry	`budget_line_id`	15	4	—
project_ledger_entries	System, Security & Telemetry	`ledger_id`	25	7	—
project_resource_assignments	System, Security & Telemetry	`assignment_id`	6	2	—
project_resources	System, Security & Telemetry	`resource_id`	13	4	—
project_settings	System, Security & Telemetry	`settings_id`	4	0	—
project_tasks	System, Security & Telemetry	`project_task_id`	16	2	—
purchasing_settings	System, Security & Telemetry	`purchasing_settings_id`	4	0	—
sales_settings	System, Security & Telemetry	`sales_settings_id`	5	0	—
stocktake_counts	System, Security & Telemetry	`stocktake_count_id`	10	4	—
stocktake_lines	System, Security & Telemetry	`stocktake_line_id`	10	3	—
stocktakes	System, Security & Telemetry	`stocktake_id`	22	2	—

Foreign Key Relationships Catalog

The table below catalogs all referential constraints across the application database:

From Table	Column	To Table	Target Column	On Delete Action
`app_settings`	`default_customer_tax_position_id`	`tax_positions`	`tax_position_id`	`RESTRICT`
`app_settings`	`default_customer_terms_id`	`trading_terms`	`trading_terms_id`	`RESTRICT`
`app_settings`	`default_fulfillment_location_id`	`locations`	`location_id`	`RESTRICT`
`app_settings`	`default_purchase_tax_category_id`	`tax_categories`	`tax_category_id`	`RESTRICT`
`app_settings`	`default_sales_tax_category_id`	`tax_categories`	`tax_category_id`	`RESTRICT`
`app_settings`	`default_supplier_tax_position_id`	`tax_positions`	`tax_position_id`	`RESTRICT`
`app_settings`	`default_supplier_terms_id`	`trading_terms`	`trading_terms_id`	`RESTRICT`
`backorders`	`demand_work_order_id`	`work_orders`	`work_order_id`	`RESTRICT`
`backorders`	`product_id`	`products`	`product_id`	`RESTRICT`
`backorders`	`purchase_order_id`	`purchase_orders`	`purchase_order_id`	`RESTRICT`
`backorders`	`purchase_order_line_id`	`purchase_order_lines`	`purchase_order_line_id`	`RESTRICT`
`backorders`	`sales_order_id`	`sales_orders`	`sales_order_id`	`RESTRICT`
`backorders`	`sales_order_line_id`	`sales_order_lines`	`sales_order_line_id`	`RESTRICT`
`backorders`	`transfer_order_id`	`transfer_orders`	`transfer_order_id`	`RESTRICT`
`backorders`	`transfer_order_line_id`	`transfer_order_lines`	`transfer_order_line_id`	`RESTRICT`
`backorders`	`work_order_component_id`	`work_order_components`	`work_order_component_id`	`RESTRICT`
`backorders`	`work_order_id`	`work_orders`	`work_order_id`	`RESTRICT`
`bank_statement_lines`	`gl_account_id`	`gl_accounts`	`gl_account_id`	`RESTRICT`
`bank_statement_lines`	`match_group_id`	`gl_match_groups`	`match_group_id`	`RESTRICT`
`bank_statement_lines`	`matched_journal_line_id`	`gl_journal_lines`	`journal_line_id`	`RESTRICT`
`bank_statement_lines`	`reconciliation_id`	`gl_reconciliations`	`reconciliation_id`	`RESTRICT`
`bin_contents`	`bin_id`	`bins`	`bin_id`	`cascade`
`bin_contents`	`product_id`	`products`	`product_id`	`cascade`
`bins`	`zone_id`	`zones`	`zone_id`	`RESTRICT`
`contacts`	`referred_by_contact_id`	`contacts`	`contact_id`	`RESTRICT`
`contacts`	`referred_by_organization_id`	`organizations`	`organization_id`	`RESTRICT`
`crm_activities`	`assigned_to_user_id`	`users`	`user_id`	`RESTRICT`
`crm_activities`	`completed_by_user_id`	`users`	`user_id`	`RESTRICT`
`crm_activities`	`created_by_id`	`users`	`user_id`	`RESTRICT`
`crm_activities`	`opportunity_id`	`opportunities`	`opportunity_id`	`RESTRICT`
`crm_activities`	`organization_id`	`organizations`	`organization_id`	`RESTRICT`
`crm_activity_contacts`	`activity_id`	`crm_activities`	`activity_id`	`cascade`
`crm_activity_contacts`	`contact_id`	`contacts`	`contact_id`	`cascade`
`customer_delivery_addresses`	`customer_id`	`customers`	`customer_id`	`RESTRICT`
`customer_groups`	`default_activity_id`	`activities`	`activity_id`	`RESTRICT`
`customer_groups`	`default_ar_account_id`	`gl_accounts`	`gl_account_id`	`RESTRICT`
`customer_groups`	`default_cost_center_id`	`cost_centers`	`cost_center_id`	`RESTRICT`
`customer_groups`	`default_revenue_account_id`	`gl_accounts`	`gl_account_id`	`RESTRICT`
`customer_groups`	`tax_position_id`	`tax_positions`	`tax_position_id`	`RESTRICT`
`customer_groups`	`trading_terms_id`	`trading_terms`	`trading_terms_id`	`RESTRICT`
`customers`	`customer_group_id`	`customer_groups`	`customer_group_id`	`RESTRICT`
`customers`	`organization_id`	`organizations`	`organization_id`	`RESTRICT`
`customers`	`tax_position_id`	`tax_positions`	`tax_position_id`	`RESTRICT`
`customers`	`trading_terms_id`	`trading_terms`	`trading_terms_id`	`RESTRICT`
`discount_matrix`	`customer_group_id`	`customer_groups`	`customer_group_id`	`RESTRICT`
`discount_matrix`	`customer_id`	`customers`	`customer_id`	`RESTRICT`
`discount_matrix`	`product_group_id`	`product_groups`	`product_group_id`	`RESTRICT`
`gl_journal_entries`	`reversed_by`	`gl_journal_entries`	`journal_entry_id`	`RESTRICT`
`gl_journal_lines`	`activity_id`	`activities`	`activity_id`	`RESTRICT`
`gl_journal_lines`	`cost_center_id`	`cost_centers`	`cost_center_id`	`RESTRICT`
`gl_journal_lines`	`gl_account_id`	`gl_accounts`	`gl_account_id`	`RESTRICT`
`gl_journal_lines`	`journal_entry_id`	`gl_journal_entries`	`journal_entry_id`	`RESTRICT`
`gl_journal_lines`	`match_group_id`	`gl_match_groups`	`match_group_id`	`RESTRICT`
`gl_journal_lines`	`reconciliation_id`	`gl_reconciliations`	`reconciliation_id`	`RESTRICT`
`gl_match_groups`	`rule_id`	`reconciliation_rules`	`rule_id`	`RESTRICT`
`gl_reconciliations`	`gl_account_id`	`gl_accounts`	`gl_account_id`	`RESTRICT`
`gl_settings`	`default_activity_id`	`activities`	`activity_id`	`RESTRICT`
`gl_settings`	`default_ap_account_id`	`gl_accounts`	`gl_account_id`	`RESTRICT`
`gl_settings`	`default_ar_account_id`	`gl_accounts`	`gl_account_id`	`RESTRICT`
`gl_settings`	`default_cogs_account_id`	`gl_accounts`	`gl_account_id`	`RESTRICT`
`gl_settings`	`default_cost_center_id`	`cost_centers`	`cost_center_id`	`RESTRICT`
`gl_settings`	`default_discounts_given_account_id`	`gl_accounts`	`gl_account_id`	`RESTRICT`
`gl_settings`	`default_discounts_received_account_id`	`gl_accounts`	`gl_account_id`	`RESTRICT`
`gl_settings`	`default_expense_account_id`	`gl_accounts`	`gl_account_id`	`RESTRICT`
`gl_settings`	`default_fee_revenue_account_id`	`gl_accounts`	`gl_account_id`	`RESTRICT`
`gl_settings`	`default_grni_account_id`	`gl_accounts`	`gl_account_id`	`RESTRICT`
`gl_settings`	`default_inventory_account_id`	`gl_accounts`	`gl_account_id`	`RESTRICT`
`gl_settings`	`default_otc_card_account_id`	`gl_accounts`	`gl_account_id`	`RESTRICT`
`gl_settings`	`default_otc_cash_account_id`	`gl_accounts`	`gl_account_id`	`RESTRICT`
`gl_settings`	`default_ppv_account_id`	`gl_accounts`	`gl_account_id`	`RESTRICT`
`gl_settings`	`default_purchase_tax_account_id`	`gl_accounts`	`gl_account_id`	`RESTRICT`
`gl_settings`	`default_retained_earnings_account_id`	`gl_accounts`	`gl_account_id`	`RESTRICT`
`gl_settings`	`default_revenue_account_id`	`gl_accounts`	`gl_account_id`	`RESTRICT`
`gl_settings`	`default_sales_tax_account_id`	`gl_accounts`	`gl_account_id`	`RESTRICT`
`gl_settings`	`default_shrinkage_account_id`	`gl_accounts`	`gl_account_id`	`RESTRICT`
`gl_settings`	`default_suspense_account_id`	`gl_accounts`	`gl_account_id`	`RESTRICT`
`gl_settings`	`realised_fx_gain_account_id`	`gl_accounts`	`gl_account_id`	`RESTRICT`
`gl_settings`	`realised_fx_loss_account_id`	`gl_accounts`	`gl_account_id`	`RESTRICT`
`gl_settings`	`unrealised_fx_gain_account_id`	`gl_accounts`	`gl_account_id`	`RESTRICT`
`gl_settings`	`unrealised_fx_loss_account_id`	`gl_accounts`	`gl_account_id`	`RESTRICT`
`goods_received`	`location_id`	`locations`	`location_id`	`RESTRICT`
`goods_received`	`vendor_id`	`suppliers`	`vendor_id`	`RESTRICT`
`goods_received_lines`	`goods_received_id`	`goods_received`	`goods_received_id`	`RESTRICT`
`goods_received_lines`	`product_id`	`products`	`product_id`	`RESTRICT`
`goods_received_lines`	`purchase_order_id`	`purchase_orders`	`purchase_order_id`	`RESTRICT`
`goods_received_lines`	`purchase_order_line_id`	`purchase_order_lines`	`purchase_order_line_id`	`RESTRICT`
`inventory_ledger`	`bin_id`	`bins`	`bin_id`	`RESTRICT`
`inventory_ledger`	`entry_id`	`inventory_entries`	`entry_id`	`RESTRICT`
`inventory_ledger`	`location_id`	`locations`	`location_id`	`RESTRICT`
`inventory_ledger`	`product_id`	`products`	`product_id`	`RESTRICT`
`inventory_ledger`	`zone_id`	`zones`	`zone_id`	`RESTRICT`
`opportunities`	`owner_id`	`users`	`user_id`	`RESTRICT`
`opportunity_contacts`	`contact_id`	`contacts`	`contact_id`	`RESTRICT`
`opportunity_contacts`	`opportunity_id`	`opportunities`	`opportunity_id`	`RESTRICT`
`opportunity_notes`	`created_by_id`	`users`	`user_id`	`RESTRICT`
`opportunity_notes`	`opportunity_id`	`opportunities`	`opportunity_id`	`RESTRICT`
`opportunity_organizations`	`opportunity_id`	`opportunities`	`opportunity_id`	`RESTRICT`
`opportunity_organizations`	`organization_id`	`organizations`	`organization_id`	`RESTRICT`
`organization_contact_links`	`contact_id`	`contacts`	`contact_id`	`RESTRICT`
`organization_contact_links`	`organization_id`	`organizations`	`organization_id`	`RESTRICT`
`organization_notes`	`created_by_id`	`users`	`user_id`	`RESTRICT`
`organization_notes`	`organization_id`	`organizations`	`organization_id`	`RESTRICT`
`organization_organization_links`	`source_organization_id`	`organizations`	`organization_id`	`RESTRICT`
`organization_organization_links`	`target_organization_id`	`organizations`	`organization_id`	`RESTRICT`
`organizations`	`owner_id`	`users`	`user_id`	`RESTRICT`
`organizations`	`referred_by_contact_id`	`contacts`	`contact_id`	`RESTRICT`
`organizations`	`referred_by_organization_id`	`organizations`	`organization_id`	`RESTRICT`
`payment_allocations`	`payment_id`	`payment_entries`	`payment_id`	`RESTRICT`
`payment_entries`	`gl_account_bank`	`gl_accounts`	`gl_account_id`	`RESTRICT`
`payment_lines`	`gl_account_id`	`gl_accounts`	`gl_account_id`	`RESTRICT`
`payment_lines`	`payment_id`	`payment_entries`	`payment_id`	`RESTRICT`
`pdf_template_contexts`	`template_id`	`pdf_templates`	`id`	`cascade`
`pdf_template_hooks`	`report_id`	`pdf_templates`	`id`	`cascade`
`product_components`	`child_product_id`	`products`	`product_id`	`RESTRICT`
`product_components`	`parent_product_id`	`products`	`product_id`	`RESTRICT`
`product_default_bins`	`bin_id`	`bins`	`bin_id`	`RESTRICT`
`product_default_bins`	`location_id`	`locations`	`location_id`	`RESTRICT`
`product_default_bins`	`product_id`	`products`	`product_id`	`RESTRICT`
`product_groups`	`default_activity_id`	`activities`	`activity_id`	`RESTRICT`
`product_groups`	`default_cost_center_id`	`cost_centers`	`cost_center_id`	`RESTRICT`
`product_groups`	`default_expense_account_id`	`gl_accounts`	`gl_account_id`	`RESTRICT`
`product_groups`	`default_revenue_account_id`	`gl_accounts`	`gl_account_id`	`RESTRICT`
`product_groups`	`purchase_tax_category_id`	`tax_categories`	`tax_category_id`	`RESTRICT`
`product_groups`	`sales_tax_category_id`	`tax_categories`	`tax_category_id`	`RESTRICT`
`product_images`	`product_id`	`products`	`product_id`	`cascade`
`product_suppliers`	`product_id`	`products`	`product_id`	`RESTRICT`
`product_suppliers`	`vendor_id`	`suppliers`	`vendor_id`	`RESTRICT`
`product_uoms`	`product_id`	`products`	`product_id`	`RESTRICT`
`product_uoms`	`uom_code`	`uom_dictionary`	`uom_code`	`RESTRICT`
`products`	`base_uom`	`uom_dictionary`	`uom_code`	`RESTRICT`
`products`	`default_purchase_uom_id`	`product_uoms`	`product_uom_id`	`RESTRICT`
`products`	`default_sales_uom_id`	`product_uoms`	`product_uom_id`	`RESTRICT`
`products`	`product_group_id`	`product_groups`	`product_group_id`	`RESTRICT`
`products`	`purchase_tax_category_id`	`tax_categories`	`tax_category_id`	`RESTRICT`
`products`	`sales_tax_category_id`	`tax_categories`	`tax_category_id`	`RESTRICT`
`project_budget_lines`	`product_id`	`products`	`product_id`	`RESTRICT`
`project_budget_lines`	`project_id`	`projects`	`project_id`	`RESTRICT`
`project_budget_lines`	`project_task_id`	`project_tasks`	`project_task_id`	`RESTRICT`
`project_budget_lines`	`resource_id`	`project_resources`	`resource_id`	`RESTRICT`
`project_ledger_entries`	`budget_line_id`	`project_budget_lines`	`budget_line_id`	`SET NULL`
`project_ledger_entries`	`inventory_entry_id`	`inventory_entries`	`entry_id`	`RESTRICT`
`project_ledger_entries`	`product_id`	`products`	`product_id`	`RESTRICT`
`project_ledger_entries`	`project_id`	`projects`	`project_id`	`RESTRICT`
`project_ledger_entries`	`project_task_id`	`project_tasks`	`project_task_id`	`RESTRICT`
`project_ledger_entries`	`resource_id`	`project_resources`	`resource_id`	`RESTRICT`
`project_ledger_entries`	`user_id`	`users`	`user_id`	`RESTRICT`
`project_notes`	`created_by_id`	`users`	`user_id`	`RESTRICT`
`project_notes`	`project_id`	`projects`	`project_id`	`cascade`
`project_resource_assignments`	`project_id`	`projects`	`project_id`	`cascade`
`project_resource_assignments`	`resource_id`	`project_resources`	`resource_id`	`RESTRICT`
`project_resources`	`base_uom`	`uom_dictionary`	`uom_code`	`RESTRICT`
`project_resources`	`service_product_id`	`products`	`product_id`	`RESTRICT`
`project_resources`	`user_id`	`users`	`user_id`	`RESTRICT`
`project_resources`	`vendor_id`	`suppliers`	`vendor_id`	`RESTRICT`
`project_tasks`	`parent_task_id`	`project_tasks`	`project_task_id`	`RESTRICT`
`project_tasks`	`project_id`	`projects`	`project_id`	`RESTRICT`
`projects`	`customer_id`	`customers`	`customer_id`	`RESTRICT`
`projects`	`opportunity_id`	`opportunities`	`opportunity_id`	`RESTRICT`
`projects`	`project_manager_id`	`users`	`user_id`	`RESTRICT`
`projects`	`staging_bin_id`	`bins`	`bin_id`	`RESTRICT`
`projects`	`staging_location_id`	`locations`	`location_id`	`RESTRICT`
`purchase_debit_note_lines`	`account_id`	`gl_accounts`	`gl_account_id`	`RESTRICT`
`purchase_debit_note_lines`	`debit_note_id`	`purchase_debit_notes`	`debit_note_id`	`RESTRICT`
`purchase_debit_note_lines`	`purchase_order_line_id`	`purchase_order_lines`	`purchase_order_line_id`	`RESTRICT`
`purchase_debit_note_lines`	`tax_category_id`	`tax_categories`	`tax_category_id`	`RESTRICT`
`purchase_debit_note_shipments`	`debit_note_line_id`	`purchase_debit_note_lines`	`debit_note_line_id`	`RESTRICT`
`purchase_debit_note_shipments`	`shipment_line_id`	`purchase_order_return_shipment_lines`	`shipment_line_id`	`RESTRICT`
`purchase_debit_notes`	`purchase_order_id`	`purchase_orders`	`purchase_order_id`	`RESTRICT`
`purchase_debit_notes`	`return_id`	`purchase_order_returns`	`return_id`	`RESTRICT`
`purchase_debit_notes`	`vendor_id`	`suppliers`	`vendor_id`	`RESTRICT`
`purchase_invoice_lines`	`gl_account_id`	`gl_accounts`	`gl_account_id`	`RESTRICT`
`purchase_invoice_lines`	`invoice_id`	`purchase_invoices`	`invoice_id`	`RESTRICT`
`purchase_invoice_lines`	`product_id`	`products`	`product_id`	`RESTRICT`
`purchase_invoice_lines`	`purchase_order_line_id`	`purchase_order_lines`	`purchase_order_line_id`	`RESTRICT`
`purchase_invoice_receipts`	`goods_received_line_id`	`goods_received_lines`	`goods_received_line_id`	`RESTRICT`
`purchase_invoice_receipts`	`invoice_line_id`	`purchase_invoice_lines`	`invoice_line_id`	`RESTRICT`
`purchase_invoices`	`purchase_order_id`	`purchase_orders`	`purchase_order_id`	`RESTRICT`
`purchase_invoices`	`vendor_id`	`suppliers`	`vendor_id`	`RESTRICT`
`purchase_order_lines`	`product_id`	`products`	`product_id`	`RESTRICT`
`purchase_order_lines`	`purchase_order_id`	`purchase_orders`	`purchase_order_id`	`RESTRICT`
`purchase_order_lines`	`tax_category_id`	`tax_categories`	`tax_category_id`	`RESTRICT`
`purchase_order_return_lines`	`purchase_order_line_id`	`purchase_order_lines`	`purchase_order_line_id`	`RESTRICT`
`purchase_order_return_lines`	`return_id`	`purchase_order_returns`	`return_id`	`RESTRICT`
`purchase_order_return_lines`	`source_bin_id`	`bins`	`bin_id`	`RESTRICT`
`purchase_order_return_shipment_lines`	`return_line_id`	`purchase_order_return_lines`	`return_line_id`	`RESTRICT`
`purchase_order_return_shipment_lines`	`shipment_id`	`purchase_order_return_shipments`	`shipment_id`	`RESTRICT`
`purchase_order_return_shipments`	`fulfillment_location_id`	`locations`	`location_id`	`RESTRICT`
`purchase_order_return_shipments`	`return_id`	`purchase_order_returns`	`return_id`	`RESTRICT`
`purchase_order_returns`	`purchase_order_id`	`purchase_orders`	`purchase_order_id`	`RESTRICT`
`purchase_orders`	`delivery_location_id`	`locations`	`location_id`	`RESTRICT`
`purchase_orders`	`vendor_id`	`suppliers`	`vendor_id`	`RESTRICT`
`reconciliation_rules`	`activity_id`	`activities`	`activity_id`	`RESTRICT`
`reconciliation_rules`	`cost_center_id`	`cost_centers`	`cost_center_id`	`RESTRICT`
`reconciliation_rules`	`target_gl_account_id`	`gl_accounts`	`gl_account_id`	`RESTRICT`
`sales_credit_note_lines`	`account_id`	`gl_accounts`	`gl_account_id`	`RESTRICT`
`sales_credit_note_lines`	`credit_note_id`	`sales_credit_notes`	`credit_note_id`	`RESTRICT`
`sales_credit_note_lines`	`sales_order_line_id`	`sales_order_lines`	`sales_order_line_id`	`RESTRICT`
`sales_credit_note_lines`	`tax_category_id`	`tax_categories`	`tax_category_id`	`RESTRICT`
`sales_credit_notes`	`customer_id`	`customers`	`customer_id`	`RESTRICT`
`sales_credit_notes`	`invoice_id`	`sales_invoices`	`invoice_id`	`RESTRICT`
`sales_credit_notes`	`return_id`	`sales_order_returns`	`return_id`	`RESTRICT`
`sales_credit_notes`	`sales_order_id`	`sales_orders`	`sales_order_id`	`RESTRICT`
`sales_invoice_lines`	`invoice_id`	`sales_invoices`	`invoice_id`	`RESTRICT`
`sales_invoice_lines`	`sales_order_line_id`	`sales_order_lines`	`sales_order_line_id`	`RESTRICT`
`sales_invoices`	`customer_id`	`customers`	`customer_id`	`RESTRICT`
`sales_invoices`	`sales_order_id`	`sales_orders`	`sales_order_id`	`RESTRICT`
`sales_order_lines`	`fulfillment_location_id`	`locations`	`location_id`	`RESTRICT`
`sales_order_lines`	`parent_line_id`	`sales_order_lines`	`sales_order_line_id`	`RESTRICT`
`sales_order_lines`	`product_id`	`products`	`product_id`	`RESTRICT`
`sales_order_lines`	`sales_order_id`	`sales_orders`	`sales_order_id`	`RESTRICT`
`sales_order_lines`	`tax_category_id`	`tax_categories`	`tax_category_id`	`RESTRICT`
`sales_order_picks`	`bin_id`	`bins`	`bin_id`	`RESTRICT`
`sales_order_picks`	`product_id`	`products`	`product_id`	`RESTRICT`
`sales_order_picks`	`sales_order_id`	`sales_orders`	`sales_order_id`	`RESTRICT`
`sales_order_picks`	`sales_order_line_id`	`sales_order_lines`	`sales_order_line_id`	`RESTRICT`
`sales_order_return_lines`	`return_id`	`sales_order_returns`	`return_id`	`RESTRICT`
`sales_order_return_lines`	`sales_order_line_id`	`sales_order_lines`	`sales_order_line_id`	`RESTRICT`
`sales_order_return_lines`	`tax_category_id`	`tax_categories`	`tax_category_id`	`RESTRICT`
`sales_order_returns`	`location_id`	`locations`	`location_id`	`RESTRICT`
`sales_order_returns`	`sales_order_id`	`sales_orders`	`sales_order_id`	`RESTRICT`
`sales_order_shipment_lines`	`sales_order_line_id`	`sales_order_lines`	`sales_order_line_id`	`RESTRICT`
`sales_order_shipment_lines`	`shipment_id`	`sales_order_shipments`	`shipment_id`	`RESTRICT`
`sales_order_shipments`	`fulfillment_location_id`	`locations`	`location_id`	`RESTRICT`
`sales_order_shipments`	`sales_order_id`	`sales_orders`	`sales_order_id`	`RESTRICT`
`sales_orders`	`customer_id`	`customers`	`customer_id`	`RESTRICT`
`sales_orders`	`fulfillment_location_id`	`locations`	`location_id`	`RESTRICT`
`sales_orders`	`opportunity_id`	`opportunities`	`opportunity_id`	`RESTRICT`
`stocktake_counts`	`bin_id`	`bins`	`bin_id`	`RESTRICT`
`stocktake_counts`	`product_id`	`products`	`product_id`	`RESTRICT`
`stocktake_counts`	`stocktake_id`	`stocktakes`	`stocktake_id`	`cascade`
`stocktake_counts`	`stocktake_line_id`	`stocktake_lines`	`stocktake_line_id`	`cascade`
`stocktake_lines`	`bin_id`	`bins`	`bin_id`	`RESTRICT`
`stocktake_lines`	`product_id`	`products`	`product_id`	`RESTRICT`
`stocktake_lines`	`stocktake_id`	`stocktakes`	`stocktake_id`	`cascade`
`stocktakes`	`inventory_entry_id`	`inventory_entries`	`entry_id`	`RESTRICT`
`stocktakes`	`location_id`	`locations`	`location_id`	`RESTRICT`
`supplier_expiries`	`vendor_id`	`suppliers`	`vendor_id`	`RESTRICT`
`supplier_groups`	`default_activity_id`	`activities`	`activity_id`	`RESTRICT`
`supplier_groups`	`default_ap_account_id`	`gl_accounts`	`gl_account_id`	`RESTRICT`
`supplier_groups`	`default_cost_center_id`	`cost_centers`	`cost_center_id`	`RESTRICT`
`supplier_groups`	`default_expense_account_id`	`gl_accounts`	`gl_account_id`	`RESTRICT`
`supplier_groups`	`tax_position_id`	`tax_positions`	`tax_position_id`	`RESTRICT`
`supplier_groups`	`trading_terms_id`	`trading_terms`	`trading_terms_id`	`RESTRICT`
`suppliers`	`organization_id`	`organizations`	`organization_id`	`RESTRICT`
`suppliers`	`supplier_group_id`	`supplier_groups`	`supplier_group_id`	`RESTRICT`
`suppliers`	`tax_position_id`	`tax_positions`	`tax_position_id`	`RESTRICT`
`suppliers`	`trading_terms_id`	`trading_terms`	`trading_terms_id`	`RESTRICT`
`tax_position_mappings`	`destination_tax_category_id`	`tax_categories`	`tax_category_id`	`cascade`
`tax_position_mappings`	`source_tax_category_id`	`tax_categories`	`tax_category_id`	`cascade`
`tax_position_mappings`	`tax_position_id`	`tax_positions`	`tax_position_id`	`cascade`
`transfer_order_lines`	`product_id`	`products`	`product_id`	`RESTRICT`
`transfer_order_lines`	`project_task_id`	`project_tasks`	`project_task_id`	`RESTRICT`
`transfer_order_lines`	`transfer_order_id`	`transfer_orders`	`transfer_order_id`	`RESTRICT`
`transfer_order_picks`	`bin_id`	`bins`	`bin_id`	`RESTRICT`
`transfer_order_picks`	`product_id`	`products`	`product_id`	`RESTRICT`
`transfer_order_picks`	`transfer_order_id`	`transfer_orders`	`transfer_order_id`	`RESTRICT`
`transfer_order_picks`	`transfer_order_line_id`	`transfer_order_lines`	`transfer_order_line_id`	`RESTRICT`
`transfer_order_receipt_lines`	`bin_id`	`bins`	`bin_id`	`RESTRICT`
`transfer_order_receipt_lines`	`product_id`	`products`	`product_id`	`RESTRICT`
`transfer_order_receipt_lines`	`receipt_id`	`transfer_order_receipts`	`receipt_id`	`RESTRICT`
`transfer_order_receipt_lines`	`transfer_order_line_id`	`transfer_order_lines`	`transfer_order_line_id`	`RESTRICT`
`transfer_order_receipts`	`transfer_order_id`	`transfer_orders`	`transfer_order_id`	`RESTRICT`
`transfer_order_shipment_lines`	`pick_id`	`transfer_order_picks`	`pick_id`	`RESTRICT`
`transfer_order_shipment_lines`	`product_id`	`products`	`product_id`	`RESTRICT`
`transfer_order_shipment_lines`	`shipment_id`	`transfer_order_shipments`	`shipment_id`	`RESTRICT`
`transfer_order_shipment_lines`	`transfer_order_line_id`	`transfer_order_lines`	`transfer_order_line_id`	`RESTRICT`
`transfer_order_shipments`	`transfer_order_id`	`transfer_orders`	`transfer_order_id`	`RESTRICT`
`transfer_orders`	`destination_location_id`	`locations`	`location_id`	`RESTRICT`
`transfer_orders`	`project_id`	`projects`	`project_id`	`RESTRICT`
`transfer_orders`	`project_task_id`	`project_tasks`	`project_task_id`	`RESTRICT`
`transfer_orders`	`source_location_id`	`locations`	`location_id`	`RESTRICT`
`user_settings`	`user_id`	`users`	`user_id`	`cascade`
`user_two_factor`	`user_id`	`users`	`user_id`	`cascade`
`work_order_components`	`product_id`	`products`	`product_id`	`RESTRICT`
`work_order_components`	`work_order_id`	`work_orders`	`work_order_id`	`RESTRICT`
`work_order_picks`	`bin_id`	`bins`	`bin_id`	`RESTRICT`
`work_order_picks`	`work_order_component_id`	`work_order_components`	`work_order_component_id`	`RESTRICT`
`work_order_picks`	`work_order_id`	`work_orders`	`work_order_id`	`RESTRICT`
`work_orders`	`location_id`	`locations`	`location_id`	`RESTRICT`
`work_orders`	`output_bin_id`	`bins`	`bin_id`	`RESTRICT`
`work_orders`	`product_id`	`products`	`product_id`	`RESTRICT`
`work_orders`	`wip_bin_id`	`bins`	`bin_id`	`RESTRICT`
`zones`	`location_id`	`locations`	`location_id`	`RESTRICT`

CRM & Stakeholders

Accounts, contacts, relationship graphs, CRM projects, customer groups, and addresses.

Table: `herobm_core.contacts`

#	Column	Data Type	Nullable	Default	Constraints & Relationships
1	`contact_id`	`uuid`	NO	`gen_random_uuid()`	🔑 `PK`
2	`state_code`	`text`	NO	—	—
3	`first_name`	`text`	YES	—	—
4	`last_name`	`text`	YES	—	—
5	`full_name`	`text`	YES	—	—
6	`job_title`	`text`	YES	—	—
7	`email`	`text`	YES	—	—
8	`phone`	`text`	YES	—	—
9	`mobile`	`text`	YES	—	—
10	`linkedin_profile`	`text`	YES	—	—
11	`referred_by_organization_id`	`uuid`	YES	—	🔗 `organizations.organization_id`
12	`referred_by_contact_id`	`uuid`	YES	—	🔗 `contacts.contact_id`
13	`metadata`	`jsonb`	YES	—	—
14	`created_on`	`timestamp with time zone`	YES	`now()`	—
15	`modified_on`	`timestamp with time zone`	YES	`now()`	—

Table: `herobm_core.crm_activities`

#	Column	Data Type	Nullable	Default	Constraints & Relationships
1	`activity_id`	`uuid`	NO	`gen_random_uuid()`	🔑 `PK`
2	`type`	`text`	NO	—	—
3	`subject`	`text`	NO	—	—
4	`description`	`text`	YES	—	—
5	`status`	`text`	NO	—	—
6	`priority`	`text`	NO	—	—
7	`organization_id`	`uuid`	YES	—	🔗 `organizations.organization_id`
8	`opportunity_id`	`uuid`	YES	—	🔗 `opportunities.opportunity_id`
9	`due_date`	`timestamp with time zone`	YES	—	—
10	`assigned_to_user_id`	`uuid`	YES	—	🔗 `users.user_id`
11	`completed_at`	`timestamp with time zone`	YES	—	—
12	`completed_by_user_id`	`uuid`	YES	—	🔗 `users.user_id`
13	`created_by`	`text`	NO	—	—
14	`created_by_id`	`uuid`	YES	—	🔗 `users.user_id`
15	`created_on`	`timestamp with time zone`	YES	`now()`	—
16	`modified_on`	`timestamp with time zone`	YES	`now()`	—

Table: `herobm_core.customer_delivery_addresses`

#	Column	Data Type	Nullable	Default	Constraints & Relationships
1	`id`	`uuid`	NO	`gen_random_uuid()`	🔑 `PK`
2	`customer_id`	`uuid`	NO	—	🔗 `customers.customer_id`
3	`address_name`	`text`	YES	—	—
4	`company_name`	`text`	YES	—	—
5	`recipient_name`	`text`	YES	—	—
6	`recipient_phone`	`text`	YES	—	—
7	`address_line1`	`text`	YES	—	—
8	`address_line2`	`text`	YES	—	—
9	`city`	`text`	YES	—	—
10	`state_or_province`	`text`	YES	—	—
11	`postal_code`	`text`	YES	—	—
12	`country`	`text`	YES	—	—
13	`is_primary`	`boolean`	NO	—	—
14	`source_id`	`text`	YES	—	—
15	`source`	`text`	NO	—	—
16	`created_on`	`timestamp with time zone`	YES	`now()`	—
17	`modified_on`	`timestamp with time zone`	YES	`now()`	—

Table: `herobm_core.customer_groups`

#	Column	Data Type	Nullable	Default	Constraints & Relationships
1	`customer_group_id`	`uuid`	NO	`gen_random_uuid()`	🔑 `PK`
2	`group_code`	`text`	NO	—	⚡ `UNIQUE`
3	`name`	`text`	NO	—	—
4	`state_code`	`text`	NO	—	—
5	`default_ar_account_id`	`uuid`	YES	—	🔗 `gl_accounts.gl_account_id`
6	`default_revenue_account_id`	`uuid`	YES	—	🔗 `gl_accounts.gl_account_id`
7	`trading_terms_id`	`uuid`	YES	—	🔗 `trading_terms.trading_terms_id`
8	`default_cost_center_id`	`uuid`	YES	—	🔗 `cost_centers.cost_center_id`
9	`default_activity_id`	`uuid`	YES	—	🔗 `activities.activity_id`
10	`early_payment_discount`	`numeric`	YES	—	—
11	`early_payment_discount_days`	`integer`	YES	—	—
12	`credit_limit`	`numeric`	YES	—	—
13	`is_on_credit_hold`	`boolean`	NO	—	—
14	`tax_position_id`	`uuid`	YES	—	🔗 `tax_positions.tax_position_id`

Table: `herobm_core.customers`

#	Column	Data Type	Nullable	Default	Constraints & Relationships
1	`customer_id`	`uuid`	NO	`gen_random_uuid()`	🔑 `PK`
2	`customer_number`	`text`	NO	—	⚡ `UNIQUE`
3	`customer_group_id`	`uuid`	YES	—	🔗 `customer_groups.customer_group_id`
4	`organization_id`	`uuid`	YES	—	🔗 `organizations.organization_id`
5	`state_code`	`text`	NO	—	—
6	`tax_position_id`	`uuid`	YES	—	🔗 `tax_positions.tax_position_id`
7	`currency_code`	`text`	NO	—	🏷️ `CHECK`
8	`trading_terms_id`	`uuid`	YES	—	🔗 `trading_terms.trading_terms_id`
9	`early_payment_discount`	`numeric`	YES	—	—
10	`early_payment_discount_days`	`integer`	YES	—	—
11	`credit_limit`	`numeric`	YES	—	—
12	`is_on_credit_hold`	`boolean`	YES	—	—
13	`override_credit_hold_until`	`timestamp with time zone`	YES	—	—
14	`bank_account_name`	`text`	YES	—	—
15	`bank_bsb`	`text`	YES	—	—
16	`bank_account_number`	`text`	YES	—	—
17	`external_id`	`text`	YES	—	—
18	`source_id`	`text`	YES	—	⚡ `UNIQUE`
19	`source`	`text`	NO	—	—
20	`price_tier`	`text`	YES	—	—
21	`notes`	`text`	YES	—	—
22	`metadata`	`jsonb`	YES	—	—
23	`created_by`	`text`	YES	—	—
24	`created_on`	`timestamp with time zone`	YES	`now()`	—
25	`modified_on`	`timestamp with time zone`	YES	`now()`	—

Table: `herobm_core.opportunities`

#	Column	Data Type	Nullable	Default	Constraints & Relationships
1	`opportunity_id`	`uuid`	NO	`gen_random_uuid()`	🔑 `PK`
2	`state_code`	`text`	NO	—	—
3	`name`	`text`	NO	—	—
4	`status`	`text`	NO	—	—
5	`type`	`text`	NO	—	—
6	`estimated_value`	`numeric`	YES	—	—
7	`currency_code`	`text`	YES	—	—
8	`target_close_date`	`timestamp with time zone`	YES	—	—
9	`probability`	`integer`	YES	—	—
10	`actual_value`	`numeric`	YES	—	—
11	`description`	`text`	YES	—	—
12	`metadata`	`jsonb`	YES	—	—
13	`owner_id`	`uuid`	YES	—	🔗 `users.user_id`
14	`created_on`	`timestamp with time zone`	YES	`now()`	—
15	`modified_on`	`timestamp with time zone`	YES	`now()`	—

Table: `herobm_core.opportunity_contacts`

#	Column	Data Type	Nullable	Default	Constraints & Relationships
1	`opportunity_contact_id`	`uuid`	NO	`gen_random_uuid()`	🔑 `PK`
2	`opportunity_id`	`uuid`	NO	—	🔗 `opportunities.opportunity_id`
3	`contact_id`	`uuid`	NO	—	🔗 `contacts.contact_id`
4	`roles`	`text[]`	YES	—	—
5	`created_on`	`timestamp with time zone`	YES	`now()`	—

Table: `herobm_core.opportunity_notes`

#	Column	Data Type	Nullable	Default	Constraints & Relationships
1	`note_id`	`uuid`	NO	`gen_random_uuid()`	🔑 `PK`
2	`opportunity_id`	`uuid`	NO	—	🔗 `opportunities.opportunity_id`
3	`content`	`text`	NO	—	—
4	`created_by_id`	`uuid`	YES	—	🔗 `users.user_id`
5	`created_on`	`timestamp with time zone`	YES	`now()`	—

Table: `herobm_core.opportunity_organizations`

#	Column	Data Type	Nullable	Default	Constraints & Relationships
1	`opportunity_organization_id`	`uuid`	NO	`gen_random_uuid()`	🔑 `PK`
2	`opportunity_id`	`uuid`	NO	—	🔗 `opportunities.opportunity_id`
3	`organization_id`	`uuid`	NO	—	🔗 `organizations.organization_id`
4	`roles`	`text[]`	YES	—	—
5	`created_on`	`timestamp with time zone`	YES	`now()`	—

Table: `herobm_core.organization_contact_links`

#	Column	Data Type	Nullable	Default	Constraints & Relationships
1	`link_id`	`uuid`	NO	`gen_random_uuid()`	🔑 `PK`
2	`organization_id`	`uuid`	NO	—	🔗 `organizations.organization_id`
3	`contact_id`	`uuid`	NO	—	🔗 `contacts.contact_id`
4	`link_type`	`text`	NO	—	—
5	`primary_for`	`text[]`	YES	—	—
6	`created_on`	`timestamp with time zone`	YES	`now()`	—

Table: `herobm_core.organization_notes`

#	Column	Data Type	Nullable	Default	Constraints & Relationships
1	`note_id`	`uuid`	NO	`gen_random_uuid()`	🔑 `PK`
2	`organization_id`	`uuid`	NO	—	🔗 `organizations.organization_id`
3	`content`	`text`	NO	—	—
4	`created_by_id`	`uuid`	YES	—	🔗 `users.user_id`
5	`created_on`	`timestamp with time zone`	YES	`now()`	—

Table: `herobm_core.organization_organization_links`

#	Column	Data Type	Nullable	Default	Constraints & Relationships
1	`link_id`	`uuid`	NO	`gen_random_uuid()`	🔑 `PK`
2	`source_organization_id`	`uuid`	NO	—	🔗 `organizations.organization_id`
3	`target_organization_id`	`uuid`	NO	—	🔗 `organizations.organization_id`
4	`link_type`	`text`	NO	—	—
5	`created_on`	`timestamp with time zone`	YES	`now()`	—

Table: `herobm_core.organizations`

#	Column	Data Type	Nullable	Default	Constraints & Relationships
1	`organization_id`	`uuid`	NO	`gen_random_uuid()`	🔑 `PK`
2	`state_code`	`text`	NO	—	—
3	`name`	`text`	NO	—	—
4	`owner_id`	`uuid`	YES	—	🔗 `users.user_id`
5	`legal_status`	`text`	YES	—	—
6	`headquarters_address_line1`	`text`	YES	—	—
7	`headquarters_address_line2`	`text`	YES	—	—
8	`headquarters_city`	`text`	YES	—	—
9	`headquarters_state_or_province`	`text`	YES	—	—
10	`headquarters_postal_code`	`text`	YES	—	—
11	`headquarters_country`	`text`	YES	—	—
12	`website`	`text`	YES	—	—
13	`industry`	`text`	YES	—	—
14	`telephone`	`text`	YES	—	—
15	`fax`	`text`	YES	—	—
16	`email`	`text`	YES	—	—
17	`business_number`	`text`	YES	—	—
18	`is_tax_registered`	`boolean`	NO	—	—
19	`referral_mode`	`text`	YES	—	—
20	`referred_by_organization_id`	`uuid`	YES	—	🔗 `organizations.organization_id`
21	`referred_by_contact_id`	`uuid`	YES	—	🔗 `contacts.contact_id`
22	`referral_note`	`text`	YES	—	—
23	`tags`	`text[]`	YES	—	—
24	`metadata`	`jsonb`	YES	—	—
25	`created_on`	`timestamp with time zone`	YES	`now()`	—
26	`modified_on`	`timestamp with time zone`	YES	`now()`	—

Table: `herobm_core.project_notes`

#	Column	Data Type	Nullable	Default	Constraints & Relationships
1	`note_id`	`uuid`	NO	`gen_random_uuid()`	🔑 `PK`
2	`project_id`	`uuid`	NO	—	🔗 `projects.project_id` (cascade)
3	`content`	`text`	NO	—	—
4	`created_by_id`	`uuid`	YES	—	🔗 `users.user_id`
5	`created_on`	`timestamp with time zone`	YES	`now()`	—

Table: `herobm_core.projects`

#	Column	Data Type	Nullable	Default	Constraints & Relationships
1	`project_id`	`uuid`	NO	`gen_random_uuid()`	🔑 `PK`
2	`project_number`	`text`	NO	—	⚡ `UNIQUE`
3	`name`	`text`	NO	—	—
4	`description`	`text`	YES	—	—
5	`customer_id`	`uuid`	NO	—	🔗 `customers.customer_id`
6	`opportunity_id`	`uuid`	YES	—	🔗 `opportunities.opportunity_id`
7	`state_code`	`text`	NO	—	—
8	`stage`	`text`	YES	—	—
9	`billing_type`	`text`	NO	—	—
10	`project_manager_id`	`uuid`	YES	—	🔗 `users.user_id`
11	`currency_code`	`text`	NO	—	🏷️ `CHECK`
12	`staging_location_id`	`uuid`	YES	—	🔗 `locations.location_id`
13	`staging_bin_id`	`uuid`	YES	—	🔗 `bins.bin_id`
14	`start_date`	`timestamp with time zone`	YES	—	—
15	`target_end_date`	`timestamp with time zone`	YES	—	—
16	`actual_end_date`	`timestamp with time zone`	YES	—	—
17	`wip_method`	`text`	NO	—	—
18	`notes`	`text`	YES	—	—
19	`metadata`	`jsonb`	YES	—	—
20	`discount_percentage`	`numeric(5, 2)`	YES	—	—
21	`created_by`	`text`	YES	—	—
22	`created_on`	`timestamp with time zone`	YES	`now()`	—
23	`modified_on`	`timestamp with time zone`	YES	`now()`	—

Table: `herobm_core.trading_terms`

#	Column	Data Type	Nullable	Default	Constraints & Relationships
1	`trading_terms_id`	`uuid`	NO	`gen_random_uuid()`	🔑 `PK`
2	`source_id`	`text`	YES	—	—
3	`source`	`text`	YES	—	—
4	`code`	`text`	NO	—	⚡ `UNIQUE`
5	`description`	`text`	NO	—	—
6	`days`	`integer`	NO	—	—
7	`type`	`text`	NO	—	—
8	`is_active`	`boolean`	NO	—	—
9	`created_on`	`timestamp with time zone`	YES	`now()`	—
10	`modified_on`	`timestamp with time zone`	YES	`now()`	—

Products & Catalog

Item masters, product groups, units of measure, supplier pricing matrix, and bills of materials.

Table: `herobm_core.discount_matrix`

#	Column	Data Type	Nullable	Default	Constraints & Relationships
1	`discount_matrix_id`	`uuid`	NO	`gen_random_uuid()`	🔑 `PK`
2	`customer_group_id`	`uuid`	YES	—	🔗 `customer_groups.customer_group_id`, ⚡ `UNIQUE`, 🏷️ `CHECK`
3	`customer_id`	`uuid`	YES	—	🔗 `customers.customer_id`, ⚡ `UNIQUE`, 🏷️ `CHECK`
4	`product_group_id`	`uuid`	YES	—	🔗 `product_groups.product_group_id`, ⚡ `UNIQUE`, ⚡ `UNIQUE`
5	`discount_percentage`	`numeric`	NO	—	—
6	`created_on`	`timestamp with time zone`	YES	`now()`	—
7	`modified_on`	`timestamp with time zone`	YES	`now()`	—

Table: `herobm_core.product_components`

#	Column	Data Type	Nullable	Default	Constraints & Relationships
1	`component_id`	`uuid`	NO	`gen_random_uuid()`	🔑 `PK`
2	`parent_product_id`	`uuid`	NO	—	🔗 `products.product_id`
3	`child_product_id`	`uuid`	NO	—	🔗 `products.product_id`
4	`parent_quantity`	`numeric(14, 4)`	NO	—	—
5	`quantity`	`numeric(14, 4)`	NO	—	—
6	`sequence_number`	`integer`	YES	—	—
7	`fractional_behavior`	`fractional_behavior`	YES	—	—

Table: `herobm_core.product_default_bins`

#	Column	Data Type	Nullable	Default	Constraints & Relationships
1	`product_default_bin_id`	`uuid`	NO	`gen_random_uuid()`	🔑 `PK`
2	`product_id`	`uuid`	NO	—	🔗 `products.product_id`, ⚡ `UNIQUE`
3	`location_id`	`uuid`	NO	—	🔗 `locations.location_id`, ⚡ `UNIQUE`
4	`bin_id`	`uuid`	NO	—	🔗 `bins.bin_id`, ⚡ `UNIQUE`
5	`is_primary_per_loc`	`boolean`	NO	—	—
6	`min_quantity`	`numeric`	YES	—	—
7	`max_quantity`	`numeric`	YES	—	—
8	`created_on`	`timestamp with time zone`	YES	`now()`	—
9	`modified_on`	`timestamp with time zone`	YES	`now()`	—

Table: `herobm_core.product_groups`

#	Column	Data Type	Nullable	Default	Constraints & Relationships
1	`product_group_id`	`uuid`	NO	`gen_random_uuid()`	🔑 `PK`
2	`group_code`	`text`	NO	—	⚡ `UNIQUE`
3	`name`	`text`	NO	—	—
4	`default_revenue_account_id`	`uuid`	YES	—	🔗 `gl_accounts.gl_account_id`
5	`default_expense_account_id`	`uuid`	YES	—	🔗 `gl_accounts.gl_account_id`
6	`default_cost_center_id`	`uuid`	YES	—	🔗 `cost_centers.cost_center_id`
7	`default_activity_id`	`uuid`	YES	—	🔗 `activities.activity_id`
8	`purchase_tax_category_id`	`uuid`	YES	—	🔗 `tax_categories.tax_category_id`
9	`sales_tax_category_id`	`uuid`	YES	—	🔗 `tax_categories.tax_category_id`

Table: `herobm_core.product_images`

#	Column	Data Type	Nullable	Default	Constraints & Relationships
1	`image_id`	`uuid`	NO	`gen_random_uuid()`	🔑 `PK`
2	`product_id`	`uuid`	NO	—	🔗 `products.product_id` (cascade)
3	`storage_path`	`text`	NO	—	—
4	`file_name`	`text`	NO	—	—
5	`mime_type`	`text`	NO	—	—
6	`byte_size`	`integer`	NO	—	—
7	`is_primary`	`boolean`	NO	—	—
8	`sort_order`	`integer`	NO	—	—
9	`created_by`	`text`	YES	—	—
10	`created_on`	`timestamp with time zone`	YES	`now()`	—

Table: `herobm_core.product_suppliers`

#	Column	Data Type	Nullable	Default	Constraints & Relationships
1	`product_supplier_id`	`uuid`	NO	`gen_random_uuid()`	🔑 `PK`
2	`product_id`	`uuid`	NO	—	🔗 `products.product_id`, ⚡ `UNIQUE`
3	`vendor_id`	`uuid`	NO	—	🔗 `suppliers.vendor_id`, ⚡ `UNIQUE`
4	`supplier_part_number`	`text`	YES	—	—
5	`cost_price`	`numeric`	YES	—	—
6	`discount_percent`	`numeric`	YES	—	—
7	`price_break_quantity`	`numeric`	YES	—	—
8	`is_preferred`	`boolean`	NO	—	—
9	`min_purchase_qty`	`numeric`	YES	—	—
10	`purchase_unit`	`text`	YES	—	—
11	`effective_from`	`timestamp with time zone`	YES	—	—
12	`effective_to`	`timestamp with time zone`	YES	—	—
13	`state_code`	`text`	NO	—	—
14	`source_id`	`text`	YES	—	⚡ `UNIQUE`
15	`source`	`text`	NO	—	—
16	`created_by`	`text`	YES	—	—
17	`created_on`	`timestamp with time zone`	YES	`now()`	—
18	`modified_on`	`timestamp with time zone`	YES	`now()`	—

Table: `herobm_core.product_uoms`

#	Column	Data Type	Nullable	Default	Constraints & Relationships
1	`product_uom_id`	`uuid`	NO	`gen_random_uuid()`	🔑 `PK`
2	`product_id`	`uuid`	NO	—	🔗 `products.product_id`, ⚡ `UNIQUE`
3	`uom_code`	`text`	NO	—	🔗 `uom_dictionary.uom_code`, ⚡ `UNIQUE`
4	`ratio`	`numeric(12, 4)`	NO	—	—
5	`barcode`	`text`	YES	—	—
6	`is_sales_default`	`boolean`	YES	—	—
7	`is_purchase_default`	`boolean`	YES	—	—

Table: `herobm_core.products`

#	Column	Data Type	Nullable	Default	Constraints & Relationships
1	`product_id`	`uuid`	NO	`gen_random_uuid()`	🔑 `PK`
2	`product_number`	`text`	NO	—	⚡ `UNIQUE`
3	`name`	`text`	NO	—	—
4	`product_type`	`product_type`	NO	—	—
5	`structure_type`	`product_structure`	NO	—	—
6	`product_group_id`	`uuid`	YES	—	🔗 `product_groups.product_group_id`
7	`barcode`	`text`	YES	—	—
8	`list_price`	`numeric(12, 2)`	YES	—	—
9	`standard_cost`	`numeric(12, 2)`	YES	—	—
10	`trade_price`	`numeric(12, 2)`	YES	—	—
11	`price_level_3`	`numeric(12, 2)`	YES	—	—
12	`price_level_4`	`numeric(12, 2)`	YES	—	—
13	`weighted_average_cost`	`numeric`	YES	—	—
14	`weight`	`numeric(12, 4)`	YES	—	—
15	`alternate_invoice_description`	`text`	YES	—	—
16	`box_quantity`	`numeric`	YES	—	—
17	`base_uom`	`text`	NO	—	🔗 `uom_dictionary.uom_code`
18	`default_sales_uom_id`	`uuid`	YES	—	🔗 `product_uoms.product_uom_id`
19	`default_purchase_uom_id`	`uuid`	YES	—	🔗 `product_uoms.product_uom_id`
20	`purchase_tax_category_id`	`uuid`	YES	—	🔗 `tax_categories.tax_category_id`
21	`sales_tax_category_id`	`uuid`	YES	—	🔗 `tax_categories.tax_category_id`
22	`external_tax_code`	`text`	YES	—	—
23	`alternate_product_number`	`text`	YES	—	—
24	`image_path`	`text`	YES	—	—
25	`state_code`	`text`	NO	—	—
26	`notes`	`text`	YES	—	—
27	`metadata`	`jsonb`	YES	—	—
28	`source_id`	`text`	YES	—	⚡ `UNIQUE`
29	`source`	`text`	NO	—	—
30	`created_by`	`text`	YES	—	—
31	`created_on`	`timestamp with time zone`	YES	`now()`	—
32	`modified_on`	`timestamp with time zone`	YES	`now()`	—

Table: `herobm_core.uom_dictionary`

#	Column	Data Type	Nullable	Default	Constraints & Relationships
1	`uom_code`	`text`	NO	—	🔑 `PK`
2	`description`	`text`	NO	—	—
3	`category`	`text`	NO	—	—
4	`created_on`	`timestamp with time zone`	YES	`now()`	—

Sales & Distribution

Sales quotations, confirmed orders, pick lists, shipments, sales invoices, and customer credit notes.

Table: `herobm_core.backorders`

#	Column	Data Type	Nullable	Default	Constraints & Relationships
1	`backorder_id`	`uuid`	NO	`gen_random_uuid()`	🔑 `PK`
2	`sales_order_id`	`uuid`	YES	—	🔗 `sales_orders.sales_order_id`
3	`sales_order_line_id`	`uuid`	YES	—	🔗 `sales_order_lines.sales_order_line_id`
4	`demand_work_order_id`	`uuid`	YES	—	🔗 `work_orders.work_order_id`
5	`work_order_component_id`	`uuid`	YES	—	🔗 `work_order_components.work_order_component_id`
6	`product_id`	`uuid`	NO	—	🔗 `products.product_id`
7	`purchase_order_id`	`uuid`	YES	—	🔗 `purchase_orders.purchase_order_id`
8	`purchase_order_line_id`	`uuid`	YES	—	🔗 `purchase_order_lines.purchase_order_line_id`
9	`transfer_order_id`	`uuid`	YES	—	🔗 `transfer_orders.transfer_order_id`
10	`transfer_order_line_id`	`uuid`	YES	—	🔗 `transfer_order_lines.transfer_order_line_id`
11	`work_order_id`	`uuid`	YES	—	🔗 `work_orders.work_order_id`
12	`quantity`	`numeric`	NO	—	—
13	`state_code`	`text`	NO	—	—
14	`created_on`	`timestamp with time zone`	YES	`now()`	—
15	`modified_on`	`timestamp with time zone`	YES	`now()`	—

Table: `herobm_core.sales_credit_note_lines`

#	Column	Data Type	Nullable	Default	Constraints & Relationships
1	`credit_note_line_id`	`uuid`	NO	`gen_random_uuid()`	🔑 `PK`
2	`credit_note_id`	`uuid`	NO	—	🔗 `sales_credit_notes.credit_note_id`
3	`sales_order_line_id`	`uuid`	YES	—	🔗 `sales_order_lines.sales_order_line_id`
4	`description`	`text`	YES	—	—
5	`account_id`	`uuid`	YES	—	🔗 `gl_accounts.gl_account_id`
6	`tax_category_id`	`uuid`	YES	—	🔗 `tax_categories.tax_category_id`
7	`quantity_credited`	`numeric`	NO	—	—
8	`price_per_unit`	`numeric`	NO	—	—
9	`discount_percentage`	`numeric`	YES	—	—
10	`amount`	`numeric`	NO	—	—
11	`tax_amount`	`numeric`	YES	—	—
12	`product_number`	`text`	YES	—	—
13	`product_name`	`text`	YES	—	—

Table: `herobm_core.sales_credit_notes`

#	Column	Data Type	Nullable	Default	Constraints & Relationships
1	`credit_note_id`	`uuid`	NO	`gen_random_uuid()`	🔑 `PK`
2	`credit_note_number`	`text`	NO	—	⚡ `UNIQUE`
3	`customer_id`	`uuid`	NO	—	🔗 `customers.customer_id`
4	`return_id`	`uuid`	YES	—	🔗 `sales_order_returns.return_id`
5	`sales_order_id`	`uuid`	YES	—	🔗 `sales_orders.sales_order_id`
6	`invoice_id`	`uuid`	YES	—	🔗 `sales_invoices.invoice_id`
7	`total_amount`	`numeric`	NO	—	—
8	`tax_amount`	`numeric`	YES	—	—
9	`fee_amount`	`numeric`	YES	—	—
10	`outstanding_amount`	`numeric`	NO	—	—
11	`base_total_amount`	`numeric`	YES	—	—
12	`base_outstanding_amount`	`numeric`	YES	—	—
13	`currency_code`	`text`	NO	—	🏷️ `CHECK`
14	`exchange_rate`	`numeric`	NO	—	—
15	`state_code`	`text`	NO	—	—
16	`notes`	`text`	YES	—	—
17	`metadata`	`jsonb`	YES	—	—
18	`created_by`	`text`	YES	—	—
19	`created_on`	`timestamp with time zone`	YES	`now()`	—
20	`modified_on`	`timestamp with time zone`	YES	`now()`	—

Table: `herobm_core.sales_events`

#	Column	Data Type	Nullable	Default	Constraints & Relationships
1	`event_id`	`uuid`	NO	`gen_random_uuid()`	🔑 `PK`
2	`entity_type`	`text`	NO	—	—
3	`entity_id`	`uuid`	NO	—	—
4	`event_type`	`text`	NO	—	—
5	`entity_display_name`	`text`	YES	—	—
6	`payload`	`jsonb`	YES	—	—
7	`actor`	`text`	YES	—	—
8	`created_on`	`timestamp with time zone`	YES	`now()`	—

Table: `herobm_core.sales_invoice_lines`

#	Column	Data Type	Nullable	Default	Constraints & Relationships
1	`invoice_line_id`	`uuid`	NO	`gen_random_uuid()`	🔑 `PK`
2	`invoice_id`	`uuid`	NO	—	🔗 `sales_invoices.invoice_id`
3	`sales_order_line_id`	`uuid`	NO	—	🔗 `sales_order_lines.sales_order_line_id`
4	`quantity_invoiced`	`numeric`	NO	—	—
5	`price_per_unit`	`numeric`	NO	—	—
6	`amount`	`numeric`	NO	—	—

Table: `herobm_core.sales_invoices`

#	Column	Data Type	Nullable	Default	Constraints & Relationships
1	`invoice_id`	`uuid`	NO	`gen_random_uuid()`	🔑 `PK`
2	`invoice_number`	`text`	NO	—	⚡ `UNIQUE`
3	`sales_order_id`	`uuid`	NO	—	🔗 `sales_orders.sales_order_id`
4	`customer_id`	`uuid`	YES	—	🔗 `customers.customer_id`
5	`customer_name_display`	`text`	YES	—	—
6	`customer_order_number`	`text`	YES	—	—
7	`total_amount`	`numeric`	NO	—	—
8	`outstanding_amount`	`numeric`	NO	—	—
9	`tax_amount`	`numeric`	YES	—	—
10	`base_total_amount`	`numeric`	YES	—	—
11	`base_outstanding_amount`	`numeric`	YES	—	—
12	`currency_code`	`text`	NO	—	🏷️ `CHECK`
13	`exchange_rate`	`numeric`	NO	—	—
14	`state_code`	`text`	NO	—	—
15	`invoice_date`	`timestamp with time zone`	YES	—	—
16	`due_date`	`timestamp with time zone`	YES	—	—
17	`terms_description`	`text`	YES	—	—
18	`notes`	`text`	YES	—	—
19	`metadata`	`jsonb`	YES	—	—
20	`early_payment_discount`	`numeric`	YES	—	—
21	`early_payment_discount_days`	`integer`	YES	—	—
22	`created_by`	`text`	YES	—	—
23	`created_on`	`timestamp with time zone`	YES	`now()`	—
24	`modified_on`	`timestamp with time zone`	YES	`now()`	—

Table: `herobm_core.sales_order_lines`

#	Column	Data Type	Nullable	Default	Constraints & Relationships
1	`sales_order_line_id`	`uuid`	NO	`gen_random_uuid()`	🔑 `PK`
2	`sales_order_id`	`uuid`	NO	—	🔗 `sales_orders.sales_order_id`
3	`line_number`	`integer`	NO	—	—
4	`line_type`	`text`	YES	—	🏷️ `CHECK`
5	`product_id`	`uuid`	YES	—	🔗 `products.product_id`
6	`product_description`	`text`	YES	—	—
7	`quantity`	`numeric`	NO	—	—
8	`price_per_unit`	`numeric`	NO	—	—
9	`unit_cost`	`numeric`	YES	—	—
10	`discount_percentage`	`numeric`	YES	—	—
11	`amount`	`numeric`	YES	—	—
12	`tax_category_id`	`uuid`	YES	—	🔗 `tax_categories.tax_category_id`, 🏷️ `CHECK`
13	`tax`	`numeric`	YES	—	🏷️ `CHECK`
14	`total_amount`	`numeric`	YES	—	—
15	`unit_of_measure`	`text`	YES	—	—
16	`quantity_picked`	`numeric`	YES	—	—
17	`fulfillment_location_id`	`uuid`	YES	—	🔗 `locations.location_id`, 🏷️ `CHECK`
18	`is_post_confirmation`	`boolean`	YES	—	—
19	`parent_line_id`	`uuid`	YES	—	🔗 `sales_order_lines.sales_order_line_id`

Table: `herobm_core.sales_order_picks`

#	Column	Data Type	Nullable	Default	Constraints & Relationships
1	`pick_id`	`uuid`	NO	`gen_random_uuid()`	🔑 `PK`
2	`sales_order_id`	`uuid`	NO	—	🔗 `sales_orders.sales_order_id`
3	`sales_order_line_id`	`uuid`	NO	—	🔗 `sales_order_lines.sales_order_line_id`
4	`product_id`	`uuid`	NO	—	🔗 `products.product_id`
5	`bin_id`	`uuid`	YES	—	🔗 `bins.bin_id`
6	`quantity`	`numeric`	NO	—	—
7	`state_code`	`text`	NO	—	🏷️ `CHECK`
8	`created_by`	`text`	YES	—	—
9	`created_on`	`timestamp with time zone`	YES	`now()`	—
10	`modified_on`	`timestamp with time zone`	YES	`now()`	—

Table: `herobm_core.sales_order_return_lines`

#	Column	Data Type	Nullable	Default	Constraints & Relationships
1	`return_line_id`	`uuid`	NO	`gen_random_uuid()`	🔑 `PK`
2	`return_id`	`uuid`	NO	—	🔗 `sales_order_returns.return_id`
3	`sales_order_line_id`	`uuid`	NO	—	🔗 `sales_order_lines.sales_order_line_id`
4	`quantity_returned`	`numeric`	NO	—	—
5	`quantity_received`	`numeric`	YES	—	—
6	`reason`	`text`	YES	—	—
7	`resolution`	`text`	NO	—	—
8	`return_fee`	`numeric`	YES	—	—
9	`putaway_status`	`text`	NO	—	—
10	`product_number`	`text`	YES	—	—
11	`product_name`	`text`	YES	—	—
12	`price_per_unit`	`numeric`	YES	—	—
13	`discount_percentage`	`numeric`	YES	—	—
14	`tax_category_id`	`uuid`	YES	—	🔗 `tax_categories.tax_category_id`

Table: `herobm_core.sales_order_returns`

#	Column	Data Type	Nullable	Default	Constraints & Relationships
1	`return_id`	`uuid`	NO	`gen_random_uuid()`	🔑 `PK`
2	`return_number`	`text`	NO	—	⚡ `UNIQUE`
3	`sales_order_id`	`uuid`	NO	—	🔗 `sales_orders.sales_order_id`
4	`state_code`	`text`	NO	—	🏷️ `CHECK`
5	`location_id`	`uuid`	YES	—	🔗 `locations.location_id`
6	`notes`	`text`	YES	—	—
7	`created_by`	`text`	YES	—	—
8	`created_on`	`timestamp with time zone`	YES	`now()`	—
9	`modified_on`	`timestamp with time zone`	YES	`now()`	—

Table: `herobm_core.sales_order_shipment_lines`

#	Column	Data Type	Nullable	Default	Constraints & Relationships
1	`shipment_line_id`	`uuid`	NO	`gen_random_uuid()`	🔑 `PK`
2	`shipment_id`	`uuid`	NO	—	🔗 `sales_order_shipments.shipment_id`
3	`sales_order_line_id`	`uuid`	NO	—	🔗 `sales_order_lines.sales_order_line_id`
4	`quantity_shipped`	`numeric`	NO	—	—

Table: `herobm_core.sales_order_shipments`

#	Column	Data Type	Nullable	Default	Constraints & Relationships
1	`shipment_id`	`uuid`	NO	`gen_random_uuid()`	🔑 `PK`
2	`shipment_number`	`text`	NO	—	⚡ `UNIQUE`
3	`sales_order_id`	`uuid`	NO	—	🔗 `sales_orders.sales_order_id`
4	`state_code`	`text`	NO	—	🏷️ `CHECK`
5	`notes`	`text`	YES	—	—
6	`shipping_notes`	`text`	YES	—	—
7	`tracking_number`	`text`	YES	—	—
8	`delivery_company_name`	`text`	YES	—	—
9	`fulfillment_location_id`	`uuid`	YES	—	🔗 `locations.location_id`
10	`created_by`	`text`	YES	—	—
11	`created_on`	`timestamp with time zone`	YES	`now()`	—
12	`modified_on`	`timestamp with time zone`	YES	`now()`	—

Table: `herobm_core.sales_orders`

#	Column	Data Type	Nullable	Default	Constraints & Relationships
1	`sales_order_id`	`uuid`	NO	`gen_random_uuid()`	🔑 `PK`
2	`order_number`	`text`	NO	—	⚡ `UNIQUE`
3	`name`	`text`	YES	—	—
4	`customer_id`	`uuid`	YES	—	🔗 `customers.customer_id`
5	`customer_order_number`	`text`	YES	—	—
6	`fulfillment_location_id`	`uuid`	NO	—	🔗 `locations.location_id`
7	`state_code`	`text`	NO	—	🏷️ `CHECK`
8	`base_total_amount`	`numeric`	YES	—	—
9	`currency_code`	`text`	NO	—	🏷️ `CHECK`
10	`exchange_rate`	`numeric`	NO	—	—
11	`notes`	`text`	YES	—	—
12	`shipping_notes`	`text`	YES	—	—
13	`delivery_company_name`	`text`	YES	—	—
14	`delivery_name`	`text`	YES	—	—
15	`delivery_phone`	`text`	YES	—	—
16	`delivery_address_line1`	`text`	YES	—	—
17	`delivery_address_line2`	`text`	YES	—	—
18	`delivery_city`	`text`	YES	—	—
19	`delivery_state`	`text`	YES	—	—
20	`delivery_postal_code`	`text`	YES	—	—
21	`delivery_country`	`text`	YES	—	—
22	`custom_fields`	`jsonb`	YES	—	—
23	`metadata`	`jsonb`	YES	—	—
24	`discrepancies_acknowledged`	`boolean`	NO	—	—
25	`source_id`	`text`	YES	—	⚡ `UNIQUE`
26	`source`	`text`	NO	—	—
27	`terms_description`	`text`	YES	—	—
28	`credit_hold_override_at`	`timestamp with time zone`	YES	—	—
29	`credit_hold_override_by`	`text`	YES	—	—
30	`credit_hold_override_reason`	`text`	YES	—	—
31	`opportunity_id`	`uuid`	YES	—	🔗 `opportunities.opportunity_id`
32	`created_by`	`text`	YES	—	—
33	`created_on`	`timestamp with time zone`	YES	`now()`	—
34	`modified_on`	`timestamp with time zone`	YES	`now()`	—

Purchasing & Procurement

Purchase orders, goods receipts, purchase bills, vendor debit notes, returns, and supplier masters.

Table: `herobm_core.goods_received`

#	Column	Data Type	Nullable	Default	Constraints & Relationships
1	`goods_received_id`	`uuid`	NO	`gen_random_uuid()`	🔑 `PK`
2	`receipt_number`	`text`	NO	—	⚡ `UNIQUE`
3	`vendor_id`	`uuid`	NO	—	🔗 `suppliers.vendor_id`
4	`location_id`	`uuid`	NO	—	🔗 `locations.location_id`
5	`packing_slip_number`	`text`	YES	—	—
6	`notes`	`text`	YES	—	—
7	`state_code`	`text`	NO	—	—
8	`created_by`	`text`	YES	—	—
9	`created_on`	`timestamp with time zone`	YES	`now()`	—
10	`modified_on`	`timestamp with time zone`	YES	`now()`	—

Table: `herobm_core.goods_received_lines`

#	Column	Data Type	Nullable	Default	Constraints & Relationships
1	`goods_received_line_id`	`uuid`	NO	`gen_random_uuid()`	🔑 `PK`
2	`goods_received_id`	`uuid`	NO	—	🔗 `goods_received.goods_received_id`
3	`product_id`	`uuid`	NO	—	🔗 `products.product_id`
4	`quantity_received`	`numeric`	NO	—	—
5	`unit_cost`	`numeric`	YES	—	—
6	`match_status`	`text`	NO	—	—
7	`putaway_status`	`text`	NO	—	—
8	`purchase_order_line_id`	`uuid`	YES	—	🔗 `purchase_order_lines.purchase_order_line_id`
9	`purchase_order_id`	`uuid`	YES	—	🔗 `purchase_orders.purchase_order_id`

Table: `herobm_core.procurement_events`

#	Column	Data Type	Nullable	Default	Constraints & Relationships
1	`event_id`	`uuid`	NO	`gen_random_uuid()`	🔑 `PK`
2	`entity_type`	`text`	NO	—	—
3	`entity_id`	`uuid`	NO	—	—
4	`event_type`	`text`	NO	—	—
5	`entity_display_name`	`text`	YES	—	—
6	`payload`	`jsonb`	YES	—	—
7	`actor`	`text`	YES	—	—
8	`created_on`	`timestamp with time zone`	YES	`now()`	—

Table: `herobm_core.purchase_debit_note_lines`

#	Column	Data Type	Nullable	Default	Constraints & Relationships
1	`debit_note_line_id`	`uuid`	NO	`gen_random_uuid()`	🔑 `PK`
2	`debit_note_id`	`uuid`	NO	—	🔗 `purchase_debit_notes.debit_note_id`
3	`purchase_order_line_id`	`uuid`	YES	—	🔗 `purchase_order_lines.purchase_order_line_id`
4	`description`	`text`	YES	—	—
5	`account_id`	`uuid`	YES	—	🔗 `gl_accounts.gl_account_id`
6	`tax_category_id`	`uuid`	YES	—	🔗 `tax_categories.tax_category_id`
7	`quantity_invoiced`	`numeric`	NO	—	—
8	`price_per_unit`	`numeric`	NO	—	—
9	`amount`	`numeric`	NO	—	—
10	`tax_amount`	`numeric`	YES	—	—

Table: `herobm_core.purchase_debit_note_shipments`

#	Column	Data Type	Nullable	Default	Constraints & Relationships
1	`debit_note_shipment_id`	`uuid`	NO	`gen_random_uuid()`	🔑 `PK`
2	`debit_note_line_id`	`uuid`	NO	—	🔗 `purchase_debit_note_lines.debit_note_line_id`
3	`shipment_line_id`	`uuid`	NO	—	🔗 `purchase_order_return_shipment_lines.shipment_line_id`
4	`quantity_credited`	`numeric`	NO	—	—

Table: `herobm_core.purchase_debit_notes`

#	Column	Data Type	Nullable	Default	Constraints & Relationships
1	`debit_note_id`	`uuid`	NO	`gen_random_uuid()`	🔑 `PK`
2	`debit_note_number`	`text`	NO	—	⚡ `UNIQUE`
3	`supplier_reference_number`	`text`	YES	—	—
4	`return_id`	`uuid`	YES	—	🔗 `purchase_order_returns.return_id`
5	`purchase_order_id`	`uuid`	YES	—	🔗 `purchase_orders.purchase_order_id`
6	`vendor_id`	`uuid`	NO	—	🔗 `suppliers.vendor_id`
7	`total_amount`	`numeric`	NO	—	—
8	`tax_amount`	`numeric`	YES	—	—
9	`fee_amount`	`numeric`	YES	—	—
10	`outstanding_amount`	`numeric`	NO	—	—
11	`base_total_amount`	`numeric`	YES	—	—
12	`base_outstanding_amount`	`numeric`	YES	—	—
13	`currency_code`	`text`	NO	—	🏷️ `CHECK`
14	`exchange_rate`	`numeric`	NO	—	—
15	`state_code`	`text`	NO	—	🏷️ `CHECK`
16	`notes`	`text`	YES	—	—
17	`metadata`	`jsonb`	YES	—	—
18	`created_by`	`text`	YES	—	—
19	`created_on`	`timestamp with time zone`	YES	`now()`	—
20	`modified_on`	`timestamp with time zone`	YES	`now()`	—

Table: `herobm_core.purchase_invoice_lines`

#	Column	Data Type	Nullable	Default	Constraints & Relationships
1	`invoice_line_id`	`uuid`	NO	`gen_random_uuid()`	🔑 `PK`
2	`invoice_id`	`uuid`	NO	—	🔗 `purchase_invoices.invoice_id`
3	`purchase_order_line_id`	`uuid`	YES	—	🔗 `purchase_order_lines.purchase_order_line_id`
4	`product_id`	`uuid`	YES	—	🔗 `products.product_id`
5	`gl_account_id`	`uuid`	YES	—	🔗 `gl_accounts.gl_account_id`
6	`description`	`text`	YES	—	—
7	`quantity_invoiced`	`numeric`	NO	—	—
8	`price_per_unit`	`numeric`	NO	—	—
9	`amount`	`numeric`	NO	—	—
10	`match_status`	`text`	NO	—	—

Table: `herobm_core.purchase_invoice_receipts`

#	Column	Data Type	Nullable	Default	Constraints & Relationships
1	`invoice_receipt_id`	`uuid`	NO	`gen_random_uuid()`	🔑 `PK`
2	`invoice_line_id`	`uuid`	NO	—	🔗 `purchase_invoice_lines.invoice_line_id`
3	`goods_received_line_id`	`uuid`	NO	—	🔗 `goods_received_lines.goods_received_line_id`
4	`quantity_billed`	`numeric`	NO	—	—

Table: `herobm_core.purchase_invoices`

#	Column	Data Type	Nullable	Default	Constraints & Relationships
1	`invoice_id`	`uuid`	NO	`gen_random_uuid()`	🔑 `PK`
2	`invoice_number`	`text`	NO	—	⚡ `UNIQUE`
3	`vendor_id`	`uuid`	NO	—	🔗 `suppliers.vendor_id`
4	`purchase_order_id`	`uuid`	YES	—	🔗 `purchase_orders.purchase_order_id`
5	`supplier_invoice_number`	`text`	YES	—	—
6	`receipt_filename`	`text`	YES	—	—
7	`total_amount`	`numeric`	NO	—	—
8	`outstanding_amount`	`numeric`	NO	—	—
9	`tax_amount`	`numeric`	YES	—	—
10	`base_total_amount`	`numeric`	YES	—	—
11	`base_outstanding_amount`	`numeric`	YES	—	—
12	`currency_code`	`text`	NO	—	🏷️ `CHECK`
13	`exchange_rate`	`numeric`	NO	—	—
14	`state_code`	`text`	NO	—	—
15	`invoice_date`	`timestamp with time zone`	YES	—	—
16	`due_date`	`timestamp with time zone`	YES	—	—
17	`terms_description`	`text`	YES	—	—
18	`notes`	`text`	YES	—	—
19	`early_payment_discount`	`numeric`	YES	—	—
20	`early_payment_discount_days`	`integer`	YES	—	—
21	`created_by`	`text`	YES	—	—
22	`created_on`	`timestamp with time zone`	YES	`now()`	—
23	`modified_on`	`timestamp with time zone`	YES	`now()`	—

Table: `herobm_core.purchase_order_lines`

#	Column	Data Type	Nullable	Default	Constraints & Relationships
1	`purchase_order_line_id`	`uuid`	NO	`gen_random_uuid()`	🔑 `PK`
2	`purchase_order_id`	`uuid`	NO	—	🔗 `purchase_orders.purchase_order_id`
3	`line_number`	`integer`	NO	—	—
4	`line_type`	`text`	YES	—	🏷️ `CHECK`
5	`product_id`	`uuid`	YES	—	🔗 `products.product_id`
6	`product_description`	`text`	YES	—	—
7	`quantity`	`numeric`	NO	—	—
8	`price_per_unit`	`numeric`	NO	—	—
9	`discount_percentage`	`numeric`	YES	—	—
10	`amount`	`numeric`	YES	—	—
11	`tax_category_id`	`uuid`	YES	—	🔗 `tax_categories.tax_category_id`, 🏷️ `CHECK`
12	`tax`	`numeric`	YES	—	🏷️ `CHECK`
13	`total_amount`	`numeric`	YES	—	—
14	`unit_of_measure`	`text`	YES	—	—
15	`quantity_received`	`numeric`	YES	—	—

Table: `herobm_core.purchase_order_return_lines`

#	Column	Data Type	Nullable	Default	Constraints & Relationships
1	`return_line_id`	`uuid`	NO	`gen_random_uuid()`	🔑 `PK`
2	`return_id`	`uuid`	NO	—	🔗 `purchase_order_returns.return_id`
3	`purchase_order_line_id`	`uuid`	NO	—	🔗 `purchase_order_lines.purchase_order_line_id`
4	`quantity_returned`	`numeric`	NO	—	—
5	`reason`	`text`	YES	—	—
6	`return_fee`	`numeric`	YES	—	—
7	`source_bin_id`	`uuid`	YES	—	🔗 `bins.bin_id`

Table: `herobm_core.purchase_order_return_shipment_lines`

#	Column	Data Type	Nullable	Default	Constraints & Relationships
1	`shipment_line_id`	`uuid`	NO	`gen_random_uuid()`	🔑 `PK`
2	`shipment_id`	`uuid`	NO	—	🔗 `purchase_order_return_shipments.shipment_id`
3	`return_line_id`	`uuid`	NO	—	🔗 `purchase_order_return_lines.return_line_id`
4	`quantity_shipped`	`numeric`	NO	—	—

Table: `herobm_core.purchase_order_return_shipments`

#	Column	Data Type	Nullable	Default	Constraints & Relationships
1	`shipment_id`	`uuid`	NO	`gen_random_uuid()`	🔑 `PK`
2	`shipment_number`	`text`	NO	—	⚡ `UNIQUE`
3	`return_id`	`uuid`	NO	—	🔗 `purchase_order_returns.return_id`
4	`state_code`	`text`	NO	—	🏷️ `CHECK`
5	`notes`	`text`	YES	—	—
6	`tracking_number`	`text`	YES	—	—
7	`fulfillment_location_id`	`uuid`	YES	—	🔗 `locations.location_id`
8	`created_by`	`text`	YES	—	—
9	`created_on`	`timestamp with time zone`	YES	`now()`	—
10	`modified_on`	`timestamp with time zone`	YES	`now()`	—

Table: `herobm_core.purchase_order_returns`

#	Column	Data Type	Nullable	Default	Constraints & Relationships
1	`return_id`	`uuid`	NO	`gen_random_uuid()`	🔑 `PK`
2	`return_number`	`text`	NO	—	⚡ `UNIQUE`
3	`purchase_order_id`	`uuid`	NO	—	🔗 `purchase_orders.purchase_order_id`
4	`state_code`	`text`	NO	—	🏷️ `CHECK`
5	`notes`	`text`	YES	—	—
6	`created_by`	`text`	YES	—	—
7	`created_on`	`timestamp with time zone`	YES	`now()`	—
8	`modified_on`	`timestamp with time zone`	YES	`now()`	—

Table: `herobm_core.purchase_orders`

#	Column	Data Type	Nullable	Default	Constraints & Relationships
1	`purchase_order_id`	`uuid`	NO	`gen_random_uuid()`	🔑 `PK`
2	`order_number`	`text`	NO	—	⚡ `UNIQUE`
3	`name`	`text`	YES	—	—
4	`vendor_id`	`uuid`	YES	—	🔗 `suppliers.vendor_id`
5	`delivery_location_id`	`uuid`	NO	—	🔗 `locations.location_id`
6	`reference_number`	`text`	YES	—	—
7	`state_code`	`text`	NO	—	🏷️ `CHECK`
8	`base_total_amount`	`numeric`	YES	—	—
9	`currency_code`	`text`	NO	—	🏷️ `CHECK`
10	`exchange_rate`	`numeric`	NO	—	—
11	`notes`	`text`	YES	—	—
12	`custom_fields`	`jsonb`	YES	—	—
13	`metadata`	`jsonb`	YES	—	—
14	`expected_date`	`timestamp with time zone`	YES	—	—
15	`terms_description`	`text`	YES	—	—
16	`created_by`	`text`	YES	—	—
17	`created_on`	`timestamp with time zone`	YES	`now()`	—
18	`modified_on`	`timestamp with time zone`	YES	`now()`	—

Table: `herobm_core.supplier_expiries`

#	Column	Data Type	Nullable	Default	Constraints & Relationships
1	`expiry_id`	`uuid`	NO	`gen_random_uuid()`	🔑 `PK`
2	`vendor_id`	`uuid`	NO	—	🔗 `suppliers.vendor_id`
3	`expiry_type`	`text`	NO	—	—
4	`expiry_date`	`date`	NO	—	—
5	`notes`	`text`	YES	—	—
6	`created_by`	`text`	YES	—	—
7	`created_on`	`timestamp with time zone`	YES	`now()`	—
8	`modified_on`	`timestamp with time zone`	YES	`now()`	—

Table: `herobm_core.supplier_groups`

#	Column	Data Type	Nullable	Default	Constraints & Relationships
1	`supplier_group_id`	`uuid`	NO	`gen_random_uuid()`	🔑 `PK`
2	`group_code`	`text`	NO	—	⚡ `UNIQUE`
3	`name`	`text`	NO	—	—
4	`default_ap_account_id`	`uuid`	YES	—	🔗 `gl_accounts.gl_account_id`
5	`default_expense_account_id`	`uuid`	YES	—	🔗 `gl_accounts.gl_account_id`
6	`default_cost_center_id`	`uuid`	YES	—	🔗 `cost_centers.cost_center_id`
7	`default_activity_id`	`uuid`	YES	—	🔗 `activities.activity_id`
8	`trading_terms_id`	`uuid`	YES	—	🔗 `trading_terms.trading_terms_id`
9	`tax_position_id`	`uuid`	YES	—	🔗 `tax_positions.tax_position_id`
10	`early_payment_discount`	`numeric`	YES	—	—
11	`early_payment_discount_days`	`integer`	YES	—	—
12	`credit_limit`	`numeric`	YES	—	—
13	`is_purchasing_blocked`	`boolean`	NO	—	—
14	`purchasing_block_reason`	`text`	YES	—	—
15	`is_payment_blocked`	`boolean`	NO	—	—
16	`payment_block_reason`	`text`	YES	—	—
17	`block_notes`	`text`	YES	—	—

Table: `herobm_core.suppliers`

#	Column	Data Type	Nullable	Default	Constraints & Relationships
1	`vendor_id`	`uuid`	NO	`gen_random_uuid()`	🔑 `PK`
2	`vendor_number`	`text`	NO	—	⚡ `UNIQUE`
3	`supplier_group_id`	`uuid`	YES	—	🔗 `supplier_groups.supplier_group_id`
4	`organization_id`	`uuid`	YES	—	🔗 `organizations.organization_id`
5	`trading_terms_id`	`uuid`	YES	—	🔗 `trading_terms.trading_terms_id`
6	`early_payment_discount`	`numeric`	YES	—	—
7	`early_payment_discount_days`	`integer`	YES	—	—
8	`credit_limit`	`numeric`	YES	—	—
9	`is_purchasing_blocked`	`boolean`	NO	—	—
10	`purchasing_block_reason`	`text`	YES	—	—
11	`is_payment_blocked`	`boolean`	YES	—	—
12	`payment_block_reason`	`text`	YES	—	—
13	`block_notes`	`text`	YES	—	—
14	`currency_code`	`text`	NO	—	🏷️ `CHECK`
15	`state_code`	`text`	NO	—	—
16	`external_id`	`text`	YES	—	—
17	`notes`	`text`	YES	—	—
18	`metadata`	`jsonb`	YES	—	—
19	`bank_account_name`	`text`	YES	—	—
20	`bank_bsb`	`text`	YES	—	—
21	`bank_account_number`	`text`	YES	—	—
22	`tax_position_id`	`uuid`	YES	—	🔗 `tax_positions.tax_position_id`
23	`source_id`	`text`	YES	—	⚡ `UNIQUE`
24	`source`	`text`	NO	—	—
25	`created_by`	`text`	YES	—	—
26	`created_on`	`timestamp with time zone`	YES	`now()`	—
27	`modified_on`	`timestamp with time zone`	YES	`now()`	—

Warehouse & Inventory

Locations, warehouse zones, bin storage, stock ledger, stock balances, and internal transfer orders.

Table: `herobm_core.bin_contents`

#	Column	Data Type	Nullable	Default	Constraints & Relationships
1	`bin_content_id`	`uuid`	NO	`gen_random_uuid()`	🔑 `PK`
2	`bin_id`	`uuid`	NO	—	🔗 `bins.bin_id` (cascade), ⚡ `UNIQUE`
3	`product_id`	`uuid`	NO	—	🔗 `products.product_id` (cascade), ⚡ `UNIQUE`
4	`actual_quantity`	`numeric`	NO	—	—
5	`modified_on`	`timestamp with time zone`	YES	`now()`	—

Table: `herobm_core.bins`

#	Column	Data Type	Nullable	Default	Constraints & Relationships
1	`bin_id`	`uuid`	NO	`gen_random_uuid()`	🔑 `PK`
2	`bin_number`	`text`	NO	—	⚡ `UNIQUE`
3	`zone_id`	`uuid`	NO	—	🔗 `zones.zone_id`, ⚡ `UNIQUE`
4	`bin_type`	`bin_type_enum`	NO	—	—
5	`is_consignment`	`boolean`	YES	—	—
6	`is_bonded`	`boolean`	YES	—	—
7	`is_unavailable`	`boolean`	YES	—	—
8	`source_id`	`text`	YES	—	⚡ `UNIQUE`
9	`source`	`text`	NO	—	—
10	`created_by`	`text`	YES	—	—
11	`created_on`	`timestamp with time zone`	YES	`now()`	—
12	`modified_on`	`timestamp with time zone`	YES	`now()`	—

Table: `herobm_core.inventory_entries`

#	Column	Data Type	Nullable	Default	Constraints & Relationships
1	`entry_id`	`uuid`	NO	`gen_random_uuid()`	🔑 `PK`
2	`entry_number`	`text`	NO	—	⚡ `UNIQUE`
3	`entry_date`	`timestamp with time zone`	NO	`now()`	—
4	`memo`	`text`	YES	—	—
5	`source_type`	`text`	NO	—	—
6	`source_id`	`uuid`	YES	—	—
7	`is_reversed`	`boolean`	NO	—	—
8	`reversed_by`	`uuid`	YES	—	—
9	`created_by`	`text`	YES	—	—
10	`created_on`	`timestamp with time zone`	YES	`now()`	—

Table: `herobm_core.inventory_events`

#	Column	Data Type	Nullable	Default	Constraints & Relationships
1	`event_id`	`uuid`	NO	`gen_random_uuid()`	🔑 `PK`
2	`entity_type`	`text`	NO	—	—
3	`entity_id`	`uuid`	NO	—	—
4	`event_type`	`text`	NO	—	—
5	`entity_display_name`	`text`	YES	—	—
6	`payload`	`jsonb`	YES	—	—
7	`actor`	`text`	YES	—	—
8	`created_on`	`timestamp with time zone`	YES	`now()`	—

Table: `herobm_core.inventory_ledger`

#	Column	Data Type	Nullable	Default	Constraints & Relationships
1	`ledger_id`	`uuid`	NO	`gen_random_uuid()`	🔑 `PK`
2	`entry_id`	`uuid`	NO	—	🔗 `inventory_entries.entry_id`
3	`product_id`	`uuid`	NO	—	🔗 `products.product_id`
4	`bin_id`	`uuid`	NO	—	🔗 `bins.bin_id`
5	`location_id`	`uuid`	NO	—	🔗 `locations.location_id`
6	`zone_id`	`uuid`	NO	—	🔗 `zones.zone_id`
7	`quantity`	`numeric`	NO	—	—

Table: `herobm_core.locations`

#	Column	Data Type	Nullable	Default	Constraints & Relationships
1	`location_id`	`uuid`	NO	`gen_random_uuid()`	🔑 `PK`
2	`code`	`text`	NO	—	⚡ `UNIQUE`
3	`name`	`text`	NO	—	—
4	`address_line_1`	`text`	YES	—	—
5	`address_line_2`	`text`	YES	—	—
6	`city`	`text`	YES	—	—
7	`state_or_province`	`text`	YES	—	—
8	`country`	`text`	YES	—	—
9	`postal_code`	`text`	YES	—	—
10	`source_id`	`text`	YES	—	⚡ `UNIQUE`
11	`source`	`text`	NO	—	—
12	`created_by`	`text`	YES	—	—
13	`created_on`	`timestamp with time zone`	YES	`now()`	—
14	`modified_on`	`timestamp with time zone`	YES	`now()`	—

Table: `herobm_core.transfer_order_lines`

#	Column	Data Type	Nullable	Default	Constraints & Relationships
1	`transfer_order_line_id`	`uuid`	NO	`gen_random_uuid()`	🔑 `PK`
2	`transfer_order_id`	`uuid`	NO	—	🔗 `transfer_orders.transfer_order_id`
3	`product_id`	`uuid`	NO	—	🔗 `products.product_id`
4	`project_task_id`	`uuid`	YES	—	🔗 `project_tasks.project_task_id`
5	`quantity`	`numeric`	NO	—	—
6	`quantity_shipped`	`numeric`	YES	—	—
7	`quantity_received`	`numeric`	YES	—	—

Table: `herobm_core.transfer_order_picks`

#	Column	Data Type	Nullable	Default	Constraints & Relationships
1	`pick_id`	`uuid`	NO	`gen_random_uuid()`	🔑 `PK`
2	`transfer_order_id`	`uuid`	NO	—	🔗 `transfer_orders.transfer_order_id`
3	`transfer_order_line_id`	`uuid`	NO	—	🔗 `transfer_order_lines.transfer_order_line_id`
4	`product_id`	`uuid`	NO	—	🔗 `products.product_id`
5	`bin_id`	`uuid`	YES	—	🔗 `bins.bin_id`
6	`quantity`	`numeric`	NO	—	—
7	`state_code`	`text`	NO	—	—
8	`created_by`	`text`	YES	—	—
9	`created_on`	`timestamp with time zone`	YES	`now()`	—
10	`modified_on`	`timestamp with time zone`	YES	`now()`	—

Table: `herobm_core.transfer_order_receipt_lines`

#	Column	Data Type	Nullable	Default	Constraints & Relationships
1	`receipt_line_id`	`uuid`	NO	`gen_random_uuid()`	🔑 `PK`
2	`receipt_id`	`uuid`	NO	—	🔗 `transfer_order_receipts.receipt_id`
3	`transfer_order_line_id`	`uuid`	NO	—	🔗 `transfer_order_lines.transfer_order_line_id`
4	`product_id`	`uuid`	NO	—	🔗 `products.product_id`
5	`bin_id`	`uuid`	NO	—	🔗 `bins.bin_id`
6	`quantity`	`numeric`	NO	—	—
7	`putaway_status`	`text`	NO	—	—

Table: `herobm_core.transfer_order_receipts`

#	Column	Data Type	Nullable	Default	Constraints & Relationships
1	`receipt_id`	`uuid`	NO	`gen_random_uuid()`	🔑 `PK`
2	`transfer_order_id`	`uuid`	NO	—	🔗 `transfer_orders.transfer_order_id`
3	`receipt_number`	`text`	NO	—	⚡ `UNIQUE`
4	`state_code`	`text`	NO	—	—
5	`received_by`	`text`	YES	—	—
6	`received_on`	`timestamp with time zone`	YES	`now()`	—
7	`created_on`	`timestamp with time zone`	YES	`now()`	—

Table: `herobm_core.transfer_order_shipment_lines`

#	Column	Data Type	Nullable	Default	Constraints & Relationships
1	`shipment_line_id`	`uuid`	NO	`gen_random_uuid()`	🔑 `PK`
2	`shipment_id`	`uuid`	NO	—	🔗 `transfer_order_shipments.shipment_id`
3	`transfer_order_line_id`	`uuid`	NO	—	🔗 `transfer_order_lines.transfer_order_line_id`
4	`pick_id`	`uuid`	YES	—	🔗 `transfer_order_picks.pick_id`
5	`product_id`	`uuid`	NO	—	🔗 `products.product_id`
6	`quantity`	`numeric`	NO	—	—

Table: `herobm_core.transfer_order_shipments`

#	Column	Data Type	Nullable	Default	Constraints & Relationships
1	`shipment_id`	`uuid`	NO	`gen_random_uuid()`	🔑 `PK`
2	`transfer_order_id`	`uuid`	NO	—	🔗 `transfer_orders.transfer_order_id`
3	`shipment_number`	`text`	NO	—	⚡ `UNIQUE`
4	`tracking_number`	`text`	YES	—	—
5	`carrier_id`	`uuid`	YES	—	—
6	`notes`	`text`	YES	—	—
7	`shipping_notes`	`text`	YES	—	—
8	`state_code`	`text`	NO	—	—
9	`shipped_by`	`text`	YES	—	—
10	`shipped_on`	`timestamp with time zone`	YES	`now()`	—
11	`created_on`	`timestamp with time zone`	YES	`now()`	—

Table: `herobm_core.transfer_orders`

#	Column	Data Type	Nullable	Default	Constraints & Relationships
1	`transfer_order_id`	`uuid`	NO	`gen_random_uuid()`	🔑 `PK`
2	`order_number`	`text`	NO	—	⚡ `UNIQUE`
3	`source_location_id`	`uuid`	NO	—	🔗 `locations.location_id`
4	`destination_location_id`	`uuid`	NO	—	🔗 `locations.location_id`
5	`project_id`	`uuid`	YES	—	🔗 `projects.project_id`
6	`project_task_id`	`uuid`	YES	—	🔗 `project_tasks.project_task_id`
7	`is_project_return`	`boolean`	NO	—	—
8	`state_code`	`text`	NO	—	—
9	`notes`	`text`	YES	—	—
10	`shipping_notes`	`text`	YES	—	—
11	`created_by`	`text`	YES	—	—
12	`created_on`	`timestamp with time zone`	YES	`now()`	—
13	`modified_on`	`timestamp with time zone`	YES	`now()`	—

Table: `herobm_core.warehouse_events`

#	Column	Data Type	Nullable	Default	Constraints & Relationships
1	`event_id`	`uuid`	NO	`gen_random_uuid()`	🔑 `PK`
2	`entity_type`	`text`	NO	—	—
3	`entity_id`	`uuid`	NO	—	—
4	`event_type`	`text`	NO	—	—
5	`entity_display_name`	`text`	YES	—	—
6	`payload`	`jsonb`	YES	—	—
7	`actor`	`text`	YES	—	—
8	`created_on`	`timestamp with time zone`	YES	`now()`	—

Table: `herobm_core.zones`

#	Column	Data Type	Nullable	Default	Constraints & Relationships
1	`zone_id`	`uuid`	NO	`gen_random_uuid()`	🔑 `PK`
2	`location_id`	`uuid`	NO	—	🔗 `locations.location_id`, ⚡ `UNIQUE`
3	`code`	`text`	NO	—	⚡ `UNIQUE`
4	`name`	`text`	NO	—	—
5	`source_id`	`text`	YES	—	⚡ `UNIQUE`
6	`source`	`text`	NO	—	—
7	`created_by`	`text`	YES	—	—
8	`created_on`	`timestamp with time zone`	YES	`now()`	—
9	`modified_on`	`timestamp with time zone`	YES	`now()`	—

Financials & General Ledger

Chart of accounts, double-entry journals, fiscal periods, bank reconciliation, tax positions, and payments.

Table: `herobm_core.activities`

#	Column	Data Type	Nullable	Default	Constraints & Relationships
1	`activity_id`	`uuid`	NO	`gen_random_uuid()`	🔑 `PK`
2	`code`	`text`	NO	—	⚡ `UNIQUE`
3	`name`	`text`	NO	—	—
4	`is_system`	`boolean`	NO	—	—
5	`is_active`	`boolean`	NO	—	—
6	`created_on`	`timestamp with time zone`	YES	`now()`	—
7	`modified_on`	`timestamp with time zone`	YES	`now()`	—

Table: `herobm_core.bank_statement_lines`

#	Column	Data Type	Nullable	Default	Constraints & Relationships
1	`line_id`	`uuid`	NO	`gen_random_uuid()`	🔑 `PK`
2	`gl_account_id`	`uuid`	NO	—	🔗 `gl_accounts.gl_account_id`
3	`date`	`date`	NO	—	—
4	`description`	`text`	NO	—	—
5	`amount`	`numeric`	NO	—	—
6	`reference`	`text`	YES	—	—
7	`type`	`text`	YES	—	—
8	`payee`	`text`	YES	—	—
9	`is_reconciled`	`boolean`	NO	—	—
10	`reconciliation_id`	`uuid`	YES	—	🔗 `gl_reconciliations.reconciliation_id`
11	`matched_journal_line_id`	`uuid`	YES	—	🔗 `gl_journal_lines.journal_line_id`
12	`match_group_id`	`uuid`	YES	—	🔗 `gl_match_groups.match_group_id`
13	`created_on`	`timestamp with time zone`	YES	`now()`	—

Table: `herobm_core.cost_centers`

#	Column	Data Type	Nullable	Default	Constraints & Relationships
1	`cost_center_id`	`uuid`	NO	`gen_random_uuid()`	🔑 `PK`
2	`code`	`text`	NO	—	⚡ `UNIQUE`
3	`name`	`text`	NO	—	—
4	`is_system`	`boolean`	NO	—	—
5	`is_active`	`boolean`	NO	—	—
6	`created_on`	`timestamp with time zone`	YES	`now()`	—
7	`modified_on`	`timestamp with time zone`	YES	`now()`	—

Table: `herobm_core.csv_mapping_profiles`

#	Column	Data Type	Nullable	Default	Constraints & Relationships
1	`profile_id`	`uuid`	NO	`gen_random_uuid()`	🔑 `PK`
2	`name`	`text`	NO	—	—
3	`date_column`	`text`	NO	—	—
4	`amount_column`	`text`	YES	—	—
5	`debit_column`	`text`	YES	—	—
6	`credit_column`	`text`	YES	—	—
7	`description_column`	`text`	NO	—	—
8	`type_column`	`text`	YES	—	—
9	`payee_column`	`text`	YES	—	—
10	`reference_column`	`text`	YES	—	—
11	`header_rows`	`integer`	NO	—	—
12	`created_on`	`timestamp with time zone`	YES	`now()`	—

Table: `herobm_core.exchange_rates`

#	Column	Data Type	Nullable	Default	Constraints & Relationships
1	`exchange_rate_id`	`uuid`	NO	`gen_random_uuid()`	🔑 `PK`
2	`currency_code`	`text`	NO	—	⚡ `UNIQUE`, 🏷️ `CHECK`
3	`currency_name`	`text`	NO	—	—
4	`buy_rate`	`numeric`	NO	—	—
5	`sell_rate`	`numeric`	NO	—	—
6	`effective_date`	`timestamp`	YES	`now()`	⚡ `UNIQUE`
7	`updated_on`	`timestamp`	YES	`now()`	—

Table: `herobm_core.financial_events`

#	Column	Data Type	Nullable	Default	Constraints & Relationships
1	`event_id`	`uuid`	NO	`gen_random_uuid()`	🔑 `PK`
2	`entity_type`	`text`	NO	—	—
3	`entity_id`	`uuid`	NO	—	—
4	`event_type`	`text`	NO	—	—
5	`entity_display_name`	`text`	YES	—	—
6	`payload`	`jsonb`	YES	—	—
7	`actor`	`text`	YES	—	—
8	`created_on`	`timestamp with time zone`	YES	`now()`	—

Table: `herobm_core.gl_accounts`

#	Column	Data Type	Nullable	Default	Constraints & Relationships
1	`gl_account_id`	`uuid`	NO	`gen_random_uuid()`	🔑 `PK`
2	`account_code`	`text`	NO	—	⚡ `UNIQUE`
3	`name`	`text`	NO	—	—
4	`account_type`	`text`	NO	—	—
5	`report_category`	`text`	YES	—	—
6	`parent_account_id`	`uuid`	YES	—	—
7	`is_group`	`boolean`	NO	—	—
8	`is_system`	`boolean`	NO	—	—
9	`is_bank_account`	`boolean`	NO	—	—
10	`currency_code`	`text`	NO	—	🏷️ `CHECK`
11	`metadata`	`jsonb`	YES	—	—
12	`is_active`	`boolean`	NO	—	—
13	`created_on`	`timestamp with time zone`	YES	`now()`	—

Table: `herobm_core.gl_fiscal_periods`

#	Column	Data Type	Nullable	Default	Constraints & Relationships
1	`period_id`	`uuid`	NO	`gen_random_uuid()`	🔑 `PK`
2	`period_name`	`text`	NO	—	⚡ `UNIQUE`
3	`fiscal_year`	`integer`	NO	—	—
4	`period_number`	`integer`	NO	—	—
5	`start_date`	`date`	NO	—	—
6	`end_date`	`date`	NO	—	—
7	`status`	`text`	NO	—	—
8	`locked_by`	`text`	YES	—	—
9	`locked_at`	`timestamp with time zone`	YES	—	—
10	`closed_by`	`text`	YES	—	—
11	`closed_at`	`timestamp with time zone`	YES	—	—
12	`notes`	`text`	YES	—	—
13	`created_on`	`timestamp with time zone`	YES	`now()`	—
14	`modified_on`	`timestamp with time zone`	YES	`now()`	—

Table: `herobm_core.gl_journal_entries`

#	Column	Data Type	Nullable	Default	Constraints & Relationships
1	`journal_entry_id`	`uuid`	NO	`gen_random_uuid()`	🔑 `PK`
2	`sequence_number`	`integer`	YES	—	—
3	`entry_number`	`text`	NO	—	⚡ `UNIQUE`
4	`entry_date`	`date`	NO	—	—
5	`memo`	`text`	YES	—	—
6	`source_type`	`text`	NO	—	—
7	`source_id`	`uuid`	YES	—	—
8	`prev_hash`	`text`	YES	—	—
9	`entry_hash`	`text`	YES	—	—
10	`is_reversed`	`boolean`	NO	—	—
11	`reversed_by`	`uuid`	YES	—	🔗 `gl_journal_entries.journal_entry_id`
12	`created_by`	`text`	YES	—	—
13	`created_on`	`timestamp with time zone`	YES	`now()`	—

Table: `herobm_core.gl_journal_lines`

#	Column	Data Type	Nullable	Default	Constraints & Relationships
1	`journal_line_id`	`uuid`	NO	`gen_random_uuid()`	🔑 `PK`
2	`journal_entry_id`	`uuid`	NO	—	🔗 `gl_journal_entries.journal_entry_id`
3	`gl_account_id`	`uuid`	NO	—	🔗 `gl_accounts.gl_account_id`
4	`party_type`	`text`	YES	—	—
5	`party_id`	`text`	YES	—	—
6	`debit`	`numeric`	NO	—	—
7	`credit`	`numeric`	NO	—	—
8	`foreign_debit`	`numeric`	NO	—	—
9	`foreign_credit`	`numeric`	NO	—	—
10	`foreign_currency_code`	`text`	YES	—	—
11	`exchange_rate`	`numeric`	YES	—	—
12	`memo`	`text`	YES	—	—
13	`is_reconciled`	`boolean`	NO	—	—
14	`reconciliation_id`	`uuid`	YES	—	🔗 `gl_reconciliations.reconciliation_id`
15	`cost_center_id`	`uuid`	YES	—	🔗 `cost_centers.cost_center_id`
16	`activity_id`	`uuid`	YES	—	🔗 `activities.activity_id`
17	`match_group_id`	`uuid`	YES	—	🔗 `gl_match_groups.match_group_id`

Table: `herobm_core.gl_match_groups`

#	Column	Data Type	Nullable	Default	Constraints & Relationships
1	`match_group_id`	`uuid`	NO	`gen_random_uuid()`	🔑 `PK`
2	`match_type`	`text`	NO	—	—
3	`rule_id`	`uuid`	YES	—	🔗 `reconciliation_rules.rule_id`
4	`created_by`	`text`	NO	—	—
5	`created_on`	`timestamp with time zone`	YES	`now()`	—

Table: `herobm_core.gl_reconciliations`

#	Column	Data Type	Nullable	Default	Constraints & Relationships
1	`reconciliation_id`	`uuid`	NO	`gen_random_uuid()`	🔑 `PK`
2	`gl_account_id`	`uuid`	NO	—	🔗 `gl_accounts.gl_account_id`
3	`statement_date`	`date`	NO	—	—
4	`statement_balance`	`numeric`	NO	—	—
5	`status`	`text`	NO	—	—
6	`created_by`	`text`	YES	—	—
7	`created_on`	`timestamp with time zone`	YES	`now()`	—
8	`posted_on`	`timestamp with time zone`	YES	—	—

Table: `herobm_core.gl_settings`

#	Column	Data Type	Nullable	Default	Constraints & Relationships
1	`settings_id`	`uuid`	NO	`gen_random_uuid()`	🔑 `PK`
2	`account_metadata_schema`	`jsonb`	YES	—	—
3	`fiscal_year_start_month`	`integer`	NO	—	—
4	`bank_match_date_tolerance_days`	`integer`	NO	—	—
5	`default_ar_account_id`	`uuid`	YES	—	🔗 `gl_accounts.gl_account_id`
6	`default_ap_account_id`	`uuid`	YES	—	🔗 `gl_accounts.gl_account_id`
7	`default_revenue_account_id`	`uuid`	YES	—	🔗 `gl_accounts.gl_account_id`
8	`default_cogs_account_id`	`uuid`	YES	—	🔗 `gl_accounts.gl_account_id`
9	`default_sales_tax_account_id`	`uuid`	YES	—	🔗 `gl_accounts.gl_account_id`
10	`default_purchase_tax_account_id`	`uuid`	YES	—	🔗 `gl_accounts.gl_account_id`
11	`default_expense_account_id`	`uuid`	YES	—	🔗 `gl_accounts.gl_account_id`
12	`default_inventory_account_id`	`uuid`	YES	—	🔗 `gl_accounts.gl_account_id`
13	`default_grni_account_id`	`uuid`	YES	—	🔗 `gl_accounts.gl_account_id`
14	`realised_fx_gain_account_id`	`uuid`	YES	—	🔗 `gl_accounts.gl_account_id`
15	`realised_fx_loss_account_id`	`uuid`	YES	—	🔗 `gl_accounts.gl_account_id`
16	`unrealised_fx_gain_account_id`	`uuid`	YES	—	🔗 `gl_accounts.gl_account_id`
17	`unrealised_fx_loss_account_id`	`uuid`	YES	—	🔗 `gl_accounts.gl_account_id`
18	`default_shrinkage_account_id`	`uuid`	YES	—	🔗 `gl_accounts.gl_account_id`
19	`default_ppv_account_id`	`uuid`	YES	—	🔗 `gl_accounts.gl_account_id`
20	`default_cost_center_id`	`uuid`	YES	—	🔗 `cost_centers.cost_center_id`
21	`default_activity_id`	`uuid`	YES	—	🔗 `activities.activity_id`
22	`base_currency`	`text`	NO	—	—
23	`supported_batch_payment_formats`	`jsonb`	YES	—	—
24	`revenue_routing_precedence`	`text`	NO	—	—
25	`expense_routing_precedence`	`text`	NO	—	—
26	`default_fee_revenue_account_id`	`uuid`	YES	—	🔗 `gl_accounts.gl_account_id`
27	`default_discounts_received_account_id`	`uuid`	YES	—	🔗 `gl_accounts.gl_account_id`
28	`default_discounts_given_account_id`	`uuid`	YES	—	🔗 `gl_accounts.gl_account_id`
29	`default_otc_cash_account_id`	`uuid`	YES	—	🔗 `gl_accounts.gl_account_id`
30	`default_otc_card_account_id`	`uuid`	YES	—	🔗 `gl_accounts.gl_account_id`
31	`default_suspense_account_id`	`uuid`	YES	—	🔗 `gl_accounts.gl_account_id`
32	`default_retained_earnings_account_id`	`uuid`	YES	—	🔗 `gl_accounts.gl_account_id`

Table: `herobm_core.payment_allocations`

#	Column	Data Type	Nullable	Default	Constraints & Relationships
1	`allocation_id`	`uuid`	NO	`gen_random_uuid()`	🔑 `PK`
2	`payment_id`	`uuid`	NO	—	🔗 `payment_entries.payment_id`
3	`reference_type`	`text`	NO	—	—
4	`reference_id`	`uuid`	NO	—	—
5	`allocated_amount`	`numeric`	NO	—	—
6	`discount_amount`	`numeric`	YES	—	—
7	`created_on`	`timestamp with time zone`	YES	`now()`	—

Table: `herobm_core.payment_entries`

#	Column	Data Type	Nullable	Default	Constraints & Relationships
1	`payment_id`	`uuid`	NO	`gen_random_uuid()`	🔑 `PK`
2	`payment_number`	`text`	NO	—	⚡ `UNIQUE`
3	`payment_type`	`text`	NO	—	—
4	`party_id`	`uuid`	YES	—	—
5	`payment_date`	`timestamp with time zone`	NO	—	—
6	`mode_of_payment`	`text`	NO	—	—
7	`total_amount`	`numeric`	NO	—	—
8	`unallocated_amount`	`numeric`	NO	—	—
9	`gl_account_bank`	`uuid`	NO	—	🔗 `gl_accounts.gl_account_id`
10	`reference_number`	`text`	YES	—	—
11	`state_code`	`text`	NO	—	—
12	`base_total_amount`	`numeric`	YES	—	—
13	`base_unallocated_amount`	`numeric`	YES	—	—
14	`currency_code`	`text`	NO	—	—
15	`exchange_rate`	`numeric`	NO	—	—
16	`created_by`	`text`	YES	—	—
17	`aba_exported_at`	`timestamp with time zone`	YES	—	—
18	`created_on`	`timestamp with time zone`	YES	`now()`	—
19	`modified_on`	`timestamp with time zone`	YES	`now()`	—

Table: `herobm_core.payment_lines`

#	Column	Data Type	Nullable	Default	Constraints & Relationships
1	`payment_line_id`	`uuid`	NO	`gen_random_uuid()`	🔑 `PK`
2	`payment_id`	`uuid`	NO	—	🔗 `payment_entries.payment_id`
3	`gl_account_id`	`uuid`	NO	—	🔗 `gl_accounts.gl_account_id`
4	`amount`	`numeric`	NO	—	—
5	`memo`	`text`	YES	—	—

Table: `herobm_core.reconciliation_events`

#	Column	Data Type	Nullable	Default	Constraints & Relationships
1	`event_id`	`uuid`	NO	`gen_random_uuid()`	🔑 `PK`
2	`entity_type`	`text`	NO	—	—
3	`entity_id`	`uuid`	NO	—	—
4	`event_type`	`text`	NO	—	—
5	`entity_display_name`	`text`	YES	—	—
6	`payload`	`jsonb`	YES	—	—
7	`actor`	`text`	YES	—	—
8	`created_on`	`timestamp with time zone`	YES	`now()`	—

Table: `herobm_core.reconciliation_rules`

#	Column	Data Type	Nullable	Default	Constraints & Relationships
1	`rule_id`	`uuid`	NO	`gen_random_uuid()`	🔑 `PK`
2	`gl_account_ids`	`jsonb`	YES	—	—
3	`condition_type`	`text`	YES	—	—
4	`condition_value`	`text`	YES	—	—
5	`type_condition`	`text`	YES	—	—
6	`payee_condition_type`	`text`	YES	—	—
7	`payee_condition_value`	`text`	YES	—	—
8	`amount_min`	`numeric`	YES	—	—
9	`amount_max`	`numeric`	YES	—	—
10	`target_gl_account_id`	`uuid`	NO	—	🔗 `gl_accounts.gl_account_id`
11	`cost_center_id`	`uuid`	YES	—	🔗 `cost_centers.cost_center_id`
12	`activity_id`	`uuid`	YES	—	🔗 `activities.activity_id`
13	`party_type`	`text`	YES	—	—
14	`party_id`	`text`	YES	—	—
15	`memo`	`text`	YES	—	—
16	`priority`	`integer`	NO	—	—
17	`created_on`	`timestamp with time zone`	YES	`now()`	—

Table: `herobm_core.tax_categories`

#	Column	Data Type	Nullable	Default	Constraints & Relationships
1	`tax_category_id`	`uuid`	NO	`gen_random_uuid()`	🔑 `PK`
2	`code`	`text`	NO	—	⚡ `UNIQUE`
3	`title`	`text`	NO	—	—
4	`type`	`text`	NO	—	—
5	`rate`	`numeric`	YES	—	—
6	`sales_gl_account_id`	`uuid`	YES	—	—
7	`purchase_gl_account_id`	`uuid`	YES	—	—

Table: `herobm_core.tax_position_mappings`

#	Column	Data Type	Nullable	Default	Constraints & Relationships
1	`mapping_id`	`uuid`	NO	`gen_random_uuid()`	🔑 `PK`
2	`tax_position_id`	`uuid`	NO	—	🔗 `tax_positions.tax_position_id` (cascade)
3	`source_tax_category_id`	`uuid`	NO	—	🔗 `tax_categories.tax_category_id` (cascade)
4	`destination_tax_category_id`	`uuid`	NO	—	🔗 `tax_categories.tax_category_id` (cascade)

Table: `herobm_core.tax_positions`

#	Column	Data Type	Nullable	Default	Constraints & Relationships
1	`tax_position_id`	`uuid`	NO	`gen_random_uuid()`	🔑 `PK`
2	`code`	`text`	NO	—	⚡ `UNIQUE`
3	`title`	`text`	NO	—	—

Manufacturing & Work Orders

Production work orders, component allocations, and manufacturing picking tickets.

Table: `herobm_core.work_order_components`

#	Column	Data Type	Nullable	Default	Constraints & Relationships
1	`work_order_component_id`	`uuid`	NO	`gen_random_uuid()`	🔑 `PK`
2	`work_order_id`	`uuid`	NO	—	🔗 `work_orders.work_order_id`
3	`product_id`	`uuid`	NO	—	🔗 `products.product_id`
4	`expected_quantity`	`numeric`	NO	—	—
5	`unit_cost`	`numeric`	YES	—	—

Table: `herobm_core.work_order_picks`

#	Column	Data Type	Nullable	Default	Constraints & Relationships
1	`pick_id`	`uuid`	NO	`gen_random_uuid()`	🔑 `PK`
2	`work_order_id`	`uuid`	NO	—	🔗 `work_orders.work_order_id`
3	`work_order_component_id`	`uuid`	NO	—	🔗 `work_order_components.work_order_component_id`
4	`bin_id`	`uuid`	YES	—	🔗 `bins.bin_id`
5	`quantity`	`numeric`	NO	—	—
6	`state_code`	`text`	NO	—	🏷️ `CHECK`
7	`created_by`	`text`	YES	—	—
8	`created_on`	`timestamp with time zone`	YES	`now()`	—
9	`modified_on`	`timestamp with time zone`	YES	`now()`	—

Table: `herobm_core.work_orders`

#	Column	Data Type	Nullable	Default	Constraints & Relationships
1	`work_order_id`	`uuid`	NO	`gen_random_uuid()`	🔑 `PK`
2	`order_number`	`text`	NO	—	⚡ `UNIQUE`
3	`product_id`	`uuid`	NO	—	🔗 `products.product_id`
4	`target_quantity`	`numeric`	NO	—	—
5	`completed_quantity`	`numeric`	NO	—	—
6	`location_id`	`uuid`	NO	—	🔗 `locations.location_id`
7	`wip_bin_id`	`uuid`	YES	—	🔗 `bins.bin_id`
8	`output_bin_id`	`uuid`	YES	—	🔗 `bins.bin_id`
9	`state_code`	`text`	NO	—	🏷️ `CHECK`
10	`putaway_status`	`text`	YES	—	—
11	`assembly_cost_per_unit`	`numeric`	YES	—	—
12	`additional_cost`	`numeric`	YES	—	—
13	`total_cost`	`numeric`	YES	—	—
14	`metadata`	`jsonb`	YES	—	—
15	`created_by`	`text`	YES	—	—
16	`created_on`	`timestamp with time zone`	YES	`now()`	—
17	`modified_on`	`timestamp with time zone`	YES	`now()`	—

System, Security & Telemetry

User access control, API keys, webhook outbox, PDF reports, async ELT pipeline jobs, and system event logs.

Table: `herobm_core._pipeline_jobs`

#	Column	Data Type	Nullable	Default	Constraints & Relationships
1	`job_id`	`text`	NO	—	🔑 `PK`
2	`type`	`text`	NO	—	—
3	`status`	`text`	NO	—	—
4	`config_json`	`jsonb`	YES	—	—
5	`progress_json`	`jsonb`	YES	—	—
6	`logs_json`	`jsonb`	YES	—	—
7	`created_at`	`timestamp`	NO	`now()`	—
8	`updated_at`	`timestamp`	NO	`now()`	—

Table: `herobm_core.api_keys`

#	Column	Data Type	Nullable	Default	Constraints & Relationships
1	`api_key_id`	`uuid`	NO	`gen_random_uuid()`	🔑 `PK`
2	`name`	`text`	NO	—	—
3	`key_hash`	`text`	NO	—	—
4	`prefix`	`text`	NO	—	—
5	`role`	`text`	NO	—	—
6	`is_active`	`boolean`	NO	—	—
7	`created_by`	`text`	NO	—	—
8	`created_on`	`timestamp with time zone`	YES	`now()`	—

Table: `herobm_core.app_settings`

#	Column	Data Type	Nullable	Default	Constraints & Relationships
1	`settings_id`	`uuid`	NO	`gen_random_uuid()`	🔑 `PK`
2	`default_fulfillment_location_id`	`uuid`	YES	—	🔗 `locations.location_id`
3	`default_customer_terms_id`	`uuid`	YES	—	🔗 `trading_terms.trading_terms_id`
4	`default_supplier_terms_id`	`uuid`	YES	—	🔗 `trading_terms.trading_terms_id`
5	`default_customer_tax_position_id`	`uuid`	YES	—	🔗 `tax_positions.tax_position_id`
6	`default_supplier_tax_position_id`	`uuid`	YES	—	🔗 `tax_positions.tax_position_id`
7	`default_purchase_tax_category_id`	`uuid`	YES	—	🔗 `tax_categories.tax_category_id`
8	`default_sales_tax_category_id`	`uuid`	YES	—	🔗 `tax_categories.tax_category_id`
9	`inventory_valuation_method`	`text`	NO	—	—
10	`inventory_accounting_mode`	`text`	NO	—	—
11	`allow_negative_inventory`	`boolean`	NO	—	—
12	`credit_limit_behavior`	`text`	NO	—	—
13	`smtp_host`	`text`	YES	—	—
14	`smtp_port`	`integer`	YES	—	—
15	`smtp_user`	`text`	YES	—	—
16	`smtp_pass_encrypted`	`text`	YES	—	—
17	`smtp_from_address`	`text`	YES	—	—
18	`organization_tags`	`jsonb`	YES	—	—
19	`organization_contact_roles`	`jsonb`	YES	—	—
20	`opportunity_contact_roles`	`jsonb`	YES	—	—
21	`opportunity_organization_roles`	`jsonb`	YES	—	—
22	`opportunity_stages`	`jsonb`	YES	—	—
23	`opportunity_types`	`jsonb`	YES	—	—
24	`project_stages`	`jsonb`	YES	—	—
25	`referral_modes`	`jsonb`	YES	—	—
26	`activity_types`	`jsonb`	YES	—	—
27	`sales_analysis_codes`	`jsonb`	YES	—	—
28	`api_rate_limit`	`numeric`	NO	—	—
29	`setup_completed_at`	`timestamp with time zone`	YES	—	—
30	`system_identifier`	`text`	YES	—	—
31	`active_license_key`	`text`	YES	—	—
32	`active_license_payload`	`jsonb`	YES	—	—
33	`tax_provider_mappings`	`jsonb`	YES	—	—
34	`enrichment_provider_mappings`	`jsonb`	YES	—	—

Table: `herobm_core.business_report_events`

#	Column	Data Type	Nullable	Default	Constraints & Relationships
1	`event_id`	`uuid`	NO	`gen_random_uuid()`	🔑 `PK`
2	`entity_type`	`text`	NO	—	—
3	`entity_id`	`uuid`	NO	—	—
4	`event_type`	`text`	NO	—	—
5	`entity_display_name`	`text`	YES	—	—
6	`payload`	`jsonb`	YES	—	—
7	`actor`	`text`	YES	—	—
8	`created_on`	`timestamp with time zone`	YES	`now()`	—

Table: `herobm_core.business_reports`

#	Column	Data Type	Nullable	Default	Constraints & Relationships
1	`id`	`uuid`	NO	`gen_random_uuid()`	🔑 `PK`
2	`slug`	`text`	NO	—	⚡ `UNIQUE`
3	`name`	`text`	NO	—	—
4	`description`	`text`	YES	—	—
5	`data_source_hook`	`text`	NO	—	—
6	`ui_config`	`jsonb`	NO	—	—
7	`is_system`	`boolean`	NO	—	—
8	`created_at`	`timestamp with time zone`	NO	`now()`	—

Table: `herobm_core.casbin_rule`

#	Column	Data Type	Nullable	Default	Constraints & Relationships
1	`id`	`uuid`	NO	`gen_random_uuid()`	🔑 `PK`
2	`ptype`	`text`	NO	—	—
3	`v0`	`text`	YES	—	—
4	`v1`	`text`	YES	—	—
5	`v2`	`text`	YES	—	—
6	`v3`	`text`	YES	—	—
7	`v4`	`text`	YES	—	—
8	`v5`	`text`	YES	—	—

Table: `herobm_core.email_events`

#	Column	Data Type	Nullable	Default	Constraints & Relationships
1	`event_id`	`uuid`	NO	`gen_random_uuid()`	🔑 `PK`
2	`entity_type`	`text`	NO	—	—
3	`entity_id`	`uuid`	NO	—	—
4	`event_type`	`text`	NO	—	—
5	`entity_display_name`	`text`	YES	—	—
6	`payload`	`jsonb`	YES	—	—
7	`actor`	`text`	YES	—	—
8	`created_on`	`timestamp with time zone`	YES	`now()`	—

Table: `herobm_core.email_outbox`

#	Column	Data Type	Nullable	Default	Constraints & Relationships
1	`id`	`uuid`	NO	`gen_random_uuid()`	🔑 `PK`
2	`entity_type`	`text`	YES	—	—
3	`entity_id`	`uuid`	YES	—	—
4	`to_address`	`text`	NO	—	—
5	`reply_to`	`text`	YES	—	—
6	`subject`	`text`	NO	—	—
7	`html_body`	`text`	NO	—	—
8	`attachments`	`jsonb`	YES	—	—
9	`status`	`email_status`	NO	—	—
10	`retries`	`integer`	NO	—	—
11	`last_error`	`text`	YES	—	—
12	`next_retry_at`	`timestamp with time zone`	YES	—	—
13	`created_at`	`timestamp with time zone`	NO	`now()`	—
14	`processed_at`	`timestamp with time zone`	YES	—	—

Table: `herobm_core.group_events`

#	Column	Data Type	Nullable	Default	Constraints & Relationships
1	`event_id`	`uuid`	NO	`gen_random_uuid()`	🔑 `PK`
2	`entity_type`	`text`	NO	—	—
3	`entity_id`	`uuid`	NO	—	—
4	`event_type`	`text`	NO	—	—
5	`entity_display_name`	`text`	YES	—	—
6	`payload`	`jsonb`	YES	—	—
7	`actor`	`text`	YES	—	—
8	`created_on`	`timestamp with time zone`	YES	`now()`	—

Table: `herobm_core.integration_events`

#	Column	Data Type	Nullable	Default	Constraints & Relationships
1	`event_id`	`uuid`	NO	`gen_random_uuid()`	🔑 `PK`
2	`entity_type`	`text`	NO	—	—
3	`entity_id`	`uuid`	NO	—	—
4	`event_type`	`text`	NO	—	—
5	`entity_display_name`	`text`	YES	—	—
6	`payload`	`jsonb`	YES	—	—
7	`actor`	`text`	YES	—	—
8	`created_on`	`timestamp with time zone`	YES	`now()`	—

Table: `herobm_core.integrations`

#	Column	Data Type	Nullable	Default	Constraints & Relationships
1	`integration_id`	`uuid`	NO	`gen_random_uuid()`	🔑 `PK`
2	`provider`	`text`	NO	—	⚡ `UNIQUE`
3	`config`	`jsonb`	NO	—	—
4	`is_active`	`boolean`	NO	—	—
5	`created_on`	`timestamp with time zone`	YES	`now()`	—
6	`modified_on`	`timestamp with time zone`	YES	`now()`	—

Table: `herobm_core.macros`

#	Column	Data Type	Nullable	Default	Constraints & Relationships
1	`macro_id`	`uuid`	NO	`gen_random_uuid()`	🔑 `PK`
2	`name`	`text`	NO	—	⚡ `UNIQUE`
3	`macro_type`	`text`	NO	—	—
4	`content`	`text`	NO	—	—
5	`created_on`	`timestamp with time zone`	YES	`now()`	—
6	`modified_on`	`timestamp with time zone`	YES	`now()`	—

Table: `herobm_core.master_data_events`

#	Column	Data Type	Nullable	Default	Constraints & Relationships
1	`event_id`	`uuid`	NO	`gen_random_uuid()`	🔑 `PK`
2	`entity_type`	`text`	NO	—	—
3	`entity_id`	`uuid`	NO	—	—
4	`event_type`	`text`	NO	—	—
5	`entity_display_name`	`text`	YES	—	—
6	`payload`	`jsonb`	YES	—	—
7	`actor`	`text`	YES	—	—
8	`created_on`	`timestamp with time zone`	YES	`now()`	—

Table: `herobm_core.outbox`

#	Column	Data Type	Nullable	Default	Constraints & Relationships
1	`outbox_id`	`uuid`	NO	`gen_random_uuid()`	🔑 `PK`
2	`entity_type`	`text`	NO	—	—
3	`entity_id`	`uuid`	NO	—	—
4	`event_type`	`text`	NO	—	—
5	`entity_display_name`	`text`	YES	—	—
6	`payload`	`jsonb`	YES	—	—
7	`created_on`	`timestamp with time zone`	YES	`now()`	—
8	`processed_at`	`timestamp with time zone`	YES	—	—
9	`locked_until`	`timestamp with time zone`	YES	—	—
10	`last_error`	`text`	YES	—	—

Table: `herobm_core.pdf_template_contexts`

#	Column	Data Type	Nullable	Default	Constraints & Relationships
1	`template_id`	`uuid`	NO	—	🔗 `pdf_templates.id` (cascade)
2	`context`	`text`	NO	—	—

Table: `herobm_core.pdf_template_hooks`

#	Column	Data Type	Nullable	Default	Constraints & Relationships
1	`id`	`uuid`	NO	`gen_random_uuid()`	🔑 `PK`
2	`hook_slug`	`text`	NO	—	⚡ `UNIQUE`
3	`report_id`	`uuid`	NO	—	🔗 `pdf_templates.id` (cascade)
4	`context_slug`	`text`	NO	—	—
5	`updated_at`	`timestamp with time zone`	NO	`now()`	—

Table: `herobm_core.pdf_templates`

#	Column	Data Type	Nullable	Default	Constraints & Relationships
1	`id`	`uuid`	NO	`gen_random_uuid()`	🔑 `PK`
2	`slug`	`text`	NO	—	⚡ `UNIQUE`
3	`name`	`text`	NO	—	—
4	`description`	`text`	YES	—	—
5	`template`	`text`	NO	—	—
6	`mock_data`	`jsonb`	YES	—	—
7	`context_resolver`	`text`	YES	—	—
8	`output_name_pattern`	`text`	YES	—	—
9	`created_at`	`timestamp with time zone`	NO	`now()`	—

Table: `herobm_core.system_events`

#	Column	Data Type	Nullable	Default	Constraints & Relationships
1	`event_id`	`uuid`	NO	`gen_random_uuid()`	🔑 `PK`
2	`entity_type`	`text`	NO	—	—
3	`entity_id`	`uuid`	NO	—	—
4	`event_type`	`text`	NO	—	—
5	`entity_display_name`	`text`	YES	—	—
6	`payload`	`jsonb`	YES	—	—
7	`actor`	`text`	YES	—	—
8	`created_on`	`timestamp with time zone`	YES	`now()`	—

Table: `herobm_core.tenant_settings`

#	Column	Data Type	Nullable	Default	Constraints & Relationships
1	`tenant_settings_id`	`uuid`	NO	`gen_random_uuid()`	🔑 `PK`
2	`name`	`text`	NO	—	—
3	`address_line_1`	`text`	YES	—	—
4	`address_line_2`	`text`	YES	—	—
5	`city`	`text`	YES	—	—
6	`state`	`text`	YES	—	—
7	`country`	`text`	YES	—	—
8	`post_code`	`text`	YES	—	—
9	`email`	`text`	YES	—	—
10	`phone`	`text`	YES	—	—
11	`website`	`text`	YES	—	—
12	`company_number`	`text`	YES	—	—
13	`tax_number`	`text`	YES	—	—
14	`logo_url`	`text`	YES	—	—
15	`bank_name`	`text`	YES	—	—
16	`bank_account_name`	`text`	YES	—	—
17	`bank_account_number`	`text`	YES	—	—
18	`bank_swift_bic`	`text`	YES	—	—
19	`bank_iban`	`text`	YES	—	—
20	`pdf_theme_config`	`jsonb`	YES	—	—

Table: `herobm_core.user_events`

#	Column	Data Type	Nullable	Default	Constraints & Relationships
1	`event_id`	`uuid`	NO	`gen_random_uuid()`	🔑 `PK`
2	`user_id`	`uuid`	NO	—	—
3	`event_type`	`text`	NO	—	—
4	`entity_display_name`	`text`	YES	—	—
5	`payload`	`jsonb`	YES	—	—
6	`actor`	`text`	YES	—	—
7	`created_on`	`timestamp with time zone`	YES	`now()`	—

Table: `herobm_core.user_settings`

#	Column	Data Type	Nullable	Default	Constraints & Relationships
1	`user_id`	`uuid`	NO	—	🔑 `PK`, 🔗 `users.user_id` (cascade)
2	`dashboard_config`	`jsonb`	YES	—	—
3	`report_configs`	`jsonb`	YES	—	—
4	`preferences`	`jsonb`	YES	—	—
5	`created_at`	`timestamp with time zone`	NO	`now()`	—
6	`updated_at`	`timestamp with time zone`	NO	`now()`	—

Table: `herobm_core.user_two_factor`

#	Column	Data Type	Nullable	Default	Constraints & Relationships
1	`user_id`	`uuid`	NO	—	🔑 `PK`, 🔗 `users.user_id` (cascade)
2	`secret_encrypted`	`text`	NO	—	—
3	`is_enabled`	`boolean`	NO	—	—
4	`backup_codes`	`jsonb`	NO	—	—
5	`verified_at`	`timestamp with time zone`	YES	—	—
6	`created_at`	`timestamp with time zone`	NO	`now()`	—
7	`updated_at`	`timestamp with time zone`	NO	`now()`	—

Table: `herobm_core.users`

#	Column	Data Type	Nullable	Default	Constraints & Relationships
1	`user_id`	`uuid`	NO	`gen_random_uuid()`	🔑 `PK`
2	`username`	`text`	NO	—	⚡ `UNIQUE`
3	`password_hash`	`text`	NO	—	—
4	`display_name`	`text`	YES	—	—
5	`email`	`text`	YES	—	—
6	`role`	`text`	NO	—	—
7	`is_active`	`boolean`	NO	—	—
8	`created_at`	`timestamp with time zone`	NO	`now()`	—

Table: `herobm_core.webhooks`

#	Column	Data Type	Nullable	Default	Constraints & Relationships
1	`webhook_id`	`uuid`	NO	`gen_random_uuid()`	🔑 `PK`
2	`target_url`	`text`	NO	—	—
3	`event_types`	`jsonb`	NO	—	—
4	`secret_key`	`text`	NO	—	—
5	`is_active`	`boolean`	NO	—	—
6	`created_on`	`timestamp with time zone`	YES	`now()`	—

Table: `herobm_core.crm_activity_contacts`

#	Column	Data Type	Nullable	Default	Constraints & Relationships
1	`activity_contact_id`	`uuid`	NO	`gen_random_uuid()`	🔑 `PK`
2	`activity_id`	`uuid`	NO	—	🔗 `crm_activities.activity_id` (cascade)
3	`contact_id`	`uuid`	NO	—	🔗 `contacts.contact_id` (cascade)
4	`created_on`	`timestamp with time zone`	YES	`now()`	—

Table: `herobm_core.crm_settings`

#	Column	Data Type	Nullable	Default	Constraints & Relationships
1	`settings_id`	`uuid`	NO	`gen_random_uuid()`	🔑 `PK`
2	`organization_metadata_schema`	`jsonb`	YES	—	—
3	`opportunity_metadata_schema`	`jsonb`	YES	—	—
4	`contact_metadata_schema`	`jsonb`	YES	—	—
5	`customer_metadata_schema`	`jsonb`	YES	—	—
6	`supplier_metadata_schema`	`jsonb`	YES	—	—
7	`created_on`	`timestamp with time zone`	YES	`now()`	—
8	`modified_on`	`timestamp with time zone`	YES	`now()`	—

Table: `herobm_core.manufacturing_settings`

#	Column	Data Type	Nullable	Default	Constraints & Relationships
1	`manufacturing_settings_id`	`uuid`	NO	`gen_random_uuid()`	🔑 `PK`
2	`work_order_metadata_schema`	`jsonb`	YES	—	—
3	`modified_on`	`timestamp with time zone`	YES	`now()`	—

Table: `herobm_core.product_settings`

#	Column	Data Type	Nullable	Default	Constraints & Relationships
1	`settings_id`	`uuid`	NO	`gen_random_uuid()`	🔑 `PK`
2	`product_metadata_schema`	`jsonb`	YES	—	—
3	`created_on`	`timestamp with time zone`	YES	`now()`	—
4	`modified_on`	`timestamp with time zone`	YES	`now()`	—

Table: `herobm_core.project_budget_lines`

#	Column	Data Type	Nullable	Default	Constraints & Relationships
1	`budget_line_id`	`uuid`	NO	`gen_random_uuid()`	🔑 `PK`
2	`project_id`	`uuid`	NO	—	🔗 `projects.project_id`
3	`project_task_id`	`uuid`	NO	—	🔗 `project_tasks.project_task_id`
4	`line_type`	`text`	NO	—	—
5	`resource_id`	`uuid`	YES	—	🔗 `project_resources.resource_id`
6	`product_id`	`uuid`	YES	—	🔗 `products.product_id`
7	`description`	`text`	YES	—	—
8	`planned_quantity`	`numeric`	NO	—	—
9	`unit_cost`	`numeric(12, 2)`	NO	—	—
10	`total_cost`	`numeric(12, 2)`	NO	—	—
11	`unit_price`	`numeric(12, 2)`	NO	—	—
12	`discount_percentage`	`numeric(5, 2)`	YES	—	—
13	`total_price`	`numeric(12, 2)`	NO	—	—
14	`created_on`	`timestamp with time zone`	YES	`now()`	—
15	`modified_on`	`timestamp with time zone`	YES	`now()`	—

Table: `herobm_core.project_ledger_entries`

#	Column	Data Type	Nullable	Default	Constraints & Relationships
1	`ledger_id`	`uuid`	NO	`gen_random_uuid()`	🔑 `PK`
2	`project_id`	`uuid`	NO	—	🔗 `projects.project_id`
3	`project_task_id`	`uuid`	NO	—	🔗 `project_tasks.project_task_id`
4	`entry_type`	`text`	NO	—	—
5	`line_type`	`text`	NO	—	—
6	`source_type`	`text`	NO	—	—
7	`source_id`	`uuid`	YES	—	—
8	`inventory_entry_id`	`uuid`	YES	—	🔗 `inventory_entries.entry_id`
9	`resource_id`	`uuid`	YES	—	🔗 `project_resources.resource_id`
10	`product_id`	`uuid`	YES	—	🔗 `products.product_id`
11	`user_id`	`uuid`	YES	—	🔗 `users.user_id`
12	`description`	`text`	YES	—	—
13	`quantity`	`numeric`	NO	—	—
14	`unit_cost_base`	`numeric(12, 2)`	NO	—	—
15	`total_cost_base`	`numeric(12, 2)`	NO	—	—
16	`unit_price_base`	`numeric(12, 2)`	NO	—	—
17	`discount_percentage`	`numeric(5, 2)`	YES	—	—
18	`total_price_base`	`numeric(12, 2)`	NO	—	—
19	`is_billable`	`boolean`	NO	—	—
20	`is_billed`	`boolean`	NO	—	—
21	`sales_invoice_line_id`	`uuid`	YES	—	—
22	`budget_line_id`	`uuid`	YES	—	🔗 `project_budget_lines.budget_line_id`
23	`posting_date`	`timestamp with time zone`	NO	—	—
24	`created_by`	`text`	NO	—	—
25	`created_on`	`timestamp with time zone`	YES	`now()`	—

Table: `herobm_core.project_resource_assignments`

#	Column	Data Type	Nullable	Default	Constraints & Relationships
1	`assignment_id`	`uuid`	NO	`gen_random_uuid()`	🔑 `PK`
2	`project_id`	`uuid`	NO	—	🔗 `projects.project_id` (cascade), ⚡ `UNIQUE`
3	`resource_id`	`uuid`	NO	—	🔗 `project_resources.resource_id`, ⚡ `UNIQUE`
4	`notes`	`text`	YES	—	—
5	`created_by`	`text`	YES	—	—
6	`created_on`	`timestamp with time zone`	YES	`now()`	—

Table: `herobm_core.project_resources`

#	Column	Data Type	Nullable	Default	Constraints & Relationships
1	`resource_id`	`uuid`	NO	`gen_random_uuid()`	🔑 `PK`
2	`resource_number`	`text`	NO	—	⚡ `UNIQUE`
3	`name`	`text`	NO	—	—
4	`resource_type`	`text`	NO	—	—
5	`user_id`	`uuid`	YES	—	🔗 `users.user_id`
6	`vendor_id`	`uuid`	YES	—	🔗 `suppliers.vendor_id`
7	`service_product_id`	`uuid`	YES	—	🔗 `products.product_id`
8	`base_uom`	`text`	NO	—	🔗 `uom_dictionary.uom_code`
9	`direct_unit_cost`	`numeric(12, 2)`	NO	—	—
10	`unit_price`	`numeric(12, 2)`	NO	—	—
11	`is_active`	`boolean`	NO	—	—
12	`created_on`	`timestamp with time zone`	YES	`now()`	—
13	`modified_on`	`timestamp with time zone`	YES	`now()`	—

Table: `herobm_core.project_settings`

#	Column	Data Type	Nullable	Default	Constraints & Relationships
1	`settings_id`	`uuid`	NO	`gen_random_uuid()`	🔑 `PK`
2	`project_metadata_schema`	`jsonb`	YES	—	—
3	`created_on`	`timestamp with time zone`	YES	`now()`	—
4	`modified_on`	`timestamp with time zone`	YES	`now()`	—

Table: `herobm_core.project_tasks`

#	Column	Data Type	Nullable	Default	Constraints & Relationships
1	`project_task_id`	`uuid`	NO	`gen_random_uuid()`	🔑 `PK`
2	`project_id`	`uuid`	NO	—	🔗 `projects.project_id`
3	`task_code`	`text`	NO	—	—
4	`name`	`text`	NO	—	—
5	`description`	`text`	YES	—	—
6	`parent_task_id`	`uuid`	YES	—	🔗 `project_tasks.project_task_id`
7	`state_code`	`text`	NO	—	—
8	`is_milestone`	`boolean`	NO	—	—
9	`is_billable`	`boolean`	NO	—	—
10	`planned_start_date`	`timestamp with time zone`	YES	—	—
11	`planned_end_date`	`timestamp with time zone`	YES	—	—
12	`actual_start_date`	`timestamp with time zone`	YES	—	—
13	`actual_end_date`	`timestamp with time zone`	YES	—	—
14	`created_by`	`text`	YES	—	—
15	`created_on`	`timestamp with time zone`	YES	`now()`	—
16	`modified_on`	`timestamp with time zone`	YES	`now()`	—

Table: `herobm_core.purchasing_settings`

#	Column	Data Type	Nullable	Default	Constraints & Relationships
1	`purchasing_settings_id`	`uuid`	NO	`gen_random_uuid()`	🔑 `PK`
2	`purchase_order_metadata_schema`	`jsonb`	YES	—	—
3	`debit_note_metadata_schema`	`jsonb`	YES	—	—
4	`modified_on`	`timestamp with time zone`	YES	`now()`	—

Table: `herobm_core.sales_settings`

#	Column	Data Type	Nullable	Default	Constraints & Relationships
1	`sales_settings_id`	`uuid`	NO	`gen_random_uuid()`	🔑 `PK`
2	`sales_order_metadata_schema`	`jsonb`	YES	—	—
3	`sales_invoice_metadata_schema`	`jsonb`	YES	—	—
4	`credit_note_metadata_schema`	`jsonb`	YES	—	—
5	`modified_on`	`timestamp with time zone`	YES	`now()`	—

Table: `herobm_core.stocktake_counts`

#	Column	Data Type	Nullable	Default	Constraints & Relationships
1	`stocktake_count_id`	`uuid`	NO	`gen_random_uuid()`	🔑 `PK`
2	`stocktake_id`	`uuid`	NO	—	🔗 `stocktakes.stocktake_id` (cascade)
3	`stocktake_line_id`	`uuid`	YES	—	🔗 `stocktake_lines.stocktake_line_id` (cascade)
4	`product_id`	`uuid`	NO	—	🔗 `products.product_id`
5	`bin_id`	`uuid`	NO	—	🔗 `bins.bin_id`
6	`quantity`	`numeric`	NO	—	—
7	`count_mode`	`text`	NO	—	—
8	`counted_by`	`text`	NO	—	—
9	`counted_at`	`timestamp with time zone`	YES	`now()`	—
10	`notes`	`text`	YES	—	—

Table: `herobm_core.stocktake_lines`

#	Column	Data Type	Nullable	Default	Constraints & Relationships
1	`stocktake_line_id`	`uuid`	NO	`gen_random_uuid()`	🔑 `PK`
2	`stocktake_id`	`uuid`	NO	—	🔗 `stocktakes.stocktake_id` (cascade), ⚡ `UNIQUE`
3	`product_id`	`uuid`	NO	—	🔗 `products.product_id`, ⚡ `UNIQUE`
4	`bin_id`	`uuid`	NO	—	🔗 `bins.bin_id`, ⚡ `UNIQUE`
5	`expected_quantity`	`numeric`	NO	—	—
6	`counted_quantity`	`numeric`	YES	—	—
7	`is_unlisted`	`boolean`	NO	—	—
8	`notes`	`text`	YES	—	—
9	`last_counted_at`	`timestamp with time zone`	YES	—	—
10	`last_counted_by`	`text`	YES	—	—

Table: `herobm_core.stocktakes`

#	Column	Data Type	Nullable	Default	Constraints & Relationships
1	`stocktake_id`	`uuid`	NO	`gen_random_uuid()`	🔑 `PK`
2	`stocktake_number`	`text`	NO	—	⚡ `UNIQUE`
3	`name`	`text`	NO	—	—
4	`location_id`	`uuid`	NO	—	🔗 `locations.location_id`
5	`state_code`	`text`	NO	—	—
6	`scope_type`	`text`	NO	—	—
7	`zone_filter`	`text`	YES	—	—
8	`bin_pattern`	`text`	YES	—	—
9	`is_blind_count`	`boolean`	NO	—	—
10	`notes`	`text`	YES	—	—
11	`inventory_entry_id`	`uuid`	YES	—	🔗 `inventory_entries.entry_id`
12	`created_by`	`text`	YES	—	—
13	`created_on`	`timestamp with time zone`	YES	`now()`	—
14	`opened_by`	`text`	YES	—	—
15	`opened_at`	`timestamp with time zone`	YES	—	—
16	`reviewed_by`	`text`	YES	—	—
17	`reviewed_at`	`timestamp with time zone`	YES	—	—
18	`submitted_by`	`text`	YES	—	—
19	`submitted_on`	`timestamp with time zone`	YES	—	—
20	`cancelled_by`	`text`	YES	—	—
21	`cancelled_at`	`timestamp with time zone`	YES	—	—
22	`modified_on`	`timestamp with time zone`	YES	`now()`	—

Field / Parameter	Display Name	Description & Rules
table_name	Table Name	Relational table identifier within herobm_core schema.
primary_key	Primary Key	Unique UUID identifier generated by gen_random_uuid().
foreign_key	Foreign Key	Referential integrity link to another domain entity.
column_type	Column Type	PostgreSQL data type (e.g. uuid, text, numeric, jsonb, boolean, timestamptz).

Database Schema Reference

Database Schema Reference

Architectural Principles & Standards

Core Lineage & Entity Relationships

Schema Summary & Table Directory

Foreign Key Relationships Catalog

CRM & Stakeholders

Table: herobm_core.contacts

Table: herobm_core.crm_activities

Table: herobm_core.customer_delivery_addresses

Table: herobm_core.customer_groups

Table: herobm_core.customers

Table: herobm_core.opportunities

Table: herobm_core.opportunity_contacts

Table: herobm_core.opportunity_notes

Table: herobm_core.opportunity_organizations

Table: herobm_core.organization_contact_links

Table: herobm_core.organization_notes

Table: herobm_core.organization_organization_links

Table: herobm_core.organizations

Table: herobm_core.project_notes

Table: herobm_core.projects

Table: herobm_core.trading_terms

Products & Catalog

Table: herobm_core.discount_matrix

Table: herobm_core.product_components

Table: herobm_core.product_default_bins

Table: herobm_core.product_groups

Table: herobm_core.product_images

Table: herobm_core.product_suppliers

Table: herobm_core.product_uoms

Table: herobm_core.products

Table: herobm_core.uom_dictionary

Sales & Distribution

Table: herobm_core.backorders

Table: herobm_core.sales_credit_note_lines

Table: herobm_core.sales_credit_notes

Table: herobm_core.sales_events

Table: herobm_core.sales_invoice_lines

Table: herobm_core.sales_invoices

Table: herobm_core.sales_order_lines

Table: herobm_core.sales_order_picks

Table: herobm_core.sales_order_return_lines

Table: herobm_core.sales_order_returns

Table: herobm_core.sales_order_shipment_lines

Table: herobm_core.sales_order_shipments

Table: herobm_core.sales_orders

Purchasing & Procurement

Table: herobm_core.goods_received

Table: herobm_core.goods_received_lines

Table: herobm_core.procurement_events

Table: herobm_core.purchase_debit_note_lines

Table: herobm_core.purchase_debit_note_shipments

Table: herobm_core.purchase_debit_notes

Table: herobm_core.purchase_invoice_lines

Table: herobm_core.purchase_invoice_receipts

Table: herobm_core.purchase_invoices

Table: herobm_core.purchase_order_lines

Table: herobm_core.purchase_order_return_lines

Table: herobm_core.purchase_order_return_shipment_lines

Table: herobm_core.purchase_order_return_shipments

Table: herobm_core.purchase_order_returns

Table: herobm_core.purchase_orders

Table: herobm_core.supplier_expiries

Table: herobm_core.supplier_groups

Table: herobm_core.suppliers

Warehouse & Inventory

Table: herobm_core.bin_contents

Table: herobm_core.bins

Table: herobm_core.inventory_entries

Table: herobm_core.inventory_events

Table: herobm_core.inventory_ledger

Table: herobm_core.locations

Table: herobm_core.transfer_order_lines

Table: herobm_core.transfer_order_picks

Table: herobm_core.transfer_order_receipt_lines

Table: herobm_core.transfer_order_receipts

Table: herobm_core.transfer_order_shipment_lines

Table: herobm_core.transfer_order_shipments

Table: herobm_core.transfer_orders

Table: `herobm_core.contacts`

Table: `herobm_core.crm_activities`

Table: `herobm_core.customer_delivery_addresses`

Table: `herobm_core.customer_groups`

Table: `herobm_core.customers`

Table: `herobm_core.opportunities`

Table: `herobm_core.opportunity_contacts`

Table: `herobm_core.opportunity_notes`

Table: `herobm_core.opportunity_organizations`

Table: `herobm_core.organization_contact_links`

Table: `herobm_core.organization_notes`

Table: `herobm_core.organization_organization_links`

Table: `herobm_core.organizations`

Table: `herobm_core.project_notes`

Table: `herobm_core.projects`

Table: `herobm_core.trading_terms`

Table: `herobm_core.discount_matrix`

Table: `herobm_core.product_components`

Table: `herobm_core.product_default_bins`

Table: `herobm_core.product_groups`

Table: `herobm_core.product_images`

Table: `herobm_core.product_suppliers`

Table: `herobm_core.product_uoms`

Table: `herobm_core.products`

Table: `herobm_core.uom_dictionary`

Table: `herobm_core.backorders`

Table: `herobm_core.sales_credit_note_lines`

Table: `herobm_core.sales_credit_notes`

Table: `herobm_core.sales_events`

Table: `herobm_core.sales_invoice_lines`

Table: `herobm_core.sales_invoices`

Table: `herobm_core.sales_order_lines`

Table: `herobm_core.sales_order_picks`

Table: `herobm_core.sales_order_return_lines`

Table: `herobm_core.sales_order_returns`

Table: `herobm_core.sales_order_shipment_lines`

Table: `herobm_core.sales_order_shipments`

Table: `herobm_core.sales_orders`

Table: `herobm_core.goods_received`

Table: `herobm_core.goods_received_lines`

Table: `herobm_core.procurement_events`

Table: `herobm_core.purchase_debit_note_lines`

Table: `herobm_core.purchase_debit_note_shipments`

Table: `herobm_core.purchase_debit_notes`

Table: `herobm_core.purchase_invoice_lines`

Table: `herobm_core.purchase_invoice_receipts`

Table: `herobm_core.purchase_invoices`

Table: `herobm_core.purchase_order_lines`

Table: `herobm_core.purchase_order_return_lines`

Table: `herobm_core.purchase_order_return_shipment_lines`

Table: `herobm_core.purchase_order_return_shipments`

Table: `herobm_core.purchase_order_returns`

Table: `herobm_core.purchase_orders`

Table: `herobm_core.supplier_expiries`

Table: `herobm_core.supplier_groups`

Table: `herobm_core.suppliers`

Table: `herobm_core.bin_contents`

Table: `herobm_core.bins`

Table: `herobm_core.inventory_entries`

Table: `herobm_core.inventory_events`

Table: `herobm_core.inventory_ledger`

Table: `herobm_core.locations`

Table: `herobm_core.transfer_order_lines`

Table: `herobm_core.transfer_order_picks`

Table: `herobm_core.transfer_order_receipt_lines`

Table: `herobm_core.transfer_order_receipts`

Table: `herobm_core.transfer_order_shipment_lines`

Table: `herobm_core.transfer_order_shipments`

Table: `herobm_core.transfer_orders`

Table: `herobm_core.warehouse_events`

Table: `herobm_core.zones`

Table: `herobm_core.activities`

Table: `herobm_core.bank_statement_lines`

Table: `herobm_core.cost_centers`

Table: `herobm_core.csv_mapping_profiles`

Table: `herobm_core.exchange_rates`

Table: `herobm_core.financial_events`

Table: `herobm_core.gl_accounts`

Table: `herobm_core.gl_fiscal_periods`

Table: `herobm_core.gl_journal_entries`