Interop Service — Data Model

Status: populated Owner: TBD Last updated: 2026-04-18 Companion: Service Template · 03 platform-services · 02 DDD

---

1. ID Prefix Convention

Entity	Prefix
InteropConnector	CONN_
Hl7Message	MSG_
BulkExportJob	JOB_
FhirRoutingRule	RR_

---

2. TypeScript Interfaces

export interface InteropConnector {
  id: string;                          // CONN_ prefixed ULID
  tenantId: string;
  facilityId?: string;
  name: string;
  protocol: 'mllp' | 'fhir-rest' | 'sftp' | 'api-polling';
  direction: 'inbound' | 'outbound' | 'bidirectional';
  host?: string;
  port?: number;
  tlsEnabled: boolean;
  authMode: 'none' | 'client-cert' | 'shared-secret' | 'oauth2';
  authConfig?: object;                 // encrypted
  messageTypes: string[];              // e.g. ["ADT^A01", "ORU^R01"]
  status: 'configured' | 'active' | 'paused' | 'inactive';
  version: number;
  createdAt: Date;
  updatedAt: Date;
}

export interface Hl7Message {
  id: string;                          // MSG_ prefixed ULID
  tenantId: string;
  connectorId: string;
  direction: 'inbound' | 'outbound';
  messageType: string;                 // MSH-9 e.g. "ADT^A01"
  messageControlId: string;            // MSH-10
  sendingApp?: string;
  sendingFacility?: string;
  receivingApp?: string;
  rawPayload: string;                  // immutable HL7 text
  parsedData?: object;                 // structured segments JSON
  processingStatus: Hl7ProcessingStatus;
  errorDetail?: string;
  attemptCount: number;
  receivedAt: Date;
  processedAt?: Date;
  createdAt: Date;
}

export type Hl7ProcessingStatus =
  | 'pending' | 'processing' | 'processed' | 'failed' | 'dead_lettered';

export interface Hl7ResourceLink {
  id: string;
  messageId: string;
  resourceType: string;
  resourceId: string;
  action: 'created' | 'updated';
}

export interface FhirRoutingRule {
  id: string;                          // RR_ prefixed ULID
  resourceType: string;
  category?: string;
  profile?: string;
  serviceUrl: string;
  fanOut: boolean;                     // true = query multiple services + merge
  priority: number;
  active: boolean;
}

export interface BulkExportJob {
  id: string;                          // JOB_ prefixed ULID
  tenantId: string;
  requestedBy: string;
  groupId?: string;
  patientId?: string;
  resourceTypes: string[];
  sinceDate?: Date;
  status: 'accepted' | 'in-progress' | 'complete' | 'failed';
  outputFiles: BulkExportFile[];
  errorMessage?: string;
  createdAt: Date;
  completedAt?: Date;
}

---

3. PostgreSQL Schema

CREATE TABLE interop_connectors (
  id              TEXT PRIMARY KEY,
  tenant_id       TEXT NOT NULL,
  facility_id     TEXT,
  name            TEXT NOT NULL,
  protocol        TEXT NOT NULL,
  direction       TEXT NOT NULL,
  host            TEXT,
  port            INTEGER,
  tls_enabled     BOOLEAN NOT NULL DEFAULT TRUE,
  auth_mode       TEXT NOT NULL DEFAULT 'none',
  auth_config     JSONB,               -- encrypted at application layer
  message_types   TEXT[] NOT NULL DEFAULT '{}',
  status          TEXT NOT NULL DEFAULT 'configured',
  version         INTEGER NOT NULL DEFAULT 1,
  created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at      TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_connectors_tenant ON interop_connectors(tenant_id, status);
ALTER TABLE interop_connectors ENABLE ROW LEVEL SECURITY;
CREATE POLICY connectors_tenant ON interop_connectors
  USING (tenant_id = current_setting('app.current_tenant_id'));

---

CREATE TABLE hl7_messages (
  id                  TEXT PRIMARY KEY,
  tenant_id           TEXT NOT NULL,
  connector_id        TEXT NOT NULL REFERENCES interop_connectors(id),
  direction           TEXT NOT NULL,
  message_type        TEXT NOT NULL,
  message_control_id  TEXT NOT NULL,
  sending_app         TEXT,
  sending_facility    TEXT,
  receiving_app       TEXT,
  raw_payload         TEXT NOT NULL,    -- immutable
  parsed_data         JSONB,
  processing_status   TEXT NOT NULL DEFAULT 'pending',
  error_detail        TEXT,
  attempt_count       INTEGER NOT NULL DEFAULT 0,
  received_at         TIMESTAMPTZ NOT NULL,
  processed_at        TIMESTAMPTZ,
  created_at          TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  UNIQUE (connector_id, message_control_id, sending_app)
);

CREATE INDEX idx_hl7_tenant_status ON hl7_messages(tenant_id, processing_status, received_at);
CREATE INDEX idx_hl7_connector ON hl7_messages(tenant_id, connector_id, received_at DESC);
ALTER TABLE hl7_messages ENABLE ROW LEVEL SECURITY;
CREATE POLICY hl7_messages_tenant ON hl7_messages
  USING (tenant_id = current_setting('app.current_tenant_id'));

---

CREATE TABLE hl7_resource_links (
  id            TEXT PRIMARY KEY,
  message_id    TEXT NOT NULL REFERENCES hl7_messages(id),
  resource_type TEXT NOT NULL,
  resource_id   TEXT NOT NULL,
  action        TEXT NOT NULL
);

CREATE INDEX idx_hl7_resource_links ON hl7_resource_links(message_id);

---

CREATE TABLE fhir_routing_rules (
  id            TEXT PRIMARY KEY,
  resource_type TEXT NOT NULL,
  category      TEXT,
  profile       TEXT,
  service_url   TEXT NOT NULL,
  fan_out       BOOLEAN NOT NULL DEFAULT FALSE,
  priority      INTEGER NOT NULL DEFAULT 0,
  active        BOOLEAN NOT NULL DEFAULT TRUE
);

CREATE UNIQUE INDEX idx_routing_rule ON fhir_routing_rules(resource_type, COALESCE(category,''), COALESCE(profile,''))
  WHERE active = TRUE;

---

CREATE TABLE bulk_export_jobs (
  id              TEXT PRIMARY KEY,
  tenant_id       TEXT NOT NULL,
  requested_by    TEXT NOT NULL,
  group_id        TEXT,
  patient_id      TEXT,
  resource_types  TEXT[] NOT NULL,
  since_date      TIMESTAMPTZ,
  status          TEXT NOT NULL DEFAULT 'accepted',
  error_message   TEXT,
  created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  completed_at    TIMESTAMPTZ
);

CREATE TABLE bulk_export_files (
  id              TEXT PRIMARY KEY,
  job_id          TEXT NOT NULL REFERENCES bulk_export_jobs(id),
  resource_type   TEXT NOT NULL,
  file_url        TEXT NOT NULL,
  resource_count  INTEGER
);

---

CREATE TABLE outbox_events (
  id          TEXT PRIMARY KEY,
  tenant_id   TEXT NOT NULL,
  subject     TEXT NOT NULL,
  event_type  TEXT NOT NULL,
  payload     JSONB NOT NULL,
  published   BOOLEAN NOT NULL DEFAULT FALSE,
  created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_interop_outbox_unpublished ON outbox_events(published, created_at)
  WHERE published = FALSE;