Database Schema

Overview

upLegal uses PostgreSQL via Supabase with Row Level Security (RLS) for access control. The database is organized into core tables for users, appointments, payments, messaging, and reviews.

Core Tables

profiles

Stores user account information for both clients and lawyers.

user_id

uuid

required

Primary key, references Supabase Auth user

string

required

User’s email address

role

string

required

User role: 'client' or 'lawyer'

first_name

string

User’s first name

last_name

string

User’s last name

rut

string

Chilean RUT (tax ID) for lawyers

pjud_verified

boolean

default:"false"

Whether lawyer is verified through PJUD

avatar_url

string

Profile picture URL from Supabase Storage

bio

text

Lawyer bio/description

specialties

string[]

Array of legal specialties (e.g., ["Derecho Civil", "Derecho Penal"])

hourly_rate_clp

number

Lawyer’s hourly rate in Chilean pesos

contact_fee_clp

number

One-time fee to initiate conversation

location

string

Lawyer’s city/region

availability

jsonb

Weekly availability grid (JSON object)

mercadopago_account_id

string

MercadoPago account ID for payouts

balance

number

default:"0"

Current balance from completed appointments

profile_completion

number

default:"0"

Profile completion percentage (0-100)

created_at

timestamp

Account creation timestamp

updated_at

timestamp

Last profile update timestamp

appointments

Scheduled consultations between clients and lawyers.

uuid

required

Primary key

user_id

uuid

required

Client user ID

lawyer_id

uuid

required

Lawyer user ID

appointment_date

date

required

Scheduled date (YYYY-MM-DD)

appointment_time

time

required

Scheduled time (HH:MM)

duration

number

required

Duration in minutes (30, 60, 90, or 120)

consultation_type

string

required

Type: 'video', 'phone', or 'in-person'

contact_method

string

required

Preferred contact: 'google_meet', 'phone', 'address'

status

string

default:"pending"

Status: 'pending', 'confirmed', 'completed', 'cancelled'

price

number

required

Total price in CLP

amount

number

Lawyer’s payout amount (80% of price)

currency

string

default:"CLP"

Currency code

meet_link

string

Google Meet link for video consultations

name

string

required

Client name

string

required

Client email

phone

string

Client phone number

address

string

Meeting address for in-person consultations

description

text

Consultation description/notes

notes

text

Lawyer’s private notes

bookings

Payment-linked booking records created via MercadoPago.

uuid

required

Primary key

user_id

uuid

Client user ID (nullable for guest bookings)

lawyer_id

uuid

required

Lawyer user ID, foreign key to profiles.user_id

user_name

string

required

Client name

user_email

string

required

Client email

scheduled_date

date

required

Appointment date

scheduled_time

time

required

Appointment time

duration

number

required

Duration in minutes

price

number

required

Total price paid

status

string

default:"pending"

Booking status: 'pending', 'confirmed', 'cancelled'

payment_status

string

MercadoPago payment status: 'approved', 'pending', 'rejected'

payment_id

string

MercadoPago payment ID

mercadopago_preference_id

string

MercadoPago payment preference ID

confirmed_at

timestamp

Timestamp when booking was confirmed

cancelled_at

timestamp

Timestamp when booking was cancelled

messages

Real-time chat messages between clients and lawyers.

uuid

required

Primary key

conversation_id

uuid

required

Foreign key to conversations.id

sender_id

uuid

required

User ID of message sender

content

text

required

Message text content

status

string

default:"sent"

Message status: 'sending', 'sent', 'delivered', 'read'

attachment_url

string

URL to file attachment (if any)

created_at

timestamp

Message timestamp

read_at

timestamp

Timestamp when message was read

conversations

Message threads between clients and lawyers.

uuid

required

Primary key

client_id

uuid

required

Client user ID

lawyer_id

uuid

required

Lawyer user ID

contact_fee_paid

boolean

default:"false"

Whether client has paid contact fee to initiate conversation

last_message_at

timestamp

Timestamp of most recent message

created_at

timestamp

Conversation creation timestamp

reviews

Client reviews and ratings for lawyers.

uuid

required

Primary key

lawyer_id

uuid

required

Lawyer being reviewed

client_id

uuid

required

Client who wrote the review

appointment_id

uuid

Related appointment (if applicable)

rating

number

required

Star rating (1-5)

comment

text

required

Written review (max 1,000 characters)

status

string

default:"pending"

Review status: 'pending', 'approved', 'rejected'

helpful_count

number

default:"0"

Number of “helpful” votes

created_at

timestamp

Review creation timestamp

payments

Payment transaction records.

uuid

required

Primary key

user_id

uuid

required

Client user ID

lawyer_id

uuid

required

Lawyer user ID

amount

number

required

Payment amount in CLP

platform_fee

number

required

Platform fee (10% of amount)

lawyer_amount

number

required

Lawyer payout amount (90% of amount)

payment_type

string

required

Type: 'appointment', 'contact_fee', 'consultation'

status

string

default:"pending"

Status: 'pending', 'approved', 'rejected', 'refunded'

mercadopago_payment_id

string

MercadoPago payment ID

created_at

timestamp

Payment timestamp

notifications

In-app notification queue.

uuid

required

Primary key

user_id

uuid

required

Recipient user ID

type

string

required

Notification type: 'appointment', 'message', 'payment', 'system'

title

string

required

Notification title

message

text

required

Notification content

read

boolean

default:"false"

Whether notification has been read

action_url

string

URL to navigate to when clicked

created_at

timestamp

Notification timestamp

Table Relationships

profiles (user_id)
  ├─── appointments.user_id (client)
  ├─── appointments.lawyer_id (lawyer)
  ├─── bookings.user_id (client)
  ├─── bookings.lawyer_id (lawyer) [FK]
  ├─── conversations.client_id
  ├─── conversations.lawyer_id
  ├─── messages.sender_id
  ├─── reviews.lawyer_id
  ├─── reviews.client_id
  ├─── payments.user_id
  ├─── payments.lawyer_id
  └─── notifications.user_id

conversations (id)
  └─── messages.conversation_id [FK]

appointments (id)
  └─── reviews.appointment_id

Row Level Security (RLS)

All tables have RLS policies enabled:

profiles

Users can read all public lawyer profiles
Users can update only their own profile
Service role can perform all operations

appointments

Clients can read/update their own appointments
Lawyers can read/update appointments where they are the lawyer
Service role can perform all operations

messages

Users can only read messages in conversations they participate in
Users can only create messages in their own conversations
Service role can perform all operations

reviews

All users can read approved reviews
Clients can create reviews for completed appointments
Lawyers cannot edit or delete reviews
Service role can moderate reviews

payments

Clients can read their own payment history
Lawyers can read payments where they are the lawyer
No client/lawyer write access (server-side only)
Service role can perform all operations

Indexes

Key indexes for query performance:

profiles.email - Unique index
profiles.role - Filter by user type
profiles.pjud_verified - Filter verified lawyers
appointments.lawyer_id - Lawyer’s appointments
appointments.user_id - Client’s appointments
appointments.appointment_date - Date-based queries
messages.conversation_id - Conversation messages
messages.created_at - Message ordering
reviews.lawyer_id - Lawyer reviews
bookings.payment_id - Payment reconciliation

Database Functions

Custom PostgreSQL functions:

`get_lawyer_earnings(lawyer_id UUID)`

Calculates total earnings for a lawyer from completed appointments.

SELECT get_lawyer_earnings('uuid-here');
-- Returns: { total: 450000, pending: 0, paid: 450000 }

Migrations

Database schema is versioned using Supabase migrations in /supabase/migrations/:

Initial schema setup
Profile table with RLS policies
Payment tables and earnings functions
Storage buckets for avatars
Appointment and booking tables

Next Steps

API Reference

Explore API endpoints for database operations

Supabase Integration

Learn how to query the database with Supabase client

Architecture

Understand the overall system architecture

Local Setup

Set up the database for local development

Architecture

Development

Integrations

API Reference

Database Schema

Overview

Core Tables

profiles

appointments

bookings

messages

conversations

reviews

payments

notifications

Table Relationships

Row Level Security (RLS)

Indexes

Database Functions

`get_lawyer_earnings(lawyer_id UUID)`

Migrations

Next Steps

API Reference

Supabase Integration

Architecture

Local Setup

Build docs developers (and LLMs) love

Architecture

Development

Integrations

API Reference

​Overview

​Core Tables

​profiles

​appointments

​bookings

​messages

​conversations

​reviews

​payments

​notifications

​Table Relationships

​Row Level Security (RLS)

​Indexes

​Database Functions

​get_lawyer_earnings(lawyer_id UUID)

​Migrations

​Next Steps

API Reference

Supabase Integration

Architecture

Local Setup

Build docs developers (and LLMs) love

Overview

Core Tables

profiles

appointments

bookings

messages

conversations

reviews

payments

notifications

Table Relationships

Row Level Security (RLS)

Indexes

Database Functions

`get_lawyer_earnings(lawyer_id UUID)`

Migrations

Next Steps