240 lines
9.9 KiB
SQL
240 lines
9.9 KiB
SQL
CREATE TABLE `accounts` (
|
|
`id` text PRIMARY KEY NOT NULL,
|
|
`auth_subject` text NOT NULL,
|
|
`email` text NOT NULL,
|
|
`display_name` text NOT NULL,
|
|
`status` text DEFAULT 'active' NOT NULL,
|
|
`created_at` text DEFAULT CURRENT_TIMESTAMP NOT NULL,
|
|
`updated_at` text DEFAULT CURRENT_TIMESTAMP NOT NULL
|
|
);
|
|
--> statement-breakpoint
|
|
CREATE UNIQUE INDEX `idx_accounts_auth_subject` ON `accounts` (`auth_subject`);--> statement-breakpoint
|
|
CREATE INDEX `idx_accounts_email` ON `accounts` (`email`);--> statement-breakpoint
|
|
CREATE TABLE `audit_events` (
|
|
`id` text PRIMARY KEY NOT NULL,
|
|
`actor_id` text NOT NULL,
|
|
`action` text NOT NULL,
|
|
`target_type` text NOT NULL,
|
|
`target_id` text NOT NULL,
|
|
`reason` text NOT NULL,
|
|
`before_json` text,
|
|
`after_json` text,
|
|
`correlation_id` text NOT NULL,
|
|
`created_at` text DEFAULT CURRENT_TIMESTAMP NOT NULL
|
|
);
|
|
--> statement-breakpoint
|
|
CREATE INDEX `idx_audit_target_time` ON `audit_events` (`target_type`,`target_id`,`created_at`);--> statement-breakpoint
|
|
CREATE INDEX `idx_audit_actor_time` ON `audit_events` (`actor_id`,`created_at`);--> statement-breakpoint
|
|
CREATE TABLE `beta_programs` (
|
|
`id` text PRIMARY KEY NOT NULL,
|
|
`state` text NOT NULL,
|
|
`capacity_slots` integer,
|
|
`starts_at` text NOT NULL,
|
|
`ends_at` text,
|
|
`grace_days` integer DEFAULT 7 NOT NULL,
|
|
`created_by` text NOT NULL,
|
|
`created_at` text DEFAULT CURRENT_TIMESTAMP NOT NULL,
|
|
`retired_at` text
|
|
);
|
|
--> statement-breakpoint
|
|
CREATE INDEX `idx_beta_state_time` ON `beta_programs` (`state`,`starts_at`);--> statement-breakpoint
|
|
CREATE TABLE `entitlement_grants` (
|
|
`id` text PRIMARY KEY NOT NULL,
|
|
`account_id` text NOT NULL,
|
|
`host_id` text,
|
|
`source` text NOT NULL,
|
|
`source_ref` text,
|
|
`capacity_slots` integer,
|
|
`starts_at` text NOT NULL,
|
|
`ends_at` text,
|
|
`state` text DEFAULT 'scheduled' NOT NULL,
|
|
`reason` text NOT NULL,
|
|
`created_by` text NOT NULL,
|
|
`created_at` text DEFAULT CURRENT_TIMESTAMP NOT NULL,
|
|
`revoked_at` text,
|
|
FOREIGN KEY (`account_id`) REFERENCES `accounts`(`id`) ON UPDATE no action ON DELETE no action,
|
|
FOREIGN KEY (`host_id`) REFERENCES `hosts`(`id`) ON UPDATE no action ON DELETE no action
|
|
);
|
|
--> statement-breakpoint
|
|
CREATE INDEX `idx_grants_account_state_time` ON `entitlement_grants` (`account_id`,`state`,`starts_at`);--> statement-breakpoint
|
|
CREATE UNIQUE INDEX `idx_grants_source_ref` ON `entitlement_grants` (`source`,`source_ref`);--> statement-breakpoint
|
|
CREATE TABLE `hosts` (
|
|
`id` text PRIMARY KEY NOT NULL,
|
|
`account_id` text NOT NULL,
|
|
`name` text NOT NULL,
|
|
`os` text NOT NULL,
|
|
`lifecycle` text DEFAULT 'active' NOT NULL,
|
|
`slot_state` text DEFAULT 'active' NOT NULL,
|
|
`connection_state` text DEFAULT 'unknown' NOT NULL,
|
|
`daemon_version` text,
|
|
`recovery_fingerprint` text,
|
|
`last_seen_at` text,
|
|
`created_at` text DEFAULT CURRENT_TIMESTAMP NOT NULL,
|
|
`deactivated_at` text,
|
|
FOREIGN KEY (`account_id`) REFERENCES `accounts`(`id`) ON UPDATE no action ON DELETE no action
|
|
);
|
|
--> statement-breakpoint
|
|
CREATE INDEX `idx_hosts_account_lifecycle` ON `hosts` (`account_id`,`lifecycle`);--> statement-breakpoint
|
|
CREATE UNIQUE INDEX `idx_hosts_recovery_fingerprint` ON `hosts` (`recovery_fingerprint`);--> statement-breakpoint
|
|
CREATE TABLE `idempotency_records` (
|
|
`scope` text NOT NULL,
|
|
`key` text NOT NULL,
|
|
`request_hash` text NOT NULL,
|
|
`response_json` text NOT NULL,
|
|
`status_code` integer NOT NULL,
|
|
`expires_at` text NOT NULL,
|
|
`created_at` text DEFAULT CURRENT_TIMESTAMP NOT NULL,
|
|
PRIMARY KEY(`scope`, `key`)
|
|
);
|
|
--> statement-breakpoint
|
|
CREATE INDEX `idx_idempotency_expires` ON `idempotency_records` (`expires_at`);--> statement-breakpoint
|
|
CREATE TABLE `invoices` (
|
|
`id` text PRIMARY KEY NOT NULL,
|
|
`order_id` text NOT NULL,
|
|
`invoice_type` text DEFAULT 'commercial' NOT NULL,
|
|
`invoice_number` text,
|
|
`amount_minor` integer NOT NULL,
|
|
`tax_minor` integer,
|
|
`currency` text NOT NULL,
|
|
`status` text DEFAULT 'draft' NOT NULL,
|
|
`document_ref` text,
|
|
`created_at` text DEFAULT CURRENT_TIMESTAMP NOT NULL,
|
|
`updated_at` text DEFAULT CURRENT_TIMESTAMP NOT NULL,
|
|
FOREIGN KEY (`order_id`) REFERENCES `orders`(`id`) ON UPDATE no action ON DELETE no action
|
|
);
|
|
--> statement-breakpoint
|
|
CREATE UNIQUE INDEX `idx_invoices_order` ON `invoices` (`order_id`);--> statement-breakpoint
|
|
CREATE TABLE `launch_gates` (
|
|
`key` text PRIMARY KEY NOT NULL,
|
|
`priority` text NOT NULL,
|
|
`category` text NOT NULL,
|
|
`title` text NOT NULL,
|
|
`status` text DEFAULT 'blocked' NOT NULL,
|
|
`owner` text,
|
|
`evidence_url` text,
|
|
`notes` text DEFAULT '' NOT NULL,
|
|
`reviewed_at` text,
|
|
`updated_at` text DEFAULT CURRENT_TIMESTAMP NOT NULL
|
|
);
|
|
--> statement-breakpoint
|
|
CREATE INDEX `idx_launch_gates_status` ON `launch_gates` (`priority`,`status`);--> statement-breakpoint
|
|
CREATE TABLE `orders` (
|
|
`id` text PRIMARY KEY NOT NULL,
|
|
`account_id` text NOT NULL,
|
|
`client_order_id` text NOT NULL,
|
|
`kind` text DEFAULT 'purchase' NOT NULL,
|
|
`price_version_id` text NOT NULL,
|
|
`slot_quantity` integer NOT NULL,
|
|
`term_start` text NOT NULL,
|
|
`term_end` text NOT NULL,
|
|
`amount_minor` integer NOT NULL,
|
|
`currency` text NOT NULL,
|
|
`status` text DEFAULT 'draft' NOT NULL,
|
|
`quote_expires_at` text NOT NULL,
|
|
`created_at` text DEFAULT CURRENT_TIMESTAMP NOT NULL,
|
|
`updated_at` text DEFAULT CURRENT_TIMESTAMP NOT NULL,
|
|
FOREIGN KEY (`account_id`) REFERENCES `accounts`(`id`) ON UPDATE no action ON DELETE no action,
|
|
FOREIGN KEY (`price_version_id`) REFERENCES `price_versions`(`id`) ON UPDATE no action ON DELETE no action
|
|
);
|
|
--> statement-breakpoint
|
|
CREATE UNIQUE INDEX `idx_orders_account_client` ON `orders` (`account_id`,`client_order_id`);--> statement-breakpoint
|
|
CREATE INDEX `idx_orders_account_status` ON `orders` (`account_id`,`status`);--> statement-breakpoint
|
|
CREATE TABLE `pairing_requests` (
|
|
`id` text PRIMARY KEY NOT NULL,
|
|
`account_id` text NOT NULL,
|
|
`requested_name` text NOT NULL,
|
|
`os` text NOT NULL,
|
|
`code_hash` text NOT NULL,
|
|
`status` text DEFAULT 'waiting' NOT NULL,
|
|
`expires_at` text NOT NULL,
|
|
`claimed_host_id` text,
|
|
`created_at` text DEFAULT CURRENT_TIMESTAMP NOT NULL,
|
|
`claimed_at` text,
|
|
FOREIGN KEY (`account_id`) REFERENCES `accounts`(`id`) ON UPDATE no action ON DELETE no action,
|
|
FOREIGN KEY (`claimed_host_id`) REFERENCES `hosts`(`id`) ON UPDATE no action ON DELETE no action
|
|
);
|
|
--> statement-breakpoint
|
|
CREATE UNIQUE INDEX `idx_pairing_code_hash` ON `pairing_requests` (`code_hash`);--> statement-breakpoint
|
|
CREATE INDEX `idx_pairing_account_status` ON `pairing_requests` (`account_id`,`status`);--> statement-breakpoint
|
|
CREATE TABLE `payment_attempts` (
|
|
`id` text PRIMARY KEY NOT NULL,
|
|
`order_id` text NOT NULL,
|
|
`provider` text NOT NULL,
|
|
`provider_payment_ref` text,
|
|
`provider_event_id` text,
|
|
`amount_minor` integer NOT NULL,
|
|
`currency` text NOT NULL,
|
|
`status` text DEFAULT 'created' NOT NULL,
|
|
`created_at` text DEFAULT CURRENT_TIMESTAMP NOT NULL,
|
|
`updated_at` text DEFAULT CURRENT_TIMESTAMP NOT NULL,
|
|
FOREIGN KEY (`order_id`) REFERENCES `orders`(`id`) ON UPDATE no action ON DELETE no action
|
|
);
|
|
--> statement-breakpoint
|
|
CREATE UNIQUE INDEX `idx_payments_provider_event` ON `payment_attempts` (`provider`,`provider_event_id`);--> statement-breakpoint
|
|
CREATE UNIQUE INDEX `idx_payments_provider_ref` ON `payment_attempts` (`provider`,`provider_payment_ref`);--> statement-breakpoint
|
|
CREATE TABLE `price_versions` (
|
|
`id` text PRIMARY KEY NOT NULL,
|
|
`product_code` text DEFAULT 'host_slot' NOT NULL,
|
|
`billing_period` text NOT NULL,
|
|
`unit_slots` integer DEFAULT 1 NOT NULL,
|
|
`amount_minor` integer NOT NULL,
|
|
`currency` text DEFAULT 'CNY' NOT NULL,
|
|
`tax_mode` text DEFAULT 'undecided' NOT NULL,
|
|
`quote_ttl_seconds` integer DEFAULT 900 NOT NULL,
|
|
`status` text DEFAULT 'draft' NOT NULL,
|
|
`effective_from` text NOT NULL,
|
|
`effective_to` text,
|
|
`created_by` text NOT NULL,
|
|
`created_at` text DEFAULT CURRENT_TIMESTAMP NOT NULL
|
|
);
|
|
--> statement-breakpoint
|
|
CREATE INDEX `idx_prices_catalog` ON `price_versions` (`product_code`,`billing_period`,`status`);--> statement-breakpoint
|
|
CREATE TABLE `provisioning_operations` (
|
|
`id` text PRIMARY KEY NOT NULL,
|
|
`tenant_id` text NOT NULL,
|
|
`operation` text NOT NULL,
|
|
`idempotency_key` text NOT NULL,
|
|
`correlation_id` text NOT NULL,
|
|
`target_generation` integer NOT NULL,
|
|
`status` text DEFAULT 'requested' NOT NULL,
|
|
`error_code` text,
|
|
`error_detail` 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
|
|
);
|
|
--> statement-breakpoint
|
|
CREATE UNIQUE INDEX `idx_operations_idempotency` ON `provisioning_operations` (`tenant_id`,`idempotency_key`);--> statement-breakpoint
|
|
CREATE INDEX `idx_operations_status` ON `provisioning_operations` (`status`);--> statement-breakpoint
|
|
CREATE TABLE `refunds` (
|
|
`id` text PRIMARY KEY NOT NULL,
|
|
`payment_attempt_id` text NOT NULL,
|
|
`invoice_id` text,
|
|
`amount_minor` integer NOT NULL,
|
|
`currency` text NOT NULL,
|
|
`reason` text NOT NULL,
|
|
`status` text DEFAULT 'requested' NOT NULL,
|
|
`entitlement_effect` text DEFAULT 'pending' NOT NULL,
|
|
`created_at` text DEFAULT CURRENT_TIMESTAMP NOT NULL,
|
|
`updated_at` text DEFAULT CURRENT_TIMESTAMP NOT NULL,
|
|
FOREIGN KEY (`payment_attempt_id`) REFERENCES `payment_attempts`(`id`) ON UPDATE no action ON DELETE no action,
|
|
FOREIGN KEY (`invoice_id`) REFERENCES `invoices`(`id`) ON UPDATE no action ON DELETE no action
|
|
);
|
|
--> statement-breakpoint
|
|
CREATE INDEX `idx_refunds_payment` ON `refunds` (`payment_attempt_id`);--> statement-breakpoint
|
|
CREATE TABLE `tenant_instances` (
|
|
`id` text PRIMARY KEY NOT NULL,
|
|
`account_id` text NOT NULL,
|
|
`slug` text NOT NULL,
|
|
`lifecycle` text DEFAULT 'requested' NOT NULL,
|
|
`desired_generation` integer DEFAULT 1 NOT NULL,
|
|
`active_generation` integer,
|
|
`runtime_version` text,
|
|
`last_health_at` text,
|
|
`created_at` text DEFAULT CURRENT_TIMESTAMP NOT NULL,
|
|
`updated_at` text DEFAULT CURRENT_TIMESTAMP NOT NULL,
|
|
FOREIGN KEY (`account_id`) REFERENCES `accounts`(`id`) ON UPDATE no action ON DELETE no action
|
|
);
|
|
--> statement-breakpoint
|
|
CREATE UNIQUE INDEX `idx_tenants_account` ON `tenant_instances` (`account_id`);--> statement-breakpoint
|
|
CREATE UNIQUE INDEX `idx_tenants_slug` ON `tenant_instances` (`slug`); |