51 lines
2.8 KiB
SQL
51 lines
2.8 KiB
SQL
-- VC Gateway Schema
|
|
-- Invite links, visitor sessions, and DM support for anonymous visitors.
|
|
-- Part of Sprinter Hub - supports fundraising / investor access.
|
|
|
|
-- Invite links with expiration and channel scoping
|
|
CREATE TABLE IF NOT EXISTS Hub_InviteLinks (
|
|
ID INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
|
|
Token VARCHAR(64) NOT NULL COMMENT 'URL-safe token for link',
|
|
Label VARCHAR(255) NOT NULL DEFAULT '' COMMENT 'Human label e.g. investor name',
|
|
AllowedChannels JSON NOT NULL COMMENT 'Array of Hub_Channels.ID the visitor can read',
|
|
HostAddress VARCHAR(150) NOT NULL COMMENT 'Sprinter agent address of the host (DM target)',
|
|
ExpiresAt DATETIME NULL COMMENT 'NULL = never expires',
|
|
MaxUses INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '0 = unlimited',
|
|
UseCount INT UNSIGNED NOT NULL DEFAULT 0,
|
|
IsRevoked TINYINT(1) NOT NULL DEFAULT 0,
|
|
CreatedBy VARCHAR(150) NOT NULL COMMENT 'Agent who created the link',
|
|
CreatedAt DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
UpdatedAt DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
|
|
UNIQUE KEY uq_invite_token (Token),
|
|
INDEX idx_created_by (CreatedBy),
|
|
INDEX idx_revoked (IsRevoked),
|
|
INDEX idx_expires (ExpiresAt)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
-- Visitor sessions - created when someone authenticates via invite link
|
|
CREATE TABLE IF NOT EXISTS Hub_Visitors (
|
|
ID INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
|
|
InviteLinkID INT UNSIGNED NOT NULL,
|
|
VisitorToken VARCHAR(64) NOT NULL COMMENT 'Session token for visitor API calls',
|
|
DisplayName VARCHAR(100) NOT NULL DEFAULT 'Visitor',
|
|
DMChannelID INT UNSIGNED NULL COMMENT 'Hub_Channels.ID for visitor<->host DM',
|
|
CreatedAt DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
LastActiveAt DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
|
|
UNIQUE KEY uq_visitor_token (VisitorToken),
|
|
INDEX idx_invite_link (InviteLinkID),
|
|
INDEX idx_dm_channel (DMChannelID),
|
|
CONSTRAINT fk_visitor_invite FOREIGN KEY (InviteLinkID) REFERENCES Hub_InviteLinks(ID) ON DELETE CASCADE
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
-- Rate limiting: track visitor message counts per sliding window
|
|
CREATE TABLE IF NOT EXISTS Hub_VisitorRateLimit (
|
|
ID INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
|
|
VisitorID INT UNSIGNED NOT NULL,
|
|
WindowStart DATETIME NOT NULL,
|
|
MessageCount INT UNSIGNED NOT NULL DEFAULT 1,
|
|
|
|
UNIQUE KEY uq_visitor_window (VisitorID, WindowStart),
|
|
CONSTRAINT fk_ratelimit_visitor FOREIGN KEY (VisitorID) REFERENCES Hub_Visitors(ID) ON DELETE CASCADE
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|