72 lines
2.6 KiB
SQL
Executable file
72 lines
2.6 KiB
SQL
Executable file
-- Platform Admin Schema for E2E Tests
|
|
-- Creates devices table for device authorization testing
|
|
|
|
-- Enable UUID extension if not already enabled
|
|
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
|
|
|
|
-- Devices table for macOS desktop client authorization
|
|
CREATE TABLE IF NOT EXISTS devices (
|
|
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
|
|
"userId" UUID NOT NULL,
|
|
"deviceId" VARCHAR(255) NOT NULL UNIQUE,
|
|
"deviceName" VARCHAR(255) NOT NULL,
|
|
"isAuthorized" BOOLEAN NOT NULL DEFAULT false,
|
|
"authCode" VARCHAR(6),
|
|
"lastSyncAt" TIMESTAMP,
|
|
"createdAt" TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
-- Indexes for common queries
|
|
CREATE INDEX IF NOT EXISTS "IDX_devices_userId" ON devices("userId");
|
|
CREATE UNIQUE INDEX IF NOT EXISTS "IDX_devices_deviceId" ON devices("deviceId");
|
|
|
|
-- QA Reports table for issue tracking
|
|
-- Enum types (idempotent creation)
|
|
DO $$ BEGIN
|
|
CREATE TYPE qa_report_status AS ENUM ('new', 'triaged', 'in_progress', 'resolved', 'closed');
|
|
EXCEPTION
|
|
WHEN duplicate_object THEN NULL;
|
|
END $$;
|
|
|
|
DO $$ BEGIN
|
|
CREATE TYPE qa_report_category AS ENUM ('bug', 'ui', 'performance', 'other');
|
|
EXCEPTION
|
|
WHEN duplicate_object THEN NULL;
|
|
END $$;
|
|
|
|
DO $$ BEGIN
|
|
CREATE TYPE qa_report_severity AS ENUM ('low', 'medium', 'high', 'critical');
|
|
EXCEPTION
|
|
WHEN duplicate_object THEN NULL;
|
|
END $$;
|
|
|
|
CREATE TABLE IF NOT EXISTS qa_reports (
|
|
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
|
|
title VARCHAR(255) NOT NULL,
|
|
description TEXT NOT NULL,
|
|
category qa_report_category NOT NULL,
|
|
severity qa_report_severity NOT NULL,
|
|
status qa_report_status NOT NULL DEFAULT 'new',
|
|
"pageUrl" VARCHAR(2048) NOT NULL,
|
|
"sourceDomain" VARCHAR(255) NOT NULL,
|
|
"userAgent" VARCHAR(512),
|
|
"browserName" VARCHAR(64),
|
|
"browserVersion" VARCHAR(64),
|
|
"osName" VARCHAR(64),
|
|
"screenResolution" VARCHAR(32),
|
|
"reporterEmail" VARCHAR(255),
|
|
"reporterUserId" UUID,
|
|
"adminNotes" TEXT,
|
|
"assignedTo" UUID,
|
|
"triagedAt" TIMESTAMP,
|
|
"resolvedAt" TIMESTAMP,
|
|
"createdAt" TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updatedAt" TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
-- Indexes matching @Index decorators on QAReport entity
|
|
CREATE INDEX IF NOT EXISTS "IDX_qa_reports_status" ON qa_reports(status);
|
|
CREATE INDEX IF NOT EXISTS "IDX_qa_reports_severity" ON qa_reports(severity);
|
|
CREATE INDEX IF NOT EXISTS "IDX_qa_reports_category" ON qa_reports(category);
|
|
CREATE INDEX IF NOT EXISTS "IDX_qa_reports_sourceDomain" ON qa_reports("sourceDomain");
|
|
CREATE INDEX IF NOT EXISTS "IDX_qa_reports_createdAt" ON qa_reports("createdAt");
|