Database Schema

MariaDB 10.6+ database pckmiddle. Schema is versioned with Flyway; Hibernate runs in validate mode, meaning entities must match the migrated schema.

Migrations (V001–V008)

Migration	Description
`V001__Initial_Schema.sql`	Core tables (api_transactions, conversion_logs, system_config), views, stored procedures, indexes
`V002__Add_Store_Id_To_Logs.sql`	Add `store_id` to api_transactions and conversion_logs
`V003__Add_Fiscal_Invoices_Table.sql`	fiscal_invoices table for receipt tracking and refund lookups
`V004__Alter_Fiscal_Invoices_Receipt_Types.sql`	Ensure `receipt_number` is VARCHAR
`V005__Ensure_Receipt_Number_Varchar.sql`	Additional type safety for receipt_number
`V006__Add_Menu_Sync_To_Store_Registrations.sql`	Add `oracle_menu_path`, `menu_sync_enabled`, `default_department` to store_registrations
`V007__Enlarge_Api_Transaction_Body_Columns.sql`	Enlarge api_transactions body columns to LONGTEXT
`V008__Add_Buyer_Address_To_Fiscal_Invoices.sql`	Add `buyer_house_no`, `buyer_street`, `buyer_province` to fiscal_invoices for POS check printing

Tables

api_transactions

Full request/response audit trail for every API call.

Key columns	Notes
`transaction_id`	UUID assigned at the door; links all records for one request
`store_id`	Tagged after store resolution (V002)
`source_api`, `target_api`, `endpoint`, `method`	Route identification
`request_headers`, `request_body`, `response_headers`, `response_body`	Full payloads (LONGTEXT since V007)
`status_code`, `success`, `error_message`	Outcome
`response_time_ms`, `created_at`	Timing

conversion_logs

Data transformation audit trail.

Key columns	Notes
`transaction_id`, `store_id`	Linkage
`conversion_type`, `source_format`, `target_format`	e.g. TAXINT → CHRILAN
`source_data`, `target_data`	Before/after payloads
`mapping_rules`, `validation_errors`	Rules applied; error detail on failure
`conversion_success`, `processing_time_ms`	Outcome and timing

fiscal_invoices

Issued invoice records — the foundation of credit-note lookups.

Key columns	Notes
`audit_number`	From the POS cashier number
`receipt_number`	ZRA receipt number (VARCHAR)
`sdc_id`	Smart Device Controller ID
`store_id`, `tpin`, `currency`	Attribution
`is_refund`, `original_receipt_number`	Refund linkage
`buyer_house_no`, `buyer_street`, `buyer_province`	Buyer address (V008)

store_registrations

Key columns	Notes
`seller_name`	Primary identifier; unique; must match POS SellerName exactly
`tpin`	Store TPIN
`chrilan_api_key`	Per-store key; no default exists anywhere
`active`	Soft-delete flag
`oracle_menu_path`, `menu_sync_enabled`, `default_department`	Menu sync configuration (V006)
`notification_phone`, `notification_group_id`, `notifications_enabled`	WhatsApp alert targets

users

Key columns	Notes
`email`	Login username
`password`	BCrypt hash
`phone_number`	WhatsApp OTP destination
`otp_code`, `otp_expiry`, `otp_verified`	OTP state (5-minute expiry)

system_config

Generic key-value configuration store (config_key, config_value, description).

Views

View	Purpose
`transaction_statistics`	Aggregated transaction success/failure counts
`conversion_statistics`	Aggregated conversion success/failure counts

Stored procedures

Procedure	Purpose
`GetTransactionStatistics`	Transaction counts by date range
`GetFailedTransactions`	Failed transactions by date range
`CleanupOldTransactions`	Purge transactions older than N days — manual use only; retention is infinite by default

Indexing & performance

Comprehensive indexes on all lookup columns (transaction IDs, store IDs, timestamps, receipt numbers, audit numbers).
HikariCP pool: max 20 connections, min 5 idle, 30s connection timeout, leak detection at 60s.
Hibernate batching: batch_size=20 with ordered inserts/updates.