Infinity Portal
Docs menu

Core / Shared

Read Conventions first - the rules there (tenant scoping, status vocabularies, numbering) apply throughout this page without being repeated per table.

businesses

The tenant root. Also carries letterhead identity facts (name/logo/address/TIN), not operational preferences.

  • id - uuid - PK
  • name - text NOT NULL
  • created_at
  • logo_url - text, nullable
  • address - text NOT NULL DEFAULT ‘’ - required on Sri Lanka IRD VAT tax invoices (Gazette 2481/22)
  • phone, email, tax_id - text NOT NULL DEFAULT ‘’ (tax_id = supplier TIN)
  • admin_unlock_password_hash - text NULL - shared step-up password gating Full grants on Administration permissions

branches

Physical/organisational locations under a business.

  • id, business_id → businesses
  • name NOT NULL; color text DEFAULT ‘#6b7280’ (UI tag colour)
  • code, landmark, city, state, zip_code, country, mobile, alternate_contact_number, email, website - text NOT NULL DEFAULT ‘’
  • default_warehouse_id → warehouses
  • default_price_list_id → price_lists - POS at this branch prices from this list instead of items.default_selling_price
  • is_headquarters - boolean DEFAULT false - partial UNIQUE index (business_id) WHERE is_headquarters: at most one HQ branch per business
  • allow_oversell - boolean NULL - override; NULL = inherit business setting (precedence item → branch → business)
  • created_at

users

  • id, business_id → businesses
  • email - text NOT NULL UNIQUE globally (not per business)
  • password_hash
  • first_name, last_name - text NOT NULL DEFAULT ‘’ (replaced the original single name column)
  • mobile - for SMS OTP password reset
  • employee_id, avatar_url
  • is_super_admin boolean DEFAULT false; is_active boolean DEFAULT true (login blocks on false)
  • locked_reason - text NULL - distinguishes automatic lockout from manual deactivation
  • is_ai_native - boolean DEFAULT false - unlocks the AI Business Advisor capability
  • grade_id → employee_grades; position_id → positions
  • approval_limit_override - numeric(14,2) NULL - effective limit = COALESCE(this, grade default, 0)
  • totp_secret/totp_enabled/totp_enrolled_at - web-login 2FA
  • pos_totp_secret/pos_totp_enabled/pos_totp_enrolled_at - deliberately separate secret from web 2FA; cached decryptable on the offline terminal
  • pos_login_pin_hash - the PIN a cashier signs in with
  • pos_pin_hash - authorization-only PIN (approving someone else’s void/discount); never interchangeable with the login PIN
  • pos_card_token_hash - badge-scan authorization; random opaque token, not a PIN
  • show_on_pos_terminal - boolean DEFAULT true
  • current_session_id - uuid NULL - single-session-per-user; embedded as a JWT claim, stale value = signed out
  • created_at

permissions

Global catalogue of permission keys, not business-scoped. ~270 keys, e.g. purchase_order.add, purchase_order.post.

  • key - text - PK; description NOT NULL

roles

  • id, business_id, name, created_at

role_permissions

SAP B1-style 3-tier General Authorization.

  • role_id → roles CASCADE, permission_key → permissions(key) - composite PK
  • level - text DEFAULT ‘full’ - CHECK IN (‘read’,‘full’)

user_branches

Which branches a user can work in, and with which role there.

  • user_id → users CASCADE, branch_id → branches CASCADE - composite PK
  • role_id → roles NOT NULL

audit_logs

One row per changed field. Tamper-evident hash chain.

  • id, business_id, user_id → users (nullable - system writes)
  • entity_type text NOT NULL (e.g. ‘item’), entity_id uuid NOT NULL, entity_label
  • field text NOT NULL; old_value/new_value text NULL
  • seq - bigserial - strict deterministic ordering the hash chain links against (created_at can tie)
  • prev_hash, record_hash - text NULL - NULL on rows written before the hash chain was added; chain starts clean from there
  • created_at
  • Indexes: (business_id, entity_type, entity_id, created_at DESC), (business_id, seq DESC)

notifications

In-app notifications, one row per (user, event). Stores no route - the frontend maps entity_type → route.

  • id, business_id, user_id → users NOT NULL
  • type, title NOT NULL; body
  • entity_type - text DEFAULT ‘’ (reuses document-type strings: ‘purchase_order’, ‘ar_invoice’, …); entity_id uuid NULL
  • is_read boolean DEFAULT false
  • created_at; partial index (user_id) WHERE is_read = false for the badge count

business_settings

One row per business. Absorbed the old standalone Batch Settings page.

  • business_id - uuid PK
  • batch_number_format DEFAULT '{DATE:YYYYMMDD}-{GRN}-{SEQ:4}'
  • allow_oversell boolean DEFAULT true - business-wide default (branch/item may override)
  • start_date; timezone DEFAULT ‘UTC’
  • financial_year_start_month smallint DEFAULT 1 CHECK BETWEEN 1 AND 12
  • stock_accounting_method DEFAULT ‘fifo’ - CHECK IN (‘fifo’) only
  • transaction_edit_days int DEFAULT 60
  • date_format DEFAULT ‘mm/dd/yyyy’; time_format CHECK IN (‘12h’,‘24h’)
  • currency_symbol_placement CHECK IN (‘before’,‘after’); currency_precision smallint 2; quantity_precision smallint 3
  • default_profit_percent numeric(5,2)
  • default_sale_discount numeric(5,2); default_sale_tax_group_id → tax_groups
  • sales_item_addition_method CHECK IN (‘increase_quantity’,‘new_line’)
  • amount_rounding_method CHECK IN (‘none’,‘round_up’,‘round_down’,‘round_nearest’)
  • sales_price_is_minimum boolean
  • barcode_prefix, barcode_sku_length smallint 6, barcode_qty_integer_length smallint 2, barcode_qty_fractional_length smallint 3 - weighing-scale barcode layout
  • gift_voucher_expiry_months integer >= 0 - 0 = no default policy
  • loyalty_earn_rate_lkr_per_point numeric(10,2) - 0 = off
  • loyalty_redeem_value_per_point numeric(10,4) - 0 = off
  • change_money_enabled boolean; change_money_auto_redeem_threshold numeric(10,2) DEFAULT 10
  • pos_print_cancel_receipt/pos_print_void_receipt boolean DEFAULT true
  • pos_auto_print_sale_receipt boolean DEFAULT false - kiosk-style auto print
  • updated_at

accounting_settings

Per-business fixed G/L account targets. One row per business.

  • business_id - uuid PK
  • All uuid → gl_accounts nullable: goods_receipt_offset_account_id, goods_issue_offset_account_id, opening_balance_offset_account_id, inventory_count_offset_account_id, grn_offset_account_id, goods_return_offset_account_id, accounts_payable_account_id, down_payment_offset_account_id, accounts_receivable_account_id, ar_deferred_revenue_account_id, loyalty_points_liability_account_id, loyalty_points_expense_account_id, charges_income_account_id, realized_exchange_gain_loss_account_id, unrealized_exchange_gain_loss_account_id, fixed_asset_disposal_gain_loss_account_id
  • updated_at

gl_account_determination_defaults

Company-wide fallback accounts. Used only where the item’s chosen source leaves an account unconfigured.

  • business_id - uuid PK
  • 24 nullable uuid → gl_accounts columns: inventory_account_id, expense_account_id, revenue_account_id, cogs_account_id, allocation_account_id, variance_account_id, price_difference_account_id, negative_inventory_adjustment_account_id, inventory_offset_decrease_account_id, inventory_offset_increase_account_id, sales_returns_account_id, exchange_rate_difference_account_id, goods_clearing_account_id, gl_decrease_account_id, gl_increase_account_id, wip_inventory_account_id, wip_inventory_variance_account_id, wip_offset_account_id, inventory_offset_pl_account_id, expense_clearing_account_id, stock_in_transit_account_id, shipped_goods_account_id, sales_credit_account_id, purchase_credit_account_id
  • updated_at

document_sequences

Legacy per-type counter, superseded by document_numbering_series but still used for batch_number and pos_terminal.

  • business_id, document_type - composite PK; next_number integer DEFAULT 1

document_numbering_series

Configurable prefix/suffix/period numbering per document type, optionally per branch.

  • id, business_id, document_type NOT NULL
  • branch_id → branches NULL - NULL = the default series for that type
  • prefix, suffix DEFAULT ‘’
  • first_no/next_no integer DEFAULT 1; last_no NULL (cap)
  • period_indicator DEFAULT ‘none’ - CHECK IN (‘none’,‘yearly’,‘monthly’)
  • serial_digits integer DEFAULT 6 CHECK BETWEEN 0 AND 10
  • is_locked boolean DEFAULT false
  • created_at, updated_at
  • UNIQUE (business_id, document_type, branch_id) plus a partial unique index (business_id, document_type) WHERE branch_id IS NULL - Postgres treats each NULL as distinct, so this partial index is what actually enforces “at most one default series per document type”

document_numbering_series_years

Per-year counter for a period_indicator = 'yearly' series.

  • id, series_id → document_numbering_series CASCADE, year, next_no - UNIQUE (series_id, year)

document_numbering_series_periods

Same, keyed ‘YYYY-MM’, for a 'monthly' series.

  • id, series_id CASCADE, period, next_no - UNIQUE (series_id, period)

employee_grades

A named default monetary approval limit a user inherits.

  • id, business_id, name - UNIQUE (business_id, name)
  • default_approval_limit numeric(14,2) DEFAULT 0; created_at

positions

Job title, distinct from Grade; every position belongs to one grade, which supplies the approval limit.

  • id, business_id, name - UNIQUE (business_id, name)
  • grade_id → employee_grades NOT NULL; created_at

dashboard_widget_visibility

Presence of a row = that widget is visible to that position. Zero rows for a widget = visible to everyone.

  • id, business_id, widget_key, position_id → positions - UNIQUE (business_id, widget_key, position_id)

Per-business customized print HTML. A missing row means “use the built-in default”; deleting the row is how “Reset to default” works.

  • id, business_id
  • document_type - CHECK IN 19 values: purchase_request, purchase_order, grn, ap_invoice, ap_down_payment_invoice, goods_return_request, goods_return, ap_debit_memo, grn_sticker, sales_quotation, sales_order, ar_invoice, delivery, sales_return_request, sales_return, ar_credit_memo, incoming_payment, outgoing_payment, outgoing_payment_cheque_handover
  • html NOT NULL; updated_by NOT NULL, updated_at
  • UNIQUE (business_id, document_type)

Paper size per document type, deliberately separate from print_templates. Missing row = A4.

  • business_id, document_type - composite PK (same 19-value CHECK)
  • paper_size DEFAULT ‘A4’ - CHECK IN (‘A4’,‘A5’,‘letter’,‘legal’)
  • updated_by NOT NULL, updated_at

document_print_log

One row per actual print action (not per preview load) - lets the print view stamp “REPRINT” on the 2nd+ printout.

  • id, business_id, document_type, document_id, printed_by, printed_at
  • Index (business_id, document_type, document_id)

sticker_sheet_settings

Named label-sheet geometry, in inches, calibrated by the operator.

  • id, business_id, name - UNIQUE (business_id, name); description
  • is_continuous_feed boolean DEFAULT false
  • margin_top_in, margin_left_in numeric >= 0
  • sticker_width_in, sticker_height_in numeric > 0
  • paper_width_in, paper_height_in numeric NULL, > 0 when set - CHECK required unless is_continuous_feed
  • stickers_per_row int > 0; row_distance_in, column_distance_in numeric >= 0
  • stickers_per_sheet int > 0 - user-entered, deliberately not derived from geometry
  • is_default - partial UNIQUE index (business_id) WHERE is_default
  • is_active, created_at

Notification channel settings

  • sms_settings - business_id PK; mask, username, password, test_number DEFAULT ‘’; register_close_notification_numbers text DEFAULT ‘’ (comma-separated; empty = feature off); updated_at
  • email_settings - business_id PK; host, port int DEFAULT 587, username, password; encryption DEFAULT ‘tls’ CHECK IN (‘none’,‘tls’,‘ssl’); from_address, from_name, test_email; updated_at
  • telegram_settings - business_id PK; bot_token, chat_id DEFAULT ‘’; updated_at
  • telegram_alert_topics - id, business_id, event_key, enabled boolean DEFAULT false, thread_id integer NULL - UNIQUE (business_id, event_key)
  • whatsapp_settings - business_id PK; phone_number_id text - partial UNIQUE index WHERE phone_number_id != '' (the inbound webhook carries only this, never a business_id); waba_id, access_token, app_secret, verify_token DEFAULT ‘’; is_enabled DEFAULT true; updated_at
  • whatsapp_authorized_numbers - a number only gets answers once linked to a real user account; questions run under that user’s own permissions. id, business_id, phone_number (digits only, no “+”), user_id NOT NULL, label, created_by, created_at - UNIQUE (business_id, phone_number)
  • whatsapp_pending_price_changes - staged price change from a texted “SKU price [list]” command, applied only on a YES reply from the same number; one pending change per number. id, business_id, phone_number, item_id NOT NULL, price_list_id NULL (NULL = the item’s own default selling price), old_price, new_price NOT NULL, created_at - UNIQUE (business_id, phone_number)

notification_benchmarks

Global (not business-scoped) developer-controlled catalogue of customer-notifiable document-lifecycle events.

  • id, code - text NOT NULL UNIQUE (e.g. ‘ar_invoice.posted’)
  • document_type, document_label, event_key, name NOT NULL; description
  • merge_tags jsonb DEFAULT ‘[]’
  • default_sms_body, default_email_subject, default_email_body
  • is_wired boolean DEFAULT false - only true for benchmarks that actually have a dispatch call site (‘ar_invoice.posted’, ‘incoming_payment.received’); everything else is configurable but inert
  • sort_order; created_at; index (document_type, sort_order)

notification_triggers

Per-business, per-channel configuration of a benchmark. SMS and email are two independent rows.

  • id, business_id, benchmark_code → notification_benchmarks(code) NOT NULL
  • channel - CHECK IN (‘sms’,‘email’); enabled boolean DEFAULT false
  • subject, body DEFAULT ‘’; updated_at
  • UNIQUE (business_id, benchmark_code, channel)

notification_templates_deprecated

The pre-rebuild bundled per-(business, event) template table, renamed not dropped as a one-release rollback net. Historical only.

report_definitions

A saved report: either a visual-builder definition or a raw-SQL System Query. Shared business-wide.

  • id, business_id, name, description
  • kind DEFAULT ‘builder’ - CHECK IN (‘builder’,‘system_query’)
  • base_table DEFAULT ‘’
  • definition jsonb DEFAULT ‘{}’ - matches the reporting engine’s QueryDefinition 1:1
  • sql_text DEFAULT ‘’ - the implicit first tab (“Detailed”); {{param}} placeholders always become bound SQL parameters
  • views jsonb NOT NULL DEFAULT ‘[]’ - additional named query variants (tabs)
  • is_standard boolean DEFAULT false - the seeded canned reports; handlers refuse to delete/edit these
  • created_by NOT NULL, created_at, updated_at

Currency tables

  • currencies - per-business currency master, seeded with LKR, USD, EUR, GBP, INR, AUD, SGD, AED, JPY, CNY. id, business_id, code, name, symbol, is_active, created_at - UNIQUE (business_id, code)
  • currency_settings - business_id PK; base_currency_id → currencies; spread_percent DEFAULT 1.0 CHECK >= 0 AND < 50 (buying = mid − spread, selling = mid + spread); updated_at
  • exchange_rates - manually-entered (or API) daily rates. rate = units of base currency per 1 unit of this currency. id, business_id, currency_id, rate_date, rate CHECK > 0, source DEFAULT ‘manual’ CHECK IN (‘manual’,‘api’), created_at - UNIQUE (business_id, currency_id, rate_date)

posting_periods

Closing a period blocks new posting/cancelling of documents dated inside it. Non-overlap enforced in the app layer, not by constraint.

  • id, business_id, name - UNIQUE (business_id, name)
  • start_date, end_date - CHECK (end_date >= start_date)
  • status DEFAULT ‘open’ - CHECK IN (‘open’,‘unlocked_except_sales’,‘closing’,‘closed’); ‘closing’ still permits users holding inventory_transactions.manage
  • closed_at, closed_by, created_by, created_at; index (business_id, start_date, end_date)

Sales target tables

  • sales_targets - business_id, branch_id composite PK; daily_target, hourly_target numeric NULL (NULL/0 on either turns that check off); notify_numbers text DEFAULT ‘’ comma-separated; updated_at
  • sales_target_alerts_sent - dedup lock so a reached target doesn’t re-notify on every subsequent sale. business_id, branch_id, target_type (CHECK IN (‘daily’,‘hourly’)), period_key composite PK (‘YYYY-MM-DD’ for daily, ‘YYYY-MM-DD-HH’ for hourly); sent_at

Auth support tables

  • user_totp_backup_codes - id, user_id CASCADE, code_hash, used_at NULL, created_at
  • session_takeover_events - one row per completed “remove the other session” choice, used to rate-limit forced sign-outs. id, user_id NOT NULL, created_at; index (user_id, created_at)
  • security_action_tokens - single-use expiring tokens for emailed password-reset/account-unlock links; only the SHA-256 hash is stored. id, user_id NOT NULL, purpose CHECK IN (‘password_reset’,‘account_unlock’), token_hash NOT NULL UNIQUE, expires_at NOT NULL, used_at NULL, created_at; partial index (token_hash) WHERE used_at IS NULL
  • otp_codes - short numeric SMS-delivered codes; needs attempt-limiting a URL token doesn’t. id, business_id, purpose, subject_id (users.id for password_reset, business_partners.id for loyalty_redeem), code_hash, attempts int DEFAULT 0, expires_at NOT NULL, used_at NULL, verified_at NULL (set when a loyalty_redeem OTP verifies), created_at; index (business_id, purpose, subject_id, created_at DESC)

POS terminal release tables

  • pos_terminal_releases - published pos-terminal (desktop) builds. Deliberately not business-scoped - one release history per install. id, version NOT NULL UNIQUE, download_url NOT NULL, release_notes, mandatory boolean DEFAULT false (blocking update overlay), created_by, created_at
  • pos_terminal_release_tokens - standing CI credential to publish releases without a human login. id, name, token_hash NOT NULL UNIQUE, created_by, created_at, last_used_at