schema_postgres.sql

CREATE TABLE IF NOT EXISTS app_user (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    preferred_language TEXT NOT NULL,
    role TEXT NOT NULL CHECK (role IN ('patient', 'provider'))
);

CREATE TABLE IF NOT EXISTS session (
    id SERIAL PRIMARY KEY,
    user_id INTEGER NOT NULL REFERENCES app_user (id),
    start_time TEXT NOT NULL,
    end_time TEXT
);

CREATE TABLE IF NOT EXISTS interaction (
    id SERIAL PRIMARY KEY,
    user_id INTEGER NOT NULL REFERENCES app_user (id),
    input_text TEXT,
    detected_language TEXT,
    translated_text TEXT,
    nlu_summary TEXT,
    provider_response TEXT,
    translated_response TEXT,
    timestamp TEXT NOT NULL
);