# Mirka Loyalty — Scan Workflow Database Design (Draft)

This is a design reference, not production-ready SQL. Confirm the actual MySQL/MariaDB version and review the existing platform schema before converting this into migrations.

## `customer_qr_tokens`
Suggested fields:
- `id`
- `customer_user_id`
- `business_id` (nullable if token is issued before a business membership is selected; otherwise required)
- `token_hash` (unique)
- `expires_at`
- `consumed_at` (nullable)
- `created_at`
- `created_ip` (optional; apply privacy and retention controls)

Indexes:
- Unique index on `token_hash`
- Index on `customer_user_id, expires_at`
- Index on `expires_at, consumed_at`

## `loyalty_transactions`
Suggested fields:
- `id`
- `business_id`
- `branch_id`
- `customer_user_id`
- `cashier_user_id`
- `customer_business_membership_id`
- `qr_token_id`
- `idempotency_key`
- `currency_code`
- `eligible_amount`
- `status` (for example: posted, reversed)
- `created_at`

Constraints and indexes:
- Unique `(business_id, idempotency_key)`
- Index `(business_id, branch_id, created_at)`
- Index `(customer_user_id, created_at)`
- Foreign keys to the relevant business, branch, user, membership, and QR token records where supported.

## `loyalty_stamp_ledger`
Suggested fields:
- `id`
- `business_id`
- `customer_user_id`
- `customer_business_membership_id`
- `loyalty_transaction_id`
- `entry_type` (earn, redemption, reversal, adjustment)
- `stamp_delta` (signed integer)
- `created_by_user_id`
- `reason` (nullable)
- `created_at`

Ledger entries should be append-only. A reversal creates a new negative compensating entry rather than modifying the original entry.

## Implementation notes
- Use InnoDB (or an equivalent transactional engine).
- Use a database transaction for token consumption, transaction creation, ledger append, and audit logging.
- Lock the QR token row while validating and consuming it.
- Enforce idempotency with a unique constraint, not only application-level checks.
- Calculate stamp awards on the server from the active program rules.
- Add explicit decimal precision and validation rules for monetary values after the currency and business rules are finalized.
- Consider retention and privacy requirements before storing IP/device metadata.