search

knowii-oss / knowii

https://knowii.net
GNU Affero General Public License v3.0
5 stars 1 forks source link

Create DB model #661

Open dsebastien opened 1 week ago

dsebastien commented 1 week ago
dsebastien commented 1 week ago

Old prisma schema:

model Users {
  /// Internal user id (referenced in other tables)
  id                    String         @id @db.Uuid @default(dbgenerated("gen_random_uuid()")) /// @zod.string.uuid()
  /// Same id as the id in auth.users (ie Supabase user id)
  /// Can be null because users can delete their account but we keep the entry anyway
  user_id_external      String?        @db.Uuid @unique /// @zod.string.uuid()
  /// WARNING: The Zod validation rules MUST respect the same length rules as the UI & back-end (cfr constants-more.ts)
  username              String         @unique /// @zod.string.min(3).max(36)
  email                 String         @unique /// @zod.string.email()
  user_role             KnowiiUserRole @default(USER)
  created_at            DateTime       @db.Timestamptz(6) @default(dbgenerated("timezone('utc'::text, now())"))
  updated_at            DateTime       @db.Timestamptz(6) @default(dbgenerated("timezone('utc'::text, now())")) @updatedAt
  admin_of              Communities[]  @relation("community_admins")
  member_of             Communities[]  @relation("community_members")
  owner_of              Communities[]  @relation("community_owners")
  user_profile          UserProfiles?  @relation("user_profile_user")
  user_profile_external UserProfiles?  @relation("user_profile_external_user")

  @@index([username], map: "idx_user_username")
  @@index([email], map: "idx_user_email")
  @@schema("public")
  @@map("users")
}

model UserProfiles {
  id                String   @id @db.Uuid @default(dbgenerated("gen_random_uuid()")) /// @zod.string.uuid()
  /// Internal user id
  user_id           String   @db.Uuid @unique /// @zod.string.uuid()
  /// Supabase user id
  user_id_external  String?  @db.Uuid @unique /// @zod.string.uuid()
  name              String   @default("") /// @zod.string
  avatar_url        String   @default("") /// @zod.string.url()
  phone             String   @default("") /// @zod.string
  website           String   @default("") /// @zod.string.url()
  twitter           String   @default("") /// @zod.string.url()
  facebook          String   @default("") /// @zod.string.url()
  instagram         String   @default("") /// @zod.string.url()
  tiktok            String   @default("") /// @zod.string.url()
  github            String   @default("") /// @zod.string.url()
  bio               String   @default("") /// @zod.string
  location          String   @default("") /// @zod.string
  organization_name String   @default("") /// @zod.string
  organization_link String   @default("") /// @zod.string.url()
  /// WARNING: When adding/updating/removing fields here, make sure to update the triggers in supabase-db-seed.sql
  created_at        DateTime @db.Timestamptz(6) @default(dbgenerated("timezone('utc'::text, now())"))
  updated_at        DateTime @db.Timestamptz(6) @default(dbgenerated("timezone('utc'::text, now())")) @updatedAt
  user              Users?   @relation("user_profile_user", fields: [user_id], references: [id])
  user_external     Users?   @relation("user_profile_external_user", fields: [user_id_external], references: [user_id_external])

  @@index([name], map: "idx_user_profile_name")
  @@schema("public")
  @@map("user_profiles")
}

model Communities {
  id                   String                    @id @db.Uuid @default(dbgenerated("gen_random_uuid()")) /// @zod.string.uuid()
  /// WARNING: The Zod validation rules MUST respect the same length rules as the UI & back-end (cfr constants-more.ts)
  name                 String                    @unique /// @zod.string.min(3).max(64)
  slug                 String                    @unique /// @zod.string.min(3).max(64).regex(/^[a-z0-9-]+$/igm)
  description          String                    @default("") /// @zod.string
  visibility           KnowiiCommunityVisibility @default(PUBLIC)
  created_at           DateTime                  @db.Timestamptz(6) @default(dbgenerated("timezone('utc'::text, now())"))
  updated_at           DateTime                  @db.Timestamptz(6) @default(dbgenerated("timezone('utc'::text, now())")) @updatedAt
  admins               Users[]                   @relation("community_admins")
  members              Users[]                   @relation("community_members")
  owners               Users[]                   @relation("community_owners")
  resource_collections ResourceCollections[]     @relation("community_resource_collections")
  resources            Resources[]               @relation("community_resources")
  tags                 Tags[]                    @relation("community_tags")

  @@index([name], map: "idx_community_name")
  @@schema("public")
  @@map("communities")
}

model ResourceCollections {
  id           String      @id @db.Uuid @default(dbgenerated("gen_random_uuid()")) /// @zod.string.uuid()
  name         String      @default("") /// @zod.string
  slug         String      @unique /// @zod.string.min(3).max(64).regex(/^[a-z0-9-]+$/igm)
  description  String      @default("") /// @zod.string
  community_id String      @db.Uuid /// @zod.string.uuid()
  created_at   DateTime    @db.Timestamptz(6) @default(dbgenerated("timezone('utc'::text, now())"))
  updated_at   DateTime    @db.Timestamptz(6) @default(dbgenerated("timezone('utc'::text, now())")) @updatedAt
  community    Communities @relation("community_resource_collections", fields: [community_id], references: [id])
  resources    Resources[] @relation("resource_collection_resources")

  @@index([name], map: "idx_resource_collection_name")
  @@schema("public")
  @@map("resource_collections")
}

model Resources {
  id                     String              @id @db.Uuid @default(dbgenerated("gen_random_uuid()")) /// @zod.string.uuid()
  name                   String              @default("") /// @zod.string
  description            String              @default("") /// @zod.string
  content                String              @default("") /// @zod.string
  source                 String              @default("") /// @zod.string.url()
  slug                   String              @unique /// @zod.string.min(3).max(64).regex(/^[a-z0-9-]+$/igm)
  up_votes               Int                 @default(0) /// @zod.number
  down_votes             Int                 @default(0) /// @zod.number
  community_id           String              @db.Uuid /// @zod.string.uuid()
  resource_collection_id String              @db.Uuid /// @zod.string.uuid()
  created_at             DateTime            @db.Timestamptz(6) @default(dbgenerated("timezone('utc'::text, now())"))
  updated_at             DateTime            @db.Timestamptz(6) @default(dbgenerated("timezone('utc'::text, now())")) @updatedAt
  resource_collection    ResourceCollections @relation("resource_collection_resources", fields: [resource_collection_id], references: [id])
  community              Communities         @relation("community_resources", fields: [community_id], references: [id])
  tags                   Tags[]              @relation("resource_tags")

  @@index([slug], map: "idx_resource_slug")
  @@schema("public")
  @@map("resources")
}

model Tags {
  id           String       @id @db.Uuid @default(dbgenerated("gen_random_uuid()")) /// @zod.string.uuid()
  name         String       @unique /// @zod.string
  community_id String       @db.Uuid /// @zod.string.uuid()
  community    Communities? @relation("community_tags", fields: [community_id], references: [id])
  created_at   DateTime     @db.Timestamptz(6) @default(dbgenerated("timezone('utc'::text, now())"))
  updated_at   DateTime     @db.Timestamptz(6) @default(dbgenerated("timezone('utc'::text, now())")) @updatedAt
  resources    Resources[]  @relation("resource_tags")

  @@index([name], map: "idx_tag_name")
  @@schema("public")
  @@map("tags")
}

enum KnowiiUserRole {
  USER
  ADMIN

  @@map("user_role")
  @@schema("public")
}

enum KnowiiCommunityVisibility {
  PUBLIC
  PRIVATE

  @@map("community_visibility")
  @@schema("public")
}