linumiq-invoice/prisma/schema.prisma

// This is your Prisma schema file,
// learn more about it in the docs: https://pris.ly/d/prisma-schema

generator client {
  provider = "prisma-client-js"
}

// Note that some adapters may set a maximum length for the String type by default, please ensure your strings are long
// enough when changing adapters.
// See https://www.prisma.io/docs/orm/reference/prisma-schema-reference#string for more information
datasource db {
  provider = "sqlite"
  url      = env("DATABASE_URL")
}

model Session {
  id            String    @id
  shop          String
  state         String
  isOnline      Boolean   @default(false)
  scope         String?
  expires       DateTime?
  accessToken   String
  userId        BigInt?
  firstName     String?
  lastName      String?
  email         String?
  accountOwner  Boolean   @default(false)
  locale        String?
  collaborator  Boolean?  @default(false)
  emailVerified Boolean?  @default(false)
  refreshToken        String?
  refreshTokenExpires DateTime?
}

// Per-shop issuer/configuration data. One row per installed shop.
model ShopSettings {
  id                String   @id @default(cuid())
  shopDomain        String   @unique

  // Issuer / company data
  companyName       String   @default("")
  legalForm         String   @default("")
  ownerName         String   @default("")
  addressLine1      String   @default("")
  addressLine2      String   @default("")
  postalCode        String   @default("")
  city              String   @default("")
  countryCode       String   @default("AT")
  phone             String   @default("")
  email             String   @default("")
  website           String   @default("")

  // Legal identifiers (Austria)
  vatId             String   @default("") // UID, e.g. ATU12345678
  taxNumber         String   @default("") // Steuernummer
  registrationNo    String   @default("") // FN (Firmenbuchnummer)
  registrationCourt String   @default("") // Firmenbuchgericht

  // Bank
  bankName          String   @default("")
  iban              String   @default("")
  bic               String   @default("")
  giroCodeEnabled   Boolean  @default(true)

  // Invoice numbering
  // shopify_order_number | prefix_sequential
  numberingMode     String   @default("shopify_order_number")
  invoicePrefix     String   @default("RE-")
  // Used only for prefix_sequential (the next number to issue minus 1)
  invoiceSeed       Int      @default(1000)

  // Defaults
  defaultLanguage   String   @default("de")
  paymentTermDays   Int      @default(14)
  // German footer note (also used as fallback for unspecified languages).
  footerNote        String   @default("")
  // English footer note (falls back to `footerNote` when empty).
  footerNoteEn      String   @default("")
  // Kleinunternehmer (§ 6 Abs. 1 Z 27 UStG)
  kleinunternehmer  Boolean  @default(false)

  // Logo (URL on Shopify Files or any reachable URL)
  logoUrl           String   @default("")

  // SMTP for email (used in later phase)
  smtpHost          String   @default("")
  smtpPort          Int      @default(587)
  smtpSecure        Boolean  @default(false)
  smtpUser          String   @default("")
  smtpPassword      String   @default("")
  smtpFromName      String   @default("")
  smtpFromEmail     String   @default("")
  smtpReplyTo       String   @default("")

  // Email templates (HTML, with {{var}} placeholders). Empty = use defaults.
  emailSubjectDe    String   @default("")
  emailBodyHtmlDe   String   @default("")
  emailSubjectEn    String   @default("")
  emailBodyHtmlEn   String   @default("")

  // Automations (webhook-driven, as a fallback to Shopify Flow which only
  // exposes custom-app actions on Plus stores).
  // 1) Wire-transfer order is placed → auto-email the invoice immediately.
  autoEmailOnWireTransferPlaced     Boolean @default(false)
  // 2) Order is fulfilled and is NOT a wire-transfer order → auto-email.
  autoEmailOnFulfilledNonWireTransfer Boolean @default(false)

  createdAt         DateTime @default(now())
  updatedAt         DateTime @updatedAt

  invoices          Invoice[]
}

// Generated invoice record. One row per invoice document (versions and stornos
// each create new rows; the latest is linked on the order via metafield).
model Invoice {
  id                String   @id @default(cuid())
  shopDomain        String
  settings          ShopSettings @relation(fields: [shopDomain], references: [shopDomain])

  // Shopify order references
  orderId           String   // gid://shopify/Order/...
  orderName         String   // e.g. "#1004"
  orderNumber       Int      // numeric order_number

  // Invoice identity
  invoiceNumber     String   // e.g. "RE-1004"
  language          String   @default("de")
  // invoice | storno
  kind              String   @default("invoice")
  // Increments per regeneration of the same invoiceNumber (always 1 for storno)
  version           Int      @default(1)

  // For storno rows: points to the cancelled Invoice.id
  cancelsInvoiceId  String?

  // PDF storage
  pdfFileGid        String   @default("") // gid://shopify/GenericFile/...
  pdfUrl            String   @default("")

  // Snapshots (JSON strings on sqlite)
  totalsJson        String   @default("{}")
  customerJson      String   @default("{}")

  // Lifecycle
  issuedAt          DateTime @default(now())
  sentAt            DateTime?
  cancelledAt       DateTime?

  status            String   @default("issued") // issued | sent | cancelled | failed
  lastError         String   @default("")

  createdAt         DateTime @default(now())
  updatedAt         DateTime @updatedAt

  @@index([shopDomain, orderId])
  @@index([shopDomain, invoiceNumber])
}

// Per-shop atomic counter for prefix_sequential numbering mode.
model InvoiceCounter {
  id          String   @id @default(cuid())
  shopDomain  String   @unique
  // Last issued numeric value; allocate new = lastValue + 1 atomically.
  lastValue   Int      @default(0)
  updatedAt   DateTime @updatedAt
}

// Email delivery log (used by later Flow send action; declared now so the
// generator can record manual sends as well).
model EmailLog {
  id          String   @id @default(cuid())
  shopDomain  String
  invoiceId   String
  toAddress   String
  subject     String
  status      String   // queued | sent | failed
  error       String   @default("")
  sentAt      DateTime @default(now())

  @@index([shopDomain, invoiceId])
}

// Idempotency table for inbound Shopify webhooks. See
// `app/services/webhooks/dedupe.server.ts` for details.
model ProcessedWebhook {
  webhookId   String   @id
  topic       String
  shopDomain  String
  // Reserve/commit lifecycle for at-least-once side-effect processing:
  //   "processing" — reserved, side-effect work in flight (acts as the lock)
  //   "done"       — work completed successfully; future retries are dropped
  // A "processing" row older than the stale lease (see dedupe.server.ts) is
  // treated as a crashed reservation and may be reclaimed. Existing rows
  // migrated from the old (record-before-work) design default to "done".
  status      String   @default("done")
  receivedAt  DateTime @default(now())

  @@index([shopDomain, topic])
}

// Per-shop logo bytes cache. Avoids fetching the logo from Shopify Files on
// every PDF render.
model LogoCache {
  id           String   @id @default(cuid())
  shopDomain   String   @unique
  sourceUrl    String
  bytes        Bytes
  contentType  String   @default("image/png")
  etag         String   @default("")
  fetchedAt    DateTime @default(now())
}