Canary Retail Data Model

1. Three-Schema Architecture

The canary PostgreSQL database is organized into three schemas with distinct responsibilities. Total: 99 tables.

2. Table Inventory

Schema: app (49 tables)

Core Tenant & Identity

Table	Purpose	~Cols
merchants	Master merchant/tenant record	~12
merchant_settings	Per-merchant UI and behavior settings	~23
organizations	Organization grouping above merchants	~9
users	User accounts	~13
roles	Role definitions for RBAC	~5
user_roles	User↔Role join	~5
square_oauth_tokens	Square OAuth access/refresh tokens per merchant	~7
external_identities	External IdP mapping (Google, etc.)	~7
interest_signups	Pre-launch email capture	~5

Locations & Employees

Table	Purpose	~Cols
locations	Physical merchant locations with address, timezone, and status	~18
location_hierarchy	Parent/child location grouping	~9
employees	Employee records synced from Square	~10
user_employee_links	User↔Employee link for portal access	~5
employee_location_assignments	Employee↔Location active assignments	~5

Source Systems

Table	Purpose	~Cols
source_systems	Registered data source types (Square, future adapters)	~6
merchant_sources	Per-merchant source configuration and credentials	~10

Products & Customers

Table	Purpose	~Cols
products	Product catalog synced from Square catalog API	~9
customers	Customer records synced from Square	~7

Card Profiles

Table	Purpose	~Cols
card_profiles	Card fingerprint/BIN profiles for fraud pattern detection	~13
blocked_entities	Blocked cards, customers, or device fingerprints	~9

Detection & Alerts

Table	Purpose	~Cols
detection_rules	Rule definitions — 37 rules across 10 categories	~10
merchant_rule_config	Per-merchant threshold overrides for each rule	~9
alerts	Generated alert records with severity, status, and context	~19
alert_history	Alert state transition audit trail	~9

Feature Flags & Config

Table	Purpose	~Cols
feature_flags	Global feature flag definitions	~8
merchant_feature_flags	Per-merchant flag overrides	~7
app_config	Global key-value configuration store	~8

Namespacing

Table	Purpose	~Cols
namespace_registrations	Namespace records for multi-tenant data isolation	~12
namespace_aliases	Namespace alias mappings	~8

Notifications

Table	Purpose	~Cols
notification_log	Sent notification history (email, SMS, push)	~16
notification_schedule	Scheduled and recurring notification configuration	~18

Webhooks & Schema Drift

Table	Purpose	~Cols
webhook_events	Inbound Square webhook log — raw payload, processing status	~12
schema_fingerprints	Database schema hash snapshots for drift detection	~9
schema_drift_alerts	Detected schema change events and resolution status	~8

Fox — Case Management

Table	Purpose	~Cols
fox_cases	Investigation case records with status, type, severity	~19
fox_case_actions	Actions taken on a case (notes, escalations, closures)	~15
fox_case_alerts	Alerts linked to a case	~10
fox_case_timeline	Chronological event log for a case	~14
fox_evidence	Evidence items — documents, screenshots, transaction refs	~21
fox_evidence_access_log	Access audit trail for evidence items	~12
fox_subjects	Investigation subjects (employees, customers, devices)	~9

Goose — Credit System

Table	Purpose	~Cols
merchant_wallets	Sat-denominated merchant wallets — balance, status, limits	~13
wallet_transactions	Debit/credit ledger — append-only, immutable	~15
gas_schedule	Per-operation cost schedule (sats per action type)	~8
macaroon_tokens	L402 bearer tokens for API authentication	~12
strike_invoices	Strike Lightning invoices for wallet top-ups	~16

Owl — AI

Table	Purpose	~Cols
owl_sessions	AI agent sessions — prompt, context window, status	~16
owl_findings	Logged findings from Owl analysis runs	~11
owl_merchant_memory	Per-merchant persistent memory (pgvector embeddings)	~13
owl_action_log	Agent actions taken per session	~11

Other (app schema)

Table	Purpose	~Cols
vault_memories	ALX agent memory store	~11
audit_log	General application audit trail	~11
gift_cards	Gift card entity records	~11
subscriptions	Merchant subscription plan and billing state	~16
transfer_orders	Square transfer order records	~12
bank_accounts	Linked bank accounts for payout reconciliation	~15

Schema: sales (30 tables)

Core Transactions

Table	Purpose	~Cols
transactions	Payment records — master ledger. Append-only.	56
transaction_tenders	Tender breakdown per transaction (card, cash, gift card, etc.)	~13
transaction_line_items	Line items — products, quantities, prices, modifiers	~18
refund_links	Refund↔original transaction linkage	~12

Line Item Detail

Table	Purpose	~Cols
line_item_discounts	Discounts applied to individual line items	~15
line_item_taxes	Taxes applied to individual line items	~12
line_item_modifiers	Modifiers applied to individual line items	~12
order_service_charges	Service charges at the order level	~17
order_rewards	Loyalty rewards applied to orders	~7
order_returns	Returned items at the order level	~12

Invoices & Electronic Journal

Table	Purpose	~Cols
invoices	Square invoice records — status, payment schedule, recipients	~29
ej_links	Electronic journal cross-reference links	~14

Devices & Terminal

Table	Purpose	~Cols
devices	POS device registry — device ID, type, firmware, location	~19
terminal_checkouts	Terminal checkout sessions	~33
terminal_refunds	Terminal refund sessions	~18

Cash Drawers

Table	Purpose	~Cols
cash_drawer_shifts	Cash drawer shift sessions — open/close amounts, variance	~20
cash_drawer_events	Individual events within a shift (opens, deposits, removals)	~14

Payouts & Disputes

Table	Purpose	~Cols
payouts	Square payout records — amount, status, bank routing	~12
disputes	Chargeback records — reason, amount, status, deadline	~14
evidence_records	Dispute evidence submitted to Square	~15

Gift Cards & Loyalty

Table	Purpose	~Cols
gift_card_activities	Gift card activity log — activations, loads, redemptions, voids	~19
loyalty_accounts	Customer loyalty account records	~14
loyalty_events	Loyalty point earn and redemption events	~14

Employees & Inventory

Table	Purpose	~Cols
employee_timecards	Clock-in/clock-out records per employee per shift	~13
inventory_adjustments	Inventory count adjustments from Square	~13

Bitcoin Inscription

Table	Purpose	~Cols
inscription_pool	Inscription configuration and queue management	~21
event_inscriptions	Individual inscription records and Bitcoin txids	~16

ETL Pipeline

Table	Purpose	~Cols
ingestion_log	Per-fetch tracking — endpoint, params, record count, duration	~18
etl_batches	Batch execution records — status, error counts, timing	~16
dead_letter_queue	Failed webhook events queued for retry or manual review	~18

Schema: metrics (20 tables)

Fact Tables

Table	Purpose	~Cols
daily_metrics	Per-merchant daily aggregations — revenue, counts, averages, variances	~180
hourly_metrics	Per-merchant hourly aggregations — volume and velocity	~79
employee_daily_metrics	Per-employee daily performance and exception metrics	~166
product_daily_metrics	Per-product daily sales aggregations	~71
period_metrics	Rolling 7d/30d/90d aggregations per merchant	~203
employee_period_metrics	Rolling period aggregations per employee	~165

Dimensions

Table	Purpose	~Cols
dim_date	Calendar spine — day, week, month, quarter, year, fiscal periods	~19
dim_location	Location dimension for metrics joins	~12
dim_employee	Employee dimension for metrics joins	~11

Risk & ML

Table	Purpose	~Cols
transaction_features	ML feature vectors per transaction — 55 engineered features	~55
feature_definitions	Feature metadata — name, type, description, formula	~12
ml_models	Model version registry — algorithm, training date, performance metrics	~14
entity_risk_scores	Current risk scores per entity (employee, customer, device)	~13
risk_score_history	Risk score change history — score, delta, trigger	~12
metric_baselines	Statistical baselines per merchant and location	~16
velocity_baselines	Rate-of-change baselines for velocity detection rules	~19

Scorecards

Table	Purpose	~Cols
weekly_scorecard	Weekly merchant performance scorecard	~12
monthly_scorecard	Monthly merchant performance scorecard	~12
scorecard_thresholds	Configurable scorecard threshold definitions	~16
dashboard_config	Per-merchant dashboard layout and widget configuration	~13

3. Detection Rules

37 rules across 10 categories. Rules are defined in app.detection_rules and configured per merchant in app.merchant_rule_config. Thresholds are adjustable; rule logic is fixed in the Chirp detection engine.

Category	Rule	Description
Payment	PAY-001	Transaction amount exceeds configurable threshold
Payment	PAY-002	Refund without original transaction reference
Payment	PAY-003	Refund amount exceeds original sale amount
Payment	PAY-004	Multiple refunds to same card within time window
Payment	PAY-005	High refund rate by employee (period basis)
Payment	PAY-006	Round-dollar transaction pattern (potential skimming)
Payment	PAY-007	Tender type mismatch (card used as cash, etc.)
Payment	PAY-008	Split tender threshold — order split into micro-transactions
Payment	PAY-009	Discount rate exceeds authorized maximum
Payment	PAY-010	No-sale (cash drawer open) without transaction
Payment	PAY-011	Transaction velocity spike — rate exceeds baseline by threshold
Cash Drawer	CASH-001	Shift variance exceeds configurable tolerance
Cash Drawer	CASH-002	Drawer open frequency exceeds baseline
Cash Drawer	CASH-003	Cash removal without supervisor authorization
Cash Drawer	CASH-004	Shift ended without drawer close event
Order	ORD-001	Order deleted after payment recorded
Order	ORD-002	Line item removed after order opened
Order	ORD-003	Order price modified post-tender
Order	ORD-004	Zero-price line item added to completed order
Timecard	TIME-001	Transaction processed outside clocked-in window
Timecard	TIME-002	Overlapping timecard entries for same employee
Timecard	TIME-003	Shift hours exceed configurable maximum
Void	VOID-001	Void rate by employee exceeds threshold
Void	VOID-002	High-value void without manager authorization
Gift Card	GC-001	Gift card activated without corresponding sale
Gift Card	GC-002	Gift card balance depleted and reloaded within short window
Loyalty	LOY-001	Loyalty points accrued without matching transaction
Loyalty	LOY-002	Points redemption velocity exceeds baseline
Loyalty	LOY-003	Points adjustment without system event trigger
Loyalty	LOY-004	Multiple accounts with shared contact information
Composite	COMP-001	Correlated pattern: refund + void + cash removal within single shift
Dispute	DISP-001	Chargeback rate by location exceeds threshold
Dispute	DISP-002	Dispute on transaction with no card-present signature
Dispute	DISP-003	Repeated disputes from same card fingerprint
Invoice	INV-001	Invoice cancelled after partial payment received
Invoice	INV-002	Invoice amount modified after sending
Invoice	INV-003	Duplicate invoice to same customer within time window

4. Data Flow

5. POS-Agnostic Architecture

The CRDM canonical schema does not depend on Square-specific data structures. Square fields are mapped during ingestion by the TSP (Transaction Source Parser). A Clover, Toast, or Shopify adapter would implement the same parsing interface and write to the same sales schema tables.

Adapter	Status	Notes
Square	Live	Webhook + polling. Full coverage of payments, orders, cash drawers, timecards, disputes, loyalty, gift cards.
Clover	Planned	Schema mappings designed. Not yet implemented.
Toast	Planned	Restaurant-specific fields (covers, courses) map to service charges and modifiers.
Shopify	Planned	E-commerce adapter; cash drawer and timecard sections would be null.

The app.source_systems and app.merchant_sources tables manage adapter registration and per-merchant source configuration. Multi-source ingestion (e.g., a merchant running Square and an e-commerce channel) is supported by the schema design.