SNOMED CT Guide

-- SNOMED CT Concepts
CREATE TABLE snomed_concept (
  id                  BIGINT PRIMARY KEY,              -- SCTID (e.g., 44054006)
  effective_time      DATE NOT NULL,
  active              BOOLEAN NOT NULL DEFAULT TRUE,
  module_id           BIGINT,                          -- Module (e.g., US Extension)
  definition_status   BIGINT,                          -- Primitive vs Fully Defined
  CONSTRAINT chk_def_status CHECK (definition_status IN (900000000000074008, 900000000000073002))
);

CREATE INDEX idx_snomed_concept_active ON snomed_concept(active);

-- SNOMED CT Descriptions (names/synonyms)
CREATE TABLE snomed_description (
  id                  BIGINT PRIMARY KEY,
  effective_time      DATE NOT NULL,
  active              BOOLEAN NOT NULL DEFAULT TRUE,
  concept_id          BIGINT NOT NULL REFERENCES snomed_concept(id),
  language_code       CHAR(2) DEFAULT 'en',
  type_id             BIGINT NOT NULL,                 -- FSN: 900000000000003001, Synonym: 900000000000013009
  term                TEXT NOT NULL,
  case_significance   BIGINT
);

CREATE INDEX idx_desc_concept ON snomed_description(concept_id, active);
CREATE INDEX idx_desc_term ON snomed_description USING gin(to_tsvector('english', term));

-- SNOMED CT Relationships (logical links between concepts)
CREATE TABLE snomed_relationship (
  id                  BIGINT PRIMARY KEY,
  effective_time      DATE NOT NULL,
  active              BOOLEAN NOT NULL DEFAULT TRUE,
  source_id           BIGINT NOT NULL REFERENCES snomed_concept(id),
  destination_id      BIGINT NOT NULL REFERENCES snomed_concept(id),
  relationship_group  INTEGER DEFAULT 0,
  type_id             BIGINT NOT NULL,                 -- Is a: 116680003, Finding site: 363698007, etc.
  characteristic_type BIGINT,
  modifier            BIGINT
);

CREATE INDEX idx_rel_source ON snomed_relationship(source_id, type_id, active);
CREATE INDEX idx_rel_dest ON snomed_relationship(destination_id, type_id, active);

-- SNOMED to ICD-10 Map (SNOMED refsets)
CREATE TABLE snomed_icd10_map (
  id                  BIGINT PRIMARY KEY,
  effective_time      DATE NOT NULL,
  active              BOOLEAN DEFAULT TRUE,
  refset_id           BIGINT,
  referenced_concept  BIGINT REFERENCES snomed_concept(id),  -- SNOMED SCTID
  map_group           INTEGER,
  map_priority        INTEGER,
  map_rule            TEXT,
  map_advice          TEXT,
  map_target          VARCHAR(10),                            -- ICD-10 code
  correlation_id      BIGINT
);

CREATE INDEX idx_map_snomed ON snomed_icd10_map(referenced_concept, active);
CREATE INDEX idx_map_icd10 ON snomed_icd10_map(map_target, active);