Database Schema
The Anitone backend runs on Xano (Workspace 4) with a PostgreSQL database. Tables are organized into functional domains.B2B Sales Tables
ar_distributor
B2B customer accounts (Nutrien Ag, Elders, etc.)| Field | Type | Description |
|---|---|---|
id | int | Primary key |
company_name | text | Company name |
trading_name | text | Trading name |
abn | text | Australian Business Number |
billing_address | json | |
shipping_address | json | Default shipping address |
payment_terms | int | Net days (30, 60, 90) |
credit_limit | decimal | Maximum AR balance |
price_tier | enum | standard, gold, platinum |
discount_percentage | decimal | % off retail |
xero_contact_id | text | Xero sync reference |
status | enum | prospect, active, on_hold, archived |
ar_contact
Individual contacts at distributor accounts.| Field | Type | Description |
|---|---|---|
id | int | Primary key |
distributor_id | int | → ar_distributor |
first_name | text | - |
last_name | text | - |
email | - | |
phone | text | - |
role | text | Job title |
is_primary | bool | Main contact |
is_billing_contact | bool | Receives invoices/AR comms |
ar_purchase_order
Inbound purchase orders from distributors.| Field | Type | Description |
|---|---|---|
id | int | Primary key |
distributor_id | int | → ar_distributor |
po_number | text | Distributor’s PO reference |
order_date | date | - |
required_date | date | Requested delivery |
status | enum | draft, pending_review, confirmed, invoiced, shipped, delivered, cancelled |
subtotal | decimal | - |
tax | decimal | GST |
total | decimal | - |
shipping_method | enum | parcel, pallet, pickup |
source | enum | manual, ai_parsed |
ai_confidence | decimal | Parser confidence (0-1) |
ar_purchase_order_line
Line items on purchase orders.| Field | Type | Description |
|---|---|---|
id | int | Primary key |
purchase_order_id | int | → ar_purchase_order |
product_variant_id | int | → ar_product_variant |
quantity | int | - |
unit_price | decimal | - |
line_total | decimal | - |
Financial Tables
ar_invoice
Sales invoices (B2B and B2C).| Field | Type | Description |
|---|---|---|
id | int | Primary key |
invoice_number | text | Auto-generated (INV-YYYY-NNNN) |
invoice_type | enum | b2b, b2c, credit_note |
distributor_id | int | → ar_distributor (B2B) |
consumer_email | B2C customer | |
purchase_order_id | int | → ar_purchase_order |
shopify_order_id | text | Shopify reference |
invoice_date | date | - |
due_date | date | - |
status | enum | draft, sent, viewed, partial, paid, overdue, void |
subtotal | decimal | - |
tax | decimal | GST |
total | decimal | - |
amount_paid | decimal | - |
amount_due | decimal | - |
xero_invoice_id | text | Xero sync |
last_nudge_at | timestamp | AR nudging |
nudge_count | int | Times nudged |
ar_invoice_line
Invoice line items.| Field | Type | Description |
|---|---|---|
id | int | Primary key |
invoice_id | int | → ar_invoice |
product_variant_id | int | → ar_product_variant |
description | text | - |
quantity | int | - |
unit_price | decimal | - |
discount_percent | decimal | - |
tax_rate | decimal | GST (10%) |
line_total | decimal | - |
ar_payment
Payment records against invoices.| Field | Type | Description |
|---|---|---|
id | int | Primary key |
invoice_id | int | → ar_invoice |
payment_date | date | - |
amount | decimal | - |
payment_method | enum | eft, credit_card, stripe, cash, cheque |
reference | text | Bank ref / Stripe ID |
xero_payment_id | text | Xero sync |
Product Tables
ar_product
Master product catalog (Anitone + Howler & Barkes).| Field | Type | Description |
|---|---|---|
id | int | Primary key |
sku | text | Stock keeping unit |
name | text | Product name |
description | text | Rich description (markdown) |
brand | enum | anitone, howler_barkes |
status | enum | draft, active, archived |
shopify_product_id | text | Shopify sync |
is_hazmat | bool | Freight restrictions |
weight_kg | decimal | Shipping weight |
ar_product_variant
SKU-level variants (sizes).| Field | Type | Description |
|---|---|---|
id | int | Primary key |
product_id | int | → ar_product |
sku | text | Variant SKU (A1W, A5W) |
name | text | Size label (250ml, 1L, 5L) |
barcode | text | UPC/EAN |
price_retail | decimal | RRP Inc GST |
price_wholesale | decimal | Wholesale Ex GST |
wholesale_per_carton | decimal | Carton price Ex GST |
packaging | text | e.g., “Carton of 20” |
inventory_qty | int | Current stock |
low_stock_threshold | int | Alert trigger |
shopify_variant_id | text | Shopify sync |
ar_product_category
Product categorization.| Field | Type | Description |
|---|---|---|
id | int | Primary key |
name | text | Category name |
slug | text | URL-friendly ID |
brand | enum | anitone, howler_barkes, both |
Anitone-Specific Tables
anitone_products
Anitone product variants and pricing (simplified view).| Field | Type | Description |
|---|---|---|
id | int | Primary key |
sku | text | SKU identifier |
size_label | text | Size label |
size_ml | int | Size in ml |
price_aud | decimal | Price AUD |
target_market | text | Target market |
shopify_variant_id | text | Shopify link |
in_stock | bool | Stock status |
anitone_species_dosing
Species-specific dosing rules for calculator.| Field | Type | Description |
|---|---|---|
id | int | Primary key |
species_key | text | horses, cattle, sheep_goats, etc. |
display_name | text | Human-readable name |
dosage_ml_per_kg | decimal | Dosage rate |
dosage_unit | text | Unit description |
min_weight_kg | int | Weight range min |
max_weight_kg | int | Weight range max |
default_weight_kg | int | Default for quick calc |
key_benefits | text[] | Benefits list |
shopify_collection_handle | text | Collection link |
Integration Tables
ar_integration_config
External system credentials and settings.| Field | Type | Description |
|---|---|---|
id | int | Primary key |
integration_type | enum | shopify, xero, stripe, starshipit, northline |
is_enabled | bool | Active flag |
credentials | json | Encrypted API keys |
settings | json | Integration config |
last_sync_at | timestamp | Last sync time |
sync_status | enum | ok, error, never |
ar_integration_log
API call audit trail.| Field | Type | Description |
|---|---|---|
id | int | Primary key |
integration_type | enum | shopify, xero, stripe, etc. |
operation | text | Operation description |
direction | enum | inbound, outbound |
request_payload | json | Request data |
response_payload | json | Response data |
status_code | int | HTTP status |
duration_ms | int | Performance |
ar_xero_sync_log
Xero-specific sync audit.| Field | Type | Description |
|---|---|---|
id | int | Primary key |
sync_type | enum | invoice, payment, contact |
direction | enum | push, pull |
xero_id | text | Xero reference |
local_table | text | Local table name |
local_id | int | Local record ID |
status | enum | pending, success, failed |
Supporting Tables
ar_document
Central file storage.| Field | Type | Description |
|---|---|---|
id | int | Primary key |
document_type | enum | po_pdf, invoice_pdf, contract, other |
title | text | Document title |
file_url | text | Storage URL |
file_name | text | Original filename |
related_table | text | Polymorphic reference |
related_id | int | Related record ID |
ar_team_member
Staff/contractor directory.| Field | Type | Description |
|---|---|---|
id | int | Primary key |
user_id | int | → user (optional) |
first_name | text | - |
last_name | text | - |
email | - | |
role | text | Job title |
type | enum | staff, contractor |
department | enum | operations, sales, admin, management |
status | enum | active, inactive |