public.devices

Description

Labels

svc-portal

Columns

Name	Type	Default	Nullable	Children	Parents	Comment
device_id	uuid		false	public.iot_credentials public.orders public.app_update_logs public.device_app_versions		Unique device identifier (UUID).
serial_number	varchar(64)		false			Hardware serial number.
client_id	varchar(50)		false			OAuth2 client ID.
device_name	varchar(255)		true			Device display name.
device_code	varchar(50)		true			Device code.
public_key_pem	text		false			EC P-256 public key (PEM format).
merchant_id	integer		true		public.merchants	Assigned merchant ID.
store_id	integer		true		public.stores	Assigned store ID.
status	varchar(50)		false			Device status: PENDING_ACTIVATION, ACTIVE, or SUSPENDED.
activation_code	varchar(12)		true			8-digit activation code.
activation_code_expires_at	timestamp with time zone		true			Activation code expiration.
activated_at	timestamp with time zone		true			Device activation timestamp.
activated_by_user_id	varchar(36)		true			User ID who activated device.
created_at	timestamp with time zone		false			Record creation timestamp.
updated_at	timestamp with time zone		false			Record last update timestamp.
last_token_request_at	timestamp with time zone		true			Last OAuth2 token request timestamp.
last_connected_at	timestamp with time zone		true			Last device connection timestamp.
staff_pin_hash	varchar(255)		true			Bcrypt hash of 6-digit staff PIN.
admin_pin_hash	varchar(255)		true			Bcrypt hash of 6-digit admin PIN.
is_deactive	boolean	false	false			Soft delete flag.

Viewpoints

Name	Definition
Merchant Hierarchy	Multi-tenant hierarchy — PSP → Merchant → Store → Staff/Devices

Constraints

Name	Type	Definition
devices_merchant_id_fkey	FOREIGN KEY	FOREIGN KEY (merchant_id) REFERENCES merchants(id) ON DELETE RESTRICT
devices_store_id_fkey	FOREIGN KEY	FOREIGN KEY (store_id) REFERENCES stores(id) ON DELETE RESTRICT
devices_pkey	PRIMARY KEY	PRIMARY KEY (device_id)

Indexes

Name	Definition
devices_pkey	CREATE UNIQUE INDEX devices_pkey ON public.devices USING btree (device_id)
ix_devices_activation_code	CREATE INDEX ix_devices_activation_code ON public.devices USING btree (activation_code)
ix_devices_client_id	CREATE UNIQUE INDEX ix_devices_client_id ON public.devices USING btree (client_id)
ix_devices_merchant_id	CREATE INDEX ix_devices_merchant_id ON public.devices USING btree (merchant_id)
ix_devices_serial_number	CREATE UNIQUE INDEX ix_devices_serial_number ON public.devices USING btree (serial_number)
ix_devices_status	CREATE INDEX ix_devices_status ON public.devices USING btree (status)
ix_devices_store_id	CREATE INDEX ix_devices_store_id ON public.devices USING btree (store_id)

Relations

---

Generated by tbls