database.dbml

// Use DBML to define your database structure
// Docs: https://dbml.dbdiagram.io/docs

Table Set {
  id      varchar(36) [primary key, unique, note: "uuid"]
  created DateTime
  changed DateTime
}

Table Card {
  hash    varchar(64) [primary key, unique, note: "sha256 of setId + card index in hex"]
  created DateTime
  set     varchar(36) [ref: > Set.id, null]
}

Table CardVersion {
  id                varchar(36) [primary key, unique, note: "uuid"]
  card              varchar(64) [ref: > Card.hash]
  created           DateTime
  lnurlP            varchar(36) [ref: - LnurlP.lnbitsId, null, default: null]
  lnurlW            varchar(36) [ref: > LnurlW.lnbitsId, null, default: null]
  textForWithdraw   text
  noteForStatusPage text
  sharedFunding     boolean
  landingPageViewed DateTime    [null, default: null]
}

Table Invoice {
  amount         integer
  feeAmount      integer     [default: 0]
  paymentHash    varchar(64) [primary key, unique, note: "sha256 of payment preimage in hex"]
  paymentRequest text
  created        DateTime
  paid           DateTime    [null, default: null]
  expiresAt      DateTime
  extra          text        [note: "additional info used in lnbits, stores info like lnurlp id that created the invoice"]
}

Table CardVersionHasInvoice {
  cardVersion varchar(36) [ref: > CardVersion.id]
  invoice     varchar(64) [ref: > Invoice.paymentHash]

  indexes {
    (cardVersion, invoice) [pk, unique]
  }
}

Table LnurlP {
  lnbitsId  varchar(36)   [primary key, unique, note: "id from lnbits lnurlp link"]
  created   DateTime
  expiresAt DateTime      [null, default: null]
  finished  DateTime      [null, default: null, note: "for shared funding the user has to manually trigger the finishing, for single funding this is set as soon as the first payment is registered"]
}

Table LnurlW {
  lnbitsId       varchar(36) [primary key, unique, note: "id from lnbits lnurlw link"]
  created        DateTime
  expiresAt      DateTime    [null, default: null]
  withdrawn      DateTime    [null, default: null]
  bulkWithdrawId varchar(64) [unique, null, default: null, note: "deprecated, but needs application rework, as the application backend currently creates a bulkWithdrawId and returns it to the frontend"]
}

Table SetSettings {
  set           varchar(36) [primary key, unique, ref: - Set.id]
  name          text
  numberOfCards integer
  cardHeadline  text
  cardCopytext  text
  image         varchar(36) [ref: > Image.id, null]
  landingPage   varchar(36) [ref: > LandingPage.id]
}

enum ImageType {
  svg
  png
}

Table Image {
  id   varchar(36) [primary key, unique, note: "uuid"]
  type ImageType
  name text        [note: "display name for UI"]
  data text        [note: "image data (e.g. plaintext for svg, base64 encoded for png)"]
}

enum LandingPageType {
  core     [note: "landing page is integrated into tip cards core, maybe there will be multiple version to choose from in the future"]
  external [note: "user will be redirected to another page when scanning a funded card"]
}

Table LandingPage {
  id   varchar(36)      [primary key, unique, note: "uuid"]
  type LandingPageType
  name text             [note: "display name for UI"]
  url  text             [null, default: null, note: "used+required for type external"]
}

enum Permission {
  statistics [note: "allow read access to https://tipcards.io/statistics"]
  support    [note: "allow access to the support dashboard"]
}

Table User {
  id           varchar(64)  [primary key, unique, note: "sha256 of foreign id or uuid"]
  lnurlAuthKey varchar(128) [note: "signed sha256 generated by lightning wallet"]
  created      DateTime
  permissions  json         [note: "make array if database supports it: permissions[], otherwise json"]
}

Table UserCanUseSet {
  user    varchar(64) [ref: > User.id]
  set     varchar(36) [ref: > Set.id]
  canEdit boolean     [default: false]

  indexes {
    (user, set) [pk, unique]
  }
}

Table UserCanUseImage {
  user    varchar(64) [ref: > User.id]
  image   varchar(36) [ref: > Image.id]
  canEdit boolean     [default: false]

  indexes {
    (user, image) [pk, unique]
  }
}

Table UserCanUseLandingPage {
  user        varchar(64) [ref: > User.id]
  landingPage varchar(36) [ref: > LandingPage.id]
  canEdit     boolean     [default: false]

  indexes {
    (user, landingPage) [pk, unique]
  }
}

Table AllowedSession [note: "Every successful login creates a new session for the user"] {
  user     varchar(64) [ref: > User.id]
  sessionId  varchar(36) [primary key, unique, note: "random UUID"]

  indexes {
    sessionId [name: "sessionIdIndex"]
  }
}

Table Profile {
  user        varchar(64) [primary key, unique, ref: - User.id]
  accountName text        [note: "for support and for the user if he has more than one account"]
  displayName text        [note: "for future features where the name might be displayed"]
  email       text        [note: "email is used for account recovery only. it's not even validated as it belongs to the user"]
}