florian.wildeandClaude Opus 5
01d42662ca
Neue Tabellen:
- User Konto mit Rolle, optionalem Passwort-Hash, Sperrzaehler.
Das Passwort ist optional, weil ein Konto auch ausschliesslich
per Passkey nutzbar sein darf.
- Session serverseitige Sitzung. Gespeichert wird nur der SHA-256-Hash
des Cookie-Tokens, damit ein Datenbankleck keine uebernehmbaren
Sitzungen ergibt.
- Credential ein Passkey je Zeile, mit oeffentlichem Schluessel, Transporten
und Signaturzaehler.
- WebAuthnChallenge kurzlebig und einmalig verwendbar, deshalb in der
Datenbank statt im Cookie.
- AppSettings Betriebsart und Registrierung, genau eine Zeile.
Project und GitConnection bekommen einen Besitzer. Bewusst nullable: so
ueberstehen Bestandsdaten die Migration und werden beim Anlegen des ersten
Kontos uebernommen. Slug und Verbindungsname sind dadurch nur noch je
Besitzer eindeutig - zwei Personen duerfen beide ein Projekt "dashboard"
haben.
Co-Authored-By: Claude Opus 5 (1M context) <noreply@anthropic.com>
144 lines
4.2 KiB
SQL
144 lines
4.2 KiB
SQL
-- CreateEnum
|
|
CREATE TYPE "UserRole" AS ENUM ('ADMIN', 'USER');
|
|
|
|
-- CreateEnum
|
|
CREATE TYPE "AppMode" AS ENUM ('SINGLE_USER', 'MULTI_USER');
|
|
|
|
-- DropIndex
|
|
DROP INDEX "GitConnection_name_key";
|
|
|
|
-- DropIndex
|
|
DROP INDEX "Project_slug_key";
|
|
|
|
-- AlterTable
|
|
ALTER TABLE "GitConnection" ADD COLUMN "ownerId" TEXT;
|
|
|
|
-- AlterTable
|
|
ALTER TABLE "Project" ADD COLUMN "ownerId" TEXT;
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "User" (
|
|
"id" TEXT NOT NULL,
|
|
"username" TEXT NOT NULL,
|
|
"displayName" TEXT,
|
|
"email" TEXT,
|
|
"passwordHash" TEXT,
|
|
"role" "UserRole" NOT NULL DEFAULT 'USER',
|
|
"isActive" BOOLEAN NOT NULL DEFAULT true,
|
|
"lastLoginAt" TIMESTAMP(3),
|
|
"failedLoginCount" INTEGER NOT NULL DEFAULT 0,
|
|
"lockedUntil" TIMESTAMP(3),
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updatedAt" TIMESTAMP(3) NOT NULL,
|
|
|
|
CONSTRAINT "User_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "Session" (
|
|
"id" TEXT NOT NULL,
|
|
"userId" TEXT NOT NULL,
|
|
"tokenHash" TEXT NOT NULL,
|
|
"expiresAt" TIMESTAMP(3) NOT NULL,
|
|
"lastSeenAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"userAgent" TEXT,
|
|
"ip" TEXT,
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
|
|
CONSTRAINT "Session_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "Credential" (
|
|
"id" TEXT NOT NULL,
|
|
"userId" TEXT NOT NULL,
|
|
"credentialId" TEXT NOT NULL,
|
|
"publicKey" BYTEA NOT NULL,
|
|
"counter" INTEGER NOT NULL DEFAULT 0,
|
|
"transports" TEXT[] DEFAULT ARRAY[]::TEXT[],
|
|
"deviceType" TEXT,
|
|
"backedUp" BOOLEAN NOT NULL DEFAULT false,
|
|
"name" TEXT,
|
|
"lastUsedAt" TIMESTAMP(3),
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
|
|
CONSTRAINT "Credential_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "WebAuthnChallenge" (
|
|
"id" TEXT NOT NULL,
|
|
"challenge" TEXT NOT NULL,
|
|
"userId" TEXT,
|
|
"purpose" TEXT NOT NULL,
|
|
"expiresAt" TIMESTAMP(3) NOT NULL,
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
|
|
CONSTRAINT "WebAuthnChallenge_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "AppSettings" (
|
|
"id" TEXT NOT NULL DEFAULT 'singleton',
|
|
"mode" "AppMode" NOT NULL DEFAULT 'SINGLE_USER',
|
|
"allowRegistration" BOOLEAN NOT NULL DEFAULT false,
|
|
"rpName" TEXT NOT NULL DEFAULT 'Nebula',
|
|
"updatedAt" TIMESTAMP(3) NOT NULL,
|
|
|
|
CONSTRAINT "AppSettings_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateIndex
|
|
CREATE UNIQUE INDEX "User_username_key" ON "User"("username");
|
|
|
|
-- CreateIndex
|
|
CREATE UNIQUE INDEX "User_email_key" ON "User"("email");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "User_role_idx" ON "User"("role");
|
|
|
|
-- CreateIndex
|
|
CREATE UNIQUE INDEX "Session_tokenHash_key" ON "Session"("tokenHash");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "Session_userId_idx" ON "Session"("userId");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "Session_expiresAt_idx" ON "Session"("expiresAt");
|
|
|
|
-- CreateIndex
|
|
CREATE UNIQUE INDEX "Credential_credentialId_key" ON "Credential"("credentialId");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "Credential_userId_idx" ON "Credential"("userId");
|
|
|
|
-- CreateIndex
|
|
CREATE UNIQUE INDEX "WebAuthnChallenge_challenge_key" ON "WebAuthnChallenge"("challenge");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "WebAuthnChallenge_expiresAt_idx" ON "WebAuthnChallenge"("expiresAt");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "GitConnection_ownerId_idx" ON "GitConnection"("ownerId");
|
|
|
|
-- CreateIndex
|
|
CREATE UNIQUE INDEX "GitConnection_ownerId_name_key" ON "GitConnection"("ownerId", "name");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "Project_ownerId_idx" ON "Project"("ownerId");
|
|
|
|
-- CreateIndex
|
|
CREATE UNIQUE INDEX "Project_ownerId_slug_key" ON "Project"("ownerId", "slug");
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "Session" ADD CONSTRAINT "Session_userId_fkey" FOREIGN KEY ("userId") REFERENCES "User"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "Credential" ADD CONSTRAINT "Credential_userId_fkey" FOREIGN KEY ("userId") REFERENCES "User"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "GitConnection" ADD CONSTRAINT "GitConnection_ownerId_fkey" FOREIGN KEY ("ownerId") REFERENCES "User"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "Project" ADD CONSTRAINT "Project_ownerId_fkey" FOREIGN KEY ("ownerId") REFERENCES "User"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|