183 lines
7.8 KiB
SQL
183 lines
7.8 KiB
SQL
CREATE TABLE `relay_regions` (
|
|
`id` text PRIMARY KEY NOT NULL,
|
|
`code` text NOT NULL,
|
|
`display_name` text NOT NULL,
|
|
`status` text DEFAULT 'active' NOT NULL,
|
|
`public_origin` text NOT NULL,
|
|
`created_at` text DEFAULT CURRENT_TIMESTAMP NOT NULL,
|
|
`updated_at` text DEFAULT CURRENT_TIMESTAMP NOT NULL
|
|
);
|
|
--> statement-breakpoint
|
|
CREATE UNIQUE INDEX `idx_relay_regions_code` ON `relay_regions` (`code`);
|
|
--> statement-breakpoint
|
|
CREATE UNIQUE INDEX `idx_relay_regions_public_origin` ON `relay_regions` (`public_origin`);
|
|
--> statement-breakpoint
|
|
CREATE TABLE `relay_nodes` (
|
|
`id` text PRIMARY KEY NOT NULL,
|
|
`region_id` text NOT NULL,
|
|
`status` text DEFAULT 'provisioning' NOT NULL,
|
|
`internal_endpoint_ref` text NOT NULL,
|
|
`capacity_tenants` integer DEFAULT 0 NOT NULL,
|
|
`last_heartbeat_at` text,
|
|
`heartbeat_generation` integer DEFAULT 0 NOT NULL,
|
|
`created_at` text DEFAULT CURRENT_TIMESTAMP NOT NULL,
|
|
`updated_at` text DEFAULT CURRENT_TIMESTAMP NOT NULL,
|
|
FOREIGN KEY (`region_id`) REFERENCES `relay_regions`(`id`) ON UPDATE no action ON DELETE no action
|
|
);
|
|
--> statement-breakpoint
|
|
CREATE INDEX `idx_relay_nodes_region_status` ON `relay_nodes` (`region_id`,`status`);
|
|
--> statement-breakpoint
|
|
CREATE TABLE `tenant_placements` (
|
|
`tenant_id` text PRIMARY KEY NOT NULL,
|
|
`home_region_id` text NOT NULL,
|
|
`relay_node_id` text,
|
|
`generation` integer DEFAULT 1 NOT NULL,
|
|
`state` text DEFAULT 'provisioning' NOT NULL,
|
|
`last_error_code` text,
|
|
`created_at` text DEFAULT CURRENT_TIMESTAMP NOT NULL,
|
|
`updated_at` text DEFAULT CURRENT_TIMESTAMP NOT NULL,
|
|
FOREIGN KEY (`tenant_id`) REFERENCES `tenant_instances`(`id`) ON UPDATE no action ON DELETE no action,
|
|
FOREIGN KEY (`home_region_id`) REFERENCES `relay_regions`(`id`) ON UPDATE no action ON DELETE no action,
|
|
FOREIGN KEY (`relay_node_id`) REFERENCES `relay_nodes`(`id`) ON UPDATE no action ON DELETE no action
|
|
);
|
|
--> statement-breakpoint
|
|
CREATE INDEX `idx_tenant_placements_region_state` ON `tenant_placements` (`home_region_id`,`state`);
|
|
--> statement-breakpoint
|
|
CREATE INDEX `idx_tenant_placements_node_state` ON `tenant_placements` (`relay_node_id`,`state`);
|
|
--> statement-breakpoint
|
|
CREATE TABLE `tenant_authorization_state` (
|
|
`tenant_id` text PRIMARY KEY NOT NULL,
|
|
`revision` integer DEFAULT 0 NOT NULL,
|
|
`status` text DEFAULT 'active' NOT NULL,
|
|
`updated_at` text DEFAULT CURRENT_TIMESTAMP NOT NULL,
|
|
FOREIGN KEY (`tenant_id`) REFERENCES `tenant_instances`(`id`) ON UPDATE no action ON DELETE no action
|
|
);
|
|
--> statement-breakpoint
|
|
CREATE INDEX `idx_tenant_authorization_revision` ON `tenant_authorization_state` (`revision`);
|
|
--> statement-breakpoint
|
|
CREATE TABLE `relay_signing_keys` (
|
|
`kid` text PRIMARY KEY NOT NULL,
|
|
`algorithm` text DEFAULT 'Ed25519' NOT NULL,
|
|
`public_key_jwk` text NOT NULL,
|
|
`private_key_ref` text NOT NULL,
|
|
`status` text DEFAULT 'active' NOT NULL,
|
|
`not_before` text NOT NULL,
|
|
`not_after` text NOT NULL,
|
|
`created_at` text DEFAULT CURRENT_TIMESTAMP NOT NULL,
|
|
`retired_at` text
|
|
);
|
|
--> statement-breakpoint
|
|
CREATE TABLE `relay_node_credentials` (
|
|
`id` text PRIMARY KEY NOT NULL,
|
|
`node_id` text NOT NULL,
|
|
`mtls_spiffe_id` text NOT NULL,
|
|
`certificate_fingerprint_sha256` text NOT NULL,
|
|
`status` text DEFAULT 'active' NOT NULL,
|
|
`bootstrap_reason` text NOT NULL,
|
|
`issued_by` text NOT NULL,
|
|
`issued_at` text NOT NULL,
|
|
`expires_at` text,
|
|
`last_used_at` text,
|
|
`revoked_at` text,
|
|
`created_at` text DEFAULT CURRENT_TIMESTAMP NOT NULL,
|
|
FOREIGN KEY (`node_id`) REFERENCES `relay_nodes`(`id`) ON UPDATE no action ON DELETE no action
|
|
);
|
|
--> statement-breakpoint
|
|
CREATE UNIQUE INDEX `idx_relay_node_credentials_mtls` ON `relay_node_credentials` (`mtls_spiffe_id`,`certificate_fingerprint_sha256`);
|
|
--> statement-breakpoint
|
|
CREATE INDEX `idx_relay_node_credentials_node_status` ON `relay_node_credentials` (`node_id`,`status`);
|
|
--> statement-breakpoint
|
|
CREATE TABLE `phone_handoff_tickets` (
|
|
`id` text PRIMARY KEY NOT NULL,
|
|
`ticket_hash` text NOT NULL,
|
|
`account_id` text NOT NULL,
|
|
`tenant_id` text NOT NULL,
|
|
`expected_origin` text NOT NULL,
|
|
`expires_at` text NOT NULL,
|
|
`consumed_at` text,
|
|
`consumed_by_node_id` text,
|
|
`pending_phone_name` text,
|
|
`pending_ed25519_public` text,
|
|
`pending_x25519_public` text,
|
|
`pending_identity_fingerprint` text,
|
|
`completed_at` text,
|
|
`created_at` text DEFAULT CURRENT_TIMESTAMP NOT NULL,
|
|
FOREIGN KEY (`account_id`) REFERENCES `accounts`(`id`) ON UPDATE no action ON DELETE no action,
|
|
FOREIGN KEY (`tenant_id`) REFERENCES `tenant_instances`(`id`) ON UPDATE no action ON DELETE no action,
|
|
FOREIGN KEY (`consumed_by_node_id`) REFERENCES `relay_nodes`(`id`) ON UPDATE no action ON DELETE no action
|
|
);
|
|
--> statement-breakpoint
|
|
CREATE UNIQUE INDEX `idx_phone_handoff_ticket_hash` ON `phone_handoff_tickets` (`ticket_hash`);
|
|
--> statement-breakpoint
|
|
CREATE INDEX `idx_phone_handoff_account_expiry` ON `phone_handoff_tickets` (`account_id`,`expires_at`);
|
|
--> statement-breakpoint
|
|
CREATE TABLE `phone_route_handles` (
|
|
`id` text PRIMARY KEY NOT NULL,
|
|
`handle_hash` text NOT NULL,
|
|
`tenant_id` text NOT NULL,
|
|
`phone_id` text NOT NULL,
|
|
`status` text DEFAULT 'active' NOT NULL,
|
|
`created_at` text DEFAULT CURRENT_TIMESTAMP NOT NULL,
|
|
`last_used_at` text,
|
|
`revoked_at` text,
|
|
FOREIGN KEY (`tenant_id`) REFERENCES `tenant_instances`(`id`) ON UPDATE no action ON DELETE no action
|
|
);
|
|
--> statement-breakpoint
|
|
CREATE UNIQUE INDEX `idx_phone_route_handle_hash` ON `phone_route_handles` (`handle_hash`);
|
|
--> statement-breakpoint
|
|
CREATE INDEX `idx_phone_route_tenant_status` ON `phone_route_handles` (`tenant_id`,`status`);
|
|
--> statement-breakpoint
|
|
CREATE TABLE `relay_phone_principals` (
|
|
`phone_id` text PRIMARY KEY NOT NULL,
|
|
`tenant_id` text NOT NULL,
|
|
`token_hash` text NOT NULL,
|
|
`name` text NOT NULL,
|
|
`ed25519_public` text NOT NULL,
|
|
`x25519_public` text NOT NULL,
|
|
`identity_fingerprint` text NOT NULL,
|
|
`status` text DEFAULT 'active' NOT NULL,
|
|
`created_at` text DEFAULT CURRENT_TIMESTAMP NOT NULL,
|
|
`last_used_at` text,
|
|
`revoked_at` text,
|
|
FOREIGN KEY (`tenant_id`) REFERENCES `tenant_instances`(`id`) ON UPDATE no action ON DELETE no action
|
|
);
|
|
--> statement-breakpoint
|
|
CREATE UNIQUE INDEX `idx_relay_phone_token_hash` ON `relay_phone_principals` (`token_hash`);
|
|
--> statement-breakpoint
|
|
CREATE UNIQUE INDEX `idx_relay_phone_tenant_identity` ON `relay_phone_principals` (`tenant_id`,`identity_fingerprint`);
|
|
--> statement-breakpoint
|
|
CREATE INDEX `idx_relay_phone_tenant_status` ON `relay_phone_principals` (`tenant_id`,`status`);
|
|
--> statement-breakpoint
|
|
CREATE TABLE `device_registration_replays` (
|
|
`pairing_id` text PRIMARY KEY NOT NULL,
|
|
`request_hash` text NOT NULL,
|
|
`response_ciphertext` text NOT NULL,
|
|
`response_nonce` text NOT NULL,
|
|
`expires_at` text NOT NULL,
|
|
`created_at` text DEFAULT CURRENT_TIMESTAMP NOT NULL,
|
|
FOREIGN KEY (`pairing_id`) REFERENCES `pairing_requests`(`id`) ON UPDATE no action ON DELETE no action
|
|
);
|
|
--> statement-breakpoint
|
|
CREATE INDEX `idx_device_registration_replay_expiry` ON `device_registration_replays` (`expires_at`);
|
|
--> statement-breakpoint
|
|
INSERT OR IGNORE INTO `relay_regions`
|
|
(`id`, `code`, `display_name`, `status`, `public_origin`, `created_at`, `updated_at`)
|
|
VALUES
|
|
('region_default', 'default', 'Default region', 'active', 'https://connect.invalid', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
|
|
--> statement-breakpoint
|
|
INSERT OR IGNORE INTO `tenant_placements`
|
|
(`tenant_id`, `home_region_id`, `generation`, `state`, `created_at`, `updated_at`)
|
|
SELECT `id`, 'region_default', 1, 'provisioning', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP
|
|
FROM `tenant_instances`;
|
|
--> statement-breakpoint
|
|
DROP TABLE IF EXISTS `provisioner_workers`;
|
|
--> statement-breakpoint
|
|
DROP TABLE IF EXISTS `provisioning_operations`;
|
|
--> statement-breakpoint
|
|
INSERT OR IGNORE INTO `tenant_authorization_state`
|
|
(`tenant_id`, `revision`, `status`, `updated_at`)
|
|
SELECT `id`, `credential_revision`,
|
|
CASE WHEN `tombstoned_at` IS NULL THEN 'active' ELSE 'suspended' END,
|
|
CURRENT_TIMESTAMP
|
|
FROM `tenant_instances`;
|