dataModelSceme.sql

-- Base table for all claims
CREATE TABLE "Claim" (
  "id" UUID PRIMARY KEY,
  "name" VARCHAR NOT NULL,
  "claim_type" VARCHAR NOT NULL DEFAULT 'ERROR',
  "public_id" VARCHAR NULL,
  "created" TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  "stored_at" TIMESTAMP NULL,
  "advocate_version" VARCHAR NOT NULL DEFAULT 'unknown'
);

CREATE INDEX "idx_claim_public_id" ON "Claim" ("public_id");
CREATE INDEX "idx_claim_stored_at" ON "Claim" ("stored_at");

-- Node claims representing advocate nodes
CREATE TABLE "NodeClaim" (
  "claim_id" UUID PRIMARY KEY REFERENCES "Claim" ("id"),
  "country_code" INTEGER NOT NULL DEFAULT 999,
  "settings_hash" VARCHAR NOT NULL,
  "address" VARCHAR NOT NULL,
  "organization_id" INTEGER REFERENCES "Organization" ("id")
);

-- Deployment claims for Kubernetes deployments
CREATE TABLE "DeploymentClaim" (
  "claim_id" UUID PRIMARY KEY REFERENCES "Claim" ("id"),
  "config_file" VARCHAR NULL,
  "kind" VARCHAR NULL,
  "namespace" VARCHAR NULL,
  "image_verification" BOOLEAN NULL,
  "lake_id" VARCHAR NULL,
  "generator" VARCHAR NULL
);

-- Federated Data Product claims
CREATE TABLE "FDPClaim" (
  "claim_id" UUID PRIMARY KEY REFERENCES "Claim" ("id"),
  "catalogue_url" VARCHAR NULL,
  "owner_id" INTEGER REFERENCES "Organization" ("id"),
  "mandate" VARCHAR NULL,
  "lake_id" VARCHAR NULL,
  "generator" VARCHAR NULL
);

-- Transaction claims between services
CREATE TABLE "TransactionClaim" (
  "claim_id" UUID PRIMARY KEY REFERENCES "Claim" ("id"),
  "consumer_id" VARCHAR NULL,
  "consumer_org" INTEGER REFERENCES "Organization" ("id"),
  "data_product" UUID REFERENCES "FDPClaim" ("claim_id"),
  "lake_id" VARCHAR NULL,
  "generator" VARCHAR NULL
);

-- Catalogue event claims
CREATE TABLE "CatalogueEventClaim" (
  "claim_id" UUID PRIMARY KEY REFERENCES "Claim" ("id"),
  "user" VARCHAR NULL,
  "action" VARCHAR NULL,
  "object" VARCHAR NULL,
  "identifier" VARCHAR NULL,
  "lake_id" VARCHAR NULL,
  "generator" VARCHAR NULL
);

-- Aggregation claims for aggregated data
CREATE TABLE "AggregationClaim" (
  "claim_id" UUID PRIMARY KEY REFERENCES "Claim" ("id"),
  "aggregation_description" VARCHAR NOT NULL,
  "aggregation_data" VARCHAR NOT NULL,
  "aggregation_hash" VARCHAR NOT NULL
);

-- Data claims for unformatted information
CREATE TABLE "DataClaim" (
  "claim_id" UUID PRIMARY KEY REFERENCES "Claim" ("id"),
  "data" VARCHAR NOT NULL,
  "data_hash" VARCHAR NOT NULL
);

-- Organizations table
CREATE TABLE "Organization" (
  "id" INTEGER PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
  "name" VARCHAR NOT NULL
);

-- Base telemetry table
CREATE TABLE "Telemetry" (
  "id" UUID PRIMARY KEY,
  "lake_id" VARCHAR NOT NULL,
  "observed_at" TIMESTAMP NOT NULL,
  "stored_at" TIMESTAMP NULL,
  "telemetry_type" VARCHAR NOT NULL DEFAULT 'ERROR',
  "source" VARCHAR NULL,
  "claim_id" UUID REFERENCES "Claim" ("id"),
  "type" VARCHAR NOT NULL DEFAULT 'ERROR'
);

CREATE INDEX "idx_telemetry_stored_at" ON "Telemetry" ("stored_at");

-- Container telemetry
CREATE TABLE "Container" (
  "telemetry_id" UUID PRIMARY KEY REFERENCES "Telemetry" ("id"),
  "name" VARCHAR NOT NULL DEFAULT '',
  "image" VARCHAR NOT NULL DEFAULT '',
  "environment" VARCHAR NOT NULL DEFAULT '',
  "tag" VARCHAR NULL,
  "hash" VARCHAR NULL,
  "command" VARCHAR NOT NULL DEFAULT ''
);

-- Volume telemetry
CREATE TABLE "Volume" (
  "telemetry_id" UUID PRIMARY KEY REFERENCES "Telemetry" ("id"),
  "name" VARCHAR NOT NULL DEFAULT '',
  "environment" VARCHAR NOT NULL DEFAULT ''
);

-- Log telemetry
CREATE TABLE "Log" (
  "telemetry_id" UUID PRIMARY KEY REFERENCES "Telemetry" ("id"),
  "log_message" VARCHAR NOT NULL DEFAULT ''
);

-- Service chain telemetry
CREATE TABLE "ServiceChainTelemetry" (
  "telemetry_id" UUID PRIMARY KEY REFERENCES "Telemetry" ("id"),
  "service_chain" VARCHAR NOT NULL DEFAULT '',
  "count" INTEGER NOT NULL DEFAULT 0
);

-- Trace table
CREATE TABLE "Trace" (
  "id" INTEGER PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
  "telemetry_id" UUID NOT NULL REFERENCES "Telemetry" ("id"),
  "start_span" VARCHAR NULL
);

-- Span table
CREATE TABLE "Span" (
  "id" INTEGER PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
  "trace_id" INTEGER NOT NULL REFERENCES "Trace" ("id"),
  "parent_span_id" INTEGER REFERENCES "Span" ("id"),
  "name" VARCHAR NOT NULL,
  "start_time" TIMESTAMP NOT NULL,
  "end_time" TIMESTAMP NOT NULL
);

-- Tag table
CREATE TABLE "Tag" (
  "id" INTEGER PRIMARY KEY,
  "span" INTEGER NOT NULL REFERENCES "Span" ("id"),
  "key_id" INTEGER NOT NULL,
  "key" VARCHAR NOT NULL,
  "value" VARCHAR NOT NULL
);

-- Join table for TransactionClaim to Telemetry
CREATE TABLE "TransactionClaim_observations" (
  "claim_id" UUID NOT NULL REFERENCES "TransactionClaim" ("claim_id"),
  "telemetry_id" UUID NOT NULL REFERENCES "Telemetry" ("id"),
  PRIMARY KEY ("claim_id", "telemetry_id")
);

-- Create indexes for common query patterns
CREATE INDEX "idx_claim_type" ON "Claim" ("claim_type");
CREATE INDEX "idx_telemetry_type" ON "Telemetry" ("telemetry_type");
CREATE INDEX "idx_telemetry_claim" ON "Telemetry" ("claim_id");