DATA_MODEL — lock-integration-service
Bundle: SERVICE_OVERVIEW · DOMAIN_MODEL · APPLICATION_LOGIC · SECURITY_MODEL · SYNC_CONTRACT
Cross-cutting: docs/06 — Data Models, docs/standards/NAMING — Database.
Storage: Cloud SQL Postgres 16 (regional HA). Shared schema with tenant_id uuid NOT NULL + RLS on every table. Vendor configuration columns are encrypted at rest with a separate KMS key (projects/<project>/locations/<region>/keyRings/melmastoon-lock/cryptoKeys/lock-config) — see SECURITY_MODEL §1. Money columns are bigint micro-units (_micro suffix); this service has none. Audit rows are dual-written to BigQuery via streaming insert from audit-service's consumer.
1. ID prefixes (declared here)
This service is the canonical owner for these prefixes (added to NAMING — ID prefixes registry):
| Prefix | Entity | Notes |
|---|---|---|
key_ | KeyCredential | already in NAMING registry |
lck_ | LockDevice | already in NAMING registry |
kca_ | KeyCredentialAttempt | new — door-access audit row |
vad_ | VendorAdapter | new — adapter row per (tenant, property, vendor) |
vcr_ | VendorCredential | new — Secret Manager pointer row |
enc_ | EncoderSession | new — desktop USB encoder session |
mky_ | MasterKey | new — staff master key |
kkp_ | KeyKindPolicy | new — per-(tenant, property) policy |
oki_ | OfflineIssuance | new — Ed25519 cert |
whk_ | WebhookInbound | new — vendor webhook intake row |
All IDs are 26-char ULIDs prefixed (key_01HX5JZ7...).
2. TypeScript view (canonical interfaces)
The aggregates live in DOMAIN_MODEL §1-§5. The repository ports return the same shapes; mappers under infrastructure/adapters/postgres-*.adapter.ts translate to/from rows.
3. Postgres DDL
-- =========================================================================
-- 0001_init_lock_schema.sql
-- =========================================================================
CREATE EXTENSION IF NOT EXISTS pgcrypto;
-- ----- key_credentials -----
CREATE TABLE key_credentials (
id text PRIMARY KEY, -- ULID prefixed 'key_'
tenant_id uuid NOT NULL,
property_id text NOT NULL,
holder_kind text NOT NULL CHECK (holder_kind IN ('guest','staff_master')),
reservation_id text,
guest_id text,
staff_user_id text,
kind text NOT NULL CHECK (kind IN ('mobile_app','pin_code','rfid_card','qr_code','nfc_tag')),
scope jsonb NOT NULL DEFAULT '{}'::jsonb,
valid_from timestamptz NOT NULL,
valid_until timestamptz NOT NULL,
state text NOT NULL CHECK (state IN ('requested','pending','active','suspended','revoked','failed')),
vendor text NOT NULL CHECK (vendor IN ('ttlock','salto','assa-abloy','generic-wiegand')),
vendor_adapter_id text NOT NULL,
vendor_ref_ciphertext bytea, -- KMS-encrypted (lock-config key)
vendor_ref_kms_version text,
provisional boolean NOT NULL DEFAULT false,
idempotency_key text NOT NULL,
issued_at timestamptz,
suspended_at timestamptz,
revoked_at timestamptz,
failure_reason text,
suspend_reason text,
revoke_reason text,
replaces_id text REFERENCES key_credentials(id),
replaced_by_id text REFERENCES key_credentials(id),
version integer NOT NULL DEFAULT 1,
created_at timestamptz NOT NULL DEFAULT now(),
updated_at timestamptz NOT NULL DEFAULT now(),
CONSTRAINT chk_valid_window CHECK (valid_from < valid_until),
CONSTRAINT chk_holder_guest CHECK (
(holder_kind = 'guest' AND reservation_id IS NOT NULL AND guest_id IS NOT NULL AND staff_user_id IS NULL)
OR (holder_kind = 'staff_master' AND staff_user_id IS NOT NULL AND reservation_id IS NULL)
),
CONSTRAINT chk_provisional_window CHECK (
NOT provisional OR (valid_until <= valid_from + INTERVAL '48 hours')
)
);
CREATE INDEX ix_key_credentials_tenant_id_state ON key_credentials (tenant_id, state);
CREATE INDEX ix_key_credentials_reservation_id ON key_credentials (reservation_id) WHERE reservation_id IS NOT NULL;
CREATE INDEX ix_key_credentials_staff_user_id ON key_credentials (staff_user_id) WHERE staff_user_id IS NOT NULL;
CREATE INDEX ix_key_credentials_property_state ON key_credentials (tenant_id, property_id, state);
CREATE UNIQUE INDEX ux_key_credentials_idem ON key_credentials (tenant_id, idempotency_key);
CREATE INDEX ix_key_credentials_valid_until_active ON key_credentials (valid_until) WHERE state = 'active';
-- ----- key_credential_rooms (M:N) -----
CREATE TABLE key_credential_rooms (
tenant_id uuid NOT NULL,
key_credential_id text NOT NULL REFERENCES key_credentials(id) ON DELETE CASCADE,
room_id text NOT NULL,
PRIMARY KEY (key_credential_id, room_id)
);
CREATE INDEX ix_kc_rooms_tenant_room ON key_credential_rooms (tenant_id, room_id);
-- Partial unique index prevents overlapping active credentials per (room, day).
-- The application layer additionally takes a Postgres advisory lock keyed by
-- (property_id, room_id, valid_from::date) before issue to make this atomic across
-- concurrent saga instances.
-- ----- key_credential_attempts (append-only) -----
CREATE TABLE key_credential_attempts (
id text PRIMARY KEY, -- ULID 'kca_'
tenant_id uuid NOT NULL,
property_id text NOT NULL,
key_credential_id text REFERENCES key_credentials(id),
device_id text NOT NULL,
attempted_at timestamptz NOT NULL,
ingested_at timestamptz NOT NULL DEFAULT now(),
outcome text NOT NULL CHECK (outcome IN ('granted','denied')),
deny_reason text,
vendor text NOT NULL,
vendor_event_id text NOT NULL,
metadata jsonb NOT NULL DEFAULT '{}'::jsonb,
CONSTRAINT ux_kca_vendor_event UNIQUE (tenant_id, vendor, vendor_event_id)
);
CREATE INDEX ix_kca_credential_time ON key_credential_attempts (key_credential_id, attempted_at DESC);
CREATE INDEX ix_kca_property_time ON key_credential_attempts (tenant_id, property_id, attempted_at DESC);
CREATE INDEX ix_kca_device_time ON key_credential_attempts (device_id, attempted_at DESC);
-- Trigger to forbid UPDATE/DELETE.
CREATE OR REPLACE FUNCTION reject_kca_mutation() RETURNS trigger AS $$
BEGIN RAISE EXCEPTION 'key_credential_attempts is append-only'; END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER trg_kca_no_update BEFORE UPDATE OR DELETE ON key_credential_attempts
FOR EACH ROW EXECUTE FUNCTION reject_kca_mutation();
-- ----- lock_devices -----
CREATE TABLE lock_devices (
id text PRIMARY KEY, -- 'lck_'
tenant_id uuid NOT NULL,
property_id text NOT NULL,
vendor text NOT NULL,
vendor_device_ref_ciphertext bytea NOT NULL, -- KMS-encrypted
vendor_device_ref_kms_version text NOT NULL,
label text NOT NULL,
capabilities jsonb NOT NULL,
online boolean NOT NULL DEFAULT false,
battery_pct integer CHECK (battery_pct BETWEEN 0 AND 100),
battery_low_threshold_pct integer CHECK (battery_low_threshold_pct BETWEEN 5 AND 50) DEFAULT 25,
firmware text,
clock_skew_ms integer,
last_seen_at timestamptz,
registered_at timestamptz NOT NULL DEFAULT now(),
decommissioned_at timestamptz,
CONSTRAINT ux_lock_devices_vendor_ref UNIQUE (tenant_id, property_id, vendor, vendor_device_ref_ciphertext)
);
CREATE INDEX ix_lock_devices_property ON lock_devices (tenant_id, property_id) WHERE decommissioned_at IS NULL;
CREATE INDEX ix_lock_devices_battery ON lock_devices (tenant_id, property_id, battery_pct) WHERE decommissioned_at IS NULL;
CREATE TABLE lock_device_rooms (
tenant_id uuid NOT NULL,
device_id text NOT NULL REFERENCES lock_devices(id) ON DELETE CASCADE,
room_id text NOT NULL,
PRIMARY KEY (device_id, room_id)
);
CREATE INDEX ix_ldr_room ON lock_device_rooms (tenant_id, room_id);
-- ----- vendor_adapters -----
CREATE TABLE vendor_adapters (
id text PRIMARY KEY, -- 'vad_'
tenant_id uuid NOT NULL,
property_id text NOT NULL,
vendor text NOT NULL,
environment text NOT NULL CHECK (environment IN ('sandbox','production')),
capabilities jsonb NOT NULL,
enabled boolean NOT NULL DEFAULT true,
precedence integer NOT NULL DEFAULT 100,
health jsonb NOT NULL DEFAULT '{}'::jsonb,
config_ciphertext bytea NOT NULL, -- KMS-encrypted (non-secret config like URLs, rate limits)
config_kms_version text NOT NULL,
created_at timestamptz NOT NULL DEFAULT now(),
updated_at timestamptz NOT NULL DEFAULT now(),
CONSTRAINT ux_vendor_adapters UNIQUE (tenant_id, property_id, vendor, environment)
);
CREATE INDEX ix_vendor_adapters_enabled ON vendor_adapters (tenant_id, property_id, enabled) WHERE enabled;
-- ----- vendor_credentials (Secret Manager pointers only) -----
CREATE TABLE vendor_credentials (
id text PRIMARY KEY, -- 'vcr_'
tenant_id uuid NOT NULL,
property_id text NOT NULL,
vendor text NOT NULL,
environment text NOT NULL,
secret_resource_name text NOT NULL, -- 'projects/.../secrets/.../versions/latest'
rotates_at timestamptz NOT NULL,
fingerprint_sha256 text NOT NULL,
created_at timestamptz NOT NULL DEFAULT now(),
updated_at timestamptz NOT NULL DEFAULT now(),
CONSTRAINT ux_vendor_credentials UNIQUE (tenant_id, property_id, vendor, environment)
);
CREATE INDEX ix_vendor_credentials_rotation ON vendor_credentials (rotates_at);
-- ----- master_keys -----
CREATE TABLE master_keys (
id text PRIMARY KEY, -- 'mky_'
tenant_id uuid NOT NULL,
property_id text NOT NULL,
staff_user_id text NOT NULL,
shift_id text,
key_credential_id text NOT NULL REFERENCES key_credentials(id),
scope jsonb NOT NULL,
valid_from timestamptz NOT NULL,
valid_until timestamptz NOT NULL,
issued_at timestamptz NOT NULL DEFAULT now(),
revoked_at timestamptz,
CONSTRAINT ux_master_keys_credential UNIQUE (key_credential_id)
);
CREATE INDEX ix_master_keys_staff_active ON master_keys (tenant_id, staff_user_id) WHERE revoked_at IS NULL;
CREATE INDEX ix_master_keys_property ON master_keys (tenant_id, property_id);
-- ----- key_kind_policies -----
CREATE TABLE key_kind_policies (
id text PRIMARY KEY, -- 'kkp_'
tenant_id uuid NOT NULL,
property_id text NOT NULL,
preferred_order jsonb NOT NULL,
fallback_chain jsonb NOT NULL DEFAULT '[]'::jsonb,
max_valid_until_extension_hours integer NOT NULL DEFAULT 168,
id_verify_required_for jsonb NOT NULL DEFAULT '[]'::jsonb,
no_show_suspend_after_hours integer NOT NULL DEFAULT 2,
allow_offline_issuance_kinds jsonb NOT NULL DEFAULT '["rfid_card","pin_code"]'::jsonb,
updated_at timestamptz NOT NULL DEFAULT now(),
CONSTRAINT ux_key_kind_policies UNIQUE (tenant_id, property_id)
);
-- ----- encoder_sessions -----
CREATE TABLE encoder_sessions (
id text PRIMARY KEY, -- 'enc_'
tenant_id uuid NOT NULL,
property_id text NOT NULL,
desktop_device_id text NOT NULL,
encoder_model text NOT NULL,
transport text NOT NULL CHECK (transport IN ('usb_hid','serial')),
opened_at timestamptz NOT NULL DEFAULT now(),
closed_at timestamptz,
close_reason text
);
CREATE INDEX ix_encoder_sessions_open ON encoder_sessions (tenant_id, property_id) WHERE closed_at IS NULL;
-- ----- offline_issuance -----
CREATE TABLE offline_issuance (
id text PRIMARY KEY, -- 'oki_'
tenant_id uuid NOT NULL,
property_id text NOT NULL,
desktop_device_id text NOT NULL,
serial text NOT NULL UNIQUE,
public_key_ed25519 text NOT NULL,
allowed_kinds jsonb NOT NULL,
max_valid_window_hours integer NOT NULL DEFAULT 48 CHECK (max_valid_window_hours <= 48),
issued_at timestamptz NOT NULL DEFAULT now(),
expires_at timestamptz NOT NULL,
revoked_at timestamptz,
revoke_reason text
);
CREATE INDEX ix_offline_issuance_property_active
ON offline_issuance (tenant_id, property_id) WHERE revoked_at IS NULL;
CREATE INDEX ix_offline_issuance_expiry ON offline_issuance (expires_at) WHERE revoked_at IS NULL;
-- ----- webhook_inbox -----
CREATE TABLE webhook_inbox (
id text PRIMARY KEY, -- 'whk_'
tenant_id uuid NOT NULL,
vendor text NOT NULL,
external_event_id text NOT NULL,
type text NOT NULL,
payload jsonb NOT NULL,
signature_ok boolean NOT NULL,
received_at timestamptz NOT NULL DEFAULT now(),
processed_at timestamptz,
processed_outcome text,
CONSTRAINT ux_webhook_inbox UNIQUE (vendor, external_event_id)
);
CREATE INDEX ix_webhook_inbox_unprocessed ON webhook_inbox (received_at) WHERE processed_at IS NULL;
-- ----- transactional outbox -----
CREATE TABLE outbox (
id text PRIMARY KEY,
tenant_id uuid NOT NULL,
aggregate_id text NOT NULL,
topic text NOT NULL, -- 'melmastoon.lock.credential.issued.v1'
payload jsonb NOT NULL,
envelope jsonb NOT NULL,
created_at timestamptz NOT NULL DEFAULT now(),
published_at timestamptz,
publish_attempts integer NOT NULL DEFAULT 0,
last_error text
);
CREATE INDEX ix_outbox_unpublished ON outbox (created_at) WHERE published_at IS NULL;
-- ----- inbox (consumer dedupe) -----
CREATE TABLE inbox (
message_id text NOT NULL,
consumer_name text NOT NULL,
tenant_id uuid NOT NULL,
topic text NOT NULL,
processed_at timestamptz NOT NULL DEFAULT now(),
PRIMARY KEY (message_id, consumer_name)
);
-- ----- saga state -----
CREATE TABLE key_credential_saga (
reservation_id text NOT NULL,
saga_step text NOT NULL, -- 'issue','revoke','update','suspend'
tenant_id uuid NOT NULL,
state jsonb NOT NULL,
attempt integer NOT NULL DEFAULT 0,
next_attempt_at timestamptz,
completed_at timestamptz,
PRIMARY KEY (reservation_id, saga_step)
);
CREATE INDEX ix_saga_pending ON key_credential_saga (next_attempt_at) WHERE completed_at IS NULL;
-- ----- lock_audit (hot store; BigQuery is the long-term home) -----
CREATE TABLE lock_audit (
id text PRIMARY KEY,
tenant_id uuid NOT NULL,
property_id text NOT NULL,
key_credential_id text,
master_key_id text,
device_id text,
actor_user_id text,
actor_kind text NOT NULL, -- 'saga' | 'operator' | 'webhook'
action text NOT NULL, -- 'issued','updated','suspended','unsuspended','revoked','attempt'
reason text,
vendor text,
trace_id text,
created_at timestamptz NOT NULL DEFAULT now(),
payload jsonb NOT NULL DEFAULT '{}'::jsonb
);
CREATE INDEX ix_lock_audit_tenant_time ON lock_audit (tenant_id, created_at DESC);
CREATE INDEX ix_lock_audit_credential ON lock_audit (key_credential_id, created_at DESC) WHERE key_credential_id IS NOT NULL;
4. Indexes & RLS
Every table has an ENABLE ROW LEVEL SECURITY policy named <table>_tenant_isolation:
ALTER TABLE key_credentials ENABLE ROW LEVEL SECURITY;
CREATE POLICY key_credentials_tenant_isolation ON key_credentials
USING (tenant_id::text = current_setting('app.tenant_id', true));
ALTER TABLE key_credential_rooms ENABLE ROW LEVEL SECURITY;
CREATE POLICY key_credential_rooms_tenant_isolation ON key_credential_rooms
USING (tenant_id::text = current_setting('app.tenant_id', true));
ALTER TABLE key_credential_attempts ENABLE ROW LEVEL SECURITY;
CREATE POLICY key_credential_attempts_tenant_isolation ON key_credential_attempts
USING (tenant_id::text = current_setting('app.tenant_id', true));
ALTER TABLE lock_devices ENABLE ROW LEVEL SECURITY;
CREATE POLICY lock_devices_tenant_isolation ON lock_devices
USING (tenant_id::text = current_setting('app.tenant_id', true));
ALTER TABLE lock_device_rooms ENABLE ROW LEVEL SECURITY;
CREATE POLICY lock_device_rooms_tenant_isolation ON lock_device_rooms
USING (tenant_id::text = current_setting('app.tenant_id', true));
ALTER TABLE vendor_adapters ENABLE ROW LEVEL SECURITY;
CREATE POLICY vendor_adapters_tenant_isolation ON vendor_adapters
USING (tenant_id::text = current_setting('app.tenant_id', true));
ALTER TABLE vendor_credentials ENABLE ROW LEVEL SECURITY;
CREATE POLICY vendor_credentials_tenant_isolation ON vendor_credentials
USING (tenant_id::text = current_setting('app.tenant_id', true));
ALTER TABLE master_keys ENABLE ROW LEVEL SECURITY;
CREATE POLICY master_keys_tenant_isolation ON master_keys
USING (tenant_id::text = current_setting('app.tenant_id', true));
ALTER TABLE key_kind_policies ENABLE ROW LEVEL SECURITY;
CREATE POLICY key_kind_policies_tenant_isolation ON key_kind_policies
USING (tenant_id::text = current_setting('app.tenant_id', true));
ALTER TABLE encoder_sessions ENABLE ROW LEVEL SECURITY;
CREATE POLICY encoder_sessions_tenant_isolation ON encoder_sessions
USING (tenant_id::text = current_setting('app.tenant_id', true));
ALTER TABLE offline_issuance ENABLE ROW LEVEL SECURITY;
CREATE POLICY offline_issuance_tenant_isolation ON offline_issuance
USING (tenant_id::text = current_setting('app.tenant_id', true));
ALTER TABLE webhook_inbox ENABLE ROW LEVEL SECURITY;
CREATE POLICY webhook_inbox_tenant_isolation ON webhook_inbox
USING (tenant_id::text = current_setting('app.tenant_id', true));
ALTER TABLE outbox ENABLE ROW LEVEL SECURITY;
CREATE POLICY outbox_tenant_isolation ON outbox
USING (tenant_id::text = current_setting('app.tenant_id', true));
ALTER TABLE inbox ENABLE ROW LEVEL SECURITY;
CREATE POLICY inbox_tenant_isolation ON inbox
USING (tenant_id::text = current_setting('app.tenant_id', true));
ALTER TABLE key_credential_saga ENABLE ROW LEVEL SECURITY;
CREATE POLICY key_credential_saga_tenant_isolation ON key_credential_saga
USING (tenant_id::text = current_setting('app.tenant_id', true));
ALTER TABLE lock_audit ENABLE ROW LEVEL SECURITY;
CREATE POLICY lock_audit_tenant_isolation ON lock_audit
USING (tenant_id::text = current_setting('app.tenant_id', true));
The runtime sets SET app.tenant_id = '<uuid>' at the start of every transaction via the TenantContextGuard (07 §6). The mandatory tests/integration/tenant-isolation.spec.ts proves cross-tenant queries return zero rows.
5. Encrypted columns (KMS)
Columns ending in _ciphertext are encrypted at the application layer using KmsEncryptor against the lock-config CMEK key (separate from platform default). Adapter-side helpers:
// infrastructure/adapters/kms-encryptor.adapter.ts
class GcpKmsLockConfigEncryptor implements KmsEncryptor {
encrypt(plaintext: Uint8Array): Promise<{ ciphertext: Uint8Array; kmsVersion: string }>;
decrypt(ciphertext: Uint8Array, kmsVersion: string): Promise<Uint8Array>;
}
Encrypted columns:
key_credentials.vendor_ref_ciphertext— opaque vendor reference; never logged or returned in API responses.lock_devices.vendor_device_ref_ciphertext— opaque vendor device id.vendor_adapters.config_ciphertext— non-secret config (rate limits, URLs) but encrypted as defense-in-depth.
6. Migrations
Per-service ordinal under migrations/:
0001_init_lock_schema.sql -- this file
0002_add_lock_audit_table.sql -- if split
0003_add_offline_issuance.sql
0004_add_master_keys.sql
0005_add_kca_append_only_trigger.sql
0006_add_partial_unique_active_credential.sql
Migrations run via Drizzle (drizzle-kit migrate) before service start; container blocks until success. Every new table must include a _tenant_isolation policy or CI fails (SERVICE_TEMPLATE — Required CI checks).
7. Desktop SQLite mirror
The Electron desktop main process maintains a local mirror with a subset of tables (no outbox, inbox, vendor_credentials, webhook_inbox, lock_audit):
-- desktop SQLite: encrypted via SQLCipher; key referenced from OS keychain via keytar
CREATE TABLE key_credentials_local (
id TEXT PRIMARY KEY,
server_id TEXT, -- mapped after sync push
tenant_id TEXT NOT NULL,
property_id TEXT NOT NULL,
reservation_id TEXT,
staff_user_id TEXT,
kind TEXT NOT NULL,
state TEXT NOT NULL,
rooms TEXT NOT NULL, -- JSON
valid_from TEXT NOT NULL,
valid_until TEXT NOT NULL,
vendor TEXT NOT NULL,
provisional INTEGER NOT NULL, -- 0/1
cert_serial TEXT, -- offline issuance cert that minted this
device_sig TEXT, -- ed25519 sig of payload
created_at TEXT NOT NULL,
updated_at TEXT NOT NULL
);
CREATE TABLE lock_devices_local (...); -- same shape minus encrypted vendor ref
CREATE TABLE master_keys_local (...);
CREATE TABLE encoder_sessions_local (...);
CREATE TABLE offline_certificates_local (...); -- pubkey only; private key in OS keychain
CREATE TABLE local_outbox (
id TEXT PRIMARY KEY,
topic TEXT NOT NULL, -- e.g. 'melmastoon.lock.credential.issued.local.v1'
payload TEXT NOT NULL, -- JSON
created_at TEXT NOT NULL,
pushed_at TEXT
);
vendorRef is not mirrored to SQLite; the desktop uses vendor_ref only when its local adapter (Wiegand encoder, BLE TTLock) physically encoded the credential and even then it is stored in the local outbox row, not the local key_credentials_local mirror.
8. Retention
| Table | Live retention | Cold storage | Destruction |
|---|---|---|---|
key_credentials (terminal: revoked / failed) | indefinite while tenant active | BigQuery sink | pseudonymize 7 years after revoked_at per 07 §14 GDPR carve-out |
key_credential_attempts | 90 days hot | 7 years BigQuery + Merkle anchor | pseudonymize after 7y |
lock_audit | 90 days hot | 7 years BigQuery + Merkle anchor | pseudonymize after 7y |
lock_devices (decommissioned) | 30 days post-decommission | BigQuery snapshot | hard delete after 7 years |
webhook_inbox (processed) | 30 days | n/a | hard delete |
outbox (published) | 7 days | n/a | hard delete |
inbox | 30 days | n/a | hard delete |
vendor_credentials (rotated-out) | 0 (replaced in place) | n/a | n/a — Secret Manager handles version retention |
offline_issuance (revoked) | 90 days | BigQuery | hard delete after retention window |
9. Cross-references
- Encryption posture: SECURITY_MODEL §1
- Sync mirror conflict policy: SYNC_CONTRACT
- Audit substrate (BigQuery + Merkle): 07 §11