Patients Table: Full Schema and Field Reference

The patients table is the central entity of the FemeSalud schema. Every appointment, consultation, clinical note, and lab result is ultimately anchored to a patient row. In addition to standard demographic fields, the table stores rich clinical background as structured JSONB columns — covering family history, personal history, gynecological data, and obstetric data — so that the complete medical profile is co-located with the patient record rather than distributed across auxiliary tables.

Patient Status

A patient’s lifecycle in the system is tracked through the status field using the following enumerated string type:

export type PatientStatus = "activo" | "en_tratamiento" | "alta" | "nuevo";

Value	Meaning
`nuevo`	Newly registered; no completed consultation yet.
`activo`	Active patient currently receiving care.
`en_tratamiento`	Patient currently undergoing a treatment programme.
`alta`	Patient has been discharged.

Core Fields

uuid

required

Primary key. Auto-generated UUID assigned on insert.

full_name

text

required

Patient’s full legal name. Must be non-empty. Uniqueness is enforced at the application layer using a case-insensitive (ilike) check before insert or update to prevent duplicate registrations.

document_id

text

National identity document — cédula or passport number. Must be unique across all patient rows. A uniqueness check is performed at the application layer before every insert or update.

historia_number

text

Medical record number auto-generated by the system when a new patient is created. Provides a human-readable identifier for paper-based cross-referencing.

text

Patient’s email address. Nullable. Used for contact and notification purposes.

phone

text

Patient’s phone number. Nullable.

birth_date

date string

ISO 8601 date string (YYYY-MM-DD). Nullable. Used to calculate the patient’s age throughout the UI.

address

text

Patient’s residential address. Nullable.

birthplace

text

City or region where the patient was born. Nullable.

marital_status

text

Patient’s marital status (e.g. soltera, casada, divorciada, viuda, unión libre). Nullable free-text field.

education_level

text

Highest educational level attained. Nullable free-text field.

occupation

text

Patient’s occupation or profession. Nullable.

ethnicity

text

Patient’s self-reported ethnicity. Nullable.

status

text

required

Patient lifecycle status. Defaults to nuevo on insert. Accepted values: activo | en_tratamiento | alta | nuevo.

assigned_doctor_id

uuid

Foreign key to profiles.id. Identifies the responsible physician. Drives Row Level Security — doctors can only read patients where this value matches their own user ID.

notes

text

General free-text notes not tied to a specific consultation. Nullable.

consultation_reason

text

Primary reason the patient first sought care. Nullable.

current_illness

text

Narrative description of the patient’s current illness at the time of registration. Nullable.

first_visit_date

date string

ISO 8601 date string (YYYY-MM-DD) recording when the patient first attended the clinic. Nullable.

JSONB History Fields

family_history

JSONB

Structured object capturing hereditary conditions reported for immediate relatives. All sub-fields are nullable strings.

{
  mother:   string | null;  // Conditions reported for mother
  father:   string | null;  // Conditions reported for father
  siblings: string | null;  // Conditions reported for siblings
  children: string | null;  // Conditions reported for children
}

personal_history

JSONB

Structured object for the patient’s personal background. All sub-fields are nullable strings.

{
  alcohol:        string | null;  // Alcohol consumption history
  drugs:          string | null;  // Drug use history
  tobacco:        string | null;  // Tobacco use history
  base_pathology: string | null;  // Underlying chronic conditions
  surgical:       string | null;  // Previous surgical procedures
  allergies:      string | null;  // Known allergies
}

gynecological_data

JSONB

Structured gynecological history. Numeric values such as menarche may be stored as either a number or a string depending on the form input.

{
  menarche:         number | string | null;  // Age at first menstruation
  sexarche:         number | string | null;  // Age at first sexual intercourse
  menstrual_cycle:  string | null;           // Cycle characteristics
  dysmenorrhea:     string | null;           // Pain during menstruation
  nps:              number | string | null;  // Number of sexual partners
  its:              string | null;           // STI history
  cytology:         string | null;           // Last Pap smear result / date
  contraceptives:   string | null;           // Current or past contraceptive use
}

obstetric_data

JSONB

Structured obstetric formula and pregnancy-related data. All numeric sub-fields may be stored as number or string.

{
  g:                number | string | null;  // Gestaciones (total pregnancies)
  p:                number | string | null;  // Partos (vaginal deliveries)
  c:                number | string | null;  // Cesáreas (cesarean sections)
  a:                number | string | null;  // Abortos (miscarriages / terminations)
  pig:              string | null;           // Previous intrauterine growth issues
  em:               number | string | null;  // Ectopic / molar pregnancies
  ee:               number | string | null;  // Stillbirths (embarazo ectópico)
  complications:    string | null;           // Obstetric complications narrative
  fum:              string | null;           // Fecha de última menstruación (LMP)
  eg:               string | null;           // Edad gestacional (gestational age)
  fpp:              string | null;           // Fecha probable de parto (EDD)
  num_consultations: number | string | null; // Number of prenatal visits
  vaccines:         string | null;           // Vaccination status
}

Audit Fields

created_at

timestamptz

required

ISO 8601 timestamp with time zone. Set automatically by the database on insert.

updated_at

timestamptz

required

ISO 8601 timestamp with time zone. Updated automatically on every row modification.

created_by

uuid

Foreign key to auth.users.id. Records which authenticated user created the patient record. Nullable — may be null for records seeded outside the application.

Uniqueness Constraints

Field	Enforcement level	Behaviour on conflict
`id`	Database (PK)	Insert rejected by PostgreSQL.
`document_id`	Application layer	`useCreatePatient` and `useUpdatePatient` perform an `eq` query before writing; an error is thrown to the caller if a match is found.
`full_name`	Application layer	Checked case-insensitively (`ilike`) before insert or update; an error is thrown on conflict.

document_id uniqueness is enforced at the application layer rather than with a database UNIQUE constraint because the field is nullable — some patients may not have a document number recorded at registration.

Database Schema

PDF Exports

API Hooks

Patients Table: Full Schema and Field Reference

Patient Status

Core Fields

JSONB History Fields

Audit Fields

Uniqueness Constraints

Build docs developers (and LLMs) love

Database Schema

PDF Exports

API Hooks

Documentation Index

​Patient Status

​Core Fields

​JSONB History Fields

​Audit Fields

​Uniqueness Constraints

Build docs developers (and LLMs) love

Patient Status

Core Fields

JSONB History Fields

Audit Fields

Uniqueness Constraints