nsecbunkerd/prisma/schema.prisma

generator client {
  provider = "prisma-client-js"
  binaryTargets = ["native", "linux-musl-openssl-3.0.x", "linux-musl-arm64-openssl-3.0.x"]
}

datasource db {
  provider = "sqlite"
  url      = env("DATABASE_URL")
}

model Request {
    id            String   @id @default(uuid())
    keyName       String?
    createdAt     DateTime  @default(now())
    requestId     String
    remotePubkey  String
    method        String
    params        String?
    allowed       Boolean?
    // Bind each request to the KeyUser it was evaluated against so usage
    // caps can be derived live by COUNTing allowed Requests, instead of
    // maintaining a mutable PolicyRule.currentUsageCount that drifts.
    // See aiolabs/nsecbunkerd#25 (Option D, derive-don't-count).
    keyUserId     Int?
    KeyUser       KeyUser? @relation(fields: [keyUserId], references: [id])

    @@index([keyUserId, method])
}

model KeyUser {
  id                Int                @id @default(autoincrement())
  keyName           String
  userPubkey        String
  createdAt         DateTime           @default(now())
  updatedAt         DateTime           @default(now()) @updatedAt
  revokedAt         DateTime?
  lastUsedAt        DateTime?
  description       String?
  logs              Log[]
  signingConditions SigningCondition[]
  Token             Token[]
  requests          Request[]
  signingLogs       SigningLog[]

  @@unique([keyName, userPubkey], name: "unique_key_user")
}

model Key {
  id        Int      @id @default(autoincrement())
  keyName   String    @unique
  createdAt DateTime @default(now())
  updatedAt DateTime @default(now()) @updatedAt
  deletedAt DateTime?
  pubkey    String
}

model User {
  id        Int      @id @default(autoincrement())
  username  String   @unique
  domain    String
  password  String
  email     String
  createdAt DateTime @default(now())
  updatedAt DateTime @default(now()) @updatedAt
  deletedAt DateTime?
  pubkey    String
}

// The SigningCondition layer is the MANUAL-OVERRIDE source of truth
// (web-approval / add_signing_condition / create_account bootstrap) — it is
// no longer materialized from token policies (see aiolabs/nsecbunkerd#25:
// applyToken stopped photocopying; token grants are evaluated live off
// Token -> Policy -> PolicyRule). Under D1 the override layer carries its
// own lifecycle so it runs through the same grantIsLive(now) predicate as
// token grants.
model SigningCondition {
  id             Int       @id @default(autoincrement())
  method         String?
  kind           String?
  content        String?
  keyUserKeyName String?
  allowed        Boolean?
  keyUserId      Int?
  createdAt      DateTime  @default(now())
  expiresAt      DateTime?
  revokedAt      DateTime?
  KeyUser        KeyUser?  @relation(fields: [keyUserId], references: [id])
}

model Log {
  id        Int      @id @default(autoincrement())
  timestamp DateTime
  type      String
  method    String?
  params    String?
  keyUserId Int?
  KeyUser   KeyUser? @relation(fields: [keyUserId], references: [id])
}

model Policy {
  id          Int          @id @default(autoincrement())
  name        String
  createdAt   DateTime     @default(now())
  updatedAt   DateTime     @default(now()) @updatedAt
  expiresAt   DateTime?
  deletedAt   DateTime?
  description String?
  rules       PolicyRule[]
  Token       Token[]
}

model PolicyRule {
  id            Int     @id @default(autoincrement())
  method        String
  kind          String?
  // Usage cap (aiolabs/nsecbunkerd#28): allow at most `maxUsageCount`
  // signings of this (method, kind) per rolling `windowSeconds`, counted
  // live from SigningLog. `maxUsageCount` NULL = uncapped; `windowSeconds`
  // NULL = lifetime window (count all-time). Replaces the never-enforced
  // mutable `currentUsageCount` (derive-don't-count). Multiple capped rules
  // for one request all bind (stacked caps, e.g. 20/hr AND 200/day).
  maxUsageCount Int?
  windowSeconds Int?
  policyId      Int?
  Policy        Policy? @relation(fields: [policyId], references: [id])
}

// Durable, append-only record of ALLOWED signings — the source of truth
// usage caps count against (aiolabs/nsecbunkerd#28). One row per allowed
// consequential request (sign_event + encrypt/decrypt; connect/ping/
// get_public_key are never recorded). Retention/pruning is a follow-up.
model SigningLog {
  id        Int      @id @default(autoincrement())
  keyUserId Int
  method    String
  kind      String?
  createdAt DateTime @default(now())
  KeyUser   KeyUser  @relation(fields: [keyUserId], references: [id])

  @@index([keyUserId, method, createdAt])
}

model Token {
  id         Int       @id @default(autoincrement())
  keyName    String
  token      String    @unique
  clientName String
  createdBy  String
  createdAt  DateTime  @default(now())
  updatedAt  DateTime  @default(now()) @updatedAt
  deletedAt  DateTime?
  expiresAt  DateTime?
  redeemedAt DateTime?
  revokedAt  DateTime?
  keyUserId  Int?
  policyId   Int?
  policy     Policy?   @relation(fields: [policyId], references: [id])
  KeyUser    KeyUser?  @relation(fields: [keyUserId], references: [id])
}