108 lines
4.7 KiB
SQL
108 lines
4.7 KiB
SQL
-- Migrate from Auth.js / custom user table to Better Auth.
|
|
-- Better Auth owns user, session, account, verification tables.
|
|
-- Drop tables in FK dependency order, then recreate with new FKs pointing to user(id).
|
|
|
|
-- Drop dependent tables first
|
|
DROP TABLE IF EXISTS audit_log;
|
|
DROP TABLE IF EXISTS resolution;
|
|
DROP TABLE IF EXISTS company_member;
|
|
DROP TABLE IF EXISTS "user";
|
|
|
|
-- Better Auth user table with displayName as additionalField
|
|
CREATE TABLE "user" (
|
|
"id" text not null primary key,
|
|
"name" text not null,
|
|
"email" text not null unique,
|
|
"email_verified" integer not null,
|
|
"image" text,
|
|
"display_name" text not null default '',
|
|
"created_at" date not null,
|
|
"updated_at" date not null
|
|
);
|
|
|
|
-- Recreate company_member referencing user(id)
|
|
CREATE TABLE company_member (
|
|
member_id TEXT PRIMARY KEY DEFAULT (lower(substr(hex(randomblob(4)), 1, 8) || '-' || substr(hex(randomblob(2)), 1, 4) || '-4' || substr(hex(randomblob(2)), 2, 3) || '-' || substr('89ab', abs(random()) % 4 + 1, 1) || substr(hex(randomblob(2)), 2, 3) || '-' || hex(randomblob(6)))),
|
|
company_id TEXT NOT NULL REFERENCES company(company_id) ON DELETE CASCADE,
|
|
user_id TEXT NOT NULL REFERENCES "user"(id) ON DELETE CASCADE,
|
|
role TEXT NOT NULL CHECK (role IN ('managing_director', 'shareholder', 'angel')),
|
|
shares INTEGER,
|
|
created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
UNIQUE (company_id, user_id)
|
|
);
|
|
|
|
CREATE INDEX idx_company_member_company ON company_member(company_id);
|
|
CREATE INDEX idx_company_member_user ON company_member(user_id);
|
|
|
|
-- Recreate resolution referencing user(id)
|
|
CREATE TABLE resolution (
|
|
resolution_id TEXT PRIMARY KEY DEFAULT (lower(substr(hex(randomblob(4)), 1, 8) || '-' || substr(hex(randomblob(2)), 1, 4) || '-4' || substr(hex(randomblob(2)), 2, 3) || '-' || substr('89ab', abs(random()) % 4 + 1, 1) || substr(hex(randomblob(2)), 2, 3) || '-' || hex(randomblob(6)))),
|
|
company_id TEXT NOT NULL REFERENCES company(company_id) ON DELETE CASCADE,
|
|
template_key TEXT NOT NULL,
|
|
title TEXT NOT NULL,
|
|
status TEXT NOT NULL DEFAULT 'draft' CHECK (status IN ('draft', 'published', 'archived')),
|
|
field_values TEXT NOT NULL DEFAULT '{}',
|
|
pdf_key TEXT,
|
|
created_by TEXT NOT NULL REFERENCES "user"(id),
|
|
created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
published_at TEXT
|
|
);
|
|
|
|
CREATE INDEX idx_resolution_company ON resolution(company_id);
|
|
CREATE INDEX idx_resolution_status ON resolution(status);
|
|
|
|
-- Recreate audit_log referencing user(id)
|
|
CREATE TABLE audit_log (
|
|
audit_id TEXT PRIMARY KEY DEFAULT (lower(substr(hex(randomblob(4)), 1, 8) || '-' || substr(hex(randomblob(2)), 1, 4) || '-4' || substr(hex(randomblob(2)), 2, 3) || '-' || substr('89ab', abs(random()) % 4 + 1, 1) || substr(hex(randomblob(2)), 2, 3) || '-' || hex(randomblob(6)))),
|
|
table_name TEXT NOT NULL,
|
|
record_id TEXT NOT NULL,
|
|
action TEXT NOT NULL CHECK (action IN ('INSERT', 'UPDATE', 'DELETE')),
|
|
user_id TEXT REFERENCES "user"(id),
|
|
changed_fields TEXT,
|
|
created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
CREATE INDEX idx_audit_log_record ON audit_log(table_name, record_id);
|
|
CREATE INDEX idx_audit_log_created ON audit_log(created_at);
|
|
|
|
-- Better Auth session/account/verification tables
|
|
CREATE TABLE IF NOT EXISTS "session" (
|
|
"id" text not null primary key,
|
|
"expires_at" date not null,
|
|
"token" text not null unique,
|
|
"created_at" date not null,
|
|
"updated_at" date not null,
|
|
"ip_address" text,
|
|
"user_agent" text,
|
|
"user_id" text not null references "user" ("id") on delete cascade
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS "account" (
|
|
"id" text not null primary key,
|
|
"account_id" text not null,
|
|
"provider_id" text not null,
|
|
"user_id" text not null references "user" ("id") on delete cascade,
|
|
"access_token" text,
|
|
"refresh_token" text,
|
|
"id_token" text,
|
|
"access_token_expires_at" date,
|
|
"refresh_token_expires_at" date,
|
|
"scope" text,
|
|
"password" text,
|
|
"created_at" date not null,
|
|
"updated_at" date not null
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS "verification" (
|
|
"id" text not null primary key,
|
|
"identifier" text not null,
|
|
"value" text not null,
|
|
"expires_at" date not null,
|
|
"created_at" date not null,
|
|
"updated_at" date not null
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS "session_user_id_idx" ON "session" ("user_id");
|
|
CREATE INDEX IF NOT EXISTS "account_user_id_idx" ON "account" ("user_id");
|
|
CREATE INDEX IF NOT EXISTS "verification_identifier_idx" ON "verification" ("identifier");
|