-- Name: COLUMN log_beh_fall_typ.orig_tableoid; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_beh_fall_typ.orig_tableoid IS 'the TG_RELID when the audit trigger was run';


--
-- Name: log_bill; Type: TABLE; Schema: audit; Owner: gm-dbo
--

CREATE TABLE audit.log_bill (
    pk_audit integer DEFAULT nextval('audit.audit_trail_pk_audit_seq'::regclass),
    row_version integer,
    modified_when timestamp with time zone,
    modified_by name,
    pk integer,
    invoice_id text,
    close_date timestamp with time zone,
    apply_vat boolean,
    fk_receiver_identity integer,
    fk_receiver_address integer,
    fk_doc integer,
    comment text
)
INHERITS (audit.audit_trail);


ALTER TABLE audit.log_bill OWNER TO "gm-dbo";

--
-- Name: COLUMN log_bill.orig_version; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_bill.orig_version IS 'the .row_version in the original row before the audited action took place, should be equal to .row_version';


--
-- Name: COLUMN log_bill.orig_when; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_bill.orig_when IS 'the .modified_when in the original row before the audited action took place, should be equal to .modified_when';


--
-- Name: COLUMN log_bill.orig_by; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_bill.orig_by IS 'the .modified_by in the original row before the audited action took place, should be equal to .modified_by';


--
-- Name: COLUMN log_bill.orig_tableoid; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_bill.orig_tableoid IS 'the TG_RELID when the audit trigger was run';


--
-- Name: log_bill_item; Type: TABLE; Schema: audit; Owner: gm-dbo
--

CREATE TABLE audit.log_bill_item (
    pk_audit integer DEFAULT nextval('audit.audit_trail_pk_audit_seq'::regclass),
    row_version integer,
    modified_when timestamp with time zone,
    modified_by name,
    pk integer,
    fk_provider integer,
    fk_encounter integer,
    date_to_bill date,
    description text,
    net_amount_per_unit numeric,
    currency text,
    status text,
    fk_billable integer,
    fk_bill integer,
    unit_count integer,
    amount_multiplier numeric
)
INHERITS (audit.audit_trail);


ALTER TABLE audit.log_bill_item OWNER TO "gm-dbo";

--
-- Name: COLUMN log_bill_item.orig_version; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_bill_item.orig_version IS 'the .row_version in the original row before the audited action took place, should be equal to .row_version';


--
-- Name: COLUMN log_bill_item.orig_when; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_bill_item.orig_when IS 'the .modified_when in the original row before the audited action took place, should be equal to .modified_when';


--
-- Name: COLUMN log_bill_item.orig_by; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_bill_item.orig_by IS 'the .modified_by in the original row before the audited action took place, should be equal to .modified_by';


--
-- Name: COLUMN log_bill_item.orig_tableoid; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_bill_item.orig_tableoid IS 'the TG_RELID when the audit trigger was run';


--
-- Name: log_billable; Type: TABLE; Schema: audit; Owner: gm-dbo
--

CREATE TABLE audit.log_billable (
    pk_coding_system integer,
    code text,
    term text,
    fk_data_source integer,
    comment text,
    pk integer,
    amount numeric,
    currency text,
    vat_multiplier numeric,
    active boolean,
    discountable boolean
)
INHERITS (audit.audit_trail);


ALTER TABLE audit.log_billable OWNER TO "gm-dbo";

--
-- Name: COLUMN log_billable.orig_version; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_billable.orig_version IS 'the .row_version in the original row before the audited action took place, should be equal to .row_version';


--
-- Name: COLUMN log_billable.orig_when; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_billable.orig_when IS 'the .modified_when in the original row before the audited action took place, should be equal to .modified_when';


--
-- Name: COLUMN log_billable.orig_by; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_billable.orig_by IS 'the .modified_by in the original row before the audited action took place, should be equal to .modified_by';


--
-- Name: COLUMN log_billable.orig_tableoid; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_billable.orig_tableoid IS 'the TG_RELID when the audit trigger was run';


--
-- Name: log_clin_aux_note; Type: TABLE; Schema: audit; Owner: gm-dbo
--

CREATE TABLE audit.log_clin_aux_note (
    pk_audit integer DEFAULT nextval('audit.audit_trail_pk_audit_seq'::regclass),
    row_version integer,
    modified_when timestamp with time zone,
    modified_by name,
    pk_item integer,
    clin_when timestamp with time zone,
    fk_encounter integer,
    fk_episode integer,
    narrative text,
    soap_cat text,
    pk integer
)
INHERITS (audit.audit_trail);


ALTER TABLE audit.log_clin_aux_note OWNER TO "gm-dbo";

--
-- Name: COLUMN log_clin_aux_note.orig_version; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_clin_aux_note.orig_version IS 'the .row_version in the original row before the audited action took place, should be equal to .row_version';


--
-- Name: COLUMN log_clin_aux_note.orig_when; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_clin_aux_note.orig_when IS 'the .modified_when in the original row before the audited action took place, should be equal to .modified_when';


--
-- Name: COLUMN log_clin_aux_note.orig_by; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_clin_aux_note.orig_by IS 'the .modified_by in the original row before the audited action took place, should be equal to .modified_by';


--
-- Name: COLUMN log_clin_aux_note.orig_tableoid; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_clin_aux_note.orig_tableoid IS 'the TG_RELID when the audit trigger was run';


--
-- Name: log_clin_diag; Type: TABLE; Schema: audit; Owner: gm-dbo
--

CREATE TABLE audit.log_clin_diag (
    pk_audit integer DEFAULT nextval('audit.audit_trail_pk_audit_seq'::regclass),
    row_version integer,
    modified_when timestamp with time zone,
    modified_by name,
    pk integer,
    fk_narrative integer,
    laterality bpchar,
    is_chronic boolean,
    is_active boolean,
    is_definite boolean,
    clinically_relevant boolean
)
INHERITS (audit.audit_trail);


ALTER TABLE audit.log_clin_diag OWNER TO "gm-dbo";

--
-- Name: COLUMN log_clin_diag.orig_version; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_clin_diag.orig_version IS 'the .row_version in the original row before the audited action took place, should be equal to .row_version';


--
-- Name: COLUMN log_clin_diag.orig_when; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_clin_diag.orig_when IS 'the .modified_when in the original row before the audited action took place, should be equal to .modified_when';


--
-- Name: COLUMN log_clin_diag.orig_by; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_clin_diag.orig_by IS 'the .modified_by in the original row before the audited action took place, should be equal to .modified_by';


--
-- Name: COLUMN log_clin_diag.orig_tableoid; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_clin_diag.orig_tableoid IS 'the TG_RELID when the audit trigger was run';


--
-- Name: log_clin_item_type; Type: TABLE; Schema: audit; Owner: gm-dbo
--

CREATE TABLE audit.log_clin_item_type (
    pk_audit integer DEFAULT nextval('audit.audit_trail_pk_audit_seq'::regclass),
    row_version integer,
    modified_when timestamp with time zone,
    modified_by name,
    pk integer,
    type text,
    code text
)
INHERITS (audit.audit_trail);


ALTER TABLE audit.log_clin_item_type OWNER TO "gm-dbo";

--
-- Name: COLUMN log_clin_item_type.orig_version; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_clin_item_type.orig_version IS 'the .row_version in the original row before the audited action took place, should be equal to .row_version';


--
-- Name: COLUMN log_clin_item_type.orig_when; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_clin_item_type.orig_when IS 'the .modified_when in the original row before the audited action took place, should be equal to .modified_when';


--
-- Name: COLUMN log_clin_item_type.orig_by; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_clin_item_type.orig_by IS 'the .modified_by in the original row before the audited action took place, should be equal to .modified_by';


--
-- Name: COLUMN log_clin_item_type.orig_tableoid; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_clin_item_type.orig_tableoid IS 'the TG_RELID when the audit trigger was run';


--
-- Name: log_clin_narrative; Type: TABLE; Schema: audit; Owner: gm-dbo
--

CREATE TABLE audit.log_clin_narrative (
    pk_audit integer DEFAULT nextval('audit.audit_trail_pk_audit_seq'::regclass),
    row_version integer,
    modified_when timestamp with time zone,
    modified_by name,
    pk_item integer,
    clin_when timestamp with time zone,
    fk_encounter integer,
    fk_episode integer,
    narrative text,
    soap_cat text,
    pk integer
)
INHERITS (audit.audit_trail);


ALTER TABLE audit.log_clin_narrative OWNER TO "gm-dbo";

--
-- Name: COLUMN log_clin_narrative.orig_version; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_clin_narrative.orig_version IS 'the .row_version in the original row before the audited action took place, should be equal to .row_version';


--
-- Name: COLUMN log_clin_narrative.orig_when; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_clin_narrative.orig_when IS 'the .modified_when in the original row before the audited action took place, should be equal to .modified_when';


--
-- Name: COLUMN log_clin_narrative.orig_by; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_clin_narrative.orig_by IS 'the .modified_by in the original row before the audited action took place, should be equal to .modified_by';


--
-- Name: COLUMN log_clin_narrative.orig_tableoid; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_clin_narrative.orig_tableoid IS 'the TG_RELID when the audit trigger was run';


--
-- Name: log_consumable_substance; Type: TABLE; Schema: audit; Owner: gm-dbo
--

CREATE TABLE audit.log_consumable_substance (
    pk_audit integer DEFAULT nextval('audit.audit_trail_pk_audit_seq'::regclass),
    row_version integer,
    modified_when timestamp with time zone,
    modified_by name,
    pk integer,
    description text,
    atc_code text,
    amount numeric,
    unit text
)
INHERITS (audit.audit_trail);


ALTER TABLE audit.log_consumable_substance OWNER TO "gm-dbo";

--
-- Name: COLUMN log_consumable_substance.orig_version; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_consumable_substance.orig_version IS 'the .row_version in the original row before the audited action took place, should be equal to .row_version';


--
-- Name: COLUMN log_consumable_substance.orig_when; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_consumable_substance.orig_when IS 'the .modified_when in the original row before the audited action took place, should be equal to .modified_when';


--
-- Name: COLUMN log_consumable_substance.orig_by; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_consumable_substance.orig_by IS 'the .modified_by in the original row before the audited action took place, should be equal to .modified_by';


--
-- Name: COLUMN log_consumable_substance.orig_tableoid; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_consumable_substance.orig_tableoid IS 'the TG_RELID when the audit trigger was run';


--
-- Name: log_consumed_substance; Type: TABLE; Schema: audit; Owner: gm-dbo
--

CREATE TABLE audit.log_consumed_substance (
    pk_audit integer DEFAULT nextval('audit.audit_trail_pk_audit_seq'::regclass),
    row_version integer,
    modified_when timestamp with time zone,
    modified_by name,
    pk integer,
    description text,
    atc_code text
)
INHERITS (audit.audit_trail);


ALTER TABLE audit.log_consumed_substance OWNER TO "gm-dbo";

--
-- Name: COLUMN log_consumed_substance.orig_version; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_consumed_substance.orig_version IS 'the .row_version in the original row before the audited action took place, should be equal to .row_version';


--
-- Name: COLUMN log_consumed_substance.orig_when; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_consumed_substance.orig_when IS 'the .modified_when in the original row before the audited action took place, should be equal to .modified_when';


--
-- Name: COLUMN log_consumed_substance.orig_by; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_consumed_substance.orig_by IS 'the .modified_by in the original row before the audited action took place, should be equal to .modified_by';


--
-- Name: COLUMN log_consumed_substance.orig_tableoid; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_consumed_substance.orig_tableoid IS 'the TG_RELID when the audit trigger was run';


--
-- Name: log_data_source; Type: TABLE; Schema: audit; Owner: gm-dbo
--

CREATE TABLE audit.log_data_source (
    pk_audit integer DEFAULT nextval('audit.audit_trail_pk_audit_seq'::regclass),
    row_version integer,
    modified_when timestamp with time zone,
    modified_by name,
    pk integer,
    name_short text,
    name_long text,
    version text,
    description text,
    source text,
    lang text
)
INHERITS (audit.audit_trail);


ALTER TABLE audit.log_data_source OWNER TO "gm-dbo";

--
-- Name: COLUMN log_data_source.orig_version; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_data_source.orig_version IS 'the .row_version in the original row before the audited action took place, should be equal to .row_version';


--
-- Name: COLUMN log_data_source.orig_when; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_data_source.orig_when IS 'the .modified_when in the original row before the audited action took place, should be equal to .modified_when';


--
-- Name: COLUMN log_data_source.orig_by; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_data_source.orig_by IS 'the .modified_by in the original row before the audited action took place, should be equal to .modified_by';


--
-- Name: COLUMN log_data_source.orig_tableoid; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_data_source.orig_tableoid IS 'the TG_RELID when the audit trigger was run';


--
-- Name: log_doc_desc; Type: TABLE; Schema: audit; Owner: gm-dbo
--

CREATE TABLE audit.log_doc_desc (
    pk_audit integer DEFAULT nextval('audit.audit_trail_pk_audit_seq'::regclass),
    row_version integer,
    modified_when timestamp with time zone,
    modified_by name,
    pk integer,
    fk_doc integer,
    text text
)
INHERITS (audit.audit_trail);


ALTER TABLE audit.log_doc_desc OWNER TO "gm-dbo";

--
-- Name: COLUMN log_doc_desc.orig_version; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_doc_desc.orig_version IS 'the .row_version in the original row before the audited action took place, should be equal to .row_version';


--
-- Name: COLUMN log_doc_desc.orig_when; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_doc_desc.orig_when IS 'the .modified_when in the original row before the audited action took place, should be equal to .modified_when';


--
-- Name: COLUMN log_doc_desc.orig_by; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_doc_desc.orig_by IS 'the .modified_by in the original row before the audited action took place, should be equal to .modified_by';


--
-- Name: COLUMN log_doc_desc.orig_tableoid; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_doc_desc.orig_tableoid IS 'the TG_RELID when the audit trigger was run';


--
-- Name: log_doc_med; Type: TABLE; Schema: audit; Owner: gm-dbo
--

CREATE TABLE audit.log_doc_med (
    pk_audit integer DEFAULT nextval('audit.audit_trail_pk_audit_seq'::regclass),
    row_version integer,
    modified_when timestamp with time zone,
    modified_by name,
    pk integer,
    fk_encounter integer,
    fk_episode integer,
    fk_type integer,
    comment text,
    clin_when timestamp with time zone,
    ext_ref text,
    fk_org_unit integer,
    unit_is_receiver boolean,
    fk_hospital_stay integer
)
INHERITS (audit.audit_trail);


ALTER TABLE audit.log_doc_med OWNER TO "gm-dbo";

--
-- Name: COLUMN log_doc_med.orig_version; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_doc_med.orig_version IS 'the .row_version in the original row before the audited action took place, should be equal to .row_version';


--
-- Name: COLUMN log_doc_med.orig_when; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_doc_med.orig_when IS 'the .modified_when in the original row before the audited action took place, should be equal to .modified_when';


--
-- Name: COLUMN log_doc_med.orig_by; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_doc_med.orig_by IS 'the .modified_by in the original row before the audited action took place, should be equal to .modified_by';


--
-- Name: COLUMN log_doc_med.orig_tableoid; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_doc_med.orig_tableoid IS 'the TG_RELID when the audit trigger was run';


--
-- Name: log_dose; Type: TABLE; Schema: audit; Owner: gm-dbo
--

CREATE TABLE audit.log_dose (
    pk integer,
    fk_substance integer,
    amount numeric,
    unit text,
    dose_unit text
)
INHERITS (audit.audit_trail);


ALTER TABLE audit.log_dose OWNER TO "gm-dbo";

--
-- Name: COLUMN log_dose.orig_version; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_dose.orig_version IS 'the .row_version in the original row before the audited action took place, should be equal to .row_version';


--
-- Name: COLUMN log_dose.orig_when; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_dose.orig_when IS 'the .modified_when in the original row before the audited action took place, should be equal to .modified_when';


--
-- Name: COLUMN log_dose.orig_by; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_dose.orig_by IS 'the .modified_by in the original row before the audited action took place, should be equal to .modified_by';


--
-- Name: COLUMN log_dose.orig_tableoid; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_dose.orig_tableoid IS 'the TG_RELID when the audit trigger was run';


--
-- Name: log_drug_product; Type: TABLE; Schema: audit; Owner: gm-dbo
--

CREATE TABLE audit.log_drug_product (
    pk_audit integer DEFAULT nextval('audit.audit_trail_pk_audit_seq'::regclass),
    row_version integer,
    modified_when timestamp with time zone,
    modified_by name,
    pk integer,
    description text,
    preparation text,
    atc_code text,
    is_fake boolean,
    fk_data_source integer,
    external_code text,
    external_code_type text
)
INHERITS (audit.audit_trail);


ALTER TABLE audit.log_drug_product OWNER TO "gm-dbo";

--
-- Name: COLUMN log_drug_product.orig_version; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_drug_product.orig_version IS 'the .row_version in the original row before the audited action took place, should be equal to .row_version';


--
-- Name: COLUMN log_drug_product.orig_when; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_drug_product.orig_when IS 'the .modified_when in the original row before the audited action took place, should be equal to .modified_when';


--
-- Name: COLUMN log_drug_product.orig_by; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_drug_product.orig_by IS 'the .modified_by in the original row before the audited action took place, should be equal to .modified_by';


--
-- Name: COLUMN log_drug_product.orig_tableoid; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_drug_product.orig_tableoid IS 'the TG_RELID when the audit trigger was run';


--
-- Name: log_encounter; Type: TABLE; Schema: audit; Owner: gm-dbo
--

CREATE TABLE audit.log_encounter (
    pk_audit integer DEFAULT nextval('audit.audit_trail_pk_audit_seq'::regclass),
    row_version integer,
    modified_when timestamp with time zone,
    modified_by name,
    pk integer,
    fk_patient integer,
    fk_type integer,
    fk_location integer,
    source_time_zone interval,
    reason_for_encounter text,
    assessment_of_encounter text,
    started timestamp with time zone,
    last_affirmed timestamp with time zone
)
INHERITS (audit.audit_trail);


ALTER TABLE audit.log_encounter OWNER TO "gm-dbo";

--
-- Name: COLUMN log_encounter.orig_version; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_encounter.orig_version IS 'the .row_version in the original row before the audited action took place, should be equal to .row_version';


--
-- Name: COLUMN log_encounter.orig_when; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_encounter.orig_when IS 'the .modified_when in the original row before the audited action took place, should be equal to .modified_when';


--
-- Name: COLUMN log_encounter.orig_by; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_encounter.orig_by IS 'the .modified_by in the original row before the audited action took place, should be equal to .modified_by';


--
-- Name: COLUMN log_encounter.orig_tableoid; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_encounter.orig_tableoid IS 'the TG_RELID when the audit trigger was run';


--
-- Name: log_episode; Type: TABLE; Schema: audit; Owner: gm-dbo
--

CREATE TABLE audit.log_episode (
    pk_audit integer DEFAULT nextval('audit.audit_trail_pk_audit_seq'::regclass),
    row_version integer,
    modified_when timestamp with time zone,
    modified_by name,
    pk integer,
    fk_health_issue integer,
    description text,
    is_open boolean,
    fk_encounter integer,
    diagnostic_certainty_classification text,
    summary text
)
INHERITS (audit.audit_trail);


ALTER TABLE audit.log_episode OWNER TO "gm-dbo";

--
-- Name: COLUMN log_episode.orig_version; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_episode.orig_version IS 'the .row_version in the original row before the audited action took place, should be equal to .row_version';


--
-- Name: COLUMN log_episode.orig_when; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_episode.orig_when IS 'the .modified_when in the original row before the audited action took place, should be equal to .modified_when';


--
-- Name: COLUMN log_episode.orig_by; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_episode.orig_by IS 'the .modified_by in the original row before the audited action took place, should be equal to .modified_by';


--
-- Name: COLUMN log_episode.orig_tableoid; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_episode.orig_tableoid IS 'the TG_RELID when the audit trigger was run';


--
-- Name: log_external_care; Type: TABLE; Schema: audit; Owner: gm-dbo
--

CREATE TABLE audit.log_external_care (
    pk_audit integer DEFAULT nextval('audit.audit_trail_pk_audit_seq'::regclass),
    row_version integer,
    modified_when timestamp with time zone,
    modified_by name,
    pk integer,
    fk_encounter integer,
    fk_health_issue integer,
    issue text,
    fk_org_unit integer,
    provider text,
    comment text,
    inactive boolean
)
INHERITS (audit.audit_trail);


ALTER TABLE audit.log_external_care OWNER TO "gm-dbo";

--
-- Name: COLUMN log_external_care.orig_version; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_external_care.orig_version IS 'the .row_version in the original row before the audited action took place, should be equal to .row_version';


--
-- Name: COLUMN log_external_care.orig_when; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_external_care.orig_when IS 'the .modified_when in the original row before the audited action took place, should be equal to .modified_when';


--
-- Name: COLUMN log_external_care.orig_by; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_external_care.orig_by IS 'the .modified_by in the original row before the audited action took place, should be equal to .modified_by';


--
-- Name: COLUMN log_external_care.orig_tableoid; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_external_care.orig_tableoid IS 'the TG_RELID when the audit trigger was run';


--
-- Name: log_family_history; Type: TABLE; Schema: audit; Owner: gm-dbo
--

CREATE TABLE audit.log_family_history (
    pk_audit integer DEFAULT nextval('audit.audit_trail_pk_audit_seq'::regclass),
    row_version integer,
    modified_when timestamp with time zone,
    modified_by name,
    pk_item integer,
    clin_when timestamp with time zone,
    fk_encounter integer,
    fk_episode integer,
    narrative text,
    soap_cat text,
    pk integer,
    fk_relation_type integer,
    age_noted text,
    age_of_death interval,
    contributed_to_death boolean,
    name_relative text,
    dob_relative timestamp with time zone,
    comment text
)
INHERITS (audit.audit_trail);


ALTER TABLE audit.log_family_history OWNER TO "gm-dbo";

--
-- Name: COLUMN log_family_history.orig_version; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_family_history.orig_version IS 'the .row_version in the original row before the audited action took place, should be equal to .row_version';


--
-- Name: COLUMN log_family_history.orig_when; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_family_history.orig_when IS 'the .modified_when in the original row before the audited action took place, should be equal to .modified_when';


--
-- Name: COLUMN log_family_history.orig_by; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_family_history.orig_by IS 'the .modified_by in the original row before the audited action took place, should be equal to .modified_by';


--
-- Name: COLUMN log_family_history.orig_tableoid; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_family_history.orig_tableoid IS 'the TG_RELID when the audit trigger was run';


--
-- Name: log_fhx_relation_type; Type: TABLE; Schema: audit; Owner: gm-dbo
--

CREATE TABLE audit.log_fhx_relation_type (
    pk_audit integer DEFAULT nextval('audit.audit_trail_pk_audit_seq'::regclass),
    row_version integer,
    modified_when timestamp with time zone,
    modified_by name,
    pk integer,
    description text,
    is_genetic boolean
)
INHERITS (audit.audit_trail);


ALTER TABLE audit.log_fhx_relation_type OWNER TO "gm-dbo";

--
-- Name: COLUMN log_fhx_relation_type.orig_version; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_fhx_relation_type.orig_version IS 'the .row_version in the original row before the audited action took place, should be equal to .row_version';


--
-- Name: COLUMN log_fhx_relation_type.orig_when; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_fhx_relation_type.orig_when IS 'the .modified_when in the original row before the audited action took place, should be equal to .modified_when';


--
-- Name: COLUMN log_fhx_relation_type.orig_by; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_fhx_relation_type.orig_by IS 'the .modified_by in the original row before the audited action took place, should be equal to .modified_by';


--
-- Name: COLUMN log_fhx_relation_type.orig_tableoid; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_fhx_relation_type.orig_tableoid IS 'the TG_RELID when the audit trigger was run';


--
-- Name: log_form_data; Type: TABLE; Schema: audit; Owner: gm-dbo
--

CREATE TABLE audit.log_form_data (
    pk_audit integer DEFAULT nextval('audit.audit_trail_pk_audit_seq'::regclass),
    row_version integer,
    modified_when timestamp with time zone,
    modified_by name,
    pk integer,
    fk_instance integer,
    fk_form_field integer,
    value text
)
INHERITS (audit.audit_trail);


ALTER TABLE audit.log_form_data OWNER TO "gm-dbo";

--
-- Name: COLUMN log_form_data.orig_version; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_form_data.orig_version IS 'the .row_version in the original row before the audited action took place, should be equal to .row_version';


--
-- Name: COLUMN log_form_data.orig_when; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_form_data.orig_when IS 'the .modified_when in the original row before the audited action took place, should be equal to .modified_when';


--
-- Name: COLUMN log_form_data.orig_by; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_form_data.orig_by IS 'the .modified_by in the original row before the audited action took place, should be equal to .modified_by';


--
-- Name: COLUMN log_form_data.orig_tableoid; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_form_data.orig_tableoid IS 'the TG_RELID when the audit trigger was run';


--
-- Name: log_form_instances; Type: TABLE; Schema: audit; Owner: gm-dbo
--

CREATE TABLE audit.log_form_instances (
    pk_audit integer DEFAULT nextval('audit.audit_trail_pk_audit_seq'::regclass),
    row_version integer,
    modified_when timestamp with time zone,
    modified_by name,
    pk_item integer,
    clin_when timestamp with time zone,
    fk_encounter integer,
    fk_episode integer,
    narrative text,
    soap_cat text,
    pk integer,
    fk_form_def integer,
    form_name text
)
INHERITS (audit.audit_trail);


ALTER TABLE audit.log_form_instances OWNER TO "gm-dbo";

--
-- Name: COLUMN log_form_instances.orig_version; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_form_instances.orig_version IS 'the .row_version in the original row before the audited action took place, should be equal to .row_version';


--
-- Name: COLUMN log_form_instances.orig_when; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_form_instances.orig_when IS 'the .modified_when in the original row before the audited action took place, should be equal to .modified_when';


--
-- Name: COLUMN log_form_instances.orig_by; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_form_instances.orig_by IS 'the .modified_by in the original row before the audited action took place, should be equal to .modified_by';


--
-- Name: COLUMN log_form_instances.orig_tableoid; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_form_instances.orig_tableoid IS 'the TG_RELID when the audit trigger was run';


--
-- Name: log_gender_label; Type: TABLE; Schema: audit; Owner: gm-dbo
--

CREATE TABLE audit.log_gender_label (
    pk_audit integer DEFAULT nextval('audit.audit_trail_pk_audit_seq'::regclass),
    row_version integer,
    modified_when timestamp with time zone,
    modified_by name,
    pk integer,
    tag text,
    label text,
    sort_weight integer,
    comment text
)
INHERITS (audit.audit_trail);


ALTER TABLE audit.log_gender_label OWNER TO "gm-dbo";

--
-- Name: COLUMN log_gender_label.orig_version; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_gender_label.orig_version IS 'the .row_version in the original row before the audited action took place, should be equal to .row_version';


--
-- Name: COLUMN log_gender_label.orig_when; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_gender_label.orig_when IS 'the .modified_when in the original row before the audited action took place, should be equal to .modified_when';


--
-- Name: COLUMN log_gender_label.orig_by; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_gender_label.orig_by IS 'the .modified_by in the original row before the audited action took place, should be equal to .modified_by';


--
-- Name: COLUMN log_gender_label.orig_tableoid; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_gender_label.orig_tableoid IS 'the TG_RELID when the audit trigger was run';


--
-- Name: log_health_issue; Type: TABLE; Schema: audit; Owner: gm-dbo
--

CREATE TABLE audit.log_health_issue (
    pk_audit integer DEFAULT nextval('audit.audit_trail_pk_audit_seq'::regclass),
    row_version integer,
    modified_when timestamp with time zone,
    modified_by name,
    pk integer,
    description text,
    laterality character varying,
    age_noted interval,
    is_active boolean,
    clinically_relevant boolean,
    is_confidential boolean,
    is_cause_of_death boolean,
    fk_encounter integer,
    "grouping" text,
    diagnostic_certainty_classification text,
    summary text
)
INHERITS (audit.audit_trail);


ALTER TABLE audit.log_health_issue OWNER TO "gm-dbo";

--
-- Name: COLUMN log_health_issue.orig_version; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_health_issue.orig_version IS 'the .row_version in the original row before the audited action took place, should be equal to .row_version';


--
-- Name: COLUMN log_health_issue.orig_when; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_health_issue.orig_when IS 'the .modified_when in the original row before the audited action took place, should be equal to .modified_when';


--
-- Name: COLUMN log_health_issue.orig_by; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_health_issue.orig_by IS 'the .modified_by in the original row before the audited action took place, should be equal to .modified_by';


--
-- Name: COLUMN log_health_issue.orig_tableoid; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_health_issue.orig_tableoid IS 'the TG_RELID when the audit trigger was run';


--
-- Name: log_hospital_stay; Type: TABLE; Schema: audit; Owner: gm-dbo
--

CREATE TABLE audit.log_hospital_stay (
    pk_audit integer DEFAULT nextval('audit.audit_trail_pk_audit_seq'::regclass),
    row_version integer,
    modified_when timestamp with time zone,
    modified_by name,
    pk_item integer,
    clin_when timestamp with time zone,
    fk_encounter integer,
    fk_episode integer,
    narrative text,
    soap_cat text,
    pk integer,
    discharge timestamp with time zone,
    fk_org_unit integer
)
INHERITS (audit.audit_trail);


ALTER TABLE audit.log_hospital_stay OWNER TO "gm-dbo";

--
-- Name: COLUMN log_hospital_stay.orig_version; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_hospital_stay.orig_version IS 'the .row_version in the original row before the audited action took place, should be equal to .row_version';


--
-- Name: COLUMN log_hospital_stay.orig_when; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_hospital_stay.orig_when IS 'the .modified_when in the original row before the audited action took place, should be equal to .modified_when';


--
-- Name: COLUMN log_hospital_stay.orig_by; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_hospital_stay.orig_by IS 'the .modified_by in the original row before the audited action took place, should be equal to .modified_by';


--
-- Name: COLUMN log_hospital_stay.orig_tableoid; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_hospital_stay.orig_tableoid IS 'the TG_RELID when the audit trigger was run';


--
-- Name: log_hx_family_item; Type: TABLE; Schema: audit; Owner: gm-dbo
--

CREATE TABLE audit.log_hx_family_item (
    pk_audit integer DEFAULT nextval('audit.audit_trail_pk_audit_seq'::regclass),
    row_version integer,
    modified_when timestamp with time zone,
    modified_by name,
    pk integer,
    fk_narrative_condition integer,
    fk_relative integer,
    name_relative text,
    dob_relative timestamp with time zone,
    condition text,
    age_noted text,
    age_of_death interval,
    is_cause_of_death boolean
)
INHERITS (audit.audit_trail);


ALTER TABLE audit.log_hx_family_item OWNER TO "gm-dbo";

--
-- Name: COLUMN log_hx_family_item.orig_version; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_hx_family_item.orig_version IS 'the .row_version in the original row before the audited action took place, should be equal to .row_version';


--
-- Name: COLUMN log_hx_family_item.orig_when; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_hx_family_item.orig_when IS 'the .modified_when in the original row before the audited action took place, should be equal to .modified_when';


--
-- Name: COLUMN log_hx_family_item.orig_by; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_hx_family_item.orig_by IS 'the .modified_by in the original row before the audited action took place, should be equal to .modified_by';


--
-- Name: COLUMN log_hx_family_item.orig_tableoid; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_hx_family_item.orig_tableoid IS 'the TG_RELID when the audit trigger was run';


--
-- Name: log_identity; Type: TABLE; Schema: audit; Owner: gm-dbo
--

CREATE TABLE audit.log_identity (
    pk_audit integer DEFAULT nextval('audit.audit_trail_pk_audit_seq'::regclass),
    row_version integer,
    modified_when timestamp with time zone,
    modified_by name,
    pk integer,
    deleted boolean,
    gender text,
    dob timestamp with time zone,
    fk_marital_status integer,
    cob bpchar,
    deceased timestamp with time zone,
    title text,
    tob time without time zone,
    emergency_contact text,
    fk_emergency_contact integer,
    comment text,
    fk_primary_provider integer,
    dob_is_estimated boolean
)
INHERITS (audit.audit_trail);


ALTER TABLE audit.log_identity OWNER TO "gm-dbo";

--
-- Name: COLUMN log_identity.orig_version; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_identity.orig_version IS 'the .row_version in the original row before the audited action took place, should be equal to .row_version';


--
-- Name: COLUMN log_identity.orig_when; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_identity.orig_when IS 'the .modified_when in the original row before the audited action took place, should be equal to .modified_when';


--
-- Name: COLUMN log_identity.orig_by; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_identity.orig_by IS 'the .modified_by in the original row before the audited action took place, should be equal to .modified_by';


--
-- Name: COLUMN log_identity.orig_tableoid; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_identity.orig_tableoid IS 'the TG_RELID when the audit trigger was run';


--
-- Name: log_identity_tag; Type: TABLE; Schema: audit; Owner: gm-dbo
--

CREATE TABLE audit.log_identity_tag (
    pk_audit integer DEFAULT nextval('audit.audit_trail_pk_audit_seq'::regclass),
    row_version integer,
    modified_when timestamp with time zone,
    modified_by name,
    pk integer,
    fk_identity integer,
    fk_tag integer,
    comment text
)
INHERITS (audit.audit_trail);


ALTER TABLE audit.log_identity_tag OWNER TO "gm-dbo";

--
-- Name: COLUMN log_identity_tag.orig_version; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_identity_tag.orig_version IS 'the .row_version in the original row before the audited action took place, should be equal to .row_version';


--
-- Name: COLUMN log_identity_tag.orig_when; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_identity_tag.orig_when IS 'the .modified_when in the original row before the audited action took place, should be equal to .modified_when';


--
-- Name: COLUMN log_identity_tag.orig_by; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_identity_tag.orig_by IS 'the .modified_by in the original row before the audited action took place, should be equal to .modified_by';


--
-- Name: COLUMN log_identity_tag.orig_tableoid; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_identity_tag.orig_tableoid IS 'the TG_RELID when the audit trigger was run';


--
-- Name: log_inbox_item_category; Type: TABLE; Schema: audit; Owner: gm-dbo
--

CREATE TABLE audit.log_inbox_item_category (
    pk_audit integer DEFAULT nextval('audit.audit_trail_pk_audit_seq'::regclass),
    row_version integer,
    modified_when timestamp with time zone,
    modified_by name,
    pk integer,
    description text,
    is_user boolean
)
INHERITS (audit.audit_trail);


ALTER TABLE audit.log_inbox_item_category OWNER TO "gm-dbo";

--
-- Name: COLUMN log_inbox_item_category.orig_version; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_inbox_item_category.orig_version IS 'the .row_version in the original row before the audited action took place, should be equal to .row_version';


--
-- Name: COLUMN log_inbox_item_category.orig_when; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_inbox_item_category.orig_when IS 'the .modified_when in the original row before the audited action took place, should be equal to .modified_when';


--
-- Name: COLUMN log_inbox_item_category.orig_by; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_inbox_item_category.orig_by IS 'the .modified_by in the original row before the audited action took place, should be equal to .modified_by';


--
-- Name: COLUMN log_inbox_item_category.orig_tableoid; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_inbox_item_category.orig_tableoid IS 'the TG_RELID when the audit trigger was run';


--
-- Name: log_inbox_item_type; Type: TABLE; Schema: audit; Owner: gm-dbo
--

CREATE TABLE audit.log_inbox_item_type (
    pk_audit integer DEFAULT nextval('audit.audit_trail_pk_audit_seq'::regclass),
    row_version integer,
    modified_when timestamp with time zone,
    modified_by name,
    pk integer,
    fk_inbox_item_category integer,
    description text,
    is_user boolean
)
INHERITS (audit.audit_trail);


ALTER TABLE audit.log_inbox_item_type OWNER TO "gm-dbo";

--
-- Name: COLUMN log_inbox_item_type.orig_version; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_inbox_item_type.orig_version IS 'the .row_version in the original row before the audited action took place, should be equal to .row_version';


--
-- Name: COLUMN log_inbox_item_type.orig_when; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_inbox_item_type.orig_when IS 'the .modified_when in the original row before the audited action took place, should be equal to .modified_when';


--
-- Name: COLUMN log_inbox_item_type.orig_by; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_inbox_item_type.orig_by IS 'the .modified_by in the original row before the audited action took place, should be equal to .modified_by';


--
-- Name: COLUMN log_inbox_item_type.orig_tableoid; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_inbox_item_type.orig_tableoid IS 'the TG_RELID when the audit trigger was run';


--
-- Name: log_incoming_data_unmatchable; Type: TABLE; Schema: audit; Owner: gm-dbo
--

CREATE TABLE audit.log_incoming_data_unmatchable (
    pk_audit integer DEFAULT nextval('audit.audit_trail_pk_audit_seq'::regclass),
    row_version integer,
    modified_when timestamp with time zone,
    modified_by name,
    pk integer,
    fk_patient_candidates integer[],
    request_id text,
    firstnames text,
    lastnames text,
    dob date,
    postcode text,
    other_info text,
    type text,
    data bytea,
    gender text,
    requestor text,
    external_data_id text,
    fk_identity_disambiguated integer
)
INHERITS (audit.audit_trail);


ALTER TABLE audit.log_incoming_data_unmatchable OWNER TO "gm-dbo";

--
-- Name: COLUMN log_incoming_data_unmatchable.orig_version; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_incoming_data_unmatchable.orig_version IS 'the .row_version in the original row before the audited action took place, should be equal to .row_version';


--
-- Name: COLUMN log_incoming_data_unmatchable.orig_when; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_incoming_data_unmatchable.orig_when IS 'the .modified_when in the original row before the audited action took place, should be equal to .modified_when';


--
-- Name: COLUMN log_incoming_data_unmatchable.orig_by; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_incoming_data_unmatchable.orig_by IS 'the .modified_by in the original row before the audited action took place, should be equal to .modified_by';


--
-- Name: COLUMN log_incoming_data_unmatchable.orig_tableoid; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_incoming_data_unmatchable.orig_tableoid IS 'the TG_RELID when the audit trigger was run';


--
-- Name: log_incoming_data_unmatched; Type: TABLE; Schema: audit; Owner: gm-dbo
--

CREATE TABLE audit.log_incoming_data_unmatched (
    pk_audit integer DEFAULT nextval('audit.audit_trail_pk_audit_seq'::regclass),
    row_version integer,
    modified_when timestamp with time zone,
    modified_by name,
    pk integer,
    fk_patient_candidates integer[],
    request_id text,
    firstnames text,
    lastnames text,
    dob date,
    postcode text,
    other_info text,
    type text,
    data bytea,
    gender text,
    requestor text,
    external_data_id text,
    fk_identity_disambiguated integer,
    comment text,
    fk_provider_disambiguated integer
)
INHERITS (audit.audit_trail);


ALTER TABLE audit.log_incoming_data_unmatched OWNER TO "gm-dbo";

--
-- Name: COLUMN log_incoming_data_unmatched.orig_version; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_incoming_data_unmatched.orig_version IS 'the .row_version in the original row before the audited action took place, should be equal to .row_version';


--
-- Name: COLUMN log_incoming_data_unmatched.orig_when; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_incoming_data_unmatched.orig_when IS 'the .modified_when in the original row before the audited action took place, should be equal to .modified_when';


--
-- Name: COLUMN log_incoming_data_unmatched.orig_by; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_incoming_data_unmatched.orig_by IS 'the .modified_by in the original row before the audited action took place, should be equal to .modified_by';


--
-- Name: COLUMN log_incoming_data_unmatched.orig_tableoid; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_incoming_data_unmatched.orig_tableoid IS 'the TG_RELID when the audit trigger was run';


--
-- Name: log_lab_request; Type: TABLE; Schema: audit; Owner: gm-dbo
--

CREATE TABLE audit.log_lab_request (
    pk_audit integer DEFAULT nextval('audit.audit_trail_pk_audit_seq'::regclass),
    row_version integer,
    modified_when timestamp with time zone,
    modified_by name,
    pk_item integer,
    clin_when timestamp with time zone,
    fk_encounter integer,
    fk_episode integer,
    narrative text,
    soap_cat text,
    pk integer,
    fk_test_org integer,
    request_id text,
    fk_requestor integer,
    lab_request_id text,
    lab_rxd_when timestamp with time zone,
    results_reported_when timestamp with time zone,
    request_status text,
    is_pending boolean,
    diagnostic_service_section text,
    ordered_service text,
    note_test_org text
)
INHERITS (audit.audit_trail);


ALTER TABLE audit.log_lab_request OWNER TO "gm-dbo";

--
-- Name: COLUMN log_lab_request.orig_version; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_lab_request.orig_version IS 'the .row_version in the original row before the audited action took place, should be equal to .row_version';


--
-- Name: COLUMN log_lab_request.orig_when; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_lab_request.orig_when IS 'the .modified_when in the original row before the audited action took place, should be equal to .modified_when';


--
-- Name: COLUMN log_lab_request.orig_by; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_lab_request.orig_by IS 'the .modified_by in the original row before the audited action took place, should be equal to .modified_by';


--
-- Name: COLUMN log_lab_request.orig_tableoid; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_lab_request.orig_tableoid IS 'the TG_RELID when the audit trigger was run';


--
-- Name: log_lab_test_gnr; Type: TABLE; Schema: audit; Owner: gm-dbo
--

CREATE TABLE audit.log_lab_test_gnr (
    pk_audit integer DEFAULT nextval('audit.audit_trail_pk_audit_seq'::regclass),
    row_version integer,
    modified_when timestamp with time zone,
    modified_by name,
    id integer,
    id_test integer
)
INHERITS (audit.audit_trail);


ALTER TABLE audit.log_lab_test_gnr OWNER TO "gm-dbo";

--
-- Name: COLUMN log_lab_test_gnr.orig_version; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_lab_test_gnr.orig_version IS 'the .row_version in the original row before the audited action took place, should be equal to .row_version';


--
-- Name: COLUMN log_lab_test_gnr.orig_when; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_lab_test_gnr.orig_when IS 'the .modified_when in the original row before the audited action took place, should be equal to .modified_when';


--
-- Name: COLUMN log_lab_test_gnr.orig_by; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_lab_test_gnr.orig_by IS 'the .modified_by in the original row before the audited action took place, should be equal to .modified_by';


--
-- Name: COLUMN log_lab_test_gnr.orig_tableoid; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_lab_test_gnr.orig_tableoid IS 'the TG_RELID when the audit trigger was run';


--
-- Name: log_lnk_code2aoe; Type: TABLE; Schema: audit; Owner: gm-dbo
--

CREATE TABLE audit.log_lnk_code2aoe (
    pk_audit integer DEFAULT nextval('audit.audit_trail_pk_audit_seq'::regclass),
    row_version integer,
    modified_when timestamp with time zone,
    modified_by name,
    pk_lnk_code2item integer,
    fk_generic_code integer,
    fk_item integer,
    code_modifier text,
    pk integer
)
INHERITS (audit.audit_trail);


ALTER TABLE audit.log_lnk_code2aoe OWNER TO "gm-dbo";

--
-- Name: COLUMN log_lnk_code2aoe.orig_version; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_lnk_code2aoe.orig_version IS 'the .row_version in the original row before the audited action took place, should be equal to .row_version';


--
-- Name: COLUMN log_lnk_code2aoe.orig_when; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_lnk_code2aoe.orig_when IS 'the .modified_when in the original row before the audited action took place, should be equal to .modified_when';


--
-- Name: COLUMN log_lnk_code2aoe.orig_by; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_lnk_code2aoe.orig_by IS 'the .modified_by in the original row before the audited action took place, should be equal to .modified_by';


--
-- Name: COLUMN log_lnk_code2aoe.orig_tableoid; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_lnk_code2aoe.orig_tableoid IS 'the TG_RELID when the audit trigger was run';


--
-- Name: log_lnk_code2episode; Type: TABLE; Schema: audit; Owner: gm-dbo
--

CREATE TABLE audit.log_lnk_code2episode (
    pk_audit integer DEFAULT nextval('audit.audit_trail_pk_audit_seq'::regclass),
    row_version integer,
    modified_when timestamp with time zone,
    modified_by name,
    pk_lnk_code2item integer,
    fk_generic_code integer,
    fk_item integer,
    code_modifier text,
    pk integer
)
INHERITS (audit.audit_trail);


ALTER TABLE audit.log_lnk_code2episode OWNER TO "gm-dbo";

--
-- Name: COLUMN log_lnk_code2episode.orig_version; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_lnk_code2episode.orig_version IS 'the .row_version in the original row before the audited action took place, should be equal to .row_version';


--
-- Name: COLUMN log_lnk_code2episode.orig_when; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_lnk_code2episode.orig_when IS 'the .modified_when in the original row before the audited action took place, should be equal to .modified_when';


--
-- Name: COLUMN log_lnk_code2episode.orig_by; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_lnk_code2episode.orig_by IS 'the .modified_by in the original row before the audited action took place, should be equal to .modified_by';


--
-- Name: COLUMN log_lnk_code2episode.orig_tableoid; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_lnk_code2episode.orig_tableoid IS 'the TG_RELID when the audit trigger was run';


--
-- Name: log_lnk_code2fhx; Type: TABLE; Schema: audit; Owner: gm-dbo
--

CREATE TABLE audit.log_lnk_code2fhx (
    pk_audit integer DEFAULT nextval('audit.audit_trail_pk_audit_seq'::regclass),
    row_version integer,
    modified_when timestamp with time zone,
    modified_by name,
    pk_lnk_code2item integer,
    fk_generic_code integer,
    fk_item integer,
    code_modifier text,
    pk integer
)
INHERITS (audit.audit_trail);


ALTER TABLE audit.log_lnk_code2fhx OWNER TO "gm-dbo";

--
-- Name: COLUMN log_lnk_code2fhx.orig_version; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_lnk_code2fhx.orig_version IS 'the .row_version in the original row before the audited action took place, should be equal to .row_version';


--
-- Name: COLUMN log_lnk_code2fhx.orig_when; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_lnk_code2fhx.orig_when IS 'the .modified_when in the original row before the audited action took place, should be equal to .modified_when';


--
-- Name: COLUMN log_lnk_code2fhx.orig_by; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_lnk_code2fhx.orig_by IS 'the .modified_by in the original row before the audited action took place, should be equal to .modified_by';


--
-- Name: COLUMN log_lnk_code2fhx.orig_tableoid; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_lnk_code2fhx.orig_tableoid IS 'the TG_RELID when the audit trigger was run';


--
-- Name: log_lnk_code2h_issue; Type: TABLE; Schema: audit; Owner: gm-dbo
--

CREATE TABLE audit.log_lnk_code2h_issue (
    pk_audit integer DEFAULT nextval('audit.audit_trail_pk_audit_seq'::regclass),
    row_version integer,
    modified_when timestamp with time zone,
    modified_by name,
    pk_lnk_code2item integer,
    fk_generic_code integer,
    fk_item integer,
    code_modifier text,
    pk integer
)
INHERITS (audit.audit_trail);


ALTER TABLE audit.log_lnk_code2h_issue OWNER TO "gm-dbo";

--
-- Name: COLUMN log_lnk_code2h_issue.orig_version; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_lnk_code2h_issue.orig_version IS 'the .row_version in the original row before the audited action took place, should be equal to .row_version';


--
-- Name: COLUMN log_lnk_code2h_issue.orig_when; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_lnk_code2h_issue.orig_when IS 'the .modified_when in the original row before the audited action took place, should be equal to .modified_when';


--
-- Name: COLUMN log_lnk_code2h_issue.orig_by; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_lnk_code2h_issue.orig_by IS 'the .modified_by in the original row before the audited action took place, should be equal to .modified_by';


--
-- Name: COLUMN log_lnk_code2h_issue.orig_tableoid; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_lnk_code2h_issue.orig_tableoid IS 'the TG_RELID when the audit trigger was run';


--
-- Name: log_lnk_code2narrative; Type: TABLE; Schema: audit; Owner: gm-dbo
--

CREATE TABLE audit.log_lnk_code2narrative (
    pk_audit integer DEFAULT nextval('audit.audit_trail_pk_audit_seq'::regclass),
    row_version integer,
    modified_when timestamp with time zone,
    modified_by name,
    pk_lnk_code2item integer,
    fk_generic_code integer,
    fk_item integer,
    code_modifier text,
    pk integer
)
INHERITS (audit.audit_trail);


ALTER TABLE audit.log_lnk_code2narrative OWNER TO "gm-dbo";

--
-- Name: COLUMN log_lnk_code2narrative.orig_version; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_lnk_code2narrative.orig_version IS 'the .row_version in the original row before the audited action took place, should be equal to .row_version';


--
-- Name: COLUMN log_lnk_code2narrative.orig_when; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_lnk_code2narrative.orig_when IS 'the .modified_when in the original row before the audited action took place, should be equal to .modified_when';


--
-- Name: COLUMN log_lnk_code2narrative.orig_by; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_lnk_code2narrative.orig_by IS 'the .modified_by in the original row before the audited action took place, should be equal to .modified_by';


--
-- Name: COLUMN log_lnk_code2narrative.orig_tableoid; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_lnk_code2narrative.orig_tableoid IS 'the TG_RELID when the audit trigger was run';


--
-- Name: log_lnk_code2procedure; Type: TABLE; Schema: audit; Owner: gm-dbo
--

CREATE TABLE audit.log_lnk_code2procedure (
    pk_audit integer DEFAULT nextval('audit.audit_trail_pk_audit_seq'::regclass),
    row_version integer,
    modified_when timestamp with time zone,
    modified_by name,
    pk_lnk_code2item integer,
    fk_generic_code integer,
    fk_item integer,
    code_modifier text,
    pk integer
)
INHERITS (audit.audit_trail);


ALTER TABLE audit.log_lnk_code2procedure OWNER TO "gm-dbo";

--
-- Name: COLUMN log_lnk_code2procedure.orig_version; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_lnk_code2procedure.orig_version IS 'the .row_version in the original row before the audited action took place, should be equal to .row_version';


--
-- Name: COLUMN log_lnk_code2procedure.orig_when; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_lnk_code2procedure.orig_when IS 'the .modified_when in the original row before the audited action took place, should be equal to .modified_when';


--
-- Name: COLUMN log_lnk_code2procedure.orig_by; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_lnk_code2procedure.orig_by IS 'the .modified_by in the original row before the audited action took place, should be equal to .modified_by';


--
-- Name: COLUMN log_lnk_code2procedure.orig_tableoid; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_lnk_code2procedure.orig_tableoid IS 'the TG_RELID when the audit trigger was run';


--
-- Name: log_lnk_code2rfe; Type: TABLE; Schema: audit; Owner: gm-dbo
--

CREATE TABLE audit.log_lnk_code2rfe (
    pk_audit integer DEFAULT nextval('audit.audit_trail_pk_audit_seq'::regclass),
    row_version integer,
    modified_when timestamp with time zone,
    modified_by name,
    pk_lnk_code2item integer,
    fk_generic_code integer,
    fk_item integer,
    code_modifier text,
    pk integer
)
INHERITS (audit.audit_trail);


ALTER TABLE audit.log_lnk_code2rfe OWNER TO "gm-dbo";

--
-- Name: COLUMN log_lnk_code2rfe.orig_version; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_lnk_code2rfe.orig_version IS 'the .row_version in the original row before the audited action took place, should be equal to .row_version';


--
-- Name: COLUMN log_lnk_code2rfe.orig_when; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_lnk_code2rfe.orig_when IS 'the .modified_when in the original row before the audited action took place, should be equal to .modified_when';


--
-- Name: COLUMN log_lnk_code2rfe.orig_by; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_lnk_code2rfe.orig_by IS 'the .modified_by in the original row before the audited action took place, should be equal to .modified_by';


--
-- Name: COLUMN log_lnk_code2rfe.orig_tableoid; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_lnk_code2rfe.orig_tableoid IS 'the TG_RELID when the audit trigger was run';


--
-- Name: log_lnk_code2tst_pnl; Type: TABLE; Schema: audit; Owner: gm-dbo
--

CREATE TABLE audit.log_lnk_code2tst_pnl (
    pk_audit integer DEFAULT nextval('audit.audit_trail_pk_audit_seq'::regclass),
    row_version integer,
    modified_when timestamp with time zone,
    modified_by name,
    pk_lnk_code2item integer,
    fk_generic_code integer,
    fk_item integer,
    code_modifier text,
    pk integer
)
INHERITS (audit.audit_trail);


ALTER TABLE audit.log_lnk_code2tst_pnl OWNER TO "gm-dbo";

--
-- Name: COLUMN log_lnk_code2tst_pnl.orig_version; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_lnk_code2tst_pnl.orig_version IS 'the .row_version in the original row before the audited action took place, should be equal to .row_version';


--
-- Name: COLUMN log_lnk_code2tst_pnl.orig_when; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_lnk_code2tst_pnl.orig_when IS 'the .modified_when in the original row before the audited action took place, should be equal to .modified_when';


--
-- Name: COLUMN log_lnk_code2tst_pnl.orig_by; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_lnk_code2tst_pnl.orig_by IS 'the .modified_by in the original row before the audited action took place, should be equal to .modified_by';


--
-- Name: COLUMN log_lnk_code2tst_pnl.orig_tableoid; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_lnk_code2tst_pnl.orig_tableoid IS 'the TG_RELID when the audit trigger was run';


--
-- Name: log_lnk_constraint2vacc_course; Type: TABLE; Schema: audit; Owner: gm-dbo
--

CREATE TABLE audit.log_lnk_constraint2vacc_course (
    pk_audit integer DEFAULT nextval('audit.audit_trail_pk_audit_seq'::regclass),
    row_version integer,
    modified_when timestamp with time zone,
    modified_by name,
    pk integer,
    fk_vaccination_course integer,
    fk_constraint integer
)
INHERITS (audit.audit_trail);


ALTER TABLE audit.log_lnk_constraint2vacc_course OWNER TO "gm-dbo";

--
-- Name: COLUMN log_lnk_constraint2vacc_course.orig_version; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_lnk_constraint2vacc_course.orig_version IS 'the .row_version in the original row before the audited action took place, should be equal to .row_version';


--
-- Name: COLUMN log_lnk_constraint2vacc_course.orig_when; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_lnk_constraint2vacc_course.orig_when IS 'the .modified_when in the original row before the audited action took place, should be equal to .modified_when';


--
-- Name: COLUMN log_lnk_constraint2vacc_course.orig_by; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_lnk_constraint2vacc_course.orig_by IS 'the .modified_by in the original row before the audited action took place, should be equal to .modified_by';


--
-- Name: COLUMN log_lnk_constraint2vacc_course.orig_tableoid; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_lnk_constraint2vacc_course.orig_tableoid IS 'the TG_RELID when the audit trigger was run';


--
-- Name: log_lnk_dose2drug; Type: TABLE; Schema: audit; Owner: gm-dbo
--

CREATE TABLE audit.log_lnk_dose2drug (
    pk integer,
    fk_drug_product integer,
    fk_dose integer
)
INHERITS (audit.audit_trail);


ALTER TABLE audit.log_lnk_dose2drug OWNER TO "gm-dbo";

--
-- Name: COLUMN log_lnk_dose2drug.orig_version; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_lnk_dose2drug.orig_version IS 'the .row_version in the original row before the audited action took place, should be equal to .row_version';


--
-- Name: COLUMN log_lnk_dose2drug.orig_when; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_lnk_dose2drug.orig_when IS 'the .modified_when in the original row before the audited action took place, should be equal to .modified_when';


--
-- Name: COLUMN log_lnk_dose2drug.orig_by; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_lnk_dose2drug.orig_by IS 'the .modified_by in the original row before the audited action took place, should be equal to .modified_by';


--
-- Name: COLUMN log_lnk_dose2drug.orig_tableoid; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_lnk_dose2drug.orig_tableoid IS 'the TG_RELID when the audit trigger was run';


--
-- Name: log_lnk_identity2ext_id; Type: TABLE; Schema: audit; Owner: gm-dbo
--

CREATE TABLE audit.log_lnk_identity2ext_id (
    pk_audit integer DEFAULT nextval('audit.audit_trail_pk_audit_seq'::regclass),
    row_version integer,
    modified_when timestamp with time zone,
    modified_by name,
    id integer,
    id_identity integer,
    external_id text,
    fk_origin integer,
    comment text
)
INHERITS (audit.audit_trail);


ALTER TABLE audit.log_lnk_identity2ext_id OWNER TO "gm-dbo";

--
-- Name: COLUMN log_lnk_identity2ext_id.orig_version; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_lnk_identity2ext_id.orig_version IS 'the .row_version in the original row before the audited action took place, should be equal to .row_version';


--
-- Name: COLUMN log_lnk_identity2ext_id.orig_when; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_lnk_identity2ext_id.orig_when IS 'the .modified_when in the original row before the audited action took place, should be equal to .modified_when';


--
-- Name: COLUMN log_lnk_identity2ext_id.orig_by; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_lnk_identity2ext_id.orig_by IS 'the .modified_by in the original row before the audited action took place, should be equal to .modified_by';


--
-- Name: COLUMN log_lnk_identity2ext_id.orig_tableoid; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_lnk_identity2ext_id.orig_tableoid IS 'the TG_RELID when the audit trigger was run';


--
-- Name: log_lnk_identity2primary_doc; Type: TABLE; Schema: audit; Owner: gm-dbo
--

CREATE TABLE audit.log_lnk_identity2primary_doc (
    pk integer,
    fk_identity integer,
    fk_primary_doc integer
)
INHERITS (audit.audit_trail);


ALTER TABLE audit.log_lnk_identity2primary_doc OWNER TO "gm-dbo";

--
-- Name: COLUMN log_lnk_identity2primary_doc.orig_version; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_lnk_identity2primary_doc.orig_version IS 'the .row_version in the original row before the audited action took place, should be equal to .row_version';


--
-- Name: COLUMN log_lnk_identity2primary_doc.orig_when; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_lnk_identity2primary_doc.orig_when IS 'the .modified_when in the original row before the audited action took place, should be equal to .modified_when';


--
-- Name: COLUMN log_lnk_identity2primary_doc.orig_by; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_lnk_identity2primary_doc.orig_by IS 'the .modified_by in the original row before the audited action took place, should be equal to .modified_by';


--
-- Name: COLUMN log_lnk_identity2primary_doc.orig_tableoid; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_lnk_identity2primary_doc.orig_tableoid IS 'the TG_RELID when the audit trigger was run';


--
-- Name: log_lnk_job2person; Type: TABLE; Schema: audit; Owner: gm-dbo
--

CREATE TABLE audit.log_lnk_job2person (
    pk_audit integer DEFAULT nextval('audit.audit_trail_pk_audit_seq'::regclass),
    row_version integer,
    modified_when timestamp with time zone,
    modified_by name,
    pk integer,
    fk_identity integer,
    fk_occupation integer,
    activities text
)
INHERITS (audit.audit_trail);


ALTER TABLE audit.log_lnk_job2person OWNER TO "gm-dbo";

--
-- Name: COLUMN log_lnk_job2person.orig_version; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_lnk_job2person.orig_version IS 'the .row_version in the original row before the audited action took place, should be equal to .row_version';


--
-- Name: COLUMN log_lnk_job2person.orig_when; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_lnk_job2person.orig_when IS 'the .modified_when in the original row before the audited action took place, should be equal to .modified_when';


--
-- Name: COLUMN log_lnk_job2person.orig_by; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_lnk_job2person.orig_by IS 'the .modified_by in the original row before the audited action took place, should be equal to .modified_by';


--
-- Name: COLUMN log_lnk_job2person.orig_tableoid; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_lnk_job2person.orig_tableoid IS 'the TG_RELID when the audit trigger was run';


--
-- Name: log_lnk_loinc2substance; Type: TABLE; Schema: audit; Owner: gm-dbo
--

CREATE TABLE audit.log_lnk_loinc2substance (
    pk integer,
    fk_substance integer,
    loinc text,
    max_age interval,
    comment text
)
INHERITS (audit.audit_trail);


ALTER TABLE audit.log_lnk_loinc2substance OWNER TO "gm-dbo";

--
-- Name: COLUMN log_lnk_loinc2substance.orig_version; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_lnk_loinc2substance.orig_version IS 'the .row_version in the original row before the audited action took place, should be equal to .row_version';


--
-- Name: COLUMN log_lnk_loinc2substance.orig_when; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_lnk_loinc2substance.orig_when IS 'the .modified_when in the original row before the audited action took place, should be equal to .modified_when';


--
-- Name: COLUMN log_lnk_loinc2substance.orig_by; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_lnk_loinc2substance.orig_by IS 'the .modified_by in the original row before the audited action took place, should be equal to .modified_by';


--
-- Name: COLUMN log_lnk_loinc2substance.orig_tableoid; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_lnk_loinc2substance.orig_tableoid IS 'the TG_RELID when the audit trigger was run';


--
-- Name: log_lnk_loinc2test_panel; Type: TABLE; Schema: audit; Owner: gm-dbo
--

CREATE TABLE audit.log_lnk_loinc2test_panel (
    pk integer,
    fk_test_panel integer,
    loinc text
)
INHERITS (audit.audit_trail);


ALTER TABLE audit.log_lnk_loinc2test_panel OWNER TO "gm-dbo";

--
-- Name: COLUMN log_lnk_loinc2test_panel.orig_version; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_lnk_loinc2test_panel.orig_version IS 'the .row_version in the original row before the audited action took place, should be equal to .row_version';


--
-- Name: COLUMN log_lnk_loinc2test_panel.orig_when; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_lnk_loinc2test_panel.orig_when IS 'the .modified_when in the original row before the audited action took place, should be equal to .modified_when';


--
-- Name: COLUMN log_lnk_loinc2test_panel.orig_by; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_lnk_loinc2test_panel.orig_by IS 'the .modified_by in the original row before the audited action took place, should be equal to .modified_by';


--
-- Name: COLUMN log_lnk_loinc2test_panel.orig_tableoid; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_lnk_loinc2test_panel.orig_tableoid IS 'the TG_RELID when the audit trigger was run';


--
-- Name: log_lnk_org_unit2comm; Type: TABLE; Schema: audit; Owner: gm-dbo
--

CREATE TABLE audit.log_lnk_org_unit2comm (
    pk_audit integer DEFAULT nextval('audit.audit_trail_pk_audit_seq'::regclass),
    row_version integer,
    modified_when timestamp with time zone,
    modified_by name,
    pk integer,
    fk_org_unit integer,
    url text,
    fk_type integer,
    is_confidential boolean,
    comment text
)
INHERITS (audit.audit_trail);


ALTER TABLE audit.log_lnk_org_unit2comm OWNER TO "gm-dbo";

--
-- Name: COLUMN log_lnk_org_unit2comm.orig_version; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_lnk_org_unit2comm.orig_version IS 'the .row_version in the original row before the audited action took place, should be equal to .row_version';


--
-- Name: COLUMN log_lnk_org_unit2comm.orig_when; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_lnk_org_unit2comm.orig_when IS 'the .modified_when in the original row before the audited action took place, should be equal to .modified_when';


--
-- Name: COLUMN log_lnk_org_unit2comm.orig_by; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_lnk_org_unit2comm.orig_by IS 'the .modified_by in the original row before the audited action took place, should be equal to .modified_by';


--
-- Name: COLUMN log_lnk_org_unit2comm.orig_tableoid; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_lnk_org_unit2comm.orig_tableoid IS 'the TG_RELID when the audit trigger was run';


--
-- Name: log_lnk_org_unit2ext_id; Type: TABLE; Schema: audit; Owner: gm-dbo
--

CREATE TABLE audit.log_lnk_org_unit2ext_id (
    pk_audit integer DEFAULT nextval('audit.audit_trail_pk_audit_seq'::regclass),
    row_version integer,
    modified_when timestamp with time zone,
    modified_by name,
    pk integer,
    fk_org_unit integer,
    external_id text,
    fk_type integer,
    comment text
)
INHERITS (audit.audit_trail);


ALTER TABLE audit.log_lnk_org_unit2ext_id OWNER TO "gm-dbo";

--
-- Name: COLUMN log_lnk_org_unit2ext_id.orig_version; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_lnk_org_unit2ext_id.orig_version IS 'the .row_version in the original row before the audited action took place, should be equal to .row_version';


--
-- Name: COLUMN log_lnk_org_unit2ext_id.orig_when; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_lnk_org_unit2ext_id.orig_when IS 'the .modified_when in the original row before the audited action took place, should be equal to .modified_when';


--
-- Name: COLUMN log_lnk_org_unit2ext_id.orig_by; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_lnk_org_unit2ext_id.orig_by IS 'the .modified_by in the original row before the audited action took place, should be equal to .modified_by';


--
-- Name: COLUMN log_lnk_org_unit2ext_id.orig_tableoid; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_lnk_org_unit2ext_id.orig_tableoid IS 'the TG_RELID when the audit trigger was run';


--
-- Name: log_lnk_pat2vaccination_course; Type: TABLE; Schema: audit; Owner: gm-dbo
--

CREATE TABLE audit.log_lnk_pat2vaccination_course (
    pk_audit integer DEFAULT nextval('audit.audit_trail_pk_audit_seq'::regclass),
    row_version integer,
    modified_when timestamp with time zone,
    modified_by name,
    pk integer,
    fk_patient integer,
    fk_course integer
)
INHERITS (audit.audit_trail);


ALTER TABLE audit.log_lnk_pat2vaccination_course OWNER TO "gm-dbo";

--
-- Name: COLUMN log_lnk_pat2vaccination_course.orig_version; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_lnk_pat2vaccination_course.orig_version IS 'the .row_version in the original row before the audited action took place, should be equal to .row_version';


--
-- Name: COLUMN log_lnk_pat2vaccination_course.orig_when; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_lnk_pat2vaccination_course.orig_when IS 'the .modified_when in the original row before the audited action took place, should be equal to .modified_when';


--
-- Name: COLUMN log_lnk_pat2vaccination_course.orig_by; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_lnk_pat2vaccination_course.orig_by IS 'the .modified_by in the original row before the audited action took place, should be equal to .modified_by';


--
-- Name: COLUMN log_lnk_pat2vaccination_course.orig_tableoid; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_lnk_pat2vaccination_course.orig_tableoid IS 'the TG_RELID when the audit trigger was run';


--
-- Name: log_lnk_person2relative; Type: TABLE; Schema: audit; Owner: gm-dbo
--

CREATE TABLE audit.log_lnk_person2relative (
    pk_audit integer DEFAULT nextval('audit.audit_trail_pk_audit_seq'::regclass),
    row_version integer,
    modified_when timestamp with time zone,
    modified_by name,
    id integer,
    id_identity integer,
    id_relative integer,
    id_relation_type integer,
    started date,
    ended date
)
INHERITS (audit.audit_trail);


ALTER TABLE audit.log_lnk_person2relative OWNER TO "gm-dbo";

--
-- Name: COLUMN log_lnk_person2relative.orig_version; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_lnk_person2relative.orig_version IS 'the .row_version in the original row before the audited action took place, should be equal to .row_version';


--
-- Name: COLUMN log_lnk_person2relative.orig_when; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_lnk_person2relative.orig_when IS 'the .modified_when in the original row before the audited action took place, should be equal to .modified_when';


--
-- Name: COLUMN log_lnk_person2relative.orig_by; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_lnk_person2relative.orig_by IS 'the .modified_by in the original row before the audited action took place, should be equal to .modified_by';


--
-- Name: COLUMN log_lnk_person2relative.orig_tableoid; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_lnk_person2relative.orig_tableoid IS 'the TG_RELID when the audit trigger was run';


--
-- Name: log_lnk_substance2brand; Type: TABLE; Schema: audit; Owner: gm-dbo
--

CREATE TABLE audit.log_lnk_substance2brand (
    pk_audit integer DEFAULT nextval('audit.audit_trail_pk_audit_seq'::regclass),
    row_version integer,
    modified_when timestamp with time zone,
    modified_by name,
    pk integer,
    fk_brand integer,
    fk_substance integer
)
INHERITS (audit.audit_trail);


ALTER TABLE audit.log_lnk_substance2brand OWNER TO "gm-dbo";

--
-- Name: COLUMN log_lnk_substance2brand.orig_version; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_lnk_substance2brand.orig_version IS 'the .row_version in the original row before the audited action took place, should be equal to .row_version';


--
-- Name: COLUMN log_lnk_substance2brand.orig_when; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_lnk_substance2brand.orig_when IS 'the .modified_when in the original row before the audited action took place, should be equal to .modified_when';


--
-- Name: COLUMN log_lnk_substance2brand.orig_by; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_lnk_substance2brand.orig_by IS 'the .modified_by in the original row before the audited action took place, should be equal to .modified_by';


--
-- Name: COLUMN log_lnk_substance2brand.orig_tableoid; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_lnk_substance2brand.orig_tableoid IS 'the TG_RELID when the audit trigger was run';


--
-- Name: log_lnk_tst2norm; Type: TABLE; Schema: audit; Owner: gm-dbo
--

CREATE TABLE audit.log_lnk_tst2norm (
    pk_audit integer DEFAULT nextval('audit.audit_trail_pk_audit_seq'::regclass),
    row_version integer,
    modified_when timestamp with time zone,
    modified_by name,
    id integer,
    id_test integer,
    id_norm integer
)
INHERITS (audit.audit_trail);


ALTER TABLE audit.log_lnk_tst2norm OWNER TO "gm-dbo";

--
-- Name: COLUMN log_lnk_tst2norm.orig_version; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_lnk_tst2norm.orig_version IS 'the .row_version in the original row before the audited action took place, should be equal to .row_version';


--
-- Name: COLUMN log_lnk_tst2norm.orig_when; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_lnk_tst2norm.orig_when IS 'the .modified_when in the original row before the audited action took place, should be equal to .modified_when';


--
-- Name: COLUMN log_lnk_tst2norm.orig_by; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_lnk_tst2norm.orig_by IS 'the .modified_by in the original row before the audited action took place, should be equal to .modified_by';


--
-- Name: COLUMN log_lnk_tst2norm.orig_tableoid; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_lnk_tst2norm.orig_tableoid IS 'the TG_RELID when the audit trigger was run';


--
-- Name: log_lnk_type2item; Type: TABLE; Schema: audit; Owner: gm-dbo
--

CREATE TABLE audit.log_lnk_type2item (
    pk_audit integer DEFAULT nextval('audit.audit_trail_pk_audit_seq'::regclass),
    row_version integer,
    modified_when timestamp with time zone,
    modified_by name,
    pk integer,
    fk_type integer,
    fk_item integer
)
INHERITS (audit.audit_trail);


ALTER TABLE audit.log_lnk_type2item OWNER TO "gm-dbo";

--
-- Name: COLUMN log_lnk_type2item.orig_version; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_lnk_type2item.orig_version IS 'the .row_version in the original row before the audited action took place, should be equal to .row_version';


--
-- Name: COLUMN log_lnk_type2item.orig_when; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_lnk_type2item.orig_when IS 'the .modified_when in the original row before the audited action took place, should be equal to .modified_when';


--
-- Name: COLUMN log_lnk_type2item.orig_by; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_lnk_type2item.orig_by IS 'the .modified_by in the original row before the audited action took place, should be equal to .modified_by';


--
-- Name: COLUMN log_lnk_type2item.orig_tableoid; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_lnk_type2item.orig_tableoid IS 'the TG_RELID when the audit trigger was run';


--
-- Name: log_lnk_vaccination_course2schedule; Type: TABLE; Schema: audit; Owner: gm-dbo
--

CREATE TABLE audit.log_lnk_vaccination_course2schedule (
    pk_audit integer DEFAULT nextval('audit.audit_trail_pk_audit_seq'::regclass),
    row_version integer,
    modified_when timestamp with time zone,
    modified_by name,
    pk integer,
    fk_course integer,
    fk_schedule integer
)
INHERITS (audit.audit_trail);


ALTER TABLE audit.log_lnk_vaccination_course2schedule OWNER TO "gm-dbo";

--
-- Name: COLUMN log_lnk_vaccination_course2schedule.orig_version; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_lnk_vaccination_course2schedule.orig_version IS 'the .row_version in the original row before the audited action took place, should be equal to .row_version';


--
-- Name: COLUMN log_lnk_vaccination_course2schedule.orig_when; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_lnk_vaccination_course2schedule.orig_when IS 'the .modified_when in the original row before the audited action took place, should be equal to .modified_when';


--
-- Name: COLUMN log_lnk_vaccination_course2schedule.orig_by; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_lnk_vaccination_course2schedule.orig_by IS 'the .modified_by in the original row before the audited action took place, should be equal to .modified_by';


--
-- Name: COLUMN log_lnk_vaccination_course2schedule.orig_tableoid; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_lnk_vaccination_course2schedule.orig_tableoid IS 'the TG_RELID when the audit trigger was run';


--
-- Name: log_lnk_vaccine2inds; Type: TABLE; Schema: audit; Owner: gm-dbo
--

CREATE TABLE audit.log_lnk_vaccine2inds (
    pk_audit integer DEFAULT nextval('audit.audit_trail_pk_audit_seq'::regclass),
    row_version integer,
    modified_when timestamp with time zone,
    modified_by name,
    id integer,
    fk_vaccine integer,
    fk_indication integer
)
INHERITS (audit.audit_trail);


ALTER TABLE audit.log_lnk_vaccine2inds OWNER TO "gm-dbo";

--
-- Name: COLUMN log_lnk_vaccine2inds.orig_version; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_lnk_vaccine2inds.orig_version IS 'the .row_version in the original row before the audited action took place, should be equal to .row_version';


--
-- Name: COLUMN log_lnk_vaccine2inds.orig_when; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_lnk_vaccine2inds.orig_when IS 'the .modified_when in the original row before the audited action took place, should be equal to .modified_when';


--
-- Name: COLUMN log_lnk_vaccine2inds.orig_by; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_lnk_vaccine2inds.orig_by IS 'the .modified_by in the original row before the audited action took place, should be equal to .modified_by';


--
-- Name: COLUMN log_lnk_vaccine2inds.orig_tableoid; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_lnk_vaccine2inds.orig_tableoid IS 'the TG_RELID when the audit trigger was run';


--
-- Name: log_message_inbox; Type: TABLE; Schema: audit; Owner: gm-dbo
--

CREATE TABLE audit.log_message_inbox (
    pk_audit integer DEFAULT nextval('audit.audit_trail_pk_audit_seq'::regclass),
    row_version integer,
    modified_when timestamp with time zone,
    modified_by name,
    pk integer,
    fk_staff integer,
    fk_inbox_item_type integer,
    comment text,
    data text,
    importance smallint,
    fk_patient integer,
    ufk_context integer[],
    due_date date,
    expiry_date date
)
INHERITS (audit.audit_trail);


ALTER TABLE audit.log_message_inbox OWNER TO "gm-dbo";

--
-- Name: COLUMN log_message_inbox.orig_version; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_message_inbox.orig_version IS 'the .row_version in the original row before the audited action took place, should be equal to .row_version';


--
-- Name: COLUMN log_message_inbox.orig_when; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_message_inbox.orig_when IS 'the .modified_when in the original row before the audited action took place, should be equal to .modified_when';


--
-- Name: COLUMN log_message_inbox.orig_by; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_message_inbox.orig_by IS 'the .modified_by in the original row before the audited action took place, should be equal to .modified_by';


--
-- Name: COLUMN log_message_inbox.orig_tableoid; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_message_inbox.orig_tableoid IS 'the TG_RELID when the audit trigger was run';


--
-- Name: log_occupation; Type: TABLE; Schema: audit; Owner: gm-dbo
--

CREATE TABLE audit.log_occupation (
    pk_audit integer DEFAULT nextval('audit.audit_trail_pk_audit_seq'::regclass),
    row_version integer,
    modified_when timestamp with time zone,
    modified_by name,
    id integer,
    name text
)
INHERITS (audit.audit_trail);


ALTER TABLE audit.log_occupation OWNER TO "gm-dbo";

--
-- Name: COLUMN log_occupation.orig_version; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_occupation.orig_version IS 'the .row_version in the original row before the audited action took place, should be equal to .row_version';


--
-- Name: COLUMN log_occupation.orig_when; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_occupation.orig_when IS 'the .modified_when in the original row before the audited action took place, should be equal to .modified_when';


--
-- Name: COLUMN log_occupation.orig_by; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_occupation.orig_by IS 'the .modified_by in the original row before the audited action took place, should be equal to .modified_by';


--
-- Name: COLUMN log_occupation.orig_tableoid; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_occupation.orig_tableoid IS 'the TG_RELID when the audit trigger was run';


--
-- Name: log_operation; Type: TABLE; Schema: audit; Owner: gm-dbo
--

CREATE TABLE audit.log_operation (
    pk_audit integer DEFAULT nextval('audit.audit_trail_pk_audit_seq'::regclass),
    row_version integer,
    modified_when timestamp with time zone,
    modified_by name,
    pk integer,
    fk_health_issue integer,
    fk_encounter integer,
    clin_where text,
    comment text
)
INHERITS (audit.audit_trail);


ALTER TABLE audit.log_operation OWNER TO "gm-dbo";

--
-- Name: COLUMN log_operation.orig_version; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_operation.orig_version IS 'the .row_version in the original row before the audited action took place, should be equal to .row_version';


--
-- Name: COLUMN log_operation.orig_when; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_operation.orig_when IS 'the .modified_when in the original row before the audited action took place, should be equal to .modified_when';


--
-- Name: COLUMN log_operation.orig_by; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_operation.orig_by IS 'the .modified_by in the original row before the audited action took place, should be equal to .modified_by';


--
-- Name: COLUMN log_operation.orig_tableoid; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_operation.orig_tableoid IS 'the TG_RELID when the audit trigger was run';


--
-- Name: log_org; Type: TABLE; Schema: audit; Owner: gm-dbo
--

CREATE TABLE audit.log_org (
    pk_audit integer DEFAULT nextval('audit.audit_trail_pk_audit_seq'::regclass),
    row_version integer,
    modified_when timestamp with time zone,
    modified_by name,
    pk integer,
    description text,
    fk_category integer,
    fk_data_source integer
)
INHERITS (audit.audit_trail);


ALTER TABLE audit.log_org OWNER TO "gm-dbo";

--
-- Name: COLUMN log_org.orig_version; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_org.orig_version IS 'the .row_version in the original row before the audited action took place, should be equal to .row_version';


--
-- Name: COLUMN log_org.orig_when; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_org.orig_when IS 'the .modified_when in the original row before the audited action took place, should be equal to .modified_when';


--
-- Name: COLUMN log_org.orig_by; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_org.orig_by IS 'the .modified_by in the original row before the audited action took place, should be equal to .modified_by';


--
-- Name: COLUMN log_org.orig_tableoid; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_org.orig_tableoid IS 'the TG_RELID when the audit trigger was run';


--
-- Name: log_org_unit; Type: TABLE; Schema: audit; Owner: gm-dbo
--

CREATE TABLE audit.log_org_unit (
    pk_audit integer DEFAULT nextval('audit.audit_trail_pk_audit_seq'::regclass),
    row_version integer,
    modified_when timestamp with time zone,
    modified_by name,
    pk integer,
    description text,
    fk_org integer,
    fk_address integer,
    fk_category integer
)
INHERITS (audit.audit_trail);


ALTER TABLE audit.log_org_unit OWNER TO "gm-dbo";

--
-- Name: COLUMN log_org_unit.orig_version; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_org_unit.orig_version IS 'the .row_version in the original row before the audited action took place, should be equal to .row_version';


--
-- Name: COLUMN log_org_unit.orig_when; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_org_unit.orig_when IS 'the .modified_when in the original row before the audited action took place, should be equal to .modified_when';


--
-- Name: COLUMN log_org_unit.orig_by; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_org_unit.orig_by IS 'the .modified_by in the original row before the audited action took place, should be equal to .modified_by';


--
-- Name: COLUMN log_org_unit.orig_tableoid; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_org_unit.orig_tableoid IS 'the TG_RELID when the audit trigger was run';


--
-- Name: log_paperwork_templates; Type: TABLE; Schema: audit; Owner: gm-dbo
--

CREATE TABLE audit.log_paperwork_templates (
    pk_audit integer DEFAULT nextval('audit.audit_trail_pk_audit_seq'::regclass),
    row_version integer,
    modified_when timestamp with time zone,
    modified_by name,
    pk integer,
    fk_template_type integer,
    instance_type text,
    name_short text,
    name_long text,
    external_version text,
    gnumed_revision double precision,
    engine text,
    in_use boolean,
    filename text,
    data bytea,
    edit_after_substitution boolean
)
INHERITS (audit.audit_trail);


ALTER TABLE audit.log_paperwork_templates OWNER TO "gm-dbo";

--
-- Name: COLUMN log_paperwork_templates.orig_version; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_paperwork_templates.orig_version IS 'the .row_version in the original row before the audited action took place, should be equal to .row_version';


--
-- Name: COLUMN log_paperwork_templates.orig_when; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_paperwork_templates.orig_when IS 'the .modified_when in the original row before the audited action took place, should be equal to .modified_when';


--
-- Name: COLUMN log_paperwork_templates.orig_by; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_paperwork_templates.orig_by IS 'the .modified_by in the original row before the audited action took place, should be equal to .modified_by';


--
-- Name: COLUMN log_paperwork_templates.orig_tableoid; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_paperwork_templates.orig_tableoid IS 'the TG_RELID when the audit trigger was run';


--
-- Name: log_patient; Type: TABLE; Schema: audit; Owner: gm-dbo
--

CREATE TABLE audit.log_patient (
    pk_audit integer DEFAULT nextval('audit.audit_trail_pk_audit_seq'::regclass),
    row_version integer,
    modified_when timestamp with time zone,
    modified_by name,
    pk integer,
    fk_identity integer,
    edc date
)
INHERITS (audit.audit_trail);


ALTER TABLE audit.log_patient OWNER TO "gm-dbo";

--
-- Name: COLUMN log_patient.orig_version; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_patient.orig_version IS 'the .row_version in the original row before the audited action took place, should be equal to .row_version';


--
-- Name: COLUMN log_patient.orig_when; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_patient.orig_when IS 'the .modified_when in the original row before the audited action took place, should be equal to .modified_when';


--
-- Name: COLUMN log_patient.orig_by; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_patient.orig_by IS 'the .modified_by in the original row before the audited action took place, should be equal to .modified_by';


--
-- Name: COLUMN log_patient.orig_tableoid; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_patient.orig_tableoid IS 'the TG_RELID when the audit trigger was run';


--
-- Name: log_prax_geb_paid; Type: TABLE; Schema: audit; Owner: gm-dbo
--

CREATE TABLE audit.log_prax_geb_paid (
    pk_audit integer DEFAULT nextval('audit.audit_trail_pk_audit_seq'::regclass),
    row_version integer,
    modified_when timestamp with time zone,
    modified_by name,
    pk integer,
    fk_fall integer,
    paid_amount numeric,
    paid_when date,
    paid_with integer
)
INHERITS (audit.audit_trail);


ALTER TABLE audit.log_prax_geb_paid OWNER TO "gm-dbo";

--
-- Name: COLUMN log_prax_geb_paid.orig_version; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_prax_geb_paid.orig_version IS 'the .row_version in the original row before the audited action took place, should be equal to .row_version';


--
-- Name: COLUMN log_prax_geb_paid.orig_when; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_prax_geb_paid.orig_when IS 'the .modified_when in the original row before the audited action took place, should be equal to .modified_when';


--
-- Name: COLUMN log_prax_geb_paid.orig_by; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_prax_geb_paid.orig_by IS 'the .modified_by in the original row before the audited action took place, should be equal to .modified_by';


--
-- Name: COLUMN log_prax_geb_paid.orig_tableoid; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_prax_geb_paid.orig_tableoid IS 'the TG_RELID when the audit trigger was run';


--
-- Name: log_praxis_branch; Type: TABLE; Schema: audit; Owner: gm-dbo
--

CREATE TABLE audit.log_praxis_branch (
    pk_audit integer DEFAULT nextval('audit.audit_trail_pk_audit_seq'::regclass),
    row_version integer,
    modified_when timestamp with time zone,
    modified_by name,
    pk integer,
    fk_org_unit integer
)
INHERITS (audit.audit_trail);


ALTER TABLE audit.log_praxis_branch OWNER TO "gm-dbo";

--
-- Name: COLUMN log_praxis_branch.orig_version; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_praxis_branch.orig_version IS 'the .row_version in the original row before the audited action took place, should be equal to .row_version';


--
-- Name: COLUMN log_praxis_branch.orig_when; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_praxis_branch.orig_when IS 'the .modified_when in the original row before the audited action took place, should be equal to .modified_when';


--
-- Name: COLUMN log_praxis_branch.orig_by; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_praxis_branch.orig_by IS 'the .modified_by in the original row before the audited action took place, should be equal to .modified_by';


--
-- Name: COLUMN log_praxis_branch.orig_tableoid; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_praxis_branch.orig_tableoid IS 'the TG_RELID when the audit trigger was run';


--
-- Name: log_procedure; Type: TABLE; Schema: audit; Owner: gm-dbo
--

CREATE TABLE audit.log_procedure (
    pk_audit integer DEFAULT nextval('audit.audit_trail_pk_audit_seq'::regclass),
    row_version integer,
    modified_when timestamp with time zone,
    modified_by name,
    pk_item integer,
    clin_when timestamp with time zone,
    fk_encounter integer,
    fk_episode integer,
    narrative text,
    soap_cat text,
    pk integer,
    clin_where text,
    fk_hospital_stay integer,
    clin_end timestamp with time zone,
    is_ongoing boolean,
    fk_org_unit integer,
    fk_doc integer,
    comment text
)
INHERITS (audit.audit_trail);


ALTER TABLE audit.log_procedure OWNER TO "gm-dbo";

--
-- Name: COLUMN log_procedure.orig_version; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_procedure.orig_version IS 'the .row_version in the original row before the audited action took place, should be equal to .row_version';


--
-- Name: COLUMN log_procedure.orig_when; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_procedure.orig_when IS 'the .modified_when in the original row before the audited action took place, should be equal to .modified_when';


--
-- Name: COLUMN log_procedure.orig_by; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_procedure.orig_by IS 'the .modified_by in the original row before the audited action took place, should be equal to .modified_by';


--
-- Name: COLUMN log_procedure.orig_tableoid; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_procedure.orig_tableoid IS 'the TG_RELID when the audit trigger was run';


--
-- Name: log_referral; Type: TABLE; Schema: audit; Owner: gm-dbo
--

CREATE TABLE audit.log_referral (
    pk_audit integer DEFAULT nextval('audit.audit_trail_pk_audit_seq'::regclass),
    row_version integer,
    modified_when timestamp with time zone,
    modified_by name,
    pk_item integer,
    clin_when timestamp with time zone,
    fk_encounter integer,
    fk_episode integer,
    narrative text,
    soap_cat text,
    id integer,
    fk_referee integer,
    fk_form integer
)
INHERITS (audit.audit_trail);


ALTER TABLE audit.log_referral OWNER TO "gm-dbo";

--
-- Name: COLUMN log_referral.orig_version; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_referral.orig_version IS 'the .row_version in the original row before the audited action took place, should be equal to .row_version';


--
-- Name: COLUMN log_referral.orig_when; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_referral.orig_when IS 'the .modified_when in the original row before the audited action took place, should be equal to .modified_when';


--
-- Name: COLUMN log_referral.orig_by; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_referral.orig_by IS 'the .modified_by in the original row before the audited action took place, should be equal to .modified_by';


--
-- Name: COLUMN log_referral.orig_tableoid; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_referral.orig_tableoid IS 'the TG_RELID when the audit trigger was run';


--
-- Name: log_region; Type: TABLE; Schema: audit; Owner: gm-dbo
--

CREATE TABLE audit.log_region (
    pk_audit integer DEFAULT nextval('audit.audit_trail_pk_audit_seq'::regclass),
    row_version integer,
    modified_when timestamp with time zone,
    modified_by name,
    pk integer,
    code text,
    country bpchar,
    name text
)
INHERITS (audit.audit_trail);


ALTER TABLE audit.log_region OWNER TO "gm-dbo";

--
-- Name: COLUMN log_region.orig_version; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_region.orig_version IS 'the .row_version in the original row before the audited action took place, should be equal to .row_version';


--
-- Name: COLUMN log_region.orig_when; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_region.orig_when IS 'the .modified_when in the original row before the audited action took place, should be equal to .modified_when';


--
-- Name: COLUMN log_region.orig_by; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_region.orig_by IS 'the .modified_by in the original row before the audited action took place, should be equal to .modified_by';


--
-- Name: COLUMN log_region.orig_tableoid; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_region.orig_tableoid IS 'the TG_RELID when the audit trigger was run';


--
-- Name: log_relation_types; Type: TABLE; Schema: audit; Owner: gm-dbo
--

CREATE TABLE audit.log_relation_types (
    pk_audit integer DEFAULT nextval('audit.audit_trail_pk_audit_seq'::regclass),
    row_version integer,
    modified_when timestamp with time zone,
    modified_by name,
    id integer,
    inverse integer,
    biological boolean,
    biol_verified boolean,
    description text
)
INHERITS (audit.audit_trail);


ALTER TABLE audit.log_relation_types OWNER TO "gm-dbo";

--
-- Name: COLUMN log_relation_types.orig_version; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_relation_types.orig_version IS 'the .row_version in the original row before the audited action took place, should be equal to .row_version';


--
-- Name: COLUMN log_relation_types.orig_when; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_relation_types.orig_when IS 'the .modified_when in the original row before the audited action took place, should be equal to .modified_when';


--
-- Name: COLUMN log_relation_types.orig_by; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_relation_types.orig_by IS 'the .modified_by in the original row before the audited action took place, should be equal to .modified_by';


--
-- Name: COLUMN log_relation_types.orig_tableoid; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_relation_types.orig_tableoid IS 'the TG_RELID when the audit trigger was run';


--
-- Name: log_report_query; Type: TABLE; Schema: audit; Owner: gm-dbo
--

CREATE TABLE audit.log_report_query (
    pk_audit integer DEFAULT nextval('audit.audit_trail_pk_audit_seq'::regclass),
    row_version integer,
    modified_when timestamp with time zone,
    modified_by name,
    pk integer,
    label text,
    cmd text
)
INHERITS (audit.audit_trail);


ALTER TABLE audit.log_report_query OWNER TO "gm-dbo";

--
-- Name: COLUMN log_report_query.orig_version; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_report_query.orig_version IS 'the .row_version in the original row before the audited action took place, should be equal to .row_version';


--
-- Name: COLUMN log_report_query.orig_when; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_report_query.orig_when IS 'the .modified_when in the original row before the audited action took place, should be equal to .modified_when';


--
-- Name: COLUMN log_report_query.orig_by; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_report_query.orig_by IS 'the .modified_by in the original row before the audited action took place, should be equal to .modified_by';


--
-- Name: COLUMN log_report_query.orig_tableoid; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_report_query.orig_tableoid IS 'the TG_RELID when the audit trigger was run';


--
-- Name: log_staff; Type: TABLE; Schema: audit; Owner: gm-dbo
--

CREATE TABLE audit.log_staff (
    pk_audit integer DEFAULT nextval('audit.audit_trail_pk_audit_seq'::regclass),
    row_version integer,
    modified_when timestamp with time zone,
    modified_by name,
    pk integer,
    fk_identity integer,
    db_user name,
    short_alias text,
    comment text,
    is_active boolean
)
INHERITS (audit.audit_trail);


ALTER TABLE audit.log_staff OWNER TO "gm-dbo";

--
-- Name: COLUMN log_staff.orig_version; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_staff.orig_version IS 'the .row_version in the original row before the audited action took place, should be equal to .row_version';


--
-- Name: COLUMN log_staff.orig_when; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_staff.orig_when IS 'the .modified_when in the original row before the audited action took place, should be equal to .modified_when';


--
-- Name: COLUMN log_staff.orig_by; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_staff.orig_by IS 'the .modified_by in the original row before the audited action took place, should be equal to .modified_by';


--
-- Name: COLUMN log_staff.orig_tableoid; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_staff.orig_tableoid IS 'the TG_RELID when the audit trigger was run';


--
-- Name: log_street; Type: TABLE; Schema: audit; Owner: gm-dbo
--

CREATE TABLE audit.log_street (
    pk_audit integer DEFAULT nextval('audit.audit_trail_pk_audit_seq'::regclass),
    row_version integer,
    modified_when timestamp with time zone,
    modified_by name,
    id integer,
    id_urb integer,
    name text,
    postcode text,
    suburb text,
    lat_lon point
)
INHERITS (audit.audit_trail);


ALTER TABLE audit.log_street OWNER TO "gm-dbo";

--
-- Name: COLUMN log_street.orig_version; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_street.orig_version IS 'the .row_version in the original row before the audited action took place, should be equal to .row_version';


--
-- Name: COLUMN log_street.orig_when; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_street.orig_when IS 'the .modified_when in the original row before the audited action took place, should be equal to .modified_when';


--
-- Name: COLUMN log_street.orig_by; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_street.orig_by IS 'the .modified_by in the original row before the audited action took place, should be equal to .modified_by';


--
-- Name: COLUMN log_street.orig_tableoid; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_street.orig_tableoid IS 'the TG_RELID when the audit trigger was run';


--
-- Name: log_substance; Type: TABLE; Schema: audit; Owner: gm-dbo
--

CREATE TABLE audit.log_substance (
    pk integer,
    description text,
    atc text,
    intake_instructions text
)
INHERITS (audit.audit_trail);


ALTER TABLE audit.log_substance OWNER TO "gm-dbo";

--
-- Name: COLUMN log_substance.orig_version; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_substance.orig_version IS 'the .row_version in the original row before the audited action took place, should be equal to .row_version';


--
-- Name: COLUMN log_substance.orig_when; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_substance.orig_when IS 'the .modified_when in the original row before the audited action took place, should be equal to .modified_when';


--
-- Name: COLUMN log_substance.orig_by; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_substance.orig_by IS 'the .modified_by in the original row before the audited action took place, should be equal to .modified_by';


--
-- Name: COLUMN log_substance.orig_tableoid; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_substance.orig_tableoid IS 'the TG_RELID when the audit trigger was run';


--
-- Name: log_substance_in_brand; Type: TABLE; Schema: audit; Owner: gm-dbo
--

CREATE TABLE audit.log_substance_in_brand (
    pk_audit integer DEFAULT nextval('audit.audit_trail_pk_audit_seq'::regclass),
    row_version integer,
    modified_when timestamp with time zone,
    modified_by name,
    pk integer,
    fk_brand integer,
    description text,
    atc_code text
)
INHERITS (audit.audit_trail);


ALTER TABLE audit.log_substance_in_brand OWNER TO "gm-dbo";

--
-- Name: COLUMN log_substance_in_brand.orig_version; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_substance_in_brand.orig_version IS 'the .row_version in the original row before the audited action took place, should be equal to .row_version';


--
-- Name: COLUMN log_substance_in_brand.orig_when; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_substance_in_brand.orig_when IS 'the .modified_when in the original row before the audited action took place, should be equal to .modified_when';


--
-- Name: COLUMN log_substance_in_brand.orig_by; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_substance_in_brand.orig_by IS 'the .modified_by in the original row before the audited action took place, should be equal to .modified_by';


--
-- Name: COLUMN log_substance_in_brand.orig_tableoid; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_substance_in_brand.orig_tableoid IS 'the TG_RELID when the audit trigger was run';


--
-- Name: log_substance_intake; Type: TABLE; Schema: audit; Owner: gm-dbo
--

CREATE TABLE audit.log_substance_intake (
    pk_audit integer DEFAULT nextval('audit.audit_trail_pk_audit_seq'::regclass),
    row_version integer,
    modified_when timestamp with time zone,
    modified_by name,
    pk_item integer,
    clin_when timestamp with time zone,
    fk_encounter integer,
    fk_episode integer,
    narrative text,
    soap_cat text,
    pk integer,
    fk_substance integer,
    preparation text,
    schedule text,
    aim text,
    duration interval,
    intake_is_approved_of boolean,
    is_long_term boolean,
    discontinued timestamp with time zone,
    discontinue_reason text,
    fk_drug_component integer,
    comment_on_start text,
    harmful_use_type integer
)
INHERITS (audit.audit_trail);


ALTER TABLE audit.log_substance_intake OWNER TO "gm-dbo";

--
-- Name: COLUMN log_substance_intake.orig_version; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_substance_intake.orig_version IS 'the .row_version in the original row before the audited action took place, should be equal to .row_version';


--
-- Name: COLUMN log_substance_intake.orig_when; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_substance_intake.orig_when IS 'the .modified_when in the original row before the audited action took place, should be equal to .modified_when';


--
-- Name: COLUMN log_substance_intake.orig_by; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_substance_intake.orig_by IS 'the .modified_by in the original row before the audited action took place, should be equal to .modified_by';


--
-- Name: COLUMN log_substance_intake.orig_tableoid; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_substance_intake.orig_tableoid IS 'the TG_RELID when the audit trigger was run';


--
-- Name: log_suppressed_hint; Type: TABLE; Schema: audit; Owner: gm-dbo
--

CREATE TABLE audit.log_suppressed_hint (
    pk_audit integer DEFAULT nextval('audit.audit_trail_pk_audit_seq'::regclass),
    row_version integer,
    modified_when timestamp with time zone,
    modified_by name,
    pk integer,
    fk_encounter integer,
    fk_hint integer,
    suppressed_by name,
    suppressed_when timestamp with time zone,
    rationale text,
    md5_sum text
)
INHERITS (audit.audit_trail);


ALTER TABLE audit.log_suppressed_hint OWNER TO "gm-dbo";

--
-- Name: COLUMN log_suppressed_hint.orig_version; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_suppressed_hint.orig_version IS 'the .row_version in the original row before the audited action took place, should be equal to .row_version';


--
-- Name: COLUMN log_suppressed_hint.orig_when; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_suppressed_hint.orig_when IS 'the .modified_when in the original row before the audited action took place, should be equal to .modified_when';


--
-- Name: COLUMN log_suppressed_hint.orig_by; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_suppressed_hint.orig_by IS 'the .modified_by in the original row before the audited action took place, should be equal to .modified_by';


--
-- Name: COLUMN log_suppressed_hint.orig_tableoid; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_suppressed_hint.orig_tableoid IS 'the TG_RELID when the audit trigger was run';


--
-- Name: log_tag_image; Type: TABLE; Schema: audit; Owner: gm-dbo
--

CREATE TABLE audit.log_tag_image (
    pk_audit integer DEFAULT nextval('audit.audit_trail_pk_audit_seq'::regclass),
    row_version integer,
    modified_when timestamp with time zone,
    modified_by name,
    pk integer,
    description text,
    filename text,
    image bytea
)
INHERITS (audit.audit_trail);


ALTER TABLE audit.log_tag_image OWNER TO "gm-dbo";

--
-- Name: COLUMN log_tag_image.orig_version; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_tag_image.orig_version IS 'the .row_version in the original row before the audited action took place, should be equal to .row_version';


--
-- Name: COLUMN log_tag_image.orig_when; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_tag_image.orig_when IS 'the .modified_when in the original row before the audited action took place, should be equal to .modified_when';


--
-- Name: COLUMN log_tag_image.orig_by; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_tag_image.orig_by IS 'the .modified_by in the original row before the audited action took place, should be equal to .modified_by';


--
-- Name: COLUMN log_tag_image.orig_tableoid; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_tag_image.orig_tableoid IS 'the TG_RELID when the audit trigger was run';


--
-- Name: log_test_org; Type: TABLE; Schema: audit; Owner: gm-dbo
--

CREATE TABLE audit.log_test_org (
    pk_audit integer DEFAULT nextval('audit.audit_trail_pk_audit_seq'::regclass),
    row_version integer,
    modified_when timestamp with time zone,
    modified_by name,
    pk integer,
    fk_org_unit integer,
    fk_adm_contact integer,
    fk_med_contact integer,
    comment text,
    contact text
)
INHERITS (audit.audit_trail);


ALTER TABLE audit.log_test_org OWNER TO "gm-dbo";

--
-- Name: COLUMN log_test_org.orig_version; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_test_org.orig_version IS 'the .row_version in the original row before the audited action took place, should be equal to .row_version';


--
-- Name: COLUMN log_test_org.orig_when; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_test_org.orig_when IS 'the .modified_when in the original row before the audited action took place, should be equal to .modified_when';


--
-- Name: COLUMN log_test_org.orig_by; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_test_org.orig_by IS 'the .modified_by in the original row before the audited action took place, should be equal to .modified_by';


--
-- Name: COLUMN log_test_org.orig_tableoid; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_test_org.orig_tableoid IS 'the TG_RELID when the audit trigger was run';


--
-- Name: log_test_panel; Type: TABLE; Schema: audit; Owner: gm-dbo
--

CREATE TABLE audit.log_test_panel (
    pk_audit integer DEFAULT nextval('audit.audit_trail_pk_audit_seq'::regclass),
    row_version integer,
    modified_when timestamp with time zone,
    modified_by name,
    pk integer,
    description text,
    comment text,
    fk_test_types integer[]
)
INHERITS (audit.audit_trail);


ALTER TABLE audit.log_test_panel OWNER TO "gm-dbo";

--
-- Name: COLUMN log_test_panel.orig_version; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_test_panel.orig_version IS 'the .row_version in the original row before the audited action took place, should be equal to .row_version';


--
-- Name: COLUMN log_test_panel.orig_when; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_test_panel.orig_when IS 'the .modified_when in the original row before the audited action took place, should be equal to .modified_when';


--
-- Name: COLUMN log_test_panel.orig_by; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_test_panel.orig_by IS 'the .modified_by in the original row before the audited action took place, should be equal to .modified_by';


--
-- Name: COLUMN log_test_panel.orig_tableoid; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_test_panel.orig_tableoid IS 'the TG_RELID when the audit trigger was run';


--
-- Name: log_test_result; Type: TABLE; Schema: audit; Owner: gm-dbo
--

CREATE TABLE audit.log_test_result (
    pk_audit integer DEFAULT nextval('audit.audit_trail_pk_audit_seq'::regclass),
    row_version integer,
    modified_when timestamp with time zone,
    modified_by name,
    pk_item integer,
    clin_when timestamp with time zone,
    fk_encounter integer,
    fk_episode integer,
    narrative text,
    soap_cat text,
    pk integer,
    fk_type integer,
    val_num numeric,
    val_alpha text,
    val_unit text,
    val_normal_min numeric,
    val_normal_max numeric,
    val_normal_range text,
    val_target_min numeric,
    val_target_max numeric,
    val_target_range text,
    abnormality_indicator text,
    norm_ref_group text,
    note_test_org text,
    material text,
    material_detail text,
    fk_intended_reviewer integer,
    fk_request integer,
    status text,
    source_data text,
    val_grouping text
)
INHERITS (audit.audit_trail);


ALTER TABLE audit.log_test_result OWNER TO "gm-dbo";

--
-- Name: COLUMN log_test_result.orig_version; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_test_result.orig_version IS 'the .row_version in the original row before the audited action took place, should be equal to .row_version';


--
-- Name: COLUMN log_test_result.orig_when; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_test_result.orig_when IS 'the .modified_when in the original row before the audited action took place, should be equal to .modified_when';


--
-- Name: COLUMN log_test_result.orig_by; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_test_result.orig_by IS 'the .modified_by in the original row before the audited action took place, should be equal to .modified_by';


--
-- Name: COLUMN log_test_result.orig_tableoid; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_test_result.orig_tableoid IS 'the TG_RELID when the audit trigger was run';


--
-- Name: log_test_type; Type: TABLE; Schema: audit; Owner: gm-dbo
--

CREATE TABLE audit.log_test_type (
    pk_audit integer DEFAULT nextval('audit.audit_trail_pk_audit_seq'::regclass),
    row_version integer,
    modified_when timestamp with time zone,
    modified_by name,
    pk integer,
    fk_test_org integer,
    name text,
    comment text,
    reference_unit text,
    loinc text,
    abbrev text,
    fk_meta_test_type integer
)
INHERITS (audit.audit_trail);


ALTER TABLE audit.log_test_type OWNER TO "gm-dbo";

--
-- Name: COLUMN log_test_type.orig_version; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_test_type.orig_version IS 'the .row_version in the original row before the audited action took place, should be equal to .row_version';


--
-- Name: COLUMN log_test_type.orig_when; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_test_type.orig_when IS 'the .modified_when in the original row before the audited action took place, should be equal to .modified_when';


--
-- Name: COLUMN log_test_type.orig_by; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_test_type.orig_by IS 'the .modified_by in the original row before the audited action took place, should be equal to .modified_by';


--
-- Name: COLUMN log_test_type.orig_tableoid; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_test_type.orig_tableoid IS 'the TG_RELID when the audit trigger was run';


--
-- Name: log_urb; Type: TABLE; Schema: audit; Owner: gm-dbo
--

CREATE TABLE audit.log_urb (
    pk_audit integer DEFAULT nextval('audit.audit_trail_pk_audit_seq'::regclass),
    row_version integer,
    modified_when timestamp with time zone,
    modified_by name,
    id integer,
    fk_region integer,
    postcode text,
    lat_lon point,
    name text
)
INHERITS (audit.audit_trail);


ALTER TABLE audit.log_urb OWNER TO "gm-dbo";

--
-- Name: COLUMN log_urb.orig_version; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_urb.orig_version IS 'the .row_version in the original row before the audited action took place, should be equal to .row_version';


--
-- Name: COLUMN log_urb.orig_when; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_urb.orig_when IS 'the .modified_when in the original row before the audited action took place, should be equal to .modified_when';


--
-- Name: COLUMN log_urb.orig_by; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_urb.orig_by IS 'the .modified_by in the original row before the audited action took place, should be equal to .modified_by';


--
-- Name: COLUMN log_urb.orig_tableoid; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_urb.orig_tableoid IS 'the TG_RELID when the audit trigger was run';


--
-- Name: log_vacc_indication; Type: TABLE; Schema: audit; Owner: gm-dbo
--

CREATE TABLE audit.log_vacc_indication (
    pk_audit integer DEFAULT nextval('audit.audit_trail_pk_audit_seq'::regclass),
    row_version integer,
    modified_when timestamp with time zone,
    modified_by name,
    id integer,
    description text,
    atcs_single_indication text[],
    atcs_combi_indication text[]
)
INHERITS (audit.audit_trail);


ALTER TABLE audit.log_vacc_indication OWNER TO "gm-dbo";

--
-- Name: COLUMN log_vacc_indication.orig_version; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_vacc_indication.orig_version IS 'the .row_version in the original row before the audited action took place, should be equal to .row_version';


--
-- Name: COLUMN log_vacc_indication.orig_when; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_vacc_indication.orig_when IS 'the .modified_when in the original row before the audited action took place, should be equal to .modified_when';


--
-- Name: COLUMN log_vacc_indication.orig_by; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_vacc_indication.orig_by IS 'the .modified_by in the original row before the audited action took place, should be equal to .modified_by';


--
-- Name: COLUMN log_vacc_indication.orig_tableoid; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_vacc_indication.orig_tableoid IS 'the TG_RELID when the audit trigger was run';


--
-- Name: log_vacc_route; Type: TABLE; Schema: audit; Owner: gm-dbo
--

CREATE TABLE audit.log_vacc_route (
    pk_audit integer DEFAULT nextval('audit.audit_trail_pk_audit_seq'::regclass),
    row_version integer,
    modified_when timestamp with time zone,
    modified_by name,
    id integer,
    abbreviation text,
    description text
)
INHERITS (audit.audit_trail);


ALTER TABLE audit.log_vacc_route OWNER TO "gm-dbo";

--
-- Name: COLUMN log_vacc_route.orig_version; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_vacc_route.orig_version IS 'the .row_version in the original row before the audited action took place, should be equal to .row_version';


--
-- Name: COLUMN log_vacc_route.orig_when; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_vacc_route.orig_when IS 'the .modified_when in the original row before the audited action took place, should be equal to .modified_when';


--
-- Name: COLUMN log_vacc_route.orig_by; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_vacc_route.orig_by IS 'the .modified_by in the original row before the audited action took place, should be equal to .modified_by';


--
-- Name: COLUMN log_vacc_route.orig_tableoid; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_vacc_route.orig_tableoid IS 'the TG_RELID when the audit trigger was run';


--
-- Name: log_vaccination; Type: TABLE; Schema: audit; Owner: gm-dbo
--

CREATE TABLE audit.log_vaccination (
    pk_audit integer DEFAULT nextval('audit.audit_trail_pk_audit_seq'::regclass),
    row_version integer,
    modified_when timestamp with time zone,
    modified_by name,
    pk_item integer,
    clin_when timestamp with time zone,
    fk_encounter integer,
    fk_episode integer,
    narrative text,
    soap_cat text,
    pk integer,
    fk_patient integer,
    fk_provider integer,
    fk_vaccine integer,
    site text,
    batch_no text,
    reaction text
)
INHERITS (audit.audit_trail);


ALTER TABLE audit.log_vaccination OWNER TO "gm-dbo";

--
-- Name: COLUMN log_vaccination.orig_version; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_vaccination.orig_version IS 'the .row_version in the original row before the audited action took place, should be equal to .row_version';


--
-- Name: COLUMN log_vaccination.orig_when; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_vaccination.orig_when IS 'the .modified_when in the original row before the audited action took place, should be equal to .modified_when';


--
-- Name: COLUMN log_vaccination.orig_by; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_vaccination.orig_by IS 'the .modified_by in the original row before the audited action took place, should be equal to .modified_by';


--
-- Name: COLUMN log_vaccination.orig_tableoid; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_vaccination.orig_tableoid IS 'the TG_RELID when the audit trigger was run';


--
-- Name: log_vaccination_course; Type: TABLE; Schema: audit; Owner: gm-dbo
--

CREATE TABLE audit.log_vaccination_course (
    pk_audit integer DEFAULT nextval('audit.audit_trail_pk_audit_seq'::regclass),
    row_version integer,
    modified_when timestamp with time zone,
    modified_by name,
    pk integer,
    fk_recommended_by integer,
    fk_indication integer,
    is_active boolean,
    comment text
)
INHERITS (audit.audit_trail);


ALTER TABLE audit.log_vaccination_course OWNER TO "gm-dbo";

--
-- Name: COLUMN log_vaccination_course.orig_version; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_vaccination_course.orig_version IS 'the .row_version in the original row before the audited action took place, should be equal to .row_version';


--
-- Name: COLUMN log_vaccination_course.orig_when; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_vaccination_course.orig_when IS 'the .modified_when in the original row before the audited action took place, should be equal to .modified_when';


--
-- Name: COLUMN log_vaccination_course.orig_by; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_vaccination_course.orig_by IS 'the .modified_by in the original row before the audited action took place, should be equal to .modified_by';


--
-- Name: COLUMN log_vaccination_course.orig_tableoid; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_vaccination_course.orig_tableoid IS 'the TG_RELID when the audit trigger was run';


--
-- Name: log_vaccination_course_constraint; Type: TABLE; Schema: audit; Owner: gm-dbo
--

CREATE TABLE audit.log_vaccination_course_constraint (
    pk_audit integer DEFAULT nextval('audit.audit_trail_pk_audit_seq'::regclass),
    row_version integer,
    modified_when timestamp with time zone,
    modified_by name,
    pk integer,
    description text
)
INHERITS (audit.audit_trail);


ALTER TABLE audit.log_vaccination_course_constraint OWNER TO "gm-dbo";

--
-- Name: COLUMN log_vaccination_course_constraint.orig_version; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_vaccination_course_constraint.orig_version IS 'the .row_version in the original row before the audited action took place, should be equal to .row_version';


--
-- Name: COLUMN log_vaccination_course_constraint.orig_when; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_vaccination_course_constraint.orig_when IS 'the .modified_when in the original row before the audited action took place, should be equal to .modified_when';


--
-- Name: COLUMN log_vaccination_course_constraint.orig_by; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_vaccination_course_constraint.orig_by IS 'the .modified_by in the original row before the audited action took place, should be equal to .modified_by';


--
-- Name: COLUMN log_vaccination_course_constraint.orig_tableoid; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_vaccination_course_constraint.orig_tableoid IS 'the TG_RELID when the audit trigger was run';


--
-- Name: log_vaccination_definition; Type: TABLE; Schema: audit; Owner: gm-dbo
--

CREATE TABLE audit.log_vaccination_definition (
    pk_audit integer DEFAULT nextval('audit.audit_trail_pk_audit_seq'::regclass),
    row_version integer,
    modified_when timestamp with time zone,
    modified_by name,
    id integer,
    fk_course integer,
    is_booster boolean,
    seq_no integer,
    min_age_due interval,
    max_age_due interval,
    min_interval interval,
    comment text
)
INHERITS (audit.audit_trail);


ALTER TABLE audit.log_vaccination_definition OWNER TO "gm-dbo";

--
-- Name: COLUMN log_vaccination_definition.orig_version; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_vaccination_definition.orig_version IS 'the .row_version in the original row before the audited action took place, should be equal to .row_version';


--
-- Name: COLUMN log_vaccination_definition.orig_when; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_vaccination_definition.orig_when IS 'the .modified_when in the original row before the audited action took place, should be equal to .modified_when';


--
-- Name: COLUMN log_vaccination_definition.orig_by; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_vaccination_definition.orig_by IS 'the .modified_by in the original row before the audited action took place, should be equal to .modified_by';


--
-- Name: COLUMN log_vaccination_definition.orig_tableoid; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_vaccination_definition.orig_tableoid IS 'the TG_RELID when the audit trigger was run';


--
-- Name: log_vaccination_schedule; Type: TABLE; Schema: audit; Owner: gm-dbo
--

CREATE TABLE audit.log_vaccination_schedule (
    pk_audit integer DEFAULT nextval('audit.audit_trail_pk_audit_seq'::regclass),
    row_version integer,
    modified_when timestamp with time zone,
    modified_by name,
    pk integer,
    name text,
    comment text
)
INHERITS (audit.audit_trail);


ALTER TABLE audit.log_vaccination_schedule OWNER TO "gm-dbo";

--
-- Name: COLUMN log_vaccination_schedule.orig_version; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_vaccination_schedule.orig_version IS 'the .row_version in the original row before the audited action took place, should be equal to .row_version';


--
-- Name: COLUMN log_vaccination_schedule.orig_when; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_vaccination_schedule.orig_when IS 'the .modified_when in the original row before the audited action took place, should be equal to .modified_when';


--
-- Name: COLUMN log_vaccination_schedule.orig_by; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_vaccination_schedule.orig_by IS 'the .modified_by in the original row before the audited action took place, should be equal to .modified_by';


--
-- Name: COLUMN log_vaccination_schedule.orig_tableoid; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_vaccination_schedule.orig_tableoid IS 'the TG_RELID when the audit trigger was run';


--
-- Name: log_vaccine; Type: TABLE; Schema: audit; Owner: gm-dbo
--

CREATE TABLE audit.log_vaccine (
    pk_audit integer DEFAULT nextval('audit.audit_trail_pk_audit_seq'::regclass),
    row_version integer,
    modified_when timestamp with time zone,
    modified_by name,
    pk integer,
    id_route integer,
    is_live boolean,
    min_age interval,
    max_age interval,
    comment text,
    fk_drug_product integer
)
INHERITS (audit.audit_trail);


ALTER TABLE audit.log_vaccine OWNER TO "gm-dbo";

--
-- Name: COLUMN log_vaccine.orig_version; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_vaccine.orig_version IS 'the .row_version in the original row before the audited action took place, should be equal to .row_version';


--
-- Name: COLUMN log_vaccine.orig_when; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_vaccine.orig_when IS 'the .modified_when in the original row before the audited action took place, should be equal to .modified_when';


--
-- Name: COLUMN log_vaccine.orig_by; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_vaccine.orig_by IS 'the .modified_by in the original row before the audited action took place, should be equal to .modified_by';


--
-- Name: COLUMN log_vaccine.orig_tableoid; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_vaccine.orig_tableoid IS 'the TG_RELID when the audit trigger was run';


--
-- Name: log_vaccine_batches; Type: TABLE; Schema: audit; Owner: gm-dbo
--

CREATE TABLE audit.log_vaccine_batches (
    pk_audit integer DEFAULT nextval('audit.audit_trail_pk_audit_seq'::regclass),
    row_version integer,
    modified_when timestamp with time zone,
    modified_by name,
    pk integer,
    fk_vaccine integer,
    batch_no text
)
INHERITS (audit.audit_trail);


ALTER TABLE audit.log_vaccine_batches OWNER TO "gm-dbo";

--
-- Name: COLUMN log_vaccine_batches.orig_version; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_vaccine_batches.orig_version IS 'the .row_version in the original row before the audited action took place, should be equal to .row_version';


--
-- Name: COLUMN log_vaccine_batches.orig_when; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_vaccine_batches.orig_when IS 'the .modified_when in the original row before the audited action took place, should be equal to .modified_when';


--
-- Name: COLUMN log_vaccine_batches.orig_by; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_vaccine_batches.orig_by IS 'the .modified_by in the original row before the audited action took place, should be equal to .modified_by';


--
-- Name: COLUMN log_vaccine_batches.orig_tableoid; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_vaccine_batches.orig_tableoid IS 'the TG_RELID when the audit trigger was run';


--
-- Name: log_waiting_list; Type: TABLE; Schema: audit; Owner: gm-dbo
--

CREATE TABLE audit.log_waiting_list (
    pk_audit integer DEFAULT nextval('audit.audit_trail_pk_audit_seq'::regclass),
    row_version integer,
    modified_when timestamp with time zone,
    modified_by name,
    pk integer,
    fk_patient integer,
    registered timestamp with time zone,
    urgency integer,
    list_position integer,
    comment text,
    area text
)
INHERITS (audit.audit_trail);


ALTER TABLE audit.log_waiting_list OWNER TO "gm-dbo";

--
-- Name: COLUMN log_waiting_list.orig_version; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_waiting_list.orig_version IS 'the .row_version in the original row before the audited action took place, should be equal to .row_version';


--
-- Name: COLUMN log_waiting_list.orig_when; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_waiting_list.orig_when IS 'the .modified_when in the original row before the audited action took place, should be equal to .modified_when';


--
-- Name: COLUMN log_waiting_list.orig_by; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_waiting_list.orig_by IS 'the .modified_by in the original row before the audited action took place, should be equal to .modified_by';


--
-- Name: COLUMN log_waiting_list.orig_tableoid; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_waiting_list.orig_tableoid IS 'the TG_RELID when the audit trigger was run';


--
-- Name: log_xlnk_identity; Type: TABLE; Schema: audit; Owner: gm-dbo
--

CREATE TABLE audit.log_xlnk_identity (
    pk_audit integer DEFAULT nextval('audit.audit_trail_pk_audit_seq'::regclass),
    row_version integer,
    modified_when timestamp with time zone,
    modified_by name,
    pk integer,
    xfk_identity integer,
    pupic text,
    data text
)
INHERITS (audit.audit_trail);


ALTER TABLE audit.log_xlnk_identity OWNER TO "gm-dbo";

--
-- Name: COLUMN log_xlnk_identity.orig_version; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_xlnk_identity.orig_version IS 'the .row_version in the original row before the audited action took place, should be equal to .row_version';


--
-- Name: COLUMN log_xlnk_identity.orig_when; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_xlnk_identity.orig_when IS 'the .modified_when in the original row before the audited action took place, should be equal to .modified_when';


--
-- Name: COLUMN log_xlnk_identity.orig_by; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_xlnk_identity.orig_by IS 'the .modified_by in the original row before the audited action took place, should be equal to .modified_by';


--
-- Name: COLUMN log_xlnk_identity.orig_tableoid; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_xlnk_identity.orig_tableoid IS 'the TG_RELID when the audit trigger was run';


--
-- Name: staff; Type: TABLE; Schema: dem; Owner: gm-dbo
--

CREATE TABLE dem.staff (
    pk integer NOT NULL,
    fk_identity integer NOT NULL,
    db_user name DEFAULT "current_user"() NOT NULL,
    short_alias text NOT NULL,
    comment text,
    is_active boolean DEFAULT true NOT NULL
)
INHERITS (audit.audit_fields);


ALTER TABLE dem.staff OWNER TO "gm-dbo";

--
-- Name: TABLE staff; Type: COMMENT; Schema: dem; Owner: gm-dbo
--

COMMENT ON TABLE dem.staff IS 'one-to-one mapping of database user accounts
	 (db_user) to staff identities (fk_identity)';


--
-- Name: COLUMN staff.short_alias; Type: COMMENT; Schema: dem; Owner: gm-dbo
--

COMMENT ON COLUMN dem.staff.short_alias IS 'a short signature unique to this staff member
	 to be used in the GUI, actually this is somewhat
	 redundant with ext_person_id...';


--
-- Name: access_log; Type: TABLE; Schema: gm; Owner: gm-dbo
--

CREATE TABLE gm.access_log (
    pk integer NOT NULL,
    user_action text NOT NULL,
    CONSTRAINT non_empty_user_action CHECK ((gm.is_null_or_blank_string(user_action) IS FALSE))
)
INHERITS (audit.audit_fields);


ALTER TABLE gm.access_log OWNER TO "gm-dbo";

--
-- Name: TABLE access_log; Type: COMMENT; Schema: gm; Owner: gm-dbo
--

COMMENT ON TABLE gm.access_log IS 'This logs access to the database and to records.
 Needed for HIPAA compliance among other things.';


--
-- Name: v_audit_trail; Type: VIEW; Schema: audit; Owner: gm-dbo
--

CREATE VIEW audit.v_audit_trail AS
 SELECT to_char(aat.audit_when, 'YYYY-MM-DD HH24:MI'::text) AS event_when,
    COALESCE(( SELECT staff.short_alias
           FROM dem.staff
          WHERE (staff.db_user = aat.audit_by)), (('<'::text || (aat.audit_by)::text) || '>'::text)) AS event_by,
    (aat.orig_tableoid)::regclass AS event_table,
    aat.orig_version AS row_version_before,
        CASE
            WHEN (aat.audit_action = 'DELETE'::text) THEN NULL::integer
            ELSE (aat.orig_version + 1)
        END AS row_version_after,
    aat.audit_action AS event,
    aat.audit_when AS audit_when_ts,
    aat.pk_audit
   FROM audit.audit_trail aat
UNION ALL
 SELECT to_char(gal.modified_when, 'YYYY-MM-DD HH24:MI'::text) AS event_when,
    COALESCE(( SELECT staff.short_alias
           FROM dem.staff
          WHERE (staff.db_user = gal.modified_by)), (('<'::text || (gal.modified_by)::text) || '>'::text)) AS event_by,
    'gm.access_log'::regclass AS event_table,
    NULL::integer AS row_version_before,
    0 AS row_version_after,
    gal.user_action AS event,
    gal.modified_when AS audit_when_ts,
    gal.pk_audit
   FROM gm.access_log gal
  WHERE (gal.row_version = 0)
UNION ALL
 SELECT to_char(aaf.modified_when, 'YYYY-MM-DD HH24:MI'::text) AS event_when,
    COALESCE(( SELECT staff.short_alias
           FROM dem.staff
          WHERE (staff.db_user = aaf.modified_by)), (('<'::text || (aaf.modified_by)::text) || '>'::text)) AS event_by,
    (aaf.tableoid)::regclass AS event_table,
    NULL::integer AS row_version_before,
    0 AS row_version_after,
    'INSERT'::text AS event,
    aaf.modified_when AS audit_when_ts,
    aaf.pk_audit
   FROM audit.audit_fields aaf
  WHERE (aaf.row_version = 0);


ALTER TABLE audit.v_audit_trail OWNER TO "gm-dbo";

--
-- Name: bill; Type: TABLE; Schema: bill; Owner: gm-dbo
--

CREATE TABLE bill.bill (
    pk integer NOT NULL,
    invoice_id text,
    close_date timestamp with time zone,
    apply_vat boolean,
    fk_receiver_identity integer,
    fk_receiver_address integer,
    fk_doc integer,
    comment text,
    CONSTRAINT bill_bill_sane_apply_vat CHECK (((apply_vat IS NOT NULL) OR ((apply_vat IS NULL) AND (fk_doc IS NULL)))),
    CONSTRAINT bill_bill_sane_comment CHECK ((gm.is_null_or_non_empty_string(comment) IS TRUE)),
    CONSTRAINT bill_bill_sane_invoice_id CHECK ((gm.is_null_or_blank_string(invoice_id) IS FALSE)),
    CONSTRAINT bill_bill_sane_recv_adr CHECK (((fk_receiver_address IS NOT NULL) OR (close_date IS NULL)))
)
INHERITS (audit.audit_fields);


ALTER TABLE bill.bill OWNER TO "gm-dbo";

--
-- Name: TABLE bill; Type: COMMENT; Schema: bill; Owner: gm-dbo
--

COMMENT ON TABLE bill.bill IS 'actual bills';


--
-- Name: COLUMN bill.invoice_id; Type: COMMENT; Schema: bill; Owner: gm-dbo
--

COMMENT ON COLUMN bill.bill.invoice_id IS 'the ID of the bill';


--
-- Name: COLUMN bill.close_date; Type: COMMENT; Schema: bill; Owner: gm-dbo
--

COMMENT ON COLUMN bill.bill.close_date IS 'cannot add further bill_items after this date if not NULL';


--
-- Name: COLUMN bill.apply_vat; Type: COMMENT; Schema: bill; Owner: gm-dbo
--

COMMENT ON COLUMN bill.bill.apply_vat IS 'whether or not to apply VAT on the invoice';


--
-- Name: COLUMN bill.fk_receiver_identity; Type: COMMENT; Schema: bill; Owner: gm-dbo
--

COMMENT ON COLUMN bill.bill.fk_receiver_identity IS 'link to the receiver as a GNUmed identity, if known';


--
-- Name: COLUMN bill.fk_receiver_address; Type: COMMENT; Schema: bill; Owner: gm-dbo
--

COMMENT ON COLUMN bill.bill.fk_receiver_address IS 'links the address of the receiver of the invoice';


--
-- Name: COLUMN bill.fk_doc; Type: COMMENT; Schema: bill; Owner: gm-dbo
--

COMMENT ON COLUMN bill.bill.fk_doc IS 'links to the document which contains the invoice PDF';


--
-- Name: COLUMN bill.comment; Type: COMMENT; Schema: bill; Owner: gm-dbo
--

COMMENT ON COLUMN bill.bill.comment IS 'arbitrary comments on bills';


--
-- Name: bill_item; Type: TABLE; Schema: bill; Owner: gm-dbo
--

CREATE TABLE bill.bill_item (
    pk integer NOT NULL,
    fk_provider integer NOT NULL,
    fk_encounter integer NOT NULL,
    date_to_bill date,
    description text,
    net_amount_per_unit numeric DEFAULT 0 NOT NULL,
    currency text NOT NULL,
    status text DEFAULT 'new'::text,
    fk_billable integer NOT NULL,
    fk_bill integer,
    unit_count integer DEFAULT 1 NOT NULL,
    amount_multiplier numeric DEFAULT 1 NOT NULL,
    CONSTRAINT bill_bill_item_sane_count CHECK ((unit_count > '-1'::integer)),
    CONSTRAINT bill_bill_item_sane_currency CHECK ((gm.is_null_or_blank_string(currency) IS FALSE)),
    CONSTRAINT bill_bill_item_sane_desc CHECK ((gm.is_null_or_non_empty_string(description) IS TRUE)),
    CONSTRAINT bill_bill_item_sane_multiplier CHECK ((amount_multiplier > (0)::numeric)),
    CONSTRAINT valid_stati CHECK ((status = ANY (ARRAY['new'::text, 'transferred'::text])))
)
INHERITS (audit.audit_fields);


ALTER TABLE bill.bill_item OWNER TO "gm-dbo";

--
-- Name: TABLE bill_item; Type: COMMENT; Schema: bill; Owner: gm-dbo
--

COMMENT ON TABLE bill.bill_item IS 'items patients currently *are* billed for';


--
-- Name: COLUMN bill_item.fk_provider; Type: COMMENT; Schema: bill; Owner: gm-dbo
--

COMMENT ON COLUMN bill.bill_item.fk_provider IS 'by whom or on whose behalf did the billable activity happen';


--
-- Name: COLUMN bill_item.fk_encounter; Type: COMMENT; Schema: bill; Owner: gm-dbo
--

COMMENT ON COLUMN bill.bill_item.fk_encounter IS 'the encounter during which the billable action for this item took place';


--
-- Name: COLUMN bill_item.date_to_bill; Type: COMMENT; Schema: bill; Owner: gm-dbo
--

COMMENT ON COLUMN bill.bill_item.date_to_bill IS 'The date the bill item was caused. If NULL, use .fk_encounter -> .started';


--
-- Name: COLUMN bill_item.description; Type: COMMENT; Schema: bill; Owner: gm-dbo
--

COMMENT ON COLUMN bill.bill_item.description IS 'Can be used to further explain the bill item over and above .fk_billable.description.';


--
-- Name: COLUMN bill_item.net_amount_per_unit; Type: COMMENT; Schema: bill; Owner: gm-dbo
--

COMMENT ON COLUMN bill.bill_item.net_amount_per_unit IS 'How much to charge for one unit of this bill item.';


--
-- Name: COLUMN bill_item.currency; Type: COMMENT; Schema: bill; Owner: gm-dbo
--

COMMENT ON COLUMN bill.bill_item.currency IS 'Which currency to charge in. Must not be NULL if .net_amount_per_unit is not NULL.';


--
-- Name: COLUMN bill_item.status; Type: COMMENT; Schema: bill; Owner: gm-dbo
--

COMMENT ON COLUMN bill.bill_item.status IS 'the status of this item';


--
-- Name: COLUMN bill_item.fk_billable; Type: COMMENT; Schema: bill; Owner: gm-dbo
--

COMMENT ON COLUMN bill.bill_item.fk_billable IS 'Links to the billable item this bill item stands for.';


--
-- Name: COLUMN bill_item.fk_bill; Type: COMMENT; Schema: bill; Owner: gm-dbo
--

COMMENT ON COLUMN bill.bill_item.fk_bill IS 'Links to the bill this bill item is on if any.';


--
-- Name: COLUMN bill_item.unit_count; Type: COMMENT; Schema: bill; Owner: gm-dbo
--

COMMENT ON COLUMN bill.bill_item.unit_count IS 'The number of times this item is to be billed. 0 can be used for informative items.';


--
-- Name: COLUMN bill_item.amount_multiplier; Type: COMMENT; Schema: bill; Owner: gm-dbo
--

COMMENT ON COLUMN bill.bill_item.amount_multiplier IS 'A multiplier to apply to .net_amount_per_unit. Can be used for discounts, rebates, or increases. Must be > 0.';


--
-- Name: bill_item_pk_seq; Type: SEQUENCE; Schema: bill; Owner: gm-dbo
--

CREATE SEQUENCE bill.bill_item_pk_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE bill.bill_item_pk_seq OWNER TO "gm-dbo";

--
-- Name: bill_item_pk_seq; Type: SEQUENCE OWNED BY; Schema: bill; Owner: gm-dbo
--

ALTER SEQUENCE bill.bill_item_pk_seq OWNED BY bill.bill_item.pk;


--
-- Name: bill_pk_seq; Type: SEQUENCE; Schema: bill; Owner: gm-dbo
--

CREATE SEQUENCE bill.bill_pk_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE bill.bill_pk_seq OWNER TO "gm-dbo";

--
-- Name: bill_pk_seq; Type: SEQUENCE OWNED BY; Schema: bill; Owner: gm-dbo
--

ALTER SEQUENCE bill.bill_pk_seq OWNED BY bill.bill.pk;


--
-- Name: lnk_enc_type2billable; Type: TABLE; Schema: bill; Owner: gm-dbo
--

CREATE TABLE bill.lnk_enc_type2billable (
    pk integer NOT NULL,
    fk_encounter_type integer NOT NULL,
    fk_billable integer NOT NULL
);


ALTER TABLE bill.lnk_enc_type2billable OWNER TO "gm-dbo";

--
-- Name: TABLE lnk_enc_type2billable; Type: COMMENT; Schema: bill; Owner: gm-dbo
--

COMMENT ON TABLE bill.lnk_enc_type2billable IS 'actual bills';


--
-- Name: COLUMN lnk_enc_type2billable.fk_encounter_type; Type: COMMENT; Schema: bill; Owner: gm-dbo
--

COMMENT ON COLUMN bill.lnk_enc_type2billable.fk_encounter_type IS 'Links to encounter type this billable is associated with.';


--
-- Name: COLUMN lnk_enc_type2billable.fk_billable; Type: COMMENT; Schema: bill; Owner: gm-dbo
--

COMMENT ON COLUMN bill.lnk_enc_type2billable.fk_billable IS 'Links to the billable item the encounter type is associated with.';


--
-- Name: lnk_enc_type2billable_pk_seq; Type: SEQUENCE; Schema: bill; Owner: gm-dbo
--

CREATE SEQUENCE bill.lnk_enc_type2billable_pk_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE bill.lnk_enc_type2billable_pk_seq OWNER TO "gm-dbo";

--
-- Name: lnk_enc_type2billable_pk_seq; Type: SEQUENCE OWNED BY; Schema: bill; Owner: gm-dbo
--

ALTER SEQUENCE bill.lnk_enc_type2billable_pk_seq OWNED BY bill.lnk_enc_type2billable.pk;


--
-- Name: encounter; Type: TABLE; Schema: clin; Owner: gm-dbo
--

CREATE TABLE clin.encounter (
    pk integer NOT NULL,
    fk_patient integer NOT NULL,
    fk_type integer DEFAULT 1 NOT NULL,
    fk_location integer NOT NULL,
    source_time_zone interval,
    reason_for_encounter text,
    assessment_of_encounter text,
    started timestamp with time zone DEFAULT now() NOT NULL,
    last_affirmed timestamp with time zone DEFAULT now() NOT NULL,
    CONSTRAINT clin_enc_sane_duration CHECK ((last_affirmed >= started)),
    CONSTRAINT encounter_assessment_of_encounter_check CHECK ((btrim(COALESCE(assessment_of_encounter, 'xxxDEFAULTxxx'::text)) <> ''::text)),
    CONSTRAINT encounter_reason_for_encounter_check CHECK ((btrim(COALESCE(reason_for_encounter, 'xxxDEFAULTxxx'::text)) <> ''::text))
)
INHERITS (audit.audit_fields);


ALTER TABLE clin.encounter OWNER TO "gm-dbo";

--
-- Name: TABLE encounter; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON TABLE clin.encounter IS 'a clinical encounter between a person and the health care system';


--
-- Name: COLUMN encounter.fk_patient; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON COLUMN clin.encounter.fk_patient IS 'PK of subject of care, should be PUPIC, actually';


--
-- Name: COLUMN encounter.fk_type; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON COLUMN clin.encounter.fk_type IS 'PK of type of this encounter';


--
-- Name: COLUMN encounter.fk_location; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON COLUMN clin.encounter.fk_location IS 'PK of location *of care*, e.g. where the provider is at';


--
-- Name: COLUMN encounter.source_time_zone; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON COLUMN clin.encounter.source_time_zone IS 'time zone of location, used to approximate source time
	 zone for all timestamps in this encounter';


--
-- Name: COLUMN encounter.reason_for_encounter; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON COLUMN clin.encounter.reason_for_encounter IS 'the RFE for the encounter as related by either
	 the patient or the provider (say, in a chart
	 review)';


--
-- Name: COLUMN encounter.assessment_of_encounter; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON COLUMN clin.encounter.assessment_of_encounter IS 'the Assessment of Encounter (eg consultation summary)
	 as determined by the provider, may simply be a
	 concatenation of soAp narrative, this assessment
	 should go across all problems';


--
-- Name: coding_system_root; Type: TABLE; Schema: ref; Owner: gm-dbo
--

CREATE TABLE ref.coding_system_root (
    pk_coding_system integer NOT NULL,
    code text NOT NULL,
    term text NOT NULL,
    fk_data_source integer NOT NULL,
    comment text
);


ALTER TABLE ref.coding_system_root OWNER TO "gm-dbo";

--
-- Name: TABLE coding_system_root; Type: COMMENT; Schema: ref; Owner: gm-dbo
--

COMMENT ON TABLE ref.coding_system_root IS 'Base table for coding system tables providing common fields.';


--
-- Name: COLUMN coding_system_root.fk_data_source; Type: COMMENT; Schema: ref; Owner: gm-dbo
--

COMMENT ON COLUMN ref.coding_system_root.fk_data_source IS 'links to the data source for the external reference data set';


--
-- Name: COLUMN coding_system_root.comment; Type: COMMENT; Schema: ref; Owner: gm-dbo
--

COMMENT ON COLUMN ref.coding_system_root.comment IS 'an arbitrary comment on the code and/or term,
 child tables will use this in different ways';


--
-- Name: billable; Type: TABLE; Schema: ref; Owner: gm-dbo
--

CREATE TABLE ref.billable (
    fk_data_source integer,
    pk integer NOT NULL,
    amount numeric DEFAULT 0 NOT NULL,
    currency text DEFAULT 'u20AC'::text,
    vat_multiplier numeric DEFAULT 0 NOT NULL,
    active boolean DEFAULT true NOT NULL,
    discountable boolean DEFAULT false NOT NULL,
    CONSTRAINT ref_billable_sane_amount CHECK ((amount >= (0)::numeric)),
    CONSTRAINT ref_billable_sane_currency CHECK ((gm.is_null_or_blank_string(currency) IS FALSE)),
    CONSTRAINT ref_billable_sane_vat_multiplier CHECK ((vat_multiplier >= (0)::numeric))
)
INHERITS (ref.coding_system_root);


ALTER TABLE ref.billable OWNER TO "gm-dbo";

--
-- Name: TABLE billable; Type: COMMENT; Schema: ref; Owner: gm-dbo
--

COMMENT ON TABLE ref.billable IS 'items that *can* be billed to patients';


--
-- Name: COLUMN billable.amount; Type: COMMENT; Schema: ref; Owner: gm-dbo
--

COMMENT ON COLUMN ref.billable.amount IS 'How much to bill for this item.';


--
-- Name: COLUMN billable.currency; Type: COMMENT; Schema: ref; Owner: gm-dbo
--

COMMENT ON COLUMN ref.billable.currency IS 'The currency .amount is in.';


--
-- Name: COLUMN billable.vat_multiplier; Type: COMMENT; Schema: ref; Owner: gm-dbo
--

COMMENT ON COLUMN ref.billable.vat_multiplier IS 'Multiplier to apply to .amount to calculate VAT, eg 0.19 = 19%, 0 = no VAT';


--
-- Name: COLUMN billable.active; Type: COMMENT; Schema: ref; Owner: gm-dbo
--

COMMENT ON COLUMN ref.billable.active IS 'Whether this item is currently supposed to be used for billing patients.';


--
-- Name: COLUMN billable.discountable; Type: COMMENT; Schema: ref; Owner: gm-dbo
--

COMMENT ON COLUMN ref.billable.discountable IS 'Whether discounts can be applied to this item.';


--
-- Name: data_source; Type: TABLE; Schema: ref; Owner: gm-dbo
--

CREATE TABLE ref.data_source (
    pk integer NOT NULL,
    name_long text NOT NULL,
    name_short text NOT NULL,
    version text NOT NULL,
    description text,
    source text NOT NULL,
    lang text
)
INHERITS (audit.audit_fields);


ALTER TABLE ref.data_source OWNER TO "gm-dbo";

--
-- Name: TABLE data_source; Type: COMMENT; Schema: ref; Owner: gm-dbo
--

COMMENT ON TABLE ref.data_source IS 'lists the available coding systems, classifications, ontologies and term lists';


--
-- Name: COLUMN data_source.name_long; Type: COMMENT; Schema: ref; Owner: gm-dbo
--

COMMENT ON COLUMN ref.data_source.name_long IS 'long, complete (, ?official) name for this reference entry';


--
-- Name: COLUMN data_source.name_short; Type: COMMENT; Schema: ref; Owner: gm-dbo
--

COMMENT ON COLUMN ref.data_source.name_short IS 'shorthand for referring to this reference entry';


--
-- Name: COLUMN data_source.version; Type: COMMENT; Schema: ref; Owner: gm-dbo
--

COMMENT ON COLUMN ref.data_source.version IS 'the exact and non-ambigous version for this entry';


--
-- Name: COLUMN data_source.description; Type: COMMENT; Schema: ref; Owner: gm-dbo
--

COMMENT ON COLUMN ref.data_source.description IS 'optional arbitrary description, should include external license';


--
-- Name: COLUMN data_source.source; Type: COMMENT; Schema: ref; Owner: gm-dbo
--

COMMENT ON COLUMN ref.data_source.source IS 'non-ambigous description of source; with this info in hand it
 must be possible to locate a copy of the external data set';


--
-- Name: v_bill_items; Type: VIEW; Schema: bill; Owner: gm-dbo
--

CREATE VIEW bill.v_bill_items AS
 SELECT b_bi.pk AS pk_bill_item,
    r_b.code AS billable_code,
    r_b.term AS billable_description,
    b_bi.description AS item_detail,
    COALESCE((b_bi.date_to_bill)::timestamp with time zone, c_enc.started) AS date_to_bill,
    b_bi.net_amount_per_unit,
    b_bi.unit_count,
    b_bi.amount_multiplier,
    (((b_bi.unit_count)::numeric * b_bi.net_amount_per_unit) * b_bi.amount_multiplier) AS total_amount,
    ((((b_bi.unit_count)::numeric * b_bi.net_amount_per_unit) * b_bi.amount_multiplier) * r_b.vat_multiplier) AS vat,
    b_bi.currency,
    b_bi.date_to_bill AS raw_date_to_bill,
    r_b.amount AS billable_amount,
    r_b.vat_multiplier,
    r_b.currency AS billable_currency,
    r_b.comment AS billable_comment,
    r_b.active AS billable_active,
    r_b.discountable AS billable_discountable,
    r_ds.name_long AS catalog_long,
    r_ds.name_short AS catalog_short,
    r_ds.version AS catalog_version,
    r_ds.lang AS catalog_language,
    c_enc.fk_patient AS pk_patient,
    c_enc.fk_type AS pk_encounter_type,
    b_bi.fk_provider AS pk_provider,
    b_bi.fk_encounter AS pk_encounter_to_bill,
    b_bi.fk_bill AS pk_bill,
    r_b.pk AS pk_billable,
    r_b.fk_data_source AS pk_data_source,
    b_bi.xmin AS xmin_bill_item
   FROM (((bill.bill_item b_bi
     JOIN ref.billable r_b ON ((b_bi.fk_billable = r_b.pk)))
     LEFT JOIN ref.data_source r_ds ON ((r_b.fk_data_source = r_ds.pk)))
     LEFT JOIN clin.encounter c_enc ON ((b_bi.fk_encounter = c_enc.pk)));


ALTER TABLE bill.v_bill_items OWNER TO "gm-dbo";

--
-- Name: v_bills; Type: VIEW; Schema: bill; Owner: gm-dbo
--

CREATE VIEW bill.v_bills AS
 SELECT b_b.pk AS pk_bill,
    b_b.invoice_id,
    b_b.fk_receiver_identity AS pk_receiver_identity,
    ( SELECT round(sum(v_bill_items.total_amount), 2) AS round
           FROM bill.v_bill_items
          WHERE (v_bill_items.pk_bill = b_b.pk)) AS total_amount,
    ( SELECT round(sum(v_bill_items.vat), 2) AS round
           FROM bill.v_bill_items
          WHERE (v_bill_items.pk_bill = b_b.pk)) AS total_vat,
    ( SELECT round(sum((v_bill_items.total_amount + v_bill_items.vat)), 2) AS round
           FROM bill.v_bill_items
          WHERE (v_bill_items.pk_bill = b_b.pk)) AS total_amount_with_vat,
    ( SELECT (v_bill_items.vat_multiplier * (100)::numeric)
           FROM bill.v_bill_items
          WHERE (v_bill_items.pk_bill = b_b.pk)
         LIMIT 1) AS percent_vat,
    ( SELECT v_bill_items.currency
           FROM bill.v_bill_items
          WHERE (v_bill_items.pk_bill = b_b.pk)
         LIMIT 1) AS currency,
    b_b.close_date,
    b_b.apply_vat,
    b_b.comment,
    b_b.fk_receiver_address AS pk_receiver_address,
    b_b.fk_doc AS pk_doc,
    ( SELECT encounter.fk_patient
           FROM clin.encounter
          WHERE (encounter.pk = ( SELECT bill_item.fk_encounter
                   FROM bill.bill_item
                  WHERE (bill_item.fk_bill = b_b.pk)
                 LIMIT 1))) AS pk_patient,
    ( SELECT array_agg(sorted_values.pk_bill_item) AS array_agg
           FROM ( SELECT b_vbi.pk_bill_item
                   FROM bill.v_bill_items b_vbi
                  WHERE (b_vbi.pk_bill = b_b.pk)
                  ORDER BY b_vbi.date_to_bill, b_vbi.billable_code) sorted_values) AS pk_bill_items,
    b_b.xmin AS xmin_bill
   FROM bill.bill b_b;


ALTER TABLE bill.v_bills OWNER TO "gm-dbo";

--
-- Name: doc_desc; Type: TABLE; Schema: blobs; Owner: gm-dbo
--

CREATE TABLE blobs.doc_desc (
    pk integer NOT NULL,
    fk_doc integer,
    text text
)
INHERITS (audit.audit_fields);


ALTER TABLE blobs.doc_desc OWNER TO "gm-dbo";

--
-- Name: TABLE doc_desc; Type: COMMENT; Schema: blobs; Owner: gm-dbo
--

COMMENT ON TABLE blobs.doc_desc IS 'A textual description of the content such
	 as a result summary. Several of these may
	 belong to one document object.';


--
-- Name: doc_desc_pk_seq; Type: SEQUENCE; Schema: blobs; Owner: gm-dbo
--

CREATE SEQUENCE blobs.doc_desc_pk_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE blobs.doc_desc_pk_seq OWNER TO "gm-dbo";

--
-- Name: doc_desc_pk_seq; Type: SEQUENCE OWNED BY; Schema: blobs; Owner: gm-dbo
--

ALTER SEQUENCE blobs.doc_desc_pk_seq OWNED BY blobs.doc_desc.pk;


--
-- Name: doc_med; Type: TABLE; Schema: blobs; Owner: gm-dbo
--

CREATE TABLE blobs.doc_med (
    pk integer NOT NULL,
    fk_encounter integer NOT NULL,
    fk_episode integer NOT NULL,
    fk_type integer NOT NULL,
    comment text,
    clin_when timestamp with time zone DEFAULT now() NOT NULL,
    ext_ref text,
    fk_org_unit integer,
    unit_is_receiver boolean DEFAULT false NOT NULL,
    fk_hospital_stay integer
)
INHERITS (audit.audit_fields);


ALTER TABLE blobs.doc_med OWNER TO "gm-dbo";

--
-- Name: TABLE doc_med; Type: COMMENT; Schema: blobs; Owner: gm-dbo
--

COMMENT ON TABLE blobs.doc_med IS 'a medical document object possibly containing several
	 data objects such as several pages of a paper document';


--
-- Name: COLUMN doc_med.fk_encounter; Type: COMMENT; Schema: blobs; Owner: gm-dbo
--

COMMENT ON COLUMN blobs.doc_med.fk_encounter IS 'the encounter in which this document was entered into the system';


--
-- Name: COLUMN doc_med.fk_episode; Type: COMMENT; Schema: blobs; Owner: gm-dbo
--

COMMENT ON COLUMN blobs.doc_med.fk_episode IS 'the episode this document pertains to, this may not be the only
	 one applicable to the document (think discharge letters), see also
	 lnk_doc_med2episode';


--
-- Name: COLUMN doc_med.fk_type; Type: COMMENT; Schema: blobs; Owner: gm-dbo
--

COMMENT ON COLUMN blobs.doc_med.fk_type IS 'semantic type of document (not type of file or mime
	 type), such as "referral letter", "discharge summary", etc.';


--
-- Name: COLUMN doc_med.comment; Type: COMMENT; Schema: blobs; Owner: gm-dbo
--

COMMENT ON COLUMN blobs.doc_med.comment IS 'additional short comment such as "abdominal", "ward 3,
	 Dr. Stein", etc.';


--
-- Name: COLUMN doc_med.clin_when; Type: COMMENT; Schema: blobs; Owner: gm-dbo
--

COMMENT ON COLUMN blobs.doc_med.clin_when IS 'date of document content creation (such as exam date),
	 NOT date of document creation or date of import; may
	 be imprecise such as "7/99"';


--
-- Name: COLUMN doc_med.ext_ref; Type: COMMENT; Schema: blobs; Owner: gm-dbo
--

COMMENT ON COLUMN blobs.doc_med.ext_ref IS 'external reference string of physical document,
	 original paper copy can be found with this';


--
-- Name: COLUMN doc_med.fk_org_unit; Type: COMMENT; Schema: blobs; Owner: gm-dbo
--

COMMENT ON COLUMN blobs.doc_med.fk_org_unit IS 'Optional link to the org unit this document originates from.\n
Note that the document may contain data from several units but\n
there will always be one "sender".';


--
-- Name: COLUMN doc_med.unit_is_receiver; Type: COMMENT; Schema: blobs; Owner: gm-dbo
--

COMMENT ON COLUMN blobs.doc_med.unit_is_receiver IS 'whether the org unit is the receiver rather than the document source';


--
-- Name: COLUMN doc_med.fk_hospital_stay; Type: COMMENT; Schema: blobs; Owner: gm-dbo
--

COMMENT ON COLUMN blobs.doc_med.fk_hospital_stay IS 'the hospital stay this document is associated with';


--
-- Name: doc_med_pk_seq; Type: SEQUENCE; Schema: blobs; Owner: gm-dbo
--

CREATE SEQUENCE blobs.doc_med_pk_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE blobs.doc_med_pk_seq OWNER TO "gm-dbo";

--
-- Name: doc_med_pk_seq; Type: SEQUENCE OWNED BY; Schema: blobs; Owner: gm-dbo
--

ALTER SEQUENCE blobs.doc_med_pk_seq OWNED BY blobs.doc_med.pk;


--
-- Name: doc_obj; Type: TABLE; Schema: blobs; Owner: gm-dbo
--

CREATE TABLE blobs.doc_obj (
    pk integer NOT NULL,
    fk_doc integer NOT NULL,
    seq_idx integer,
    comment text,
    fk_intended_reviewer integer NOT NULL,
    data bytea NOT NULL,
    filename text,
    CONSTRAINT doc_obj_filename_check CHECK ((btrim(COALESCE(filename, 'NULL'::text)) <> ''::text))
);


ALTER TABLE blobs.doc_obj OWNER TO "gm-dbo";

--
-- Name: TABLE doc_obj; Type: COMMENT; Schema: blobs; Owner: gm-dbo
--

COMMENT ON TABLE blobs.doc_obj IS 'possibly several of these form a medical document
	 such as multiple scanned pages/images';


--
-- Name: COLUMN doc_obj.seq_idx; Type: COMMENT; Schema: blobs; Owner: gm-dbo
--

COMMENT ON COLUMN blobs.doc_obj.seq_idx IS 'index of this object in the sequence
	 of objects for this document';


--
-- Name: COLUMN doc_obj.comment; Type: COMMENT; Schema: blobs; Owner: gm-dbo
--

COMMENT ON COLUMN blobs.doc_obj.comment IS 'optional tiny comment for this
	 object, such as "page 1"';


--
-- Name: COLUMN doc_obj.fk_intended_reviewer; Type: COMMENT; Schema: blobs; Owner: gm-dbo
--

COMMENT ON COLUMN blobs.doc_obj.fk_intended_reviewer IS 'who is *supposed* to review this item';


--
-- Name: COLUMN doc_obj.data; Type: COMMENT; Schema: blobs; Owner: gm-dbo
--

COMMENT ON COLUMN blobs.doc_obj.data IS 'actual binary object data;

	 here is why we use bytea:

== --------------------------------------------------

To: leon@oss.minimetria.com

Cc: pgsql-sql@postgresql.org

Subject: Re: [SQL] Recommendation on bytea or blob for binary data like images 

Date: Fri, 02 Sep 2005 16:33:09 -0400

Message-ID: <17794.1125693189@sss.pgh.pa.us>

From: Tom Lane <tgl@sss.pgh.pa.us>

List-Archive: <http://archives.postgresql.org/pgsql-sql>

List-Help: <mailto:majordomo@postgresql.org?body=help>

List-ID: <pgsql-sql.postgresql.org>



leon@oss.minimetria.com writes:

> Hi, I"d like to know what the official recommendation is on which binary

> datatype to use for common small-binary size use.



If bytea will work for you, it"s definitely the thing to use.  The only

real drawback to bytea is that there"s currently no API to read and

write bytea values in a streaming fashion.  If your objects are small

enough that you can load and store them as units, bytea is fine.



BLOBs, on the other hand, have a number of drawbacks --- hard to dump,

impossible to secure, etc.



			regards, tom lane

== --------------------------------------------------';


--
-- Name: COLUMN doc_obj.filename; Type: COMMENT; Schema: blobs; Owner: gm-dbo
--

COMMENT ON COLUMN blobs.doc_obj.filename IS 'the filename from when the data was imported - if any, can be NULL,
	 useful for re-export since legacy devices/applications might expect
	 particular file names and not use mime types for file detection';


--
-- Name: doc_obj_pk_seq; Type: SEQUENCE; Schema: blobs; Owner: gm-dbo
--

CREATE SEQUENCE blobs.doc_obj_pk_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE blobs.doc_obj_pk_seq OWNER TO "gm-dbo";

--
-- Name: doc_obj_pk_seq; Type: SEQUENCE OWNED BY; Schema: blobs; Owner: gm-dbo
--

ALTER SEQUENCE blobs.doc_obj_pk_seq OWNED BY blobs.doc_obj.pk;


--
-- Name: doc_type; Type: TABLE; Schema: blobs; Owner: gm-dbo
--

CREATE TABLE blobs.doc_type (
    pk integer NOT NULL,
    name text NOT NULL
);


ALTER TABLE blobs.doc_type OWNER TO "gm-dbo";

--
-- Name: TABLE doc_type; Type: COMMENT; Schema: blobs; Owner: gm-dbo
--

COMMENT ON TABLE blobs.doc_type IS 'this table enumerates the document types known to the system';


--
-- Name: COLUMN doc_type.name; Type: COMMENT; Schema: blobs; Owner: gm-dbo
--

COMMENT ON COLUMN blobs.doc_type.name IS 'the name/label of the document type';


--
-- Name: doc_type_pk_seq; Type: SEQUENCE; Schema: blobs; Owner: gm-dbo
--

CREATE SEQUENCE blobs.doc_type_pk_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE blobs.doc_type_pk_seq OWNER TO "gm-dbo";

--
-- Name: doc_type_pk_seq; Type: SEQUENCE OWNED BY; Schema: blobs; Owner: gm-dbo
--

ALTER SEQUENCE blobs.doc_type_pk_seq OWNED BY blobs.doc_type.pk;


--
-- Name: lnk_doc_med2episode; Type: TABLE; Schema: blobs; Owner: gm-dbo
--

CREATE TABLE blobs.lnk_doc_med2episode (
    pk integer NOT NULL,
    fk_episode integer NOT NULL,
    fk_doc_med integer NOT NULL
)
INHERITS (audit.audit_fields);


ALTER TABLE blobs.lnk_doc_med2episode OWNER TO "gm-dbo";

--
-- Name: TABLE lnk_doc_med2episode; Type: COMMENT; Schema: blobs; Owner: gm-dbo
--

COMMENT ON TABLE blobs.lnk_doc_med2episode IS 'this allows linking documents to episodes,
	 each document can apply to several episodes
	 but only once each';


--
-- Name: lnk_doc_med2episode_pk_seq; Type: SEQUENCE; Schema: blobs; Owner: gm-dbo
--

CREATE SEQUENCE blobs.lnk_doc_med2episode_pk_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE blobs.lnk_doc_med2episode_pk_seq OWNER TO "gm-dbo";

--
-- Name: lnk_doc_med2episode_pk_seq; Type: SEQUENCE OWNED BY; Schema: blobs; Owner: gm-dbo
--

ALTER SEQUENCE blobs.lnk_doc_med2episode_pk_seq OWNED BY blobs.lnk_doc_med2episode.pk;


--
-- Name: review_root; Type: TABLE; Schema: clin; Owner: gm-dbo
--

CREATE TABLE clin.review_root (
    pk integer NOT NULL,
    fk_reviewed_row integer NOT NULL,
    fk_reviewer integer NOT NULL,
    is_technically_abnormal boolean NOT NULL,
    clinically_relevant boolean NOT NULL,
    comment text
)
INHERITS (audit.audit_fields);


ALTER TABLE clin.review_root OWNER TO "gm-dbo";

--
-- Name: TABLE review_root; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON TABLE clin.review_root IS 'this table tracks whether a particular clinical item
	 was reviewed by a clinician or not';


--
-- Name: COLUMN review_root.fk_reviewed_row; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON COLUMN clin.review_root.fk_reviewed_row IS 'the row the review status is for: to be qualified
	 as a proper foreign key in child tables';


--
-- Name: COLUMN review_root.fk_reviewer; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON COLUMN clin.review_root.fk_reviewer IS 'who has reviewed the item';


--
-- Name: COLUMN review_root.is_technically_abnormal; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON COLUMN clin.review_root.is_technically_abnormal IS 'whether test provider flagged this result as abnormal,
	 *not* a clinical assessment but rather a technical one
	 LDT: exist(8422)';


--
-- Name: COLUMN review_root.clinically_relevant; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON COLUMN clin.review_root.clinically_relevant IS 'whether this result is considered relevant clinically,
	 need not correspond to the value of "techically_abnormal"
	 since abnormal values may be irrelevant while normal
	 ones can be of significance';


--
-- Name: reviewed_doc_objs; Type: TABLE; Schema: blobs; Owner: gm-dbo
--

CREATE TABLE blobs.reviewed_doc_objs (
)
INHERITS (clin.review_root);


ALTER TABLE blobs.reviewed_doc_objs OWNER TO "gm-dbo";

--
-- Name: TABLE reviewed_doc_objs; Type: COMMENT; Schema: blobs; Owner: gm-dbo
--

COMMENT ON TABLE blobs.reviewed_doc_objs IS 'review table for documents (per object such as a page)';


--
-- Name: episode; Type: TABLE; Schema: clin; Owner: gm-dbo
--

CREATE TABLE clin.episode (
    pk integer NOT NULL,
    fk_health_issue integer,
    description text NOT NULL,
    is_open boolean DEFAULT true,
    fk_encounter integer NOT NULL,
    diagnostic_certainty_classification text,
    summary text,
    CONSTRAINT episode_sane_summary CHECK (gm.is_null_or_non_empty_string(summary)),
    CONSTRAINT sane_description CHECK ((gm.is_null_or_blank_string(description) IS FALSE)),
    CONSTRAINT valid_diagnostic_certainty_classification CHECK ((diagnostic_certainty_classification = ANY (ARRAY['A'::text, 'B'::text, 'C'::text, 'D'::text, NULL::text])))
)
INHERITS (audit.audit_fields);


ALTER TABLE clin.episode OWNER TO "gm-dbo";

--
-- Name: TABLE episode; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON TABLE clin.episode IS 'Clinical episodes such as "Otitis media",
	 "traffic accident 7/99", "Hepatitis B".
	 This covers a range of time in which
	 activity of illness was noted for the
	 problem episode.description.';


--
-- Name: COLUMN episode.fk_health_issue; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON COLUMN clin.episode.fk_health_issue IS 'health issue this episode belongs to';


--
-- Name: COLUMN episode.description; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON COLUMN clin.episode.description IS 'description/name of this episode';


--
-- Name: COLUMN episode.is_open; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON COLUMN clin.episode.is_open IS 'whether the episode is open (eg. there is activity for it),
	 means open in a temporal sense as in "not closed yet";
	 only one episode can be open per health issue';


--
-- Name: COLUMN episode.fk_encounter; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON COLUMN clin.episode.fk_encounter IS 'The encounter during which this episode was added (begun).';


--
-- Name: COLUMN episode.diagnostic_certainty_classification; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON COLUMN clin.episode.diagnostic_certainty_classification IS 'The certainty at which this problem is believed to be a diagnosis:
A: sign (Symptom)
B: cluster of signs (Symptomkomplex)
C: syndromic diagnosis (Bild einer Diagnose)
D: proven diagnosis (diagnostisch gesichert)';


--
-- Name: COLUMN episode.summary; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON COLUMN clin.episode.summary IS 'Used for tracking the summary of this episode.';


--
-- Name: health_issue; Type: TABLE; Schema: clin; Owner: gm-dbo
--

CREATE TABLE clin.health_issue (
    pk integer NOT NULL,
    description text NOT NULL,
    laterality character varying(2) DEFAULT NULL::character varying,
    age_noted interval,
    is_active boolean DEFAULT true,
    clinically_relevant boolean DEFAULT true,
    is_confidential boolean DEFAULT false,
    is_cause_of_death boolean DEFAULT false NOT NULL,
    fk_encounter integer NOT NULL,
    "grouping" text,
    diagnostic_certainty_classification text,
    summary text,
    CONSTRAINT health_issue_laterality_check CHECK (((laterality)::text = ANY (ARRAY[(NULL::character varying)::text, ('s'::character varying)::text, ('d'::character varying)::text, ('sd'::character varying)::text, ('na'::character varying)::text]))),
    CONSTRAINT health_issue_sane_summary CHECK (gm.is_null_or_non_empty_string(summary)),
    CONSTRAINT issue_name_not_empty CHECK ((btrim(description) <> ''::text)),
    CONSTRAINT sane_grouping CHECK (gm.is_null_or_non_empty_string("grouping")),
    CONSTRAINT valid_diagnostic_certainty_classification CHECK ((diagnostic_certainty_classification = ANY (ARRAY['A'::text, 'B'::text, 'C'::text, 'D'::text, NULL::text])))
)
INHERITS (audit.audit_fields);


ALTER TABLE clin.health_issue OWNER TO "gm-dbo";

--
-- Name: TABLE health_issue; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON TABLE clin.health_issue IS 'This is pretty much what others would call "Past Medical History"
	 or "Foundational illness", eg. longer-ranging, underlying,
	 encompassing issues with one''s health such as "immunodeficiency",
	 "type 2 diabetes". In Belgium it is called "problem".
	 L.L.Weed includes lots of little things into it, we do not.';


--
-- Name: COLUMN health_issue.description; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON COLUMN clin.health_issue.description IS 'descriptive name of this health issue, may
	 change over time as evidence increases';


--
-- Name: COLUMN health_issue.laterality; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON COLUMN clin.health_issue.laterality IS 'NULL: don''t know
	 s: sinister - left
	 d: dexter - right
	 sd: sinister and dexter - both sides
	 na: not applicable';


--
-- Name: COLUMN health_issue.age_noted; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON COLUMN clin.health_issue.age_noted IS 'at what age the patient acquired the condition';


--
-- Name: COLUMN health_issue.is_active; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON COLUMN clin.health_issue.is_active IS 'whether this health issue (problem) is active';


--
-- Name: COLUMN health_issue.clinically_relevant; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON COLUMN clin.health_issue.clinically_relevant IS 'whether this health issue (problem) has any clinical relevance';


--
-- Name: COLUMN health_issue.fk_encounter; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON COLUMN clin.health_issue.fk_encounter IS 'The encounter during which this health issue was added.';


--
-- Name: COLUMN health_issue."grouping"; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON COLUMN clin.health_issue."grouping" IS 'This can be used to entirely arbitrarily group health
 issues felt to belong to each other.';


--
-- Name: COLUMN health_issue.diagnostic_certainty_classification; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON COLUMN clin.health_issue.diagnostic_certainty_classification IS 'The certainty at which this issue is believed to be a diagnosis:
A: sign (Symptom)
B: cluster of signs (Symptomkomplex)
C: syndromic diagnosis (Bild einer Diagnose)
D: proven diagnosis (diagnostisch gesichert)';


--
-- Name: COLUMN health_issue.summary; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON COLUMN clin.health_issue.summary IS 'Used for tracking the summary of this health issue.';


--
-- Name: org; Type: TABLE; Schema: dem; Owner: gm-dbo
--

CREATE TABLE dem.org (
    pk integer NOT NULL,
    description text,
    fk_category integer,
    fk_data_source integer,
    CONSTRAINT org_sane_description CHECK ((gm.is_null_or_blank_string(description) IS FALSE))
)
INHERITS (audit.audit_fields);


ALTER TABLE dem.org OWNER TO "gm-dbo";

--
-- Name: TABLE org; Type: COMMENT; Schema: dem; Owner: gm-dbo
--

COMMENT ON TABLE dem.org IS 'Organizations at a conceptual level.';


--
-- Name: COLUMN org.description; Type: COMMENT; Schema: dem; Owner: gm-dbo
--

COMMENT ON COLUMN dem.org.description IS 'High-level, conceptual description (= name) of organization, such as "University of Manchester".';


--
-- Name: COLUMN org.fk_data_source; Type: COMMENT; Schema: dem; Owner: gm-dbo
--

COMMENT ON COLUMN dem.org.fk_data_source IS 'Source of the organization data.';


--
-- Name: org_unit; Type: TABLE; Schema: dem; Owner: gm-dbo
--

CREATE TABLE dem.org_unit (
    pk integer NOT NULL,
    description text,
    fk_org integer NOT NULL,
    fk_address integer,
    fk_category integer,
    CONSTRAINT org_unit_sane_description CHECK ((gm.is_null_or_blank_string(description) IS FALSE))
)
INHERITS (audit.audit_fields);


ALTER TABLE dem.org_unit OWNER TO "gm-dbo";

--
-- Name: TABLE org_unit; Type: COMMENT; Schema: dem; Owner: gm-dbo
--

COMMENT ON TABLE dem.org_unit IS 'Actual branches/departments/offices/... of organizations.';


--
-- Name: COLUMN org_unit.description; Type: COMMENT; Schema: dem; Owner: gm-dbo
--

COMMENT ON COLUMN dem.org_unit.description IS 'Description (= name) of branch of organization, such as "Elms Street office of Jim Busser Praxis".';


--
-- Name: v_doc_med; Type: VIEW; Schema: blobs; Owner: gm-dbo
--

CREATE VIEW blobs.v_doc_med AS
 SELECT c_enc.fk_patient AS pk_patient,
    b_dm.pk AS pk_doc,
    b_dm.clin_when,
    b_dt.name AS type,
    public._(b_dt.name) AS l10n_type,
    b_dm.ext_ref,
    b_dm.comment,
    c_epi.description AS episode,
    c_hi.description AS health_issue,
    c_epi.is_open AS episode_open,
    d_ou.description AS unit,
    d_o.description AS organization,
    b_dm.unit_is_receiver,
    COALESCE(( SELECT array_agg(b_do.seq_idx) AS array_agg
           FROM blobs.doc_obj b_do
          WHERE (b_do.fk_doc = b_dm.pk)), ARRAY[]::integer[]) AS seq_idx_list,
    b_dm.fk_type AS pk_type,
    b_dm.fk_encounter AS pk_encounter,
    b_dm.fk_episode AS pk_episode,
    c_epi.fk_health_issue AS pk_health_issue,
    d_ou.fk_org AS pk_org,
    b_dm.fk_hospital_stay AS pk_hospital_stay,
    b_dm.fk_org_unit AS pk_org_unit,
    b_dm.modified_when,
    b_dm.modified_by,
    b_dm.xmin AS xmin_doc_med
   FROM ((((((blobs.doc_med b_dm
     JOIN blobs.doc_type b_dt ON ((b_dm.fk_type = b_dt.pk)))
     JOIN clin.encounter c_enc ON ((b_dm.fk_encounter = c_enc.pk)))
     JOIN clin.episode c_epi ON ((b_dm.fk_episode = c_epi.pk)))
     LEFT JOIN clin.health_issue c_hi ON ((c_hi.pk = c_epi.fk_health_issue)))
     LEFT JOIN dem.org_unit d_ou ON ((b_dm.fk_org_unit = d_ou.pk)))
     LEFT JOIN dem.org d_o ON ((d_ou.fk_org = d_o.pk)));


ALTER TABLE blobs.v_doc_med OWNER TO "gm-dbo";

--
-- Name: v_doc_desc; Type: VIEW; Schema: blobs; Owner: gm-dbo
--

CREATE VIEW blobs.v_doc_desc AS
 SELECT vdm.pk_patient,
    dd.fk_doc AS pk_doc,
    dd.text AS description,
    vdm.pk_encounter,
    vdm.pk_episode,
    vdm.pk_health_issue,
    dd.pk AS pk_doc_desc
   FROM blobs.doc_desc dd,
    blobs.v_doc_med vdm
  WHERE (dd.fk_doc = vdm.pk_doc);


ALTER TABLE blobs.v_doc_desc OWNER TO "gm-dbo";

--
-- Name: VIEW v_doc_desc; Type: COMMENT; Schema: blobs; Owner: gm-dbo
--

COMMENT ON VIEW blobs.v_doc_desc IS 'aggregates some document data descriptions';


--
-- Name: encounter_type; Type: TABLE; Schema: clin; Owner: gm-dbo
--

CREATE TABLE clin.encounter_type (
    pk integer NOT NULL,
    description text NOT NULL
);


ALTER TABLE clin.encounter_type OWNER TO "gm-dbo";

--
-- Name: TABLE encounter_type; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON TABLE clin.encounter_type IS 'these are the types of encounter';


--
-- Name: v_doc_med_journal; Type: VIEW; Schema: blobs; Owner: gm-dbo
--

CREATE VIEW blobs.v_doc_med_journal AS
 SELECT c_enc.fk_patient AS pk_patient,
    b_dm.modified_when,
    b_dm.clin_when,
    COALESCE(( SELECT staff.short_alias
           FROM dem.staff
          WHERE (staff.db_user = b_dm.modified_by)), (('<'::text || (b_dm.modified_by)::text) || '>'::text)) AS modified_by,
    NULL::text AS soap_cat,
    ('"'::text || ((((((((((((((public._(b_dt.name) || '" '::text) || public._('with'::text)) || ' '::text) || ( SELECT count(1) AS count
           FROM blobs.doc_obj b_do
          WHERE (b_do.fk_doc = b_dm.pk))) || ' '::text) || public._('part(s)'::text)) || '
'::text) || ' '::text) || to_char(b_dm.clin_when, 'YYYY-MM-DD HH24:MI'::text)) || '
'::text) || COALESCE(((' ['::text || b_dm.ext_ref) || ']'::text), ''::text)) ||
        CASE
            WHEN b_dm.unit_is_receiver THEN COALESCE(((((((' '::text || public._('sent to'::text)) || d_ou.description) || ' '::text) || public._('of'::text)) || ' '::text) || d_o.description), ''::text)
            ELSE COALESCE((((((' @ '::text || d_ou.description) || ' '::text) || public._('of'::text)) || ' '::text) || d_o.description), ''::text)
        END) || '
'::text) || COALESCE((' '::text || b_dm.comment), ''::text))) AS narrative,
    b_dm.fk_encounter AS pk_encounter,
    b_dm.fk_episode AS pk_episode,
    c_epi.fk_health_issue AS pk_health_issue,
    b_dm.pk AS src_pk,
    'blobs.doc_med'::text AS src_table,
    b_dm.row_version,
    c_hi.description AS health_issue,
    c_hi.laterality AS issue_laterality,
    c_hi.is_active AS issue_active,
    c_hi.clinically_relevant AS issue_clinically_relevant,
    c_hi.is_confidential AS issue_confidential,
    c_epi.description AS episode,
    c_epi.is_open AS episode_open,
    c_enc.started AS encounter_started,
    c_enc.last_affirmed AS encounter_last_affirmed,
    c_ety.description AS encounter_type,
    public._(c_ety.description) AS encounter_l10n_type
   FROM (((((((blobs.doc_med b_dm
     JOIN clin.encounter c_enc ON ((b_dm.fk_encounter = c_enc.pk)))
     JOIN clin.encounter_type c_ety ON ((c_enc.fk_type = c_ety.pk)))
     JOIN blobs.doc_type b_dt ON ((b_dm.fk_type = b_dt.pk)))
     JOIN clin.episode c_epi ON ((b_dm.fk_episode = c_epi.pk)))
     LEFT JOIN clin.health_issue c_hi ON ((c_epi.fk_health_issue = c_hi.pk)))
     LEFT JOIN dem.org_unit d_ou ON ((b_dm.fk_org_unit = d_ou.pk)))
     LEFT JOIN dem.org d_o ON ((d_ou.fk_org = d_o.pk)));


ALTER TABLE blobs.v_doc_med_journal OWNER TO "gm-dbo";

--
-- Name: document_type; Type: TABLE; Schema: ref; Owner: gm-dbo
--

CREATE TABLE ref.document_type (
    pk integer NOT NULL,
    scope text,
    description text NOT NULL
);


ALTER TABLE ref.document_type OWNER TO "gm-dbo";

--
-- Name: TABLE document_type; Type: COMMENT; Schema: ref; Owner: gm-dbo
--

COMMENT ON TABLE ref.document_type IS 'pre-installed document types, do not
	 change these as they will be overwritten
	 during database upgrades at the discretion
	 of the GNUmed team';


--
-- Name: COLUMN document_type.scope; Type: COMMENT; Schema: ref; Owner: gm-dbo
--

COMMENT ON COLUMN ref.document_type.scope IS 'can be used to group document types according
	 to applicability, say, per country';


--
-- Name: v_doc_type; Type: VIEW; Schema: blobs; Owner: gm-dbo
--

CREATE VIEW blobs.v_doc_type AS
 SELECT dt.pk AS pk_doc_type,
    dt.name AS type,
    public._(dt.name) AS l10n_type,
    (NOT (EXISTS ( SELECT 1
           FROM ref.document_type
          WHERE (document_type.description = dt.name)))) AS is_user_defined,
    (EXISTS ( SELECT 1
           FROM blobs.doc_med
          WHERE (doc_med.fk_type = dt.pk))) AS is_in_use,
    dt.xmin AS xmin_doc_type
   FROM blobs.doc_type dt;


ALTER TABLE blobs.v_doc_type OWNER TO "gm-dbo";

--
-- Name: VIEW v_doc_type; Type: COMMENT; Schema: blobs; Owner: gm-dbo
--

COMMENT ON VIEW blobs.v_doc_type IS 'list active document types, those that are activated for use';


--
-- Name: v_obj4doc_no_data; Type: VIEW; Schema: blobs; Owner: gm-dbo
--

CREATE VIEW blobs.v_obj4doc_no_data AS
 SELECT b_vdm.pk_patient,
    b_do.pk AS pk_obj,
    b_do.seq_idx,
    octet_length(COALESCE(b_do.data, '\x'::bytea)) AS size,
    b_vdm.clin_when AS date_generated,
    b_vdm.type,
    b_vdm.l10n_type,
    b_vdm.ext_ref,
    b_vdm.episode,
    b_vdm.comment AS doc_comment,
    b_do.comment AS obj_comment,
    b_do.filename,
    b_do.fk_intended_reviewer AS pk_intended_reviewer,
    (EXISTS ( SELECT 1
           FROM blobs.reviewed_doc_objs
          WHERE (reviewed_doc_objs.fk_reviewed_row = b_do.pk))) AS reviewed,
    (EXISTS ( SELECT 1
           FROM blobs.reviewed_doc_objs
          WHERE ((reviewed_doc_objs.fk_reviewed_row = b_do.pk) AND (reviewed_doc_objs.fk_reviewer = ( SELECT staff.pk
                   FROM dem.staff
                  WHERE (staff.db_user = "current_user"())))))) AS reviewed_by_you,
    (EXISTS ( SELECT 1
           FROM blobs.reviewed_doc_objs
          WHERE ((reviewed_doc_objs.fk_reviewed_row = b_do.pk) AND (reviewed_doc_objs.fk_reviewer = b_do.fk_intended_reviewer)))) AS reviewed_by_intended_reviewer,
    b_vdm.pk_doc,
    b_vdm.pk_type,
    b_vdm.pk_encounter,
    b_vdm.pk_episode,
    b_vdm.pk_health_issue,
    b_vdm.pk_org,
    b_vdm.pk_org_unit,
    b_vdm.pk_hospital_stay,
    b_do.xmin AS xmin_doc_obj
   FROM (blobs.v_doc_med b_vdm
     JOIN blobs.doc_obj b_do ON ((b_do.fk_doc = b_vdm.pk_doc)))
  WHERE (b_vdm.pk_doc = b_do.fk_doc);


ALTER TABLE blobs.v_obj4doc_no_data OWNER TO "gm-dbo";

--
-- Name: VIEW v_obj4doc_no_data; Type: COMMENT; Schema: blobs; Owner: gm-dbo
--

COMMENT ON VIEW blobs.v_obj4doc_no_data IS 'denormalized metadata for blobs.doc_obj but without the data itself';


--
-- Name: v_latest_mugshot; Type: VIEW; Schema: blobs; Owner: gm-dbo
--

CREATE VIEW blobs.v_latest_mugshot AS
 SELECT b_vo4d.pk_patient,
    b_vo4d.doc_comment,
    b_vo4d.date_generated AS date_taken,
    b_vo4d.ext_ref,
    b_vo4d.seq_idx AS obj_seq_idx,
    b_vo4d.obj_comment,
    b_vo4d.pk_doc,
    b_vo4d.pk_obj,
    b_do.data AS image
   FROM blobs.v_obj4doc_no_data b_vo4d,
    blobs.doc_obj b_do
  WHERE ((b_vo4d.type = 'patient photograph'::text) AND (b_vo4d.seq_idx = ( SELECT max(b_vo4d1.seq_idx) AS max
           FROM blobs.v_obj4doc_no_data b_vo4d1
          WHERE ((b_vo4d1.pk_patient = b_vo4d.pk_patient) AND (b_vo4d1.type = 'patient photograph'::text))
          GROUP BY b_vo4d1.date_generated
          ORDER BY b_vo4d1.date_generated DESC
         LIMIT 1)) AND (b_do.pk = b_vo4d.pk_obj));


ALTER TABLE blobs.v_latest_mugshot OWNER TO "gm-dbo";

--
-- Name: VIEW v_latest_mugshot; Type: COMMENT; Schema: blobs; Owner: gm-dbo
--

COMMENT ON VIEW blobs.v_latest_mugshot IS 'shows the latest picture of the patient, currently the highest
	 seq_idx of the newest document of type "patient photograph"';


--
-- Name: v_reviewed_doc_objects; Type: VIEW; Schema: blobs; Owner: gm-dbo
--

CREATE VIEW blobs.v_reviewed_doc_objects AS
 SELECT b_rdo.fk_reviewed_row AS pk_doc_obj,
    COALESCE(d_s.short_alias, (('<#'::text || b_rdo.fk_reviewer) || '>'::text)) AS reviewer,
    b_rdo.is_technically_abnormal,
    b_rdo.clinically_relevant,
    (b_rdo.fk_reviewer = b_do.fk_intended_reviewer) AS is_review_by_responsible_reviewer,
    (b_rdo.fk_reviewer = ( SELECT staff.pk
           FROM dem.staff
          WHERE (staff.db_user = CURRENT_USER))) AS is_your_review,
    b_rdo.comment,
    b_rdo.modified_when AS reviewed_when,
    b_rdo.modified_by,
    b_rdo.pk AS pk_review_root,
    b_rdo.fk_reviewer AS pk_reviewer,
    c_enc.fk_patient AS pk_patient,
    b_dm.fk_encounter AS pk_encounter,
    b_dm.fk_episode AS pk_episode,
    c_epi.fk_health_issue AS pk_health_issue
   FROM (((((blobs.reviewed_doc_objs b_rdo
     LEFT JOIN dem.staff d_s ON ((d_s.pk = b_rdo.fk_reviewer)))
     LEFT JOIN blobs.doc_obj b_do ON ((b_do.pk = b_rdo.fk_reviewed_row)))
     LEFT JOIN blobs.doc_med b_dm ON ((b_do.fk_doc = b_dm.pk)))
     LEFT JOIN clin.episode c_epi ON ((b_dm.fk_episode = c_epi.pk)))
     LEFT JOIN clin.encounter c_enc ON ((c_epi.fk_encounter = c_enc.pk)));


ALTER TABLE blobs.v_reviewed_doc_objects OWNER TO "gm-dbo";

--
-- Name: v_unreviewed_docs; Type: VIEW; Schema: blobs; Owner: gm-dbo
--

CREATE VIEW blobs.v_unreviewed_docs AS
 SELECT DISTINCT ON (b_do.fk_doc) b_do.fk_doc AS pk_doc,
    min(b_do.fk_intended_reviewer) AS pk_intended_reviewer,
    min(c_e.fk_patient) AS pk_patient
   FROM ((blobs.doc_obj b_do
     JOIN blobs.doc_med b_dm ON ((b_dm.pk = b_do.fk_doc)))
     JOIN clin.encounter c_e ON ((b_dm.fk_encounter = c_e.pk)))
  WHERE (NOT (EXISTS ( SELECT 1
           FROM blobs.reviewed_doc_objs b_rdo
          WHERE (b_rdo.fk_reviewed_row = b_do.pk))))
  GROUP BY b_do.fk_doc;


ALTER TABLE blobs.v_unreviewed_docs OWNER TO "gm-dbo";

--
-- Name: v_unreviewed_docs_summary; Type: VIEW; Schema: blobs; Owner: gm-dbo
--

CREATE VIEW blobs.v_unreviewed_docs_summary AS
 SELECT b_vud.pk_patient,
    count(1) AS no_of_docs
   FROM blobs.v_unreviewed_docs b_vud
  GROUP BY b_vud.pk_patient;


ALTER TABLE blobs.v_unreviewed_docs_summary OWNER TO "gm-dbo";

--
-- Name: inbox_item_category; Type: TABLE; Schema: dem; Owner: gm-dbo
--

CREATE TABLE dem.inbox_item_category (
    pk integer NOT NULL,
    description text NOT NULL,
    is_user boolean DEFAULT true NOT NULL,
    CONSTRAINT inbox_item_category_description_check CHECK ((btrim(COALESCE(description, 'xxxDEFAULTxxx'::text)) <> ''::text))
)
INHERITS (audit.audit_fields);


ALTER TABLE dem.inbox_item_category OWNER TO "gm-dbo";

--
-- Name: TABLE inbox_item_category; Type: COMMENT; Schema: dem; Owner: gm-dbo
--

COMMENT ON TABLE dem.inbox_item_category IS 'Holds the various categories of messages that
	 can show up in the provider inbox.';


--
-- Name: COLUMN inbox_item_category.description; Type: COMMENT; Schema: dem; Owner: gm-dbo
--

COMMENT ON COLUMN dem.inbox_item_category.description IS '
"clinical"

"admin"

"personal"

...';


--
-- Name: COLUMN inbox_item_category.is_user; Type: COMMENT; Schema: dem; Owner: gm-dbo
--

COMMENT ON COLUMN dem.inbox_item_category.is_user IS 'whether this category was added locally,
	 as to be left alone by database upgrades';


--
-- Name: inbox_item_type; Type: TABLE; Schema: dem; Owner: gm-dbo
--

CREATE TABLE dem.inbox_item_type (
    pk integer NOT NULL,
    fk_inbox_item_category integer NOT NULL,
    description text NOT NULL,
    is_user boolean DEFAULT true NOT NULL,
    CONSTRAINT inbox_item_type_description_check CHECK ((btrim(COALESCE(description, 'xxxDEFAULTxxx'::text)) <> ''::text))
)
INHERITS (audit.audit_fields);


ALTER TABLE dem.inbox_item_type OWNER TO "gm-dbo";

--
-- Name: TABLE inbox_item_type; Type: COMMENT; Schema: dem; Owner: gm-dbo
--

COMMENT ON TABLE dem.inbox_item_type IS 'Holds the various types of messages that can
	 show up in the provider inbox.';


--
-- Name: COLUMN inbox_item_type.fk_inbox_item_category; Type: COMMENT; Schema: dem; Owner: gm-dbo
--

COMMENT ON COLUMN dem.inbox_item_type.fk_inbox_item_category IS 'The category of this item type.';


--
-- Name: COLUMN inbox_item_type.description; Type: COMMENT; Schema: dem; Owner: gm-dbo
--

COMMENT ON COLUMN dem.inbox_item_type.description IS 'the various types of inbox items';


--
-- Name: COLUMN inbox_item_type.is_user; Type: COMMENT; Schema: dem; Owner: gm-dbo
--

COMMENT ON COLUMN dem.inbox_item_type.is_user IS 'whether this type was added locally,
	 as to be left alone by database upgrades';


--
-- Name: names; Type: TABLE; Schema: dem; Owner: gm-dbo
--

CREATE TABLE dem.names (
    id integer NOT NULL,
    id_identity integer NOT NULL,
    active boolean DEFAULT true,
    lastnames text NOT NULL,
    firstnames text NOT NULL,
    preferred text,
    comment text
);


ALTER TABLE dem.names OWNER TO "gm-dbo";

--
-- Name: TABLE names; Type: COMMENT; Schema: dem; Owner: gm-dbo
--

COMMENT ON TABLE dem.names IS 'all the names an identity is known under;
	 As opposed to the versioning of all other tables, changed names
	 should not be moved into the audit trail tables. Search functionality
	 must be available at any time for all names a person ever had.';


--
-- Name: COLUMN names.active; Type: COMMENT; Schema: dem; Owner: gm-dbo
--

COMMENT ON COLUMN dem.names.active IS 'true if the name is still in use';


--
-- Name: COLUMN names.lastnames; Type: COMMENT; Schema: dem; Owner: gm-dbo
--

COMMENT ON COLUMN dem.names.lastnames IS 'all last names of an identity in legal order,

	 IOW "major" name, "group identifier", eg. family, village, tribe, ...';


--
-- Name: COLUMN names.firstnames; Type: COMMENT; Schema: dem; Owner: gm-dbo
--

COMMENT ON COLUMN dem.names.firstnames IS 'all first names of an identity in legal order,

	 IOW "minor" name, identifier of this identity within

	 the group defined by <lastnames>';


--
-- Name: COLUMN names.preferred; Type: COMMENT; Schema: dem; Owner: gm-dbo
--

COMMENT ON COLUMN dem.names.preferred IS 'preferred first name, the name a person is usually

	 called (nickname, warrior name)';


--
-- Name: COLUMN names.comment; Type: COMMENT; Schema: dem; Owner: gm-dbo
--

COMMENT ON COLUMN dem.names.comment IS 'a comment regarding this name, useful in things like "this was
	 the name before marriage" etc';


--
-- Name: v_inbox_item_type; Type: VIEW; Schema: dem; Owner: gm-dbo
--

CREATE VIEW dem.v_inbox_item_type AS
 SELECT it.description AS type,
    public._(it.description) AS l10n_type,
    ic.description AS category,
    public._(ic.description) AS l10n_category,
    it.is_user AS is_user_type,
    ic.is_user AS is_user_category,
    it.pk AS pk_type,
    it.fk_inbox_item_category AS pk_category
   FROM dem.inbox_item_type it,
    dem.inbox_item_category ic
  WHERE (it.fk_inbox_item_category = ic.pk);


ALTER TABLE dem.v_inbox_item_type OWNER TO "gm-dbo";

--
-- Name: v_unreviewed_docs_inbox; Type: VIEW; Schema: blobs; Owner: gm-dbo
--

CREATE VIEW blobs.v_unreviewed_docs_inbox AS
 SELECT now() AS received_when,
    '<system>'::text AS modified_by,
    NULL::text AS provider,
    0 AS importance,
    'clinical'::text AS category,
    public._('clinical'::text) AS l10n_category,
    'review docs'::text AS type,
    public._('review docs'::text) AS l10n_type,
    ( SELECT ((((((b_vuds.no_of_docs || ' '::text) || public._('unreviewed documents for patient'::text)) || ' '::text) || d_n.lastnames) || ', '::text) || d_n.firstnames)) AS comment,
    NULL::integer[] AS pk_context,
    NULL::text AS data,
    NULL::integer AS pk_inbox_message,
    ( SELECT staff.pk
           FROM dem.staff
          WHERE (staff.db_user = "current_user"())) AS pk_staff,
    ( SELECT v_inbox_item_type.pk_category
           FROM dem.v_inbox_item_type
          WHERE (v_inbox_item_type.type = 'review docs'::text)) AS pk_category,
    ( SELECT v_inbox_item_type.pk_type
           FROM dem.v_inbox_item_type
          WHERE (v_inbox_item_type.type = 'review docs'::text)) AS pk_type,
    b_vuds.pk_patient,
    true AS is_virtual,
    (now() - '01:00:00'::interval) AS due_date,
    NULL::timestamp with time zone AS expiry_date,
    true AS is_overdue,
    false AS is_expired,
    '01:00:00'::interval AS interval_due,
    NULL::integer AS xmin_message_inbox
   FROM (blobs.v_unreviewed_docs_summary b_vuds
     JOIN dem.names d_n ON ((b_vuds.pk_patient = d_n.id_identity)))
  WHERE (d_n.active IS TRUE);


ALTER TABLE blobs.v_unreviewed_docs_inbox OWNER TO "gm-dbo";

--
-- Name: cfg_data; Type: TABLE; Schema: cfg; Owner: gm-dbo
--

CREATE TABLE cfg.cfg_data (
    fk_item integer NOT NULL,
    value bytea NOT NULL,
    pk integer NOT NULL
);


ALTER TABLE cfg.cfg_data OWNER TO "gm-dbo";

--
-- Name: TABLE cfg_data; Type: COMMENT; Schema: cfg; Owner: gm-dbo
--

COMMENT ON TABLE cfg.cfg_data IS 'stores opaque configuration data, either text or binary,
	 note that it will be difficult to share such options
	 among different types of clients';


--
-- Name: cfg_data_pk_seq; Type: SEQUENCE; Schema: cfg; Owner: gm-dbo
--

CREATE SEQUENCE cfg.cfg_data_pk_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE cfg.cfg_data_pk_seq OWNER TO "gm-dbo";

--
-- Name: cfg_data_pk_seq; Type: SEQUENCE OWNED BY; Schema: cfg; Owner: gm-dbo
--

ALTER SEQUENCE cfg.cfg_data_pk_seq OWNED BY cfg.cfg_data.pk;


--
-- Name: cfg_item; Type: TABLE; Schema: cfg; Owner: gm-dbo
--

CREATE TABLE cfg.cfg_item (
    pk integer NOT NULL,
    fk_template integer NOT NULL,
    owner name DEFAULT "current_user"() NOT NULL,
    workplace text,
    cookie text
);


ALTER TABLE cfg.cfg_item OWNER TO "gm-dbo";

--
-- Name: TABLE cfg_item; Type: COMMENT; Schema: cfg; Owner: gm-dbo
--

COMMENT ON TABLE cfg.cfg_item IS 'this table holds all "instances" of cfg.cfg_template';


--
-- Name: COLUMN cfg_item.fk_template; Type: COMMENT; Schema: cfg; Owner: gm-dbo
--

COMMENT ON COLUMN cfg.cfg_item.fk_template IS 'this points to the class of this option, think of this as a base object, this also defines the data type';


--
-- Name: COLUMN cfg_item.owner; Type: COMMENT; Schema: cfg; Owner: gm-dbo
--

COMMENT ON COLUMN cfg.cfg_item.owner IS 'the database level user this option belongs to; this
	 is the "role" of the user from the perspective of
	 the database; can be "default" at the application
	 level to indicate that it does not care';


--
-- Name: COLUMN cfg_item.workplace; Type: COMMENT; Schema: cfg; Owner: gm-dbo
--

COMMENT ON COLUMN cfg.cfg_item.workplace IS '- the logical workplace this option pertains to
	 - can be a hostname but should be a logical rather
	   than a physical identifier as machines get moved,
	   workplaces do not, kind of like a "role" for the
	   machine
	 - associate this with a physical workplace through
	   a local config file or environment variable';


--
-- Name: COLUMN cfg_item.cookie; Type: COMMENT; Schema: cfg; Owner: gm-dbo
--

COMMENT ON COLUMN cfg.cfg_item.cookie IS 'an arbitrary, opaque entity the client code can use
	 to associate this config item with even finer grained
	 context; could be the pertinent patient ID for patient
	 specific options';


--
-- Name: cfg_item_pk_seq; Type: SEQUENCE; Schema: cfg; Owner: gm-dbo
--

CREATE SEQUENCE cfg.cfg_item_pk_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE cfg.cfg_item_pk_seq OWNER TO "gm-dbo";

--
-- Name: cfg_item_pk_seq; Type: SEQUENCE OWNED BY; Schema: cfg; Owner: gm-dbo
--

ALTER SEQUENCE cfg.cfg_item_pk_seq OWNED BY cfg.cfg_item.pk;


--
-- Name: cfg_numeric; Type: TABLE; Schema: cfg; Owner: gm-dbo
--

CREATE TABLE cfg.cfg_numeric (
    fk_item integer NOT NULL,
    value numeric NOT NULL,
    pk integer NOT NULL
);


ALTER TABLE cfg.cfg_numeric OWNER TO "gm-dbo";

--
-- Name: cfg_numeric_pk_seq; Type: SEQUENCE; Schema: cfg; Owner: gm-dbo
--

CREATE SEQUENCE cfg.cfg_numeric_pk_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE cfg.cfg_numeric_pk_seq OWNER TO "gm-dbo";

--
-- Name: cfg_numeric_pk_seq; Type: SEQUENCE OWNED BY; Schema: cfg; Owner: gm-dbo
--

ALTER SEQUENCE cfg.cfg_numeric_pk_seq OWNED BY cfg.cfg_numeric.pk;


--
-- Name: cfg_str_array; Type: TABLE; Schema: cfg; Owner: gm-dbo
--

CREATE TABLE cfg.cfg_str_array (
    fk_item integer NOT NULL,
    value text[] NOT NULL,
    pk integer NOT NULL
);


ALTER TABLE cfg.cfg_str_array OWNER TO "gm-dbo";

--
-- Name: cfg_str_array_pk_seq; Type: SEQUENCE; Schema: cfg; Owner: gm-dbo
--

CREATE SEQUENCE cfg.cfg_str_array_pk_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE cfg.cfg_str_array_pk_seq OWNER TO "gm-dbo";

--
-- Name: cfg_str_array_pk_seq; Type: SEQUENCE OWNED BY; Schema: cfg; Owner: gm-dbo
--

ALTER SEQUENCE cfg.cfg_str_array_pk_seq OWNED BY cfg.cfg_str_array.pk;


--
-- Name: cfg_string; Type: TABLE; Schema: cfg; Owner: gm-dbo
--

CREATE TABLE cfg.cfg_string (
    fk_item integer NOT NULL,
    value text NOT NULL,
    pk integer NOT NULL
);


ALTER TABLE cfg.cfg_string OWNER TO "gm-dbo";

--
-- Name: cfg_string_pk_seq; Type: SEQUENCE; Schema: cfg; Owner: gm-dbo
--

CREATE SEQUENCE cfg.cfg_string_pk_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE cfg.cfg_string_pk_seq OWNER TO "gm-dbo";

--
-- Name: cfg_string_pk_seq; Type: SEQUENCE OWNED BY; Schema: cfg; Owner: gm-dbo
--

ALTER SEQUENCE cfg.cfg_string_pk_seq OWNED BY cfg.cfg_string.pk;


--
-- Name: cfg_template; Type: TABLE; Schema: cfg; Owner: gm-dbo
--

CREATE TABLE cfg.cfg_template (
    pk integer NOT NULL,
    name text DEFAULT 'must set this !'::text NOT NULL,
    type text NOT NULL,
    cfg_group text DEFAULT 'xxxDEFAULTxxx'::text NOT NULL,
    description text DEFAULT 'programmer is an avid Camel Book Reader'::text NOT NULL
);


ALTER TABLE cfg.cfg_template OWNER TO "gm-dbo";

--
-- Name: TABLE cfg_template; Type: COMMENT; Schema: cfg; Owner: gm-dbo
--

COMMENT ON TABLE cfg.cfg_template IS 'meta definition of config items';


--
-- Name: COLUMN cfg_template.name; Type: COMMENT; Schema: cfg; Owner: gm-dbo
--

COMMENT ON COLUMN cfg.cfg_template.name IS 'the name of the option; this MUST be set to something meaningful';


--
-- Name: COLUMN cfg_template.type; Type: COMMENT; Schema: cfg; Owner: gm-dbo
--

COMMENT ON COLUMN cfg.cfg_template.type IS 'type of the value';


--
-- Name: COLUMN cfg_template.cfg_group; Type: COMMENT; Schema: cfg; Owner: gm-dbo
--

COMMENT ON COLUMN cfg.cfg_template.cfg_group IS 'just for logical grouping of options according to task sets to facilitate better config management';


--
-- Name: COLUMN cfg_template.description; Type: COMMENT; Schema: cfg; Owner: gm-dbo
--

COMMENT ON COLUMN cfg.cfg_template.description IS 'arbitrary description (user visible)';


--
-- Name: cfg_template_pk_seq; Type: SEQUENCE; Schema: cfg; Owner: gm-dbo
--

CREATE SEQUENCE cfg.cfg_template_pk_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE cfg.cfg_template_pk_seq OWNER TO "gm-dbo";

--
-- Name: cfg_template_pk_seq; Type: SEQUENCE OWNED BY; Schema: cfg; Owner: gm-dbo
--

ALTER SEQUENCE cfg.cfg_template_pk_seq OWNED BY cfg.cfg_template.pk;


--
-- Name: cfg_type_enum; Type: TABLE; Schema: cfg; Owner: gm-dbo
--

CREATE TABLE cfg.cfg_type_enum (
    name text NOT NULL
);


ALTER TABLE cfg.cfg_type_enum OWNER TO "gm-dbo";

--
-- Name: TABLE cfg_type_enum; Type: COMMENT; Schema: cfg; Owner: gm-dbo
--

COMMENT ON TABLE cfg.cfg_type_enum IS 'enumeration of config option data types';


--
-- Name: config; Type: TABLE; Schema: cfg; Owner: gm-dbo
--

CREATE TABLE cfg.config (
    id integer NOT NULL,
    profile character(25) DEFAULT 'default'::bpchar,
    username character(25) DEFAULT "current_user"(),
    ddb integer,
    db integer,
    crypt_pwd text,
    crypt_algo text,
    pwd_hash text,
    hash_algo text
);


ALTER TABLE cfg.config OWNER TO "gm-dbo";

--
-- Name: TABLE config; Type: COMMENT; Schema: cfg; Owner: gm-dbo
--

COMMENT ON TABLE cfg.config IS 'maps a service name to a database location
	 for a particular user, includes user
	 credentials for that database';


--
-- Name: COLUMN config.profile; Type: COMMENT; Schema: cfg; Owner: gm-dbo
--

COMMENT ON COLUMN cfg.config.profile IS 'allows multiple profiles per user/
	 pseudo user, one user may have different
	 configuration profiles depending on role,
	 need and location';


--
-- Name: COLUMN config.username; Type: COMMENT; Schema: cfg; Owner: gm-dbo
--

COMMENT ON COLUMN cfg.config.username IS 'user name as used within the GNUmed system';


--
-- Name: COLUMN config.ddb; Type: COMMENT; Schema: cfg; Owner: gm-dbo
--

COMMENT ON COLUMN cfg.config.ddb IS 'which GNUmed service are we mapping to a database here';


--
-- Name: COLUMN config.db; Type: COMMENT; Schema: cfg; Owner: gm-dbo
--

COMMENT ON COLUMN cfg.config.db IS 'how to reach the database host for this service';


--
-- Name: COLUMN config.crypt_pwd; Type: COMMENT; Schema: cfg; Owner: gm-dbo
--

COMMENT ON COLUMN cfg.config.crypt_pwd IS 'password for user and database, encrypted';


--
-- Name: COLUMN config.crypt_algo; Type: COMMENT; Schema: cfg; Owner: gm-dbo
--

COMMENT ON COLUMN cfg.config.crypt_algo IS 'encryption algorithm used for password encryption';


--
-- Name: COLUMN config.pwd_hash; Type: COMMENT; Schema: cfg; Owner: gm-dbo
--

COMMENT ON COLUMN cfg.config.pwd_hash IS 'hash of the unencrypted password';


--
-- Name: COLUMN config.hash_algo; Type: COMMENT; Schema: cfg; Owner: gm-dbo
--

COMMENT ON COLUMN cfg.config.hash_algo IS 'algorithm used for password hashing';


--
-- Name: config_id_seq; Type: SEQUENCE; Schema: cfg; Owner: gm-dbo
--

CREATE SEQUENCE cfg.config_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE cfg.config_id_seq OWNER TO "gm-dbo";

--
-- Name: config_id_seq; Type: SEQUENCE OWNED BY; Schema: cfg; Owner: gm-dbo
--

ALTER SEQUENCE cfg.config_id_seq OWNED BY cfg.config.id;


--
-- Name: db_logon_banner; Type: TABLE; Schema: cfg; Owner: gm-dbo
--

CREATE TABLE cfg.db_logon_banner (
    message text,
    singularizer boolean DEFAULT true,
    CONSTRAINT db_logon_banner_message_check CHECK ((btrim(message) <> ''::text)),
    CONSTRAINT db_logon_banner_singularizer_check CHECK ((singularizer IS TRUE))
);


ALTER TABLE cfg.db_logon_banner OWNER TO "gm-dbo";

--
-- Name: TABLE db_logon_banner; Type: COMMENT; Schema: cfg; Owner: gm-dbo
--

COMMENT ON TABLE cfg.db_logon_banner IS 'Stores a banner to show when clients log on.';


--
-- Name: COLUMN db_logon_banner.message; Type: COMMENT; Schema: cfg; Owner: gm-dbo
--

COMMENT ON COLUMN cfg.db_logon_banner.message IS 'The actual message to show.';


--
-- Name: COLUMN db_logon_banner.singularizer; Type: COMMENT; Schema: cfg; Owner: gm-dbo
--

COMMENT ON COLUMN cfg.db_logon_banner.singularizer IS 'Makes sure there only ever is one such banner at any given time.';


--
-- Name: report_query; Type: TABLE; Schema: cfg; Owner: gm-dbo
--

CREATE TABLE cfg.report_query (
    pk integer NOT NULL,
    label text NOT NULL,
    cmd text NOT NULL,
    CONSTRAINT report_query_cmd_check CHECK ((btrim(COALESCE(cmd, 'NULL'::text)) <> ''::text)),
    CONSTRAINT report_query_label_check CHECK ((btrim(COALESCE(label, 'NULL'::text)) <> ''::text))
)
INHERITS (audit.audit_fields);


ALTER TABLE cfg.report_query OWNER TO "gm-dbo";

--
-- Name: TABLE report_query; Type: COMMENT; Schema: cfg; Owner: gm-dbo
--

COMMENT ON TABLE cfg.report_query IS 'This table stores SQL commands to be used in frontend report style queries.';


--
-- Name: report_query_pk_seq; Type: SEQUENCE; Schema: cfg; Owner: gm-dbo
--

CREATE SEQUENCE cfg.report_query_pk_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE cfg.report_query_pk_seq OWNER TO "gm-dbo";

--
-- Name: report_query_pk_seq; Type: SEQUENCE OWNED BY; Schema: cfg; Owner: gm-dbo
--

ALTER SEQUENCE cfg.report_query_pk_seq OWNED BY cfg.report_query.pk;


--
-- Name: v_cfg_options; Type: VIEW; Schema: cfg; Owner: gm-dbo
--

CREATE VIEW cfg.v_cfg_options AS
 SELECT cfg_t.name AS option,
    cfg_t.description,
    cfg_i.owner,
    cfg_i.workplace,
    cfg_i.cookie,
    cfg_t.type,
    cfg_t.cfg_group AS "group",
    cfg_t.pk AS pk_cfg_template,
    cfg_i.pk AS pk_cfg_item
   FROM cfg.cfg_template cfg_t,
    cfg.cfg_item cfg_i
  WHERE (cfg_i.fk_template = cfg_t.pk);


ALTER TABLE cfg.v_cfg_options OWNER TO "gm-dbo";

--
-- Name: v_cfg_opts_data; Type: VIEW; Schema: cfg; Owner: gm-dbo
--

CREATE VIEW cfg.v_cfg_opts_data AS
 SELECT cfg_t.name AS option,
    cfg_v.value,
    cfg_t.description,
    cfg_i.owner,
    cfg_i.workplace,
    cfg_i.cookie,
    cfg_t.cfg_group AS "group",
    cfg_t.pk AS pk_cfg_template,
    cfg_i.pk AS pk_cfg_item
   FROM cfg.cfg_template cfg_t,
    cfg.cfg_item cfg_i,
    cfg.cfg_data cfg_v
  WHERE ((cfg_i.fk_template = cfg_t.pk) AND (cfg_v.fk_item = cfg_i.pk));


ALTER TABLE cfg.v_cfg_opts_data OWNER TO "gm-dbo";

--
-- Name: v_cfg_opts_numeric; Type: VIEW; Schema: cfg; Owner: gm-dbo
--

CREATE VIEW cfg.v_cfg_opts_numeric AS
 SELECT cfg_t.name AS option,
    cfg_v.value,
    cfg_t.description,
    cfg_i.owner,
    cfg_i.workplace,
    cfg_i.cookie,
    cfg_t.cfg_group AS "group",
    cfg_t.pk AS pk_cfg_template,
    cfg_i.pk AS pk_cfg_item
   FROM cfg.cfg_template cfg_t,
    cfg.cfg_item cfg_i,
    cfg.cfg_numeric cfg_v
  WHERE ((cfg_i.fk_template = cfg_t.pk) AND (cfg_v.fk_item = cfg_i.pk));


ALTER TABLE cfg.v_cfg_opts_numeric OWNER TO "gm-dbo";

--
-- Name: v_cfg_opts_str_array; Type: VIEW; Schema: cfg; Owner: gm-dbo
--

CREATE VIEW cfg.v_cfg_opts_str_array AS
 SELECT cfg_t.name AS option,
    cfg_v.value,
    cfg_t.description,
    cfg_i.owner,
    cfg_i.workplace,
    cfg_i.cookie,
    cfg_t.cfg_group AS "group",
    cfg_t.pk AS pk_cfg_template,
    cfg_i.pk AS pk_cfg_item
   FROM cfg.cfg_template cfg_t,
    cfg.cfg_item cfg_i,
    cfg.cfg_str_array cfg_v
  WHERE ((cfg_i.fk_template = cfg_t.pk) AND (cfg_v.fk_item = cfg_i.pk));


ALTER TABLE cfg.v_cfg_opts_str_array OWNER TO "gm-dbo";

--
-- Name: v_cfg_opts_string; Type: VIEW; Schema: cfg; Owner: gm-dbo
--

CREATE VIEW cfg.v_cfg_opts_string AS
 SELECT cfg_t.name AS option,
    cfg_v.value,
    cfg_t.description,
    cfg_i.owner,
    cfg_i.workplace,
    cfg_i.cookie,
    cfg_t.cfg_group AS "group",
    cfg_t.pk AS pk_cfg_template,
    cfg_i.pk AS pk_cfg_item
   FROM cfg.cfg_template cfg_t,
    cfg.cfg_item cfg_i,
    cfg.cfg_string cfg_v
  WHERE ((cfg_i.fk_template = cfg_t.pk) AND (cfg_v.fk_item = cfg_i.pk));


ALTER TABLE cfg.v_cfg_opts_string OWNER TO "gm-dbo";

--
-- Name: _enum_allergy_type; Type: TABLE; Schema: clin; Owner: gm-dbo
--

CREATE TABLE clin._enum_allergy_type (
    pk integer NOT NULL,
    value text NOT NULL
);


ALTER TABLE clin._enum_allergy_type OWNER TO "gm-dbo";

--
-- Name: _enum_allergy_type_id_seq; Type: SEQUENCE; Schema: clin; Owner: gm-dbo
--

CREATE SEQUENCE clin._enum_allergy_type_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE clin._enum_allergy_type_id_seq OWNER TO "gm-dbo";

--
-- Name: _enum_allergy_type_id_seq; Type: SEQUENCE OWNED BY; Schema: clin; Owner: gm-dbo
--

ALTER SEQUENCE clin._enum_allergy_type_id_seq OWNED BY clin._enum_allergy_type.pk;


--
-- Name: allergy; Type: TABLE; Schema: clin; Owner: gm-dbo
--

CREATE TABLE clin.allergy (
    pk integer NOT NULL,
    substance text NOT NULL,
    substance_code text,
    generics text,
    allergene text,
    atc_code text,
    fk_type integer NOT NULL,
    generic_specific boolean DEFAULT false,
    definite boolean DEFAULT false
)
INHERITS (clin.clin_root_item);


ALTER TABLE clin.allergy OWNER TO "gm-dbo";

--
-- Name: TABLE allergy; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON TABLE clin.allergy IS 'patient allergy details';


--
-- Name: COLUMN allergy.narrative; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON COLUMN clin.allergy.narrative IS 'used as field "reaction"';


--
-- Name: COLUMN allergy.substance; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON COLUMN clin.allergy.substance IS 'real-world name of substance the patient reacted to, brand name if drug';


--
-- Name: COLUMN allergy.substance_code; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON COLUMN clin.allergy.substance_code IS 'data source specific opaque product code; must provide a link
	 to a unique product/substance in the database in use; should follow
	 the parseable convention of "<source>::<source version>::<identifier>",
	 e.g. "MIMS::2003-1::190" for Zantac; it is left as an exercise to the
	 application to know what to do with this information';


--
-- Name: COLUMN allergy.generics; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON COLUMN clin.allergy.generics IS 'names of generic compounds if drug; brand names change/disappear, generic names do not';


--
-- Name: COLUMN allergy.allergene; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON COLUMN clin.allergy.allergene IS 'name of allergenic ingredient in substance if known';


--
-- Name: COLUMN allergy.atc_code; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON COLUMN clin.allergy.atc_code IS 'ATC code of allergene or substance if approprate, applicable for penicilline, not so for cat fur';


--
-- Name: COLUMN allergy.fk_type; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON COLUMN clin.allergy.fk_type IS 'allergy/sensitivity';


--
-- Name: COLUMN allergy.generic_specific; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON COLUMN clin.allergy.generic_specific IS 'only meaningful for *drug*/*generic* reactions:
	 1) true: applies to one in "generics" forming "substance",
			  if more than one generic listed in "generics" then
			  "allergene" *must* contain the generic in question;
	 2) false: applies to drug class of "substance";';


--
-- Name: COLUMN allergy.definite; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON COLUMN clin.allergy.definite IS 'true: definite, false: not definite';


--
-- Name: allergy_state; Type: TABLE; Schema: clin; Owner: gm-dbo
--

CREATE TABLE clin.allergy_state (
    pk integer NOT NULL,
    has_allergy integer,
    comment text,
    last_confirmed timestamp with time zone,
    fk_encounter integer NOT NULL,
    CONSTRAINT allergy_state_check CHECK (((has_allergy IS NULL) OR ((has_allergy IS NOT NULL) AND (last_confirmed IS NOT NULL)))),
    CONSTRAINT allergy_state_has_allergy_check CHECK ((has_allergy = ANY (ARRAY[NULL::integer, 0, 1])))
)
INHERITS (audit.audit_fields);


ALTER TABLE clin.allergy_state OWNER TO "gm-dbo";

--
-- Name: COLUMN allergy_state.has_allergy; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON COLUMN clin.allergy_state.has_allergy IS 'patient allergenic state:
	 - null: unknown, not asked, no data available
	 - -1: unknown, asked, no data obtained
	 - 0:  known, asked, has no known allergies
	 - 1:  known, asked, does have allergies
	';


--
-- Name: COLUMN allergy_state.comment; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON COLUMN clin.allergy_state.comment IS 'A comment on the state, such as "patient says no allergies but I think he is holding back some".';


--
-- Name: COLUMN allergy_state.last_confirmed; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON COLUMN clin.allergy_state.last_confirmed IS 'When was the state of allergies last confirmed. Must be not NULL if has_allergy is not NULL.';


--
-- Name: clin_narrative; Type: TABLE; Schema: clin; Owner: gm-dbo
--

CREATE TABLE clin.clin_narrative (
    pk integer NOT NULL,
    CONSTRAINT narrative_neither_null_nor_empty CHECK ((btrim(COALESCE(narrative, ''::text)) <> ''::text))
)
INHERITS (clin.clin_root_item);


ALTER TABLE clin.clin_narrative OWNER TO "gm-dbo";

--
-- Name: TABLE clin_narrative; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON TABLE clin.clin_narrative IS 'Used to store clinical free text *not* associated
	 with any other table. Used to implement a simple
	 SOAP structure. Also other tags can be associated
	 via link tables.';


--
-- Name: COLUMN clin_narrative.clin_when; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON COLUMN clin.clin_narrative.clin_when IS 'when did the item reach clinical reality';


--
-- Name: external_care; Type: TABLE; Schema: clin; Owner: gm-dbo
--

CREATE TABLE clin.external_care (
    pk integer NOT NULL,
    fk_encounter integer NOT NULL,
    fk_health_issue integer,
    issue text,
    fk_org_unit integer NOT NULL,
    provider text,
    comment text,
    inactive boolean DEFAULT false NOT NULL,
    CONSTRAINT clin_ext_care_issue_xor_fk_issue CHECK ((((fk_health_issue IS NULL) AND (issue IS NOT NULL)) OR ((fk_health_issue IS NOT NULL) AND (issue IS NULL)))),
    CONSTRAINT clin_ext_care_sane_comment CHECK ((gm.is_null_or_non_empty_string(comment) IS TRUE)),
    CONSTRAINT clin_ext_care_sane_issue CHECK ((gm.is_null_or_non_empty_string(issue) IS TRUE)),
    CONSTRAINT clin_ext_care_sane_provider CHECK ((gm.is_null_or_non_empty_string(provider) IS TRUE))
)
INHERITS (audit.audit_fields);


ALTER TABLE clin.external_care OWNER TO "gm-dbo";

--
-- Name: TABLE external_care; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON TABLE clin.external_care IS 'lists external care situations for patients';


--
-- Name: COLUMN external_care.fk_encounter; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON COLUMN clin.external_care.fk_encounter IS 'the encounter during which this external care item was first documented';


--
-- Name: COLUMN external_care.fk_health_issue; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON COLUMN clin.external_care.fk_health_issue IS 'link to a health issue, if any';


--
-- Name: COLUMN external_care.issue; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON COLUMN clin.external_care.issue IS 'description of the issue of external care';


--
-- Name: COLUMN external_care.fk_org_unit; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON COLUMN clin.external_care.fk_org_unit IS 'link to the org unit where care is rendered';


--
-- Name: COLUMN external_care.provider; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON COLUMN clin.external_care.provider IS 'name of actual provider at .fk_org_unit';


--
-- Name: COLUMN external_care.comment; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON COLUMN clin.external_care.comment IS 'comment on the patient/provider relationship, say role/issues cared for/...';


--
-- Name: family_history; Type: TABLE; Schema: clin; Owner: gm-dbo
--

CREATE TABLE clin.family_history (
    pk integer NOT NULL,
    fk_relation_type integer NOT NULL,
    age_noted text,
    age_of_death interval,
    contributed_to_death boolean,
    name_relative text,
    dob_relative timestamp with time zone,
    comment text,
    CONSTRAINT c_family_history_sane_age_noted CHECK ((gm.is_null_or_non_empty_string(age_noted) IS TRUE)),
    CONSTRAINT c_family_history_sane_condition CHECK ((gm.is_null_or_blank_string(narrative) IS FALSE)),
    CONSTRAINT c_family_history_sane_name CHECK ((gm.is_null_or_non_empty_string(name_relative) IS TRUE)),
    CONSTRAINT narrative_neither_null_nor_empty CHECK ((btrim(COALESCE(narrative, ''::text)) <> ''::text))
)
INHERITS (clin.clin_root_item);


ALTER TABLE clin.family_history OWNER TO "gm-dbo";

--
-- Name: TABLE family_history; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON TABLE clin.family_history IS 'This table stores family history items on persons not otherwise in the database.';


--
-- Name: COLUMN family_history.clin_when; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON COLUMN clin.family_history.clin_when IS 'When the family history item became known to the patient (not the afflicted relative).';


--
-- Name: COLUMN family_history.fk_encounter; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON COLUMN clin.family_history.fk_encounter IS 'encounter during which family history item became known';


--
-- Name: COLUMN family_history.fk_episode; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON COLUMN clin.family_history.fk_episode IS 'episode to which family history item is of importance';


--
-- Name: COLUMN family_history.narrative; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON COLUMN clin.family_history.narrative IS 'the condition this relative suffered from';


--
-- Name: COLUMN family_history.fk_relation_type; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON COLUMN clin.family_history.fk_relation_type IS 'foreign key to the type of relation the patient has to the afflicated relative';


--
-- Name: COLUMN family_history.age_noted; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON COLUMN clin.family_history.age_noted IS 'age at which the condition was noted in the relative if known';


--
-- Name: COLUMN family_history.age_of_death; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON COLUMN clin.family_history.age_of_death IS 'age at which the relative died if known';


--
-- Name: COLUMN family_history.contributed_to_death; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON COLUMN clin.family_history.contributed_to_death IS 'whether the condition contributed to the death of the relative if known';


--
-- Name: COLUMN family_history.name_relative; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON COLUMN clin.family_history.name_relative IS 'name of the relative suffering the condition';


--
-- Name: COLUMN family_history.dob_relative; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON COLUMN clin.family_history.dob_relative IS 'date of birth of the relative if known';


--
-- Name: fhx_relation_type; Type: TABLE; Schema: clin; Owner: gm-dbo
--

CREATE TABLE clin.fhx_relation_type (
    pk integer NOT NULL,
    description text,
    is_genetic boolean NOT NULL,
    CONSTRAINT c_fhx_relation_type_sane_desc CHECK ((gm.is_null_or_blank_string(description) IS FALSE))
)
INHERITS (audit.audit_fields);


ALTER TABLE clin.fhx_relation_type OWNER TO "gm-dbo";

--
-- Name: TABLE fhx_relation_type; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON TABLE clin.fhx_relation_type IS 'Enumerates inter-person relations for family history linking.';


--
-- Name: COLUMN fhx_relation_type.description; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON COLUMN clin.fhx_relation_type.description IS 'Description of the relation type, specific or unspecific: sister, father, ..., maternal family, ...';


--
-- Name: COLUMN fhx_relation_type.is_genetic; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON COLUMN clin.fhx_relation_type.is_genetic IS 'Whether or not this type of relation is biologic/genetic or not. Note that non-genetic relations may still pose a risk because of infectious diseases.';


--
-- Name: hospital_stay; Type: TABLE; Schema: clin; Owner: gm-dbo
--

CREATE TABLE clin.hospital_stay (
    pk integer NOT NULL,
    discharge timestamp with time zone,
    fk_org_unit integer NOT NULL,
    CONSTRAINT sane_duration CHECK (((discharge IS NULL) OR (discharge > clin_when))),
    CONSTRAINT sane_hospital CHECK (gm.is_null_or_non_empty_string(narrative))
)
INHERITS (clin.clin_root_item);


ALTER TABLE clin.hospital_stay OWNER TO "gm-dbo";

--
-- Name: TABLE hospital_stay; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON TABLE clin.hospital_stay IS 'collects data on hospitalisations of patients, reasons are linked via a link table';


--
-- Name: COLUMN hospital_stay.clin_when; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON COLUMN clin.hospital_stay.clin_when IS 'to be used as when the patient was admitted';


--
-- Name: COLUMN hospital_stay.narrative; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON COLUMN clin.hospital_stay.narrative IS 'a comment on the hospital stay';


--
-- Name: COLUMN hospital_stay.discharge; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON COLUMN clin.hospital_stay.discharge IS 'when was the patient discharged';


--
-- Name: COLUMN hospital_stay.fk_org_unit; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON COLUMN clin.hospital_stay.fk_org_unit IS 'links to the hospital the patient was admitted to';


--
-- Name: lnk_code2item_root; Type: TABLE; Schema: clin; Owner: gm-dbo
--

CREATE TABLE clin.lnk_code2item_root (
    pk_lnk_code2item integer NOT NULL,
    fk_generic_code integer,
    fk_item integer,
    code_modifier text
)
INHERITS (audit.audit_fields);


ALTER TABLE clin.lnk_code2item_root OWNER TO "gm-dbo";

--
-- Name: TABLE lnk_code2item_root; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON TABLE clin.lnk_code2item_root IS 'The parent table of all tables linking codes to EMR structures.
Coding tables check this table for existence of their PK in
.fk_generic_code in order to prevent cascading DELETEs/UPDATEs
from breaking referential integrity.
EMR structure tables foreign key into children of this table in
order to link structures to codes.
';


--
-- Name: COLUMN lnk_code2item_root.fk_generic_code; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON COLUMN clin.lnk_code2item_root.fk_generic_code IS 'Custom foreign key to ref.coding_system_root.pk_coding_system.';


--
-- Name: COLUMN lnk_code2item_root.fk_item; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON COLUMN clin.lnk_code2item_root.fk_item IS 'Foreign key to clin.* tables';


--
-- Name: COLUMN lnk_code2item_root.code_modifier; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON COLUMN clin.lnk_code2item_root.code_modifier IS 'Usage specific modifier on the base code, say, certainty or laterality of ICD-10 codes.';


--
-- Name: lnk_code2aoe; Type: TABLE; Schema: clin; Owner: gm-dbo
--

CREATE TABLE clin.lnk_code2aoe (
    pk integer NOT NULL
)
INHERITS (clin.lnk_code2item_root);
ALTER TABLE ONLY clin.lnk_code2aoe ALTER COLUMN fk_generic_code SET NOT NULL;


ALTER TABLE clin.lnk_code2aoe OWNER TO "gm-dbo";

--
-- Name: TABLE lnk_code2aoe; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON TABLE clin.lnk_code2aoe IS 'Links codes to encounter.aoe.';


--
-- Name: COLUMN lnk_code2aoe.fk_generic_code; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON COLUMN clin.lnk_code2aoe.fk_generic_code IS 'Custom foreign key to ref.coding_system_root.';


--
-- Name: COLUMN lnk_code2aoe.fk_item; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON COLUMN clin.lnk_code2aoe.fk_item IS 'Foreign key to clin.encounter';


--
-- Name: lnk_code2episode; Type: TABLE; Schema: clin; Owner: gm-dbo
--

CREATE TABLE clin.lnk_code2episode (
    pk integer NOT NULL
)
INHERITS (clin.lnk_code2item_root);
ALTER TABLE ONLY clin.lnk_code2episode ALTER COLUMN fk_generic_code SET NOT NULL;


ALTER TABLE clin.lnk_code2episode OWNER TO "gm-dbo";

--
-- Name: TABLE lnk_code2episode; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON TABLE clin.lnk_code2episode IS 'Links codes to episodes.';


--
-- Name: COLUMN lnk_code2episode.fk_generic_code; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON COLUMN clin.lnk_code2episode.fk_generic_code IS 'Custom foreign key to ref.coding_system_root.';


--
-- Name: COLUMN lnk_code2episode.fk_item; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON COLUMN clin.lnk_code2episode.fk_item IS 'Foreign key to clin.episode';


--
-- Name: lnk_code2fhx; Type: TABLE; Schema: clin; Owner: gm-dbo
--

CREATE TABLE clin.lnk_code2fhx (
    pk integer NOT NULL
)
INHERITS (clin.lnk_code2item_root);
ALTER TABLE ONLY clin.lnk_code2fhx ALTER COLUMN fk_generic_code SET NOT NULL;


ALTER TABLE clin.lnk_code2fhx OWNER TO "gm-dbo";

--
-- Name: TABLE lnk_code2fhx; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON TABLE clin.lnk_code2fhx IS 'Links codes to family history items.';


--
-- Name: COLUMN lnk_code2fhx.fk_generic_code; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON COLUMN clin.lnk_code2fhx.fk_generic_code IS 'Custom foreign key to ref.coding_system_root.';


--
-- Name: COLUMN lnk_code2fhx.fk_item; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON COLUMN clin.lnk_code2fhx.fk_item IS 'Foreign key to clin.family_history';


--
-- Name: lnk_code2h_issue; Type: TABLE; Schema: clin; Owner: gm-dbo
--

CREATE TABLE clin.lnk_code2h_issue (
    pk integer NOT NULL
)
INHERITS (clin.lnk_code2item_root);
ALTER TABLE ONLY clin.lnk_code2h_issue ALTER COLUMN fk_generic_code SET NOT NULL;


ALTER TABLE clin.lnk_code2h_issue OWNER TO "gm-dbo";

--
-- Name: TABLE lnk_code2h_issue; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON TABLE clin.lnk_code2h_issue IS 'Links codes to health issues.';


--
-- Name: COLUMN lnk_code2h_issue.fk_generic_code; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON COLUMN clin.lnk_code2h_issue.fk_generic_code IS 'Custom foreign key to ref.coding_system_root.';


--
-- Name: COLUMN lnk_code2h_issue.fk_item; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON COLUMN clin.lnk_code2h_issue.fk_item IS 'Foreign key to clin.health_issue';


--
-- Name: lnk_code2procedure; Type: TABLE; Schema: clin; Owner: gm-dbo
--

CREATE TABLE clin.lnk_code2procedure (
    pk integer NOT NULL
)
INHERITS (clin.lnk_code2item_root);
ALTER TABLE ONLY clin.lnk_code2procedure ALTER COLUMN fk_generic_code SET NOT NULL;


ALTER TABLE clin.lnk_code2procedure OWNER TO "gm-dbo";

--
-- Name: TABLE lnk_code2procedure; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON TABLE clin.lnk_code2procedure IS 'Links codes to procedures. OPS comes to mind.';


--
-- Name: COLUMN lnk_code2procedure.fk_generic_code; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON COLUMN clin.lnk_code2procedure.fk_generic_code IS 'Custom foreign key to ref.coding_system_root.';


--
-- Name: COLUMN lnk_code2procedure.fk_item; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON COLUMN clin.lnk_code2procedure.fk_item IS 'Foreign key to clin.procedure';


--
-- Name: lnk_code2rfe; Type: TABLE; Schema: clin; Owner: gm-dbo
--

CREATE TABLE clin.lnk_code2rfe (
    pk integer NOT NULL
)
INHERITS (clin.lnk_code2item_root);
ALTER TABLE ONLY clin.lnk_code2rfe ALTER COLUMN fk_generic_code SET NOT NULL;


ALTER TABLE clin.lnk_code2rfe OWNER TO "gm-dbo";

--
-- Name: TABLE lnk_code2rfe; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON TABLE clin.lnk_code2rfe IS 'Links codes to encounters.';


--
-- Name: COLUMN lnk_code2rfe.fk_generic_code; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON COLUMN clin.lnk_code2rfe.fk_generic_code IS 'Custom foreign key to ref.coding_system_root.';


--
-- Name: COLUMN lnk_code2rfe.fk_item; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON COLUMN clin.lnk_code2rfe.fk_item IS 'Foreign key to clin.encounter';


--
-- Name: lnk_loinc2test_panel; Type: TABLE; Schema: clin; Owner: gm-dbo
--

CREATE TABLE clin.lnk_loinc2test_panel (
    pk integer NOT NULL,
    fk_test_panel integer NOT NULL,
    loinc text,
    CONSTRAINT clin_ll2tp_sane_loinc CHECK ((gm.is_null_or_blank_string(loinc) IS FALSE))
)
INHERITS (audit.audit_fields);


ALTER TABLE clin.lnk_loinc2test_panel OWNER TO "gm-dbo";

--
-- Name: TABLE lnk_loinc2test_panel; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON TABLE clin.lnk_loinc2test_panel IS 'Links LOINC codes to test panels which thusly become part of said panel.';


--
-- Name: COLUMN lnk_loinc2test_panel.fk_test_panel; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON COLUMN clin.lnk_loinc2test_panel.fk_test_panel IS 'FK linking the test panel';


--
-- Name: COLUMN lnk_loinc2test_panel.loinc; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON COLUMN clin.lnk_loinc2test_panel.loinc IS 'LOINC to include in test panel';


--
-- Name: meta_test_type; Type: TABLE; Schema: clin; Owner: gm-dbo
--

CREATE TABLE clin.meta_test_type (
    pk integer NOT NULL,
    abbrev text NOT NULL,
    name text NOT NULL,
    loinc text,
    comment text
);


ALTER TABLE clin.meta_test_type OWNER TO "gm-dbo";

--
-- Name: TABLE meta_test_type; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON TABLE clin.meta_test_type IS 'this table merges test types from various test orgs
	 which are intended to measure the same value but have
	 differing names into one logical test type,
	 this is not intended to be used for aggregating
	 semantically different test types into "profiles"';


--
-- Name: patient; Type: TABLE; Schema: clin; Owner: gm-dbo
--

CREATE TABLE clin.patient (
    pk integer NOT NULL,
    fk_identity integer NOT NULL,
    edc date,
    CONSTRAINT clin_patient_sane_edc CHECK (((edc IS NULL) OR ((clin.get_dod(fk_identity) IS NULL) AND ((clin.get_dob(fk_identity) IS NULL) OR (edc > (clin.get_dob(fk_identity) + '5 years'::interval))))))
)
INHERITS (audit.audit_fields);


ALTER TABLE clin.patient OWNER TO "gm-dbo";

--
-- Name: TABLE patient; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON TABLE clin.patient IS 'A table to hold unique-per-identity *clinical* items, such as Expected Due Date.';


--
-- Name: COLUMN patient.fk_identity; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON COLUMN clin.patient.fk_identity IS 'the dem.identity.pk of this patient';


--
-- Name: procedure; Type: TABLE; Schema: clin; Owner: gm-dbo
--

CREATE TABLE clin.procedure (
    pk integer NOT NULL,
    fk_hospital_stay integer,
    clin_end timestamp with time zone,
    is_ongoing boolean DEFAULT false NOT NULL,
    fk_org_unit integer,
    fk_doc integer,
    comment text,
    CONSTRAINT clin_procedure_lnk_org_or_stay CHECK ((((fk_hospital_stay IS NULL) AND (fk_org_unit IS NOT NULL)) OR ((fk_hospital_stay IS NOT NULL) AND (fk_org_unit IS NULL)))),
    CONSTRAINT clin_procedure_sane_comment CHECK ((gm.is_null_or_non_empty_string(comment) IS TRUE)),
    CONSTRAINT procedure_sane_end CHECK (((clin_end IS NULL) OR (clin_end >= clin_when))),
    CONSTRAINT sane_procedure CHECK ((gm.is_null_or_blank_string(narrative) IS FALSE)),
    CONSTRAINT sane_soap_cat CHECK ((soap_cat = ANY (ARRAY['a'::text, 'p'::text])))
)
INHERITS (clin.clin_root_item);


ALTER TABLE clin.procedure OWNER TO "gm-dbo";

--
-- Name: TABLE procedure; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON TABLE clin.procedure IS 'This table holds procedure/operations performed on the patient
 both in hospital or in community care.';


--
-- Name: COLUMN procedure.narrative; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON COLUMN clin.procedure.narrative IS 'Which procedure/operation was performed.';


--
-- Name: COLUMN procedure.fk_hospital_stay; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON COLUMN clin.procedure.fk_hospital_stay IS 'At which hospital was the procedure performed,
 unless clin_where is not null,
 if null it was an ambulatory procedure.';


--
-- Name: COLUMN procedure.clin_end; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON COLUMN clin.procedure.clin_end IS 'When did this procedure end/is expected to end.
- NULL if unknown or .clin_when (=start) is sufficient (eg. insignificant duration)';


--
-- Name: COLUMN procedure.is_ongoing; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON COLUMN clin.procedure.is_ongoing IS 'Whether this procedure is still going on (such as desensibilisation, chemotherapy, etc).';


--
-- Name: COLUMN procedure.fk_org_unit; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON COLUMN clin.procedure.fk_org_unit IS 'links to the or unit the procedure was performed at';


--
-- Name: COLUMN procedure.fk_doc; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON COLUMN clin.procedure.fk_doc IS 'links to the document the procedure is documented in';


--
-- Name: COLUMN procedure.comment; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON COLUMN clin.procedure.comment IS 'a comment on the procedure (say, the outcome)';


--
-- Name: reviewed_test_results; Type: TABLE; Schema: clin; Owner: gm-dbo
--

CREATE TABLE clin.reviewed_test_results (
)
INHERITS (clin.review_root);


ALTER TABLE clin.reviewed_test_results OWNER TO "gm-dbo";

--
-- Name: TABLE reviewed_test_results; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON TABLE clin.reviewed_test_results IS 'review table for test results';


--
-- Name: substance_intake; Type: TABLE; Schema: clin; Owner: gm-dbo
--

CREATE TABLE clin.substance_intake (
    pk integer NOT NULL,
    schedule text,
    aim text,
    duration interval,
    intake_is_approved_of boolean NOT NULL,
    is_long_term boolean,
    discontinued timestamp with time zone,
    discontinue_reason text,
    fk_drug_component integer NOT NULL,
    comment_on_start text,
    harmful_use_type integer,
    CONSTRAINT clin_patient_sane_use_type CHECK (((harmful_use_type IS NULL) OR ((harmful_use_type >= 0) AND (harmful_use_type <= 3)))),
    CONSTRAINT clin_substance_intake_sane_start_comment CHECK (gm.is_null_or_non_empty_string(comment_on_start)),
    CONSTRAINT discontinued_after_started CHECK (((harmful_use_type IS NOT NULL) OR (clin_when IS NULL) OR (discontinued IS NULL) OR ((discontinued >= clin_when) AND (discontinued <= now())))),
    CONSTRAINT medication_is_plan CHECK ((soap_cat = 'p'::text)),
    CONSTRAINT sane_aim CHECK ((gm.is_null_or_non_empty_string(aim) IS TRUE)),
    CONSTRAINT sane_discontinue_reason CHECK ((((discontinued IS NULL) AND (discontinue_reason IS NULL)) OR ((discontinued IS NOT NULL) AND (gm.is_null_or_non_empty_string(discontinue_reason) IS TRUE)))),
    CONSTRAINT sane_schedule CHECK ((gm.is_null_or_non_empty_string(schedule) IS TRUE))
)
INHERITS (clin.clin_root_item);


ALTER TABLE clin.substance_intake OWNER TO "gm-dbo";

--
-- Name: TABLE substance_intake; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON TABLE clin.substance_intake IS 'The substances a patient is actually currently taking.';


--
-- Name: COLUMN substance_intake.clin_when; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON COLUMN clin.substance_intake.clin_when IS 'When was this substance started.';


--
-- Name: COLUMN substance_intake.fk_encounter; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON COLUMN clin.substance_intake.fk_encounter IS 'The encounter use of this substance was documented under.';


--
-- Name: COLUMN substance_intake.narrative; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON COLUMN clin.substance_intake.narrative IS 'Any notes on this substance use.';


--
-- Name: COLUMN substance_intake.schedule; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON COLUMN clin.substance_intake.schedule IS 'The schedule, if any, the substance is to be taken by.
 An XML snippet to be interpreted by the middleware.';


--
-- Name: COLUMN substance_intake.aim; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON COLUMN clin.substance_intake.aim IS 'The aim of taking this substance.';


--
-- Name: COLUMN substance_intake.duration; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON COLUMN clin.substance_intake.duration IS 'How long is this substances intended to be taken.';


--
-- Name: COLUMN substance_intake.intake_is_approved_of; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON COLUMN clin.substance_intake.intake_is_approved_of IS 'Whether or not intake of this substance is recommended/approved of by the provider';


--
-- Name: COLUMN substance_intake.is_long_term; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON COLUMN clin.substance_intake.is_long_term IS 'whether this is expected to be a regular/ongoing/chronic/long-term/repeat/permament/perpetual/life-long substance intake';


--
-- Name: COLUMN substance_intake.discontinued; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON COLUMN clin.substance_intake.discontinued IS 'When was this intake discontinued ?';


--
-- Name: COLUMN substance_intake.discontinue_reason; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON COLUMN clin.substance_intake.discontinue_reason IS 'Why was this intake discontinued ?';


--
-- Name: COLUMN substance_intake.fk_drug_component; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON COLUMN clin.substance_intake.fk_drug_component IS 'Links to the component of a branded drug taken by a patient.';


--
-- Name: COLUMN substance_intake.comment_on_start; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON COLUMN clin.substance_intake.comment_on_start IS 'Comment (uncertainty level) on .clin_when = started. "?" = "entirely unknown".';


--
-- Name: COLUMN substance_intake.harmful_use_type; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON COLUMN clin.substance_intake.harmful_use_type IS 'NULL=not considered=medication, 0=no or not considered harmful, 1=presently harmful use, 2=presently addicted, 3=previously addicted';


--
-- Name: suppressed_hint; Type: TABLE; Schema: clin; Owner: gm-dbo
--

CREATE TABLE clin.suppressed_hint (
    pk integer NOT NULL,
    fk_encounter integer NOT NULL,
    fk_hint integer NOT NULL,
    suppressed_by name DEFAULT "current_user"() NOT NULL,
    suppressed_when timestamp with time zone DEFAULT statement_timestamp() NOT NULL,
    rationale text,
    md5_sum text,
    CONSTRAINT clin_suppressed_hint_sane_by CHECK ((length((suppressed_by)::text) > 0)),
    CONSTRAINT clin_suppressed_hint_sane_md5 CHECK ((gm.is_null_or_blank_string(md5_sum) IS FALSE)),
    CONSTRAINT clin_suppressed_hint_sane_rationale CHECK ((gm.is_null_or_blank_string(rationale) IS FALSE))
)
INHERITS (audit.audit_fields);


ALTER TABLE clin.suppressed_hint OWNER TO "gm-dbo";

--
-- Name: TABLE suppressed_hint; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON TABLE clin.suppressed_hint IS 'A table to hold hints suppressed per patient';


--
-- Name: COLUMN suppressed_hint.fk_encounter; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON COLUMN clin.suppressed_hint.fk_encounter IS 'the encounter during which this hint was first suppressed';


--
-- Name: COLUMN suppressed_hint.fk_hint; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON COLUMN clin.suppressed_hint.fk_hint IS 'the hint that is suppressed';


--
-- Name: COLUMN suppressed_hint.suppressed_by; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON COLUMN clin.suppressed_hint.suppressed_by IS 'who suppressed this hint';


--
-- Name: COLUMN suppressed_hint.suppressed_when; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON COLUMN clin.suppressed_hint.suppressed_when IS 'when was this hint suppressed';


--
-- Name: COLUMN suppressed_hint.rationale; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON COLUMN clin.suppressed_hint.rationale IS 'rationale on why this hint is suppressed in this patient';


--
-- Name: COLUMN suppressed_hint.md5_sum; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON COLUMN clin.suppressed_hint.md5_sum IS 'md5 of relevant fields of this hint';


--
-- Name: test_org; Type: TABLE; Schema: clin; Owner: gm-dbo
--

CREATE TABLE clin.test_org (
    pk integer NOT NULL,
    fk_org_unit integer NOT NULL,
    fk_adm_contact integer,
    fk_med_contact integer,
    comment text,
    contact text,
    CONSTRAINT sane_comment CHECK ((gm.is_null_or_non_empty_string(comment) IS TRUE)),
    CONSTRAINT sane_contact CHECK ((gm.is_null_or_non_empty_string(contact) IS TRUE))
)
INHERITS (audit.audit_fields);


ALTER TABLE clin.test_org OWNER TO "gm-dbo";

--
-- Name: TABLE test_org; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON TABLE clin.test_org IS 'organisation providing results';


--
-- Name: COLUMN test_org.fk_org_unit; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON COLUMN clin.test_org.fk_org_unit IS 'link to a unit of an organization more closely defining this lab';


--
-- Name: COLUMN test_org.fk_adm_contact; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON COLUMN clin.test_org.fk_adm_contact IS 'whom to call for admin questions (modem link, etc.)';


--
-- Name: COLUMN test_org.fk_med_contact; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON COLUMN clin.test_org.fk_med_contact IS 'whom to call for medical questions (result verification,
	 additional test requests)';


--
-- Name: COLUMN test_org.comment; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON COLUMN clin.test_org.comment IS 'useful for, say, dummy records where you want
	 to mark up stuff like "pharmacy such-and-such"
	 if you don''t have it in your contacts';


--
-- Name: COLUMN test_org.contact; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON COLUMN clin.test_org.contact IS 'free-text contact information for this lab';


--
-- Name: test_panel; Type: TABLE; Schema: clin; Owner: gm-dbo
--

CREATE TABLE clin.test_panel (
    pk integer NOT NULL,
    description text,
    comment text,
    CONSTRAINT clin_test_panel_sane_cmt CHECK ((gm.is_null_or_non_empty_string(comment) IS TRUE)),
    CONSTRAINT clin_test_panel_sane_desc CHECK ((gm.is_null_or_blank_string(description) IS FALSE))
)
INHERITS (audit.audit_fields);


ALTER TABLE clin.test_panel OWNER TO "gm-dbo";

--
-- Name: TABLE test_panel; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON TABLE clin.test_panel IS 'Panels of tests. The same test can appear in several panels.';


--
-- Name: COLUMN test_panel.description; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON COLUMN clin.test_panel.description IS 'A description/label for this panel.';


--
-- Name: COLUMN test_panel.comment; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON COLUMN clin.test_panel.comment IS 'An arbitrary comment on this panel.';


--
-- Name: test_result; Type: TABLE; Schema: clin; Owner: gm-dbo
--

CREATE TABLE clin.test_result (
    pk integer NOT NULL,
    fk_type integer NOT NULL,
    val_num numeric,
    val_alpha text,
    val_unit text,
    val_normal_min numeric,
    val_normal_max numeric,
    val_normal_range text,
    val_target_min numeric,
    val_target_max numeric,
    val_target_range text,
    abnormality_indicator text,
    norm_ref_group text,
    note_test_org text,
    material text,
    material_detail text,
    fk_intended_reviewer integer NOT NULL,
    fk_request integer,
    status text,
    source_data text,
    val_grouping text,
    CONSTRAINT clin_test_result_sane_source_data CHECK (gm.is_null_or_non_empty_string(source_data)),
    CONSTRAINT clin_test_result_sane_status CHECK (gm.is_null_or_non_empty_string(status)),
    CONSTRAINT clin_test_result_sane_val_grouping CHECK (gm.is_null_or_non_empty_string(val_grouping)),
    CONSTRAINT numval_needs_unit CHECK ((((val_num IS NOT NULL) AND (btrim(COALESCE(val_unit, ''::text)) <> ''::text)) OR (val_num IS NULL))),
    CONSTRAINT test_result_check CHECK (((val_num IS NOT NULL) OR (val_alpha IS NOT NULL) OR ((val_num IS NULL) AND (val_alpha <> ''::text) AND (val_alpha IS NOT NULL))))
)
INHERITS (clin.clin_root_item);


ALTER TABLE clin.test_result OWNER TO "gm-dbo";

--
-- Name: TABLE test_result; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON TABLE clin.test_result IS 'the results of a single measurement';


--
-- Name: COLUMN test_result.clin_when; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON COLUMN clin.test_result.clin_when IS 'the time when this result was *actually* obtained,
	 if this is a lab result this should be between
	 lab_request.clin_when and lab_request.results_reported_when,
	 HL7: OBR.observation_date_time';


--
-- Name: COLUMN test_result.narrative; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON COLUMN clin.test_result.narrative IS 'clinical comment, progress note';


--
-- Name: COLUMN test_result.fk_type; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON COLUMN clin.test_result.fk_type IS 'the type of test this result is from';


--
-- Name: COLUMN test_result.val_num; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON COLUMN clin.test_result.val_num IS 'numeric value if any,
	 HL7: OBX.observation_results if OBX.value_type == NM';


--
-- Name: COLUMN test_result.val_alpha; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON COLUMN clin.test_result.val_alpha IS 'alphanumeric value if any,
	 HL7: OBX.observation_results if OBX.value_type == FT';


--
-- Name: COLUMN test_result.val_unit; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON COLUMN clin.test_result.val_unit IS 'the unit this result came in
	 HL7: OBX.units';


--
-- Name: COLUMN test_result.val_normal_min; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON COLUMN clin.test_result.val_normal_min IS 'lower bound of normal range if numerical as
	 defined by provider for this result';


--
-- Name: COLUMN test_result.val_normal_max; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON COLUMN clin.test_result.val_normal_max IS 'upper bound of normal range if numerical as
	 defined by provider for this result';


--
-- Name: COLUMN test_result.val_normal_range; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON COLUMN clin.test_result.val_normal_range IS 'range of normal values if alphanumerical
	 as defined by provider for this result, eg.
	 "less than 0.5 but detectable"
	 HL7: OBX.reference_range';


--
-- Name: COLUMN test_result.val_target_min; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON COLUMN clin.test_result.val_target_min IS 'lower bound of target range if numerical as
	 defined by clinician caring this patient';


--
-- Name: COLUMN test_result.val_target_max; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON COLUMN clin.test_result.val_target_max IS 'upper bound of target range if numerical as
	 defined by clinician caring for this patient';


--
-- Name: COLUMN test_result.val_target_range; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON COLUMN clin.test_result.val_target_range IS 'range of target values if alphanumerical
	 as defined by clinician caring for this patient';


--
-- Name: COLUMN test_result.abnormality_indicator; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON COLUMN clin.test_result.abnormality_indicator IS 'how the test provider flagged this result as abnormal,
	 *not* a clinical assessment but rather a technical one
	 LDT: 8422';


--
-- Name: COLUMN test_result.norm_ref_group; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON COLUMN clin.test_result.norm_ref_group IS 'what sample of the population does this normal range
	 applay to, eg what type of patient was assumed when
	 interpreting this result,
	 LDT: 8407';


--
-- Name: COLUMN test_result.note_test_org; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON COLUMN clin.test_result.note_test_org IS 'A comment on the test result provided by the tester or testing entity.';


--
-- Name: COLUMN test_result.material; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON COLUMN clin.test_result.material IS 'the submitted material, eg. smear, serum, urine, etc.,
	 LDT: 8430';


--
-- Name: COLUMN test_result.material_detail; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON COLUMN clin.test_result.material_detail IS 'details re the material, eg. site taken from, etc.
	 LDT: 8431';


--
-- Name: COLUMN test_result.fk_intended_reviewer; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON COLUMN clin.test_result.fk_intended_reviewer IS 'who is *supposed* to review this item';


--
-- Name: COLUMN test_result.fk_request; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON COLUMN clin.test_result.fk_request IS 'The request this result was ordered under if any.';


--
-- Name: COLUMN test_result.status; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON COLUMN clin.test_result.status IS 'The result status (say, HL7 OBX 11 Observ result status (#00579, table 0085).';


--
-- Name: COLUMN test_result.source_data; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON COLUMN clin.test_result.source_data IS 'The source data for this observation (say, HL7 OBX).';


--
-- Name: COLUMN test_result.val_grouping; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON COLUMN clin.test_result.val_grouping IS 'A grouping for related values (say, HL7 OBX Obs Sub ID, think antibiogram).';


--
-- Name: test_type; Type: TABLE; Schema: clin; Owner: gm-dbo
--

CREATE TABLE clin.test_type (
    pk integer NOT NULL,
    fk_test_org integer NOT NULL,
    name text NOT NULL,
    comment text,
    reference_unit text,
    loinc text,
    abbrev text NOT NULL,
    fk_meta_test_type integer
)
INHERITS (audit.audit_fields);


ALTER TABLE clin.test_type OWNER TO "gm-dbo";

--
-- Name: TABLE test_type; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON TABLE clin.test_type IS 'measurement type, like a "method" in a lab';


--
-- Name: COLUMN test_type.fk_test_org; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON COLUMN clin.test_type.fk_test_org IS 'organisation carrying out this type of measurement, eg. a particular lab';


--
-- Name: COLUMN test_type.name; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON COLUMN clin.test_type.name IS 'descriptive name of this measurement type,
	 HL7: OBX.observation_identifier';


--
-- Name: COLUMN test_type.comment; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON COLUMN clin.test_type.comment IS 'arbitrary comment on this type of measurement/test such
	 as "outdated" or "only reliable when ..."';


--
-- Name: COLUMN test_type.reference_unit; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON COLUMN clin.test_type.reference_unit IS 'The chosen reference unit for this test type, preferably SI, used for comparing results delivered in differing units. This does not relate to what unit the test provider delivers results in but rather the unit we think those results need to be converted to in order to be comparable to OTHER results.';


--
-- Name: COLUMN test_type.fk_meta_test_type; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON COLUMN clin.test_type.fk_meta_test_type IS 'Link to the meta test type (if any) this test type is to be aggregated under.';


--
-- Name: v_edc_journal; Type: VIEW; Schema: clin; Owner: gm-dbo
--

CREATE VIEW clin.v_edc_journal AS
 SELECT c_p.fk_identity AS pk_patient,
    c_p.modified_when,
    c_p.modified_when AS clin_when,
    COALESCE(( SELECT staff.short_alias
           FROM dem.staff
          WHERE (staff.db_user = c_p.modified_by)), (('<'::text || (c_p.modified_by)::text) || '>'::text)) AS modified_by,
    's'::text AS soap_cat,
    (public._('EDC'::text) || to_char((c_p.edc)::timestamp with time zone, ': YYYY Mon DD'::text)) AS narrative,
    ( SELECT c_e.pk
           FROM clin.encounter c_e
          WHERE (c_e.fk_patient = c_p.fk_identity)
          ORDER BY c_e.started DESC
         LIMIT 1) AS pk_encounter,
    NULL::integer AS pk_episode,
    NULL::integer AS pk_health_issue,
    c_p.pk AS src_pk,
    'clin.patient'::text AS src_table,
    c_p.row_version,
    NULL::text AS health_issue,
    NULL::text AS issue_laterality,
    NULL::boolean AS issue_active,
    NULL::boolean AS issue_clinically_relevant,
    NULL::boolean AS issue_confidential,
    NULL::text AS episode,
    NULL::boolean AS episode_open,
    NULL::timestamp with time zone AS encounter_started,
    NULL::timestamp with time zone AS encounter_last_affirmed,
    NULL::text AS encounter_type,
    NULL::text AS encounter_l10n_type
   FROM clin.patient c_p
  WHERE (c_p.edc IS NOT NULL);


ALTER TABLE clin.v_edc_journal OWNER TO "gm-dbo";

--
-- Name: v_external_care_journal; Type: VIEW; Schema: clin; Owner: gm-dbo
--

CREATE VIEW clin.v_external_care_journal AS
 SELECT c_enc.fk_patient AS pk_patient,
    c_ec.modified_when,
    c_ec.modified_when AS clin_when,
    COALESCE(( SELECT staff.short_alias
           FROM dem.staff
          WHERE (staff.db_user = c_ec.modified_by)), (('<'::text || (c_ec.modified_by)::text) || '>'::text)) AS modified_by,
    's'::text AS soap_cat,
    ((((((((((((((public._('External care'::text) ||
        CASE
            WHEN c_ec.inactive THEN ((' ('::text || public._('inactive'::text)) || ')'::text)
            ELSE ''::text
        END) || COALESCE((((' '::text || public._('by'::text)) || ' '::text) || c_ec.provider), ''::text)) || ' @ '::text) || d_ou.description) || ' '::text) || public._('of'::text)) || ' '::text) || d_o.description) || '
'::text) || public._('Issue:'::text)) || ' '::text) || COALESCE(c_hi.description, c_ec.issue)) || '
'::text) || COALESCE(((public._('Comment:'::text) || ' '::text) || c_ec.comment), ''::text)) AS narrative,
    c_ec.fk_encounter AS pk_encounter,
    NULL::integer AS pk_episode,
    c_ec.fk_health_issue AS pk_health_issue,
    c_ec.pk AS src_pk,
    'clin.external_care'::text AS src_table,
    c_ec.row_version,
    c_hi.description AS health_issue,
    c_hi.laterality AS issue_laterality,
    c_hi.is_active AS issue_active,
    c_hi.clinically_relevant AS issue_clinically_relevant,
    c_hi.is_confidential AS issue_confidential,
    NULL::text AS episode,
    NULL::boolean AS episode_open,
    c_enc.started AS encounter_started,
    c_enc.last_affirmed AS encounter_last_affirmed,
    c_ety.description AS encounter_type,
    public._(c_ety.description) AS encounter_l10n_type
   FROM (((((clin.external_care c_ec
     JOIN clin.encounter c_enc ON ((c_ec.fk_encounter = c_enc.pk)))
     JOIN clin.encounter_type c_ety ON ((c_enc.fk_type = c_ety.pk)))
     LEFT JOIN clin.health_issue c_hi ON ((c_ec.fk_health_issue = c_hi.pk)))
     LEFT JOIN dem.org_unit d_ou ON ((c_ec.fk_org_unit = d_ou.pk)))
     LEFT JOIN dem.org d_o ON ((d_ou.fk_org = d_o.pk)));


ALTER TABLE clin.v_external_care_journal OWNER TO "gm-dbo";

--
