MPAC Cloud — Refined Database Schema Design

Status: Draft Version: 1.0 Date: 2026-03-25 Supersedes: psp-admin/terminal_master_schema_design.md, user-and-role/database-schema.mdTarget Database: PostgreSQL 16+ Target Service: svc-portal (single database, shared by svc-smarttab)

---

1. Overview

This schema unifies two previously independent designs — PSP Admin (terminal lifecycle management) and User & Role (fine-grained RBAC) — into the existing svc-portal database. The result is a single coherent schema that covers:

• Multi-tenant hierarchy (Platform → PSP → Merchant → Store)
• Unified RBAC with PLATFORM, MERCHANT, and STORE permission scopes
• Device lifecycle management (merged devices + terminals)
• Delivery, billing, and maintenance tracking
• Audit logging

Design Decisions Summary

Decision	Choice	Rationale
Schema strategy	Evolve svc-portal's existing schema	Avoid new databases; extend the existing foundation
Primary key	UUIDv7 everywhere	Time-ordered, API-safe, conflict-free across services
Business identifiers	Drop unused ones	Only keep where actively needed (e.g. order_number)
Role model	Unified RBAC (Option C — flatten)	One permission check code path; no role enum
Permission scopes	PLATFORM / MERCHANT / STORE	Platform permissions for PSP Admin features; merchant/store for User & Role
Store access	Full model (store_access + store_access_roles)	Per-store role differentiation is a real business requirement
User identity	UUID PK + email login	username/login_id deferred to future decision
Device table	Merge devices + terminals into devices	Same physical hardware, one source of truth
Device status	Two columns: asset_status + connection_status	Independent lifecycles (physical asset vs software connectivity)
Communication plans	Replaced by device_profiles	Full device template (billing + hardware + operational config)
is_active columns	Dropped everywhere	status column is the single source of truth
FK naming	merchant_pk for FK to merchants	Avoids confusion with MID (Merchant ID from payment providers)
Tenant scoping	psp_id + merchant_pk on users	Derived from nullable FKs, no separate tenant_type enum

---

2. Entity Relationship Overview

psps ─────────────────────────────────────────┐
  │                                             │
  ├──► merchants                                │
  │       │                                     │
  │       ├──► stores                           │
  │       │       │                             │
  │       │       ├──► devices ◄── device_profiles
  │       │       │     │  │  │        ◄── device_models
  │       │       │     │  │  │
  │       │       │     │  │  ├──► device_activation_history
  │       │       │     │  │  ├──► device_status_history
  │       │       │     │  │  ├──► breakdown_history
  │       │       │     │  │  └──► billing_records
  │       │       │     │  │
  │       │       │     │  └──► delivery_orders ──► device_returns
  │       │       │     │
  │       │       │     └──► iot_credentials
  │       │       │
  │       │       └──► store_access ──► store_access_roles
  │       │
  │       └──► users ──► user_default_roles
  │               │
  │               └──► audit_logs
  │
  ├──► users (psp-level)
  │
  └──► users (platform-level, psp_id = NULL)

roles ──► role_permissions ◄── permissions ◄── permission_groups
  │
  ├──► user_default_roles
  └──► store_access_roles

---

3. Table Inventory (26 tables)

#	Table	Status	Category	Description
	Tenant Hierarchy
1	psps	MODIFIED	Reference	Payment Service Providers. Migrate PK to UUIDv7.
2	merchants	MODIFIED	Reference	Merchants under PSPs. Migrate PK to UUIDv7.
3	stores	MODIFIED	Reference	Stores under merchants. Migrate PK to UUIDv7. Add address/GPS.
	User & Role (RBAC)
4	users	MODIFIED	Core	All user accounts. Drop role enum + store_id. Add RBAC fields.
5	roles	NEW	Core	Named permission sets. Scope: PLATFORM / MERCHANT / STORE.
6	permissions	NEW	Seed	Permission constants (~46 keys).
7	permission_groups	NEW	Seed	Logical groupings for UI rendering.
8	role_permissions	NEW	Join	Which permissions a role grants.
9	user_default_roles	NEW	Join	User's baseline roles (PLATFORM + MERCHANT scoped).
10	store_access	NEW	Join	Per-store access mode: NO_ACCESS / DEFAULT / CUSTOM.
11	store_access_roles	NEW	Join	Custom role overrides per store (STORE scoped).
	Device Management
12	devices	MODIFIED	Core	Unified device/terminal table. Two status columns.
13	device_models	NEW	Reference	Model series + name + lot number.
14	device_profiles	NEW	Reference	Reusable config templates (billing + hardware + ops).
15	iot_credentials	MODIFIED	Core	AWS IoT metadata. FK migration only.
16	device_activation_history	NEW	History	Activation events. One active per device.
17	device_status_history	NEW	History	Asset + connection status transition log.
18	breakdown_history	NEW	Maintenance	Breakdown, replacement, battery records.
	Delivery & Billing
19	delivery_orders	NEW	Delivery	Order → Picking → Shipment → Delivery lifecycle.
20	device_returns	NEW	Delivery	Return tracking and disposal.
21	billing_records	NEW	Billing	Lease / communication / commission fees. May be removed later.
	Auth & Audit
22	jwt_keys	KEEP	Auth	Signing keys.
23	sessions	KEEP	Auth	Active sessions.
24	mfa_codes	KEEP	Auth	MFA verification codes.
25	authentication_logs	KEEP	Audit	Auth event log.
26	audit_logs	NEW	Audit	General entity change log. JSONB before/after snapshots.

Dropped:

• staff — replaced by users with STORE-scoped roles
• communication_plans — replaced by device_profiles
• users.role (enum column) — replaced by RBAC roles table
• users.store_id — replaced by store_access table
• devices.device_code — replaced by device_model_id FK
• is_active column on all tables — status is the single source of truth

Not in scope (unchanged, owned by svc-smarttab): orders, order_items, bills, splits, payments, refunds, receipts, invoices, bill_operation_histories, customers, coupons, loyalty_programs, loyalty_transactions, settlements, payment_methods, payment_providers, payment_provider_methods

---

4. ID Convention

All tables use UUIDv7 as the surrogate primary key. UUIDv7 is time-ordered (monotonically increasing), so B-tree insert performance is equivalent to auto-increment. It is opaque (safe to expose in APIs), conflict-free across services, and consistent across all tables.

Available natively in PostgreSQL 17+ via uuidv7(), or via the pg_uuidv7 extension for PostgreSQL 16.

Business-facing identifiers (e.g. order_number on delivery_orders) are kept only where actively needed by external systems or humans. They are UNIQUE but never used as FKs.

---

5. Column-Level Design

5.1 Tenant Hierarchy

psps

CREATE TABLE psps (
    id                      UUID PRIMARY KEY DEFAULT uuidv7(),
    psp_code                VARCHAR(8) NOT NULL UNIQUE,     -- PSPコード (human-readable, generated, e.g. "KAZAPI01")
    psp_name                VARCHAR(255) NOT NULL,
    contact_email           VARCHAR(255),
    contact_phone           VARCHAR(50),
    payment_config          JSONB,
    shipping_timing_policy  TEXT,
    status                  TEXT NOT NULL DEFAULT 'active'
                            CHECK (status IN ('active', 'inactive', 'suspended')),
    created_at              TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at              TIMESTAMPTZ NOT NULL DEFAULT now()
);

merchants

CREATE TABLE merchants (
    id                          UUID PRIMARY KEY DEFAULT uuidv7(),
    psp_id                      UUID NOT NULL REFERENCES psps(id) ON DELETE RESTRICT,
    merchant_code               VARCHAR(8) NOT NULL UNIQUE,  -- 加盟店コード (human-readable, generated, e.g. "KANON001")
    merchant_name               VARCHAR(255) NOT NULL,
    payment_config              JSONB,
    business_registration_number VARCHAR(50),
    status                      TEXT NOT NULL DEFAULT 'active'
                                CHECK (status IN ('active', 'inactive', 'suspended')),
    created_at                  TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at                  TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX idx_merchants_psp_id ON merchants(psp_id);

stores

CREATE TABLE stores (
    id              UUID PRIMARY KEY DEFAULT uuidv7(),
    merchant_pk     UUID NOT NULL REFERENCES merchants(id) ON DELETE RESTRICT,
    store_name      VARCHAR(255) NOT NULL,
    mps_id          VARCHAR(64) UNIQUE,         -- maps to PGW store
    -- Address
    postal_code     VARCHAR(10),
    prefecture      VARCHAR(50),
    city            VARCHAR(100),
    address_line    TEXT,
    latitude        NUMERIC(9,6) CHECK (latitude BETWEEN -90 AND 90),
    longitude       NUMERIC(9,6) CHECK (longitude BETWEEN -180 AND 180),
    -- Config
    payment_config  JSONB,
    printer_items   JSONB,
    -- Status
    status          TEXT NOT NULL DEFAULT 'active'
                    CHECK (status IN ('active', 'inactive', 'suspended', 'archived')),
    created_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at      TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX idx_stores_merchant_pk ON stores(merchant_pk);

Note: archived status is used by the RBAC store access model — archived stores preserve existing access records but block new access assignments (see invariant #13).

5.2 Users

users

CREATE TABLE users (
    id                  UUID PRIMARY KEY DEFAULT uuidv7(),
    -- Tenant scoping
    psp_id              UUID REFERENCES psps(id),           -- NULL = platform user
    merchant_pk         UUID REFERENCES merchants(id),      -- NULL = PSP-level user
    -- Identity
    email               VARCHAR(255) NOT NULL UNIQUE,
    name                VARCHAR(255) NOT NULL,
    description         TEXT,                               -- role/job label
    password_hash       TEXT NOT NULL,
    password_changed_at TIMESTAMPTZ,
    is_protected        BOOLEAN NOT NULL DEFAULT false,     -- cannot be deactivated
    -- MFA
    mfa_enabled         BOOLEAN NOT NULL DEFAULT false,
    mfa_method          TEXT CHECK (mfa_method IN ('NONE', 'SMS', 'EMAIL', 'TOTP')),
    totp_secret         TEXT,
    -- Security
    failed_login_attempts INTEGER NOT NULL DEFAULT 0,
    locked_until        TIMESTAMPTZ,
    -- Status
    status              TEXT NOT NULL DEFAULT 'active'
                        CHECK (status IN ('active', 'inactive')),
    last_login_at       TIMESTAMPTZ,
    created_at          TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at          TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX idx_users_psp_id ON users(psp_id);
CREATE INDEX idx_users_merchant_pk ON users(merchant_pk);

Tenant scoping rules (application-level enforcement):

• psp_id = NULL, merchant_pk = NULL → Platform user (sees all data)
• psp_id = set, merchant_pk = NULL → PSP user (sees data under their PSP)
• psp_id = set, merchant_pk = set → Merchant user (sees data under their merchant, store access via store_access table)
• If merchant_pk is set, psp_id must also be set
• If merchant_pk is set, merchants.psp_id must match users.psp_id

5.3 RBAC

roles

CREATE TABLE roles (
    id          UUID PRIMARY KEY DEFAULT uuidv7(),  -- deterministic UUIDs for system roles
    name        VARCHAR(100) NOT NULL,
    scope       TEXT NOT NULL CHECK (scope IN ('PLATFORM', 'MERCHANT', 'STORE')),
    is_system   BOOLEAN NOT NULL DEFAULT false,
    merchant_pk UUID REFERENCES merchants(id),      -- NULL for system/platform roles
    created_at  TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- Role names must be unique within a merchant (or globally for system roles)
CREATE UNIQUE INDEX uq_roles_name_per_merchant
    ON roles(name, COALESCE(merchant_pk, '00000000-0000-0000-0000-000000000000'));

System Roles (fixed UUIDs):

UUID	Name	Scope	Description
00000000-0000-0000-0000-000000000001	System Admin	PLATFORM	Full system access (MPS internal)
00000000-0000-0000-0000-000000000002	Operator	PLATFORM	Operational access (MPS internal)
00000000-0000-0000-0000-000000000003	CS Agent	PLATFORM	Read-only terminal search (call center)
00000000-0000-0000-0000-000000000004	PSP Manager	PLATFORM	All data under their PSP
00000000-0000-0000-0000-000000000005	Viewer	PLATFORM	Read-only within tenant scope
00000000-0000-0000-0000-000000000010	HQ Admin (本部管理者)	MERCHANT	Full merchant + all store permissions
00000000-0000-0000-0000-000000000011	Area Manager (エリアマネージャー)	MERCHANT	Multi-store oversight + merchant admin
00000000-0000-0000-0000-000000000012	General (一般)	MERCHANT	Basic org-wide + standard store access
00000000-0000-0000-0000-000000000013	Accounting (経理)	MERCHANT	Sales/financial data access
00000000-0000-0000-0000-000000000020	Store Manager (店長)	STORE	Full store operations for a specific store
00000000-0000-0000-0000-000000000021	Staff (スタッフ)	STORE	Basic store operations for a specific store

permission_groups

CREATE TABLE permission_groups (
    group_id    VARCHAR(64) PRIMARY KEY,     -- e.g. 'TERMINAL_MGMT', 'ORDER_MGMT'
    scope       TEXT NOT NULL CHECK (scope IN ('PLATFORM', 'MERCHANT', 'STORE')),
    label       VARCHAR(255) NOT NULL,       -- display name
    description TEXT,
    sort_order  SMALLINT NOT NULL DEFAULT 0
);

permissions

CREATE TABLE permissions (
    permission_key  VARCHAR(64) PRIMARY KEY,     -- e.g. 'TERMINAL_VIEW', 'ORDER_CREATE'
    group_id        VARCHAR(64) NOT NULL REFERENCES permission_groups(group_id),
    scope           TEXT NOT NULL CHECK (scope IN ('PLATFORM', 'MERCHANT', 'STORE')),
    label           VARCHAR(255) NOT NULL,       -- display label
    sort_order      SMALLINT NOT NULL DEFAULT 0
);

CREATE INDEX idx_permissions_group_id ON permissions(group_id);

Permission Registry

Platform-scoped permissions (~15 keys):

Group	Permission Keys
PSP_MGMT	PSP_VIEW, PSP_MANAGE
TERMINAL_MGMT	TERMINAL_VIEW, TERMINAL_MANAGE, TERMINAL_STATUS_CHANGE
DELIVERY_MGMT	DELIVERY_VIEW, DELIVERY_MANAGE
DEVICE_BILLING	BILLING_VIEW, BILLING_MANAGE
DEVICE_MODEL_MGMT	DEVICE_MODEL_VIEW, DEVICE_MODEL_MANAGE
DEVICE_PROFILE_MGMT	DEVICE_PROFILE_VIEW, DEVICE_PROFILE_MANAGE
BREAKDOWN_MGMT	BREAKDOWN_VIEW, BREAKDOWN_MANAGE

Merchant-scoped permissions (from User & Role spec):

Group	Permission Keys
MERCHANT_INFO	MERCHANT_VIEW
MERCHANT_SETTINGS	MERCHANT_EDIT
ACCOUNT_MGMT	ACCOUNT_VIEW, ACCOUNT_CREATE, ACCOUNT_EDIT, ACCOUNT_DISABLE, USER_DEFAULT_ROLE_ASSIGN, USER_STORE_ACCESS_ASSIGN
ROLE_MGMT	ROLE_VIEW, ROLE_CREATE, ROLE_EDIT, ROLE_DELETE
ARCHIVE_MGMT	ARCHIVE_VIEW, ARCHIVE_DOWNLOAD, ARCHIVE_MANAGE

Store-scoped permissions (from User & Role spec):

Group	Permission Keys
ORDER_MGMT	ORDER_VIEW, ORDER_CREATE, ORDER_REFUND, RECEIPT_ISSUE
ORDER_EXPORT	ORDER_CSV_DOWNLOAD
SALES_VIEW_GROUP	SALES_VIEW, SALES_DETAIL_VIEW, SALES_SUMMARY_VIEW
SALES_CSV_EXPORT	SALES_CSV_DOWNLOAD
SALES_PDF_EXPORT	SALES_PDF_DOWNLOAD
CLOSING_OPS	CLOSING_EXECUTE
STORE_MGMT	STORE_VIEW, STORE_EDIT, STORE_MANAGE
STORE_ARCHIVE	STORE_ARCHIVE, STORE_UNARCHIVE

Total: ~46 permissions across 3 scopes.

role_permissions

CREATE TABLE role_permissions (
    role_id         UUID NOT NULL REFERENCES roles(id) ON DELETE CASCADE,
    permission_key  VARCHAR(64) NOT NULL REFERENCES permissions(permission_key),
    PRIMARY KEY (role_id, permission_key)
);

user_default_roles

CREATE TABLE user_default_roles (
    user_id     UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    role_id     UUID NOT NULL REFERENCES roles(id) ON DELETE RESTRICT,
    PRIMARY KEY (user_id, role_id)
);

Only PLATFORM and MERCHANT scoped roles can be assigned here. Enforced at application level.

PLATFORM roles and store access: Platform and PSP users bypass the store_access table entirely — they see all stores within their tenant scope. The store_access / store_access_roles tables only apply to merchant-level users (where merchant_pk is set). PLATFORM roles assigned via user_default_roles contribute their platform/merchant permissions globally; they do NOT participate in store-level DEFAULT resolution. If a merchant-level user somehow holds a PLATFORM role as a default role, only that role's merchant-scoped and platform-scoped permissions apply globally — store permissions still resolve via the store_access mechanism.

store_access

CREATE TABLE store_access (
    user_id     UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    store_id    UUID NOT NULL REFERENCES stores(id) ON DELETE CASCADE,
    access_mode TEXT NOT NULL CHECK (access_mode IN ('NO_ACCESS', 'DEFAULT', 'CUSTOM')),
    created_at  TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at  TIMESTAMPTZ NOT NULL DEFAULT now(),
    PRIMARY KEY (user_id, store_id)
);

Missing row = implicit NO_ACCESS. Only applies to merchant-level users. Platform/PSP users see all stores within their tenant scope automatically.

store_access_roles

CREATE TABLE store_access_roles (
    user_id     UUID NOT NULL,
    store_id    UUID NOT NULL,
    role_id     UUID NOT NULL REFERENCES roles(id) ON DELETE RESTRICT,
    PRIMARY KEY (user_id, store_id, role_id),
    FOREIGN KEY (user_id, store_id) REFERENCES store_access(user_id, store_id) ON DELETE CASCADE
);

Only STORE-scoped roles allowed. Rows only exist when store_access.access_mode = 'CUSTOM'.

5.4 Device Management

device_models

CREATE TABLE device_models (
    id              UUID PRIMARY KEY DEFAULT uuidv7(),
    model_series    VARCHAR(100) NOT NULL,      -- e.g. "S-Pitt AIR"
    model_name      VARCHAR(100) NOT NULL,      -- e.g. "R25"
    lot_number      VARCHAR(50),                -- price may vary by lot
    unit_price      NUMERIC(10,2),
    description     TEXT,
    status          TEXT NOT NULL DEFAULT 'active'
                    CHECK (status IN ('active', 'inactive')),
    created_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at      TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- Functional unique: same series+name+lot = same model (handles NULL lot)
CREATE UNIQUE INDEX uq_device_models_series_name_lot
    ON device_models(model_series, model_name, COALESCE(lot_number, ''));

device_profiles

CREATE TABLE device_profiles (
    id                      UUID PRIMARY KEY DEFAULT uuidv7(),
    profile_name            VARCHAR(255) NOT NULL UNIQUE,
    description             TEXT,
    psp_id                  UUID REFERENCES psps(id) ON DELETE SET NULL,  -- optional PSP-specific
    -- Billing config
    monthly_lease_fee       NUMERIC(10,2),
    monthly_comm_fee        NUMERIC(10,2),                  -- default 500 yen
    commission_rate         NUMERIC(5,4),
    -- Hardware config
    target_firmware_version VARCHAR(50),
    cloudsim_enabled        BOOLEAN NOT NULL DEFAULT false,
    -- Operational config
    data_usage_limit_mb     INTEGER,                        -- NULL = unlimited
    heartbeat_alert_minutes INTEGER NOT NULL DEFAULT 1440,  -- 24h default
    billing_destination     TEXT CHECK (billing_destination IN ('PSP', 'ZERO')),
    -- Status
    status                  TEXT NOT NULL DEFAULT 'active'
                            CHECK (status IN ('active', 'inactive')),
    created_at              TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at              TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX idx_device_profiles_psp_id ON device_profiles(psp_id);

devices

CREATE TABLE devices (
    id                  UUID PRIMARY KEY DEFAULT uuidv7(),
    -- Identity
    serial_number       VARCHAR(100) NOT NULL UNIQUE,
    imei_number         VARCHAR(20) UNIQUE,
    client_id           VARCHAR(255) UNIQUE,            -- OAuth2 client identifier
    device_name         VARCHAR(255),                   -- optional user-assigned
    -- Model & hardware
    device_model_id     UUID REFERENCES device_models(id) ON DELETE SET NULL,
    device_profile_id   UUID REFERENCES device_profiles(id) ON DELETE SET NULL,
    firmware_version    VARCHAR(50),
    -- Tenant scoping
    psp_id              UUID NOT NULL REFERENCES psps(id) ON DELETE RESTRICT,
    current_store_id    UUID REFERENCES stores(id) ON DELETE SET NULL,  -- NULL = unassigned
    -- Location
    shipping_address    TEXT,
    gps_latitude        NUMERIC(9,6) CHECK (gps_latitude BETWEEN -90 AND 90),
    gps_longitude       NUMERIC(9,6) CHECK (gps_longitude BETWEEN -180 AND 180),
    gps_updated_at      TIMESTAMPTZ,
    -- Contract / lease
    ownership           TEXT CHECK (ownership IN ('rental', 'purchase')),
    contract_start_date DATE,
    contract_end_date   DATE,
    -- Communication / billing (can override device_profile)
    monthly_data_usage_mb   NUMERIC(10,2) CHECK (monthly_data_usage_mb >= 0),
    cloudsim_usage_flag     BOOLEAN,
    -- Two status columns
    asset_status        TEXT NOT NULL DEFAULT 'in_storage'
                        CHECK (asset_status IN (
                            'under_cleaning', 'in_storage', 'on_lease',
                            'returned', 'disposed'
                        )),
    connection_status   TEXT NOT NULL DEFAULT 'pending_activation'
                        CHECK (connection_status IN (
                            'pending_activation', 'active', 'suspended', 'decommissioned'
                        )),
    -- Connectivity
    public_key_pem      TEXT,
    activation_code     VARCHAR(20),
    activated_at        TIMESTAMPTZ,
    last_connected_at   TIMESTAMPTZ,
    -- Auth PINs
    staff_pin_hash      TEXT,
    admin_pin_hash      TEXT,
    -- Timestamps
    created_at          TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at          TIMESTAMPTZ NOT NULL DEFAULT now(),
    -- Constraints
    CONSTRAINT chk_contract_dates CHECK (contract_end_date >= contract_start_date)
);

CREATE INDEX idx_devices_psp_id ON devices(psp_id);
CREATE INDEX idx_devices_current_store_id ON devices(current_store_id);
CREATE INDEX idx_devices_asset_status ON devices(asset_status);
CREATE INDEX idx_devices_connection_status ON devices(connection_status);
CREATE INDEX idx_devices_last_connected_at ON devices(last_connected_at);
CREATE INDEX idx_devices_device_model_id ON devices(device_model_id);
CREATE INDEX idx_devices_device_profile_id ON devices(device_profile_id);

Merchant is derived via current_store_id → stores.merchant_pk (not denormalized on devices).

iot_credentials

CREATE TABLE iot_credentials (
    id              UUID PRIMARY KEY DEFAULT uuidv7(),
    device_id       UUID NOT NULL REFERENCES devices(id) ON DELETE CASCADE,
    thing_name      VARCHAR(255) NOT NULL,
    certificate_arn TEXT NOT NULL,
    certificate_id  VARCHAR(100) NOT NULL,
    policy_name     VARCHAR(255) NOT NULL,
    status          TEXT NOT NULL CHECK (status IN ('active', 'revoked')),
    created_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at      TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX idx_iot_credentials_device_id ON iot_credentials(device_id);

device_activation_history

CREATE TABLE device_activation_history (
    id              UUID PRIMARY KEY DEFAULT uuidv7(),
    device_id       UUID NOT NULL REFERENCES devices(id) ON DELETE CASCADE,
    store_id        UUID NOT NULL REFERENCES stores(id),
    activated_at    TIMESTAMPTZ NOT NULL DEFAULT now(),
    deactivated_at  TIMESTAMPTZ,
    activated_by    UUID REFERENCES users(id),
    notes           TEXT,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at      TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- At most one active activation per device
CREATE UNIQUE INDEX uq_activation_one_active_per_device
    ON device_activation_history(device_id) WHERE deactivated_at IS NULL;

CREATE INDEX idx_device_activation_device_id ON device_activation_history(device_id);
CREATE INDEX idx_device_activation_store_id ON device_activation_history(store_id);

device_status_history

CREATE TABLE device_status_history (
    id          UUID PRIMARY KEY DEFAULT uuidv7(),
    device_id   UUID NOT NULL REFERENCES devices(id) ON DELETE CASCADE,
    status_type TEXT NOT NULL CHECK (status_type IN ('asset', 'connection')),
    old_status  TEXT NOT NULL,
    new_status  TEXT NOT NULL,
    reason      TEXT,
    changed_by  UUID REFERENCES users(id),
    created_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX idx_device_status_history_device_id ON device_status_history(device_id);
CREATE INDEX idx_device_status_history_created_at ON device_status_history(created_at DESC);

breakdown_history

CREATE TABLE breakdown_history (
    id                      UUID PRIMARY KEY DEFAULT uuidv7(),
    device_id               UUID NOT NULL REFERENCES devices(id) ON DELETE CASCADE,
    breakdown_type          TEXT NOT NULL
                            CHECK (breakdown_type IN ('breakdown', 'replacement', 'battery_replacement')),
    description             TEXT,
    reported_at             TIMESTAMPTZ NOT NULL DEFAULT now(),
    resolved_at             TIMESTAMPTZ,
    replacement_device_id   UUID REFERENCES devices(id),
    old_battery_sn          VARCHAR(100),
    new_battery_sn          VARCHAR(100),
    reported_by             UUID REFERENCES users(id),
    created_at              TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at              TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX idx_breakdown_history_device_id ON breakdown_history(device_id);

5.5 Delivery & Billing

delivery_orders

CREATE TABLE delivery_orders (
    id                  UUID PRIMARY KEY DEFAULT uuidv7(),
    order_number        VARCHAR(50) NOT NULL UNIQUE,
    device_id           UUID REFERENCES devices(id) ON DELETE SET NULL,  -- linked at picking phase
    psp_id              UUID NOT NULL REFERENCES psps(id) ON DELETE RESTRICT,
    merchant_pk         UUID REFERENCES merchants(id) ON DELETE SET NULL,
    store_id            UUID REFERENCES stores(id) ON DELETE SET NULL,
    destination_address TEXT,
    -- Lifecycle
    status              TEXT NOT NULL DEFAULT 'ordered'
                        CHECK (status IN ('ordered', 'picking', 'shipped', 'delivered', 'cancelled')),
    ordered_at          TIMESTAMPTZ NOT NULL DEFAULT now(),
    picked_at           TIMESTAMPTZ,
    shipped_at          TIMESTAMPTZ,
    delivered_at        TIMESTAMPTZ,
    tracking_number     VARCHAR(100),
    shipping_box_sent   BOOLEAN NOT NULL DEFAULT false,
    billing_start_date  DATE,       -- auto: next month after (shipped_at + 3 days)
    -- Audit
    created_by          UUID REFERENCES users(id),
    created_at          TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at          TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX idx_delivery_orders_psp_id ON delivery_orders(psp_id);
CREATE INDEX idx_delivery_orders_device_id ON delivery_orders(device_id);
CREATE INDEX idx_delivery_orders_merchant_pk ON delivery_orders(merchant_pk);
CREATE INDEX idx_delivery_orders_store_id ON delivery_orders(store_id);
CREATE INDEX idx_delivery_orders_status ON delivery_orders(status);

device_returns

CREATE TABLE device_returns (
    id                      UUID PRIMARY KEY DEFAULT uuidv7(),
    delivery_order_id       UUID NOT NULL REFERENCES delivery_orders(id) ON DELETE RESTRICT,
    device_id               UUID NOT NULL REFERENCES devices(id) ON DELETE RESTRICT,
    return_tracking_number  VARCHAR(100),
    is_returned             BOOLEAN NOT NULL DEFAULT false,
    returned_at             TIMESTAMPTZ,
    disposed_at             TIMESTAMPTZ,
    reason                  TEXT,
    notes                   TEXT,
    created_at              TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at              TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX idx_device_returns_device_id ON device_returns(device_id);
CREATE INDEX idx_device_returns_delivery_order_id ON device_returns(delivery_order_id);

billing_records

CREATE TABLE billing_records (
    id              UUID PRIMARY KEY DEFAULT uuidv7(),
    device_id       UUID NOT NULL REFERENCES devices(id) ON DELETE RESTRICT,
    psp_id          UUID NOT NULL REFERENCES psps(id) ON DELETE RESTRICT,
    billing_type    TEXT NOT NULL
                    CHECK (billing_type IN ('lease_fee', 'communication_fee', 'commission_fee')),
    billing_period  VARCHAR(7) NOT NULL,            -- YYYY-MM format
    amount          NUMERIC(10,2) NOT NULL CHECK (amount >= 0),
    is_invoiced     BOOLEAN NOT NULL DEFAULT false,
    invoiced_at     TIMESTAMPTZ,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
    -- Prevent duplicate billing per device/type/period
    UNIQUE (device_id, billing_type, billing_period)
);

CREATE INDEX idx_billing_records_psp_id ON billing_records(psp_id);
CREATE INDEX idx_billing_records_period ON billing_records(billing_type, billing_period);

5.6 Audit

audit_logs

CREATE TABLE audit_logs (
    id          UUID PRIMARY KEY DEFAULT uuidv7(),
    user_id     UUID REFERENCES users(id),
    action      TEXT NOT NULL,          -- 'CREATE', 'UPDATE', 'DELETE', 'STATUS_CHANGE'
    entity_type TEXT NOT NULL,          -- 'device', 'user', 'role', 'delivery_order', etc.
    entity_id   VARCHAR(255) NOT NULL,  -- UUIDv7 of target entity
    before_state JSONB,
    after_state  JSONB,
    ip_address  INET,
    created_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- Append-only: no UPDATE or DELETE
CREATE INDEX idx_audit_logs_entity ON audit_logs(entity_type, entity_id);
CREATE INDEX idx_audit_logs_user ON audit_logs(user_id, created_at DESC);
CREATE INDEX idx_audit_logs_created_at ON audit_logs(created_at DESC);

Separate from authentication_logs which tracks login/auth events.

---

6. Authorization Model

6.1 Two Independent Axes

• Tenant scope (data visibility): determined by users.psp_id + users.merchant_pk. Middleware-level filtering.
• Permissions (what actions): determined by RBAC roles. Application-level checks.

6.2 Permission Resolution

1. Tenant filter (middleware)
   ├── psp_id = NULL, merchant_pk = NULL → no data filter (platform)
   ├── psp_id = set,  merchant_pk = NULL → WHERE psp_id = user.psp_id
   └── psp_id = set,  merchant_pk = set  → WHERE merchant_pk = user.merchant_pk

2. Global permissions (always active)
   └── SELECT permission_key FROM role_permissions
       WHERE role_id IN (user's default roles)
       AND scope IN ('PLATFORM', 'MERCHANT')

3. Store permissions (per-store resolution)
   ├── store_access row missing or NO_ACCESS → zero store perms
   ├── DEFAULT → store perms from user's default roles
   └── CUSTOM  → store perms from store_access_roles only

4. Effective permissions = global ∪ store perms

6.3 Scope Rules

Role Scope	Can hold permissions	Assigned where
PLATFORM	Any permission (platform + merchant + store)	user_default_roles
MERCHANT	Merchant + store permissions	user_default_roles
STORE	Store permissions only	store_access_roles (per-store custom)

---

7. Application-Level Invariants

These rules require cross-table logic and must be enforced in application code, not as DB constraints.

#	Invariant	Enforcement point
1	PLATFORM roles can hold any permission	createRole, editRole
2	MERCHANT roles can hold merchant + store permissions only	createRole, editRole
3	STORE roles can only hold store-scoped permissions	createRole, editRole
4	Default roles must have scope PLATFORM or MERCHANT	assignDefaultRoles
5	Store custom roles must have scope STORE	assignStoreAccess
6	store_access_roles rows only exist when access_mode = 'CUSTOM'	assignStoreAccess
7	At least 1 active protected user with System Admin role must exist	deactivateUser, removeDefaultRole
8	Cannot deactivate own account	Compare session user vs target
9	Cannot deactivate is_protected = true user	deactivateUser
10	Cannot delete system role (is_system = true)	deleteRole
11	Cannot delete role that is in use	Check user_default_roles + store_access_roles before delete
12	User with any store set to DEFAULT must retain >= 1 default role	removeDefaultRole
13	New access assignments on archived stores are disallowed	Check store.status in assignStoreAccess
14	If merchant_pk is set, psp_id must also be set	createUser, editUser
15	merchants.psp_id must match users.psp_id when merchant_pk is set	createUser, editUser
16	At most one active activation per device	Partial unique index enforces at DB level
17	billing_start_date = next month after (shipped_at + 3 days)	Application logic at shipment time

---

8. Index Strategy

Table	Index	Purpose
devices	UNIQUE on serial_number, imei_number, client_id	Identity lookup (auto B-tree)
devices	asset_status, connection_status	Status filtering
devices	last_connected_at	Silent device / heartbeat queries
devices	psp_id, current_store_id	Tenant-scoped queries
device_activation_history	Partial unique on device_id WHERE deactivated_at IS NULL	One active per device
device_status_history	device_id, created_at DESC	Device history timeline
billing_records	UNIQUE on (device_id, billing_type, billing_period)	Prevent duplicate billing
billing_records	(billing_type, billing_period)	Monthly aggregation
audit_logs	(entity_type, entity_id), (user_id, created_at DESC), (created_at DESC)	Entity lookup, user activity, chronological
All FK columns	Standard B-tree	Join performance

---

9. Open Items

Item	Status	Notes
Battery S/N management	Deferred	Current schema has fields in breakdown_history. May need a dedicated batteries table if serial numbers need independent lifecycle tracking.
billing_records retention	Pending	May be removed after business finalizes billing workflow. PSP-side billing system may take over.
Username / login_id	Deferred	May add username column to users for non-email login after customer decision.