Database schema

OpenDDE uses Supabase (PostgreSQL) for persistent storage. The schema tracks explored targets, their pockets, known ligands, and complex predictions.

Core tables

targets

CREATE TABLE targets (
  uniprot_id    TEXT PRIMARY KEY,
  name          TEXT NOT NULL,
  gene_name     TEXT,
  organism      TEXT NOT NULL,
  length        INTEGER NOT NULL,
  sequence      TEXT,
  structure_file TEXT,
  created_at    TIMESTAMPTZ DEFAULT now()
);

pockets

CREATE TABLE pockets (
  id            SERIAL PRIMARY KEY,
  uniprot_id    TEXT REFERENCES targets(uniprot_id),
  rank          INTEGER NOT NULL,
  score         FLOAT NOT NULL,
  residues      JSONB NOT NULL,
  center_x      FLOAT,
  center_y      FLOAT,
  center_z      FLOAT,
  UNIQUE(uniprot_id, rank)
);

ligands

CREATE TABLE ligands (
  id              SERIAL PRIMARY KEY,
  chembl_id       TEXT NOT NULL,
  uniprot_id      TEXT REFERENCES targets(uniprot_id),
  name            TEXT,
  smiles          TEXT,
  activity_type   TEXT,
  activity_value_nm FLOAT,
  phase           INTEGER,
  UNIQUE(chembl_id, uniprot_id)
);

predictions

CREATE TABLE predictions (
  id              TEXT PRIMARY KEY,
  uniprot_id      TEXT REFERENCES targets(uniprot_id),
  ligand_chembl_id TEXT,
  ligand_smiles   TEXT,
  status          TEXT DEFAULT 'pending',
  structure_file  TEXT,
  confidence      JSONB,
  created_at      TIMESTAMPTZ DEFAULT now()
);

Relationships

• A target has many pockets (1:N)
• A target has many ligands (1:N)
• A target has many predictions (1:N)

Next: API reference →