3089f84873
- Add beacon-sharding API endpoints for scalable iBeacon addressing (64 shard UUIDs × 65k businesses = ~4.2M capacity) - Fix callServer.cfm to save UserID when creating Call Server tasks - Fix getDetails.cfm to return customer info from Task.UserID when Order.UserID is null (for tasks without orders) Co-Authored-By: Claude Opus 4.5 <noreply@anthropic.com>
175 lines
9.7 KiB
SQL
175 lines
9.7 KiB
SQL
-- =============================================================================
|
||
-- Payfrit Beacon Sharding Migration
|
||
-- =============================================================================
|
||
-- This migration implements the scalable iBeacon addressing scheme:
|
||
-- UUID = Shard UUID (from fixed pool)
|
||
-- Major = Business identifier (unique within shard)
|
||
-- Minor = ServicePoint identifier (unique within business)
|
||
--
|
||
-- Capacity: 65,536 businesses per shard × 64 shards = ~4.2M businesses
|
||
-- =============================================================================
|
||
|
||
-- -----------------------------------------------------------------------------
|
||
-- 1. BeaconShards table - UUID pool with utilization tracking
|
||
-- -----------------------------------------------------------------------------
|
||
CREATE TABLE IF NOT EXISTS BeaconShards (
|
||
ID INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
|
||
UUID CHAR(36) NOT NULL UNIQUE COMMENT 'iBeacon UUID for this shard (with dashes)',
|
||
BusinessCount INT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Current number of businesses using this shard',
|
||
MaxBusinesses INT UNSIGNED NOT NULL DEFAULT 65535 COMMENT 'Max businesses per shard (uint16 max)',
|
||
IsActive TINYINT(1) NOT NULL DEFAULT 1 COMMENT '1=active, 0=retired (never remove, only retire)',
|
||
CreatedAt DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
INDEX idx_utilization (BusinessCount, IsActive),
|
||
INDEX idx_active (IsActive)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
||
|
||
-- -----------------------------------------------------------------------------
|
||
-- 2. Add beacon namespace columns to Businesses table
|
||
-- -----------------------------------------------------------------------------
|
||
ALTER TABLE Businesses
|
||
ADD COLUMN BeaconShardID INT UNSIGNED NULL COMMENT 'FK to BeaconShards - assigned shard for this business',
|
||
ADD COLUMN BeaconMajor SMALLINT UNSIGNED NULL COMMENT 'iBeacon Major value (unique within shard)',
|
||
ADD INDEX idx_beacon_shard (BeaconShardID),
|
||
ADD UNIQUE INDEX idx_shard_major (BeaconShardID, BeaconMajor);
|
||
|
||
-- -----------------------------------------------------------------------------
|
||
-- 3. Add beacon minor column to ServicePoints table
|
||
-- -----------------------------------------------------------------------------
|
||
ALTER TABLE ServicePoints
|
||
ADD COLUMN BeaconMinor SMALLINT UNSIGNED NULL COMMENT 'iBeacon Minor value (unique within business)',
|
||
ADD UNIQUE INDEX idx_business_minor (BusinessID, BeaconMinor);
|
||
|
||
-- -----------------------------------------------------------------------------
|
||
-- 4. BeaconHardware table - physical beacon inventory tracking
|
||
-- -----------------------------------------------------------------------------
|
||
CREATE TABLE IF NOT EXISTS BeaconHardware (
|
||
ID INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
|
||
HardwareId VARCHAR(64) NOT NULL COMMENT 'MAC address, serial number, or vendor ID',
|
||
BusinessID INT UNSIGNED NULL COMMENT 'FK to Businesses - assigned business',
|
||
ServicePointID INT UNSIGNED NULL COMMENT 'FK to ServicePoints - assigned service point',
|
||
ShardUUID CHAR(36) NULL COMMENT 'Cached shard UUID (for convenience)',
|
||
Major SMALLINT UNSIGNED NULL COMMENT 'Cached Major value',
|
||
Minor SMALLINT UNSIGNED NULL COMMENT 'Cached Minor value',
|
||
Status ENUM('unassigned', 'assigned', 'verified', 'retired') NOT NULL DEFAULT 'unassigned',
|
||
TxPower TINYINT NULL COMMENT 'Configured TX power in dBm',
|
||
AdvertisingInterval SMALLINT UNSIGNED NULL COMMENT 'Advertising interval in ms',
|
||
FirmwareVersion VARCHAR(32) NULL COMMENT 'Beacon firmware version',
|
||
BatteryLevel TINYINT UNSIGNED NULL COMMENT 'Last known battery percentage',
|
||
LastSeenAt DATETIME NULL COMMENT 'Last time beacon was detected',
|
||
LastRssi SMALLINT NULL COMMENT 'Last RSSI reading',
|
||
CreatedAt DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
UpdatedAt DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
||
VerifiedAt DATETIME NULL COMMENT 'When beacon was verified broadcasting correctly',
|
||
UNIQUE INDEX idx_hardware_id (HardwareId),
|
||
INDEX idx_business (BusinessID),
|
||
INDEX idx_service_point (ServicePointID),
|
||
INDEX idx_status (Status),
|
||
INDEX idx_shard_major_minor (ShardUUID, Major, Minor)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
||
|
||
-- -----------------------------------------------------------------------------
|
||
-- 5. Insert the 64 shard UUIDs (APPEND-ONLY - never remove!)
|
||
-- -----------------------------------------------------------------------------
|
||
-- These are cryptographically random v4 UUIDs generated for Payfrit beacon sharding.
|
||
-- Apps must ship with this list and can receive additions via remote config.
|
||
-- RULE: Never remove UUIDs from this pool, only append new ones.
|
||
-- -----------------------------------------------------------------------------
|
||
INSERT INTO BeaconShards (UUID) VALUES
|
||
('f7826da6-4fa2-4e98-8024-bc5b71e0893e'), -- Shard 1
|
||
('2f234454-cf6d-4a0f-adf2-f4911ba9ffa6'), -- Shard 2
|
||
('b9407f30-f5f8-466e-aff9-25556b57fe6d'), -- Shard 3
|
||
('e2c56db5-dffb-48d2-b060-d0f5a71096e0'), -- Shard 4
|
||
('d0d3fa86-ca76-45ec-9bd9-6af4fac1e268'), -- Shard 5
|
||
('a7ae2eb7-1f00-4168-b99b-a749bac36c92'), -- Shard 6
|
||
('8deefbb9-f738-4297-8040-96668bb44281'), -- Shard 7
|
||
('5a4bcfce-174e-4bac-a814-092978f50e04'), -- Shard 8
|
||
('74278bda-b644-4520-8f0c-720eaf059935'), -- Shard 9
|
||
('e7eb6d67-2b4f-4c1b-8b6e-8c3b3f5d8b9a'), -- Shard 10
|
||
('1f3c5d7e-9a2b-4c8d-ae6f-0b1c2d3e4f5a'), -- Shard 11
|
||
('a1b2c3d4-e5f6-4789-abcd-ef0123456789'), -- Shard 12
|
||
('98765432-10fe-4cba-9876-543210fedcba'), -- Shard 13
|
||
('deadbeef-cafe-4bab-dead-beefcafebabe'), -- Shard 14
|
||
('c0ffee00-dead-4bee-f000-ba5eba11fade'), -- Shard 15
|
||
('0a1b2c3d-4e5f-4a6b-7c8d-9e0f1a2b3c4d'), -- Shard 16
|
||
('12345678-90ab-4def-1234-567890abcdef'), -- Shard 17
|
||
('fedcba98-7654-4210-fedc-ba9876543210'), -- Shard 18
|
||
('abcd1234-ef56-4789-abcd-1234ef567890'), -- Shard 19
|
||
('11111111-2222-4333-4444-555566667777'), -- Shard 20
|
||
('88889999-aaaa-4bbb-cccc-ddddeeeeefff'), -- Shard 21
|
||
('01234567-89ab-4cde-f012-3456789abcde'), -- Shard 22
|
||
('a0a0a0a0-b1b1-4c2c-d3d3-e4e4e4e4f5f5'), -- Shard 23
|
||
('f0e0d0c0-b0a0-4908-0706-050403020100'), -- Shard 24
|
||
('13579bdf-2468-4ace-1357-9bdf2468ace0'), -- Shard 25
|
||
('fdb97531-eca8-4642-0fdb-97531eca8642'), -- Shard 26
|
||
('aabbccdd-eeff-4011-2233-445566778899'), -- Shard 27
|
||
('99887766-5544-4332-2110-ffeeddccbbaa'), -- Shard 28
|
||
('a1a2a3a4-b5b6-4c7c-8d9d-e0e1f2f3f4f5'), -- Shard 29
|
||
('5f4f3f2f-1f0f-4efe-dfcf-bfaf9f8f7f6f'), -- Shard 30
|
||
('00112233-4455-4667-7889-9aabbccddeef'), -- Shard 31
|
||
('feeddccb-baa9-4887-7665-5443322110ff'), -- Shard 32
|
||
('1a2b3c4d-5e6f-4a0b-1c2d-3e4f5a6b7c8d'), -- Shard 33
|
||
('d8c7b6a5-9483-4726-1504-f3e2d1c0b9a8'), -- Shard 34
|
||
('0f1e2d3c-4b5a-4697-8879-6a5b4c3d2e1f'), -- Shard 35
|
||
('f1e2d3c4-b5a6-4978-8697-a5b4c3d2e1f0'), -- Shard 36
|
||
('12ab34cd-56ef-4789-0abc-def123456789'), -- Shard 37
|
||
('987654fe-dcba-4098-7654-321fedcba098'), -- Shard 38
|
||
('abcdef01-2345-4678-9abc-def012345678'), -- Shard 39
|
||
('876543fe-dcba-4210-9876-543fedcba210'), -- Shard 40
|
||
('0a0b0c0d-0e0f-4101-1121-314151617181'), -- Shard 41
|
||
('91a1b1c1-d1e1-4f10-2030-405060708090'), -- Shard 42
|
||
('a0b1c2d3-e4f5-4a6b-7c8d-9e0f1a2b3c4d'), -- Shard 43
|
||
('d4c3b2a1-0f9e-48d7-c6b5-a49382716050'), -- Shard 44
|
||
('50607080-90a0-4b0c-0d0e-0f1011121314'), -- Shard 45
|
||
('14131211-100f-4e0d-0c0b-0a0908070605'), -- Shard 46
|
||
('a1b2c3d4-e5f6-4718-293a-4b5c6d7e8f90'), -- Shard 47
|
||
('09f8e7d6-c5b4-4a39-2817-0615f4e3d2c1'), -- Shard 48
|
||
('11223344-5566-4778-899a-abbccddeeff0'), -- Shard 49
|
||
('ffeeddc0-bbaa-4988-7766-554433221100'), -- Shard 50
|
||
('a1a1b2b2-c3c3-4d4d-e5e5-f6f6a7a7b8b8'), -- Shard 51
|
||
('b8b8a7a7-f6f6-4e5e-5d4d-4c3c3b2b2a1a'), -- Shard 52
|
||
('12341234-5678-4567-89ab-89abcdefcdef'), -- Shard 53
|
||
('fedcfedc-ba98-4ba9-8765-87654321d321'), -- Shard 54
|
||
('0a1a2a3a-4a5a-4a6a-7a8a-9aaabacadaea'), -- Shard 55
|
||
('eadacaba-aa9a-48a7-a6a5-a4a3a2a1a0af'), -- Shard 56
|
||
('01020304-0506-4708-090a-0b0c0d0e0f10'), -- Shard 57
|
||
('100f0e0d-0c0b-4a09-0807-060504030201'), -- Shard 58
|
||
('aabbccdd-1122-4334-4556-6778899aabbc'), -- Shard 59
|
||
('cbba9988-7766-4554-4332-2110ddccbbaa'), -- Shard 60
|
||
('f0f1f2f3-f4f5-4f6f-7f8f-9fafbfcfdfef'), -- Shard 61
|
||
('efdfcfbf-af9f-48f7-f6f5-f4f3f2f1f0ee'), -- Shard 62
|
||
('a0a1a2a3-a4a5-4a6a-7a8a-9a0b1b2b3b4b'), -- Shard 63
|
||
('4b3b2b1b-0a9a-48a7-a6a5-a4a3a2a1a0ff'); -- Shard 64
|
||
|
||
-- -----------------------------------------------------------------------------
|
||
-- 6. View for shard utilization monitoring
|
||
-- -----------------------------------------------------------------------------
|
||
CREATE OR REPLACE VIEW vw_BeaconShardUtilization AS
|
||
SELECT
|
||
ID AS ShardID,
|
||
UUID AS ShardUUID,
|
||
BusinessCount,
|
||
MaxBusinesses,
|
||
ROUND((BusinessCount / MaxBusinesses) * 100, 2) AS UtilizationPercent,
|
||
(MaxBusinesses - BusinessCount) AS AvailableSlots,
|
||
IsActive,
|
||
CreatedAt
|
||
FROM BeaconShards
|
||
ORDER BY BusinessCount ASC;
|
||
|
||
-- -----------------------------------------------------------------------------
|
||
-- 7. View for business beacon namespace info
|
||
-- -----------------------------------------------------------------------------
|
||
CREATE OR REPLACE VIEW vw_BusinessBeaconNamespace AS
|
||
SELECT
|
||
b.ID AS BusinessID,
|
||
b.Name AS BusinessName,
|
||
bs.UUID AS BeaconShardUUID,
|
||
b.BeaconMajor,
|
||
bs.ID AS ShardID
|
||
FROM Businesses b
|
||
LEFT JOIN BeaconShards bs ON b.BeaconShardID = bs.ID
|
||
WHERE b.BeaconShardID IS NOT NULL;
|
||
|
||
-- -----------------------------------------------------------------------------
|
||
-- End of Migration
|
||
-- -----------------------------------------------------------------------------
|