Schema

The Pocketbase relational database schema is located at pocketbase/schema.dbml

Updates

This doc page will probably get out of sync.
Check the current schema.dbml file in the repo for the up to date schema.
Last updated on: March 28, 2025

schema.dbml

// Use DBML to define your database structure
// To view, use "DBML Entity-Relationship Diagrams visualizer" extension on vscode
// Alternatively, you can use this website: https://dbdiagram.io/d

Table users {
    id integer [primary key]
    username text
    email email
    firstName text
    lastName text
    role select
}

Table members {
    id integer [primary key]
    user relation
    counselor relation
    homeAddress relation
    insuranceCard relation
    employer relation
    onBoardStep number
    agreeConsentForm boolean
    dateConsentForm datetime
    legalFirstName text
    legalMiddleName text
    legalLastName text
    sexAtBirth select
    phoneNumber text
    stripeCustomerID text
    subscriptionExpires datetime
    contactEmail email
    counselorNotes richtext
    dateOfBirth text
    bloodType text
    height text
    weight text
}

Table counselors {
    id integer [primary key]
    user relation
    contactEmail email
    firstName text
    lastName text
}

Table appointments {
    id integer [primary key]
    member relation
    counselor relation
    address relation
    title text
    startTime datetime
    endTime datetime
    timezone text
    status select
    details richtext
}

Table addresses {
    id integer [primary key]
    streetAddress text
    streetAddressLine2 text
    city text
    state text
    zipcode text
    country text
}

table employers {
    id integer [primary key]
    address relation
    name text
    legalName text
    phone text
    description text
    contactName text
    contactEmail text
    contactPhone text
    notes richtext
}

Table files {
    id integer [primary key]
    member relation
    file file
    title text
    size text
    archived boolean
    pinned boolean
}

Table insuranceCards {
    id integer [primary key]
    member relation
    groupNumber text
    policyNumber text
    policyHolder text
    insuranceProvider text
    planType text
    cardFront file
    cardBack file
    notes richtext
}

Table reports {
    id integer [primary key]
    member relation
    counselor relation
    title text
    content richtext
    file file
    read boolean
    type select
}

Table messages {
    id integer [primary key]
    sender relation
    member relation
    content richtext
    attachments file
    role select
    read boolean
}

Table tasks {
    id integer [primary key]
    member relation
    counselor relation
    title text
    completed boolean
    details richtext
    seen boolean
    hidden boolean
    status select
}

Ref: users.id - members.user
Ref: users.id - counselors.user
Ref: counselors.id < members.counselor
Ref: members.homeAddress > addresses.id
Ref: members.insuranceCard > insuranceCards.id
Ref: members.employer > employers.id
Ref: employers.address > addresses.id
Ref: files.member > members.id
Ref: reports.counselor > counselors.id
Ref: reports.member > members.id
Ref: tasks.counselor > counselors.id
Ref: tasks.member > members.id
Ref: appointments.counselor > counselors.id
Ref: appointments.member > members.id
Ref: appointments.address > addresses.id
Ref: insuranceCards.member > members.id
Ref: messages.sender > users.id
Ref: messages.member > members.id

Note that this .dbml file does nothing besides enable a viewable diagram of the schema.
It isn’t used for deploying, that is managed in Pocketbase.

---

User tables

Tables designed for interaction by members, and the permissions involved.

users

Users is the main authentication table, and holds minimal auth information on all users of the platform. It is treated differently in pocketbase to enable the auth stuff.
Users can have different roles, which are abstracted out to a direct relation with another user role table. The entries in the role table are forced to share the exact ID with the user, to make permissions simpler.

members

The main consumer role table is a member, which receive the health services. This table holds member specific data, as well as relations to all the various things a member can have.

counselor

Members are managed by a counselor. Counselors are permissioned to access info through the member entries they manage. Counselors act like admins for their members.

employers

Unused at the moment, but this is for enterprise clients to manage their members.

---

Data tables

Various datatypes that can be related to users, usually in a one-to-many relationship.

appointments

Members can have many health appointments for scheduling

addresses

Anytime an address is needed in the app, it is created as an entry in this table.

files

Members can have many file uploads.

reports

Members can have many reports. These can be health statuses, checkups or action plans.

tasks

Members can have many tasks either assigned to them, or to help their counselor perform actions on their behalf. Can be hidden from the member.

coverages

Members can have many insurance coverages, acting as digital insurance cards.