288 lines
11 KiB
SQL
288 lines
11 KiB
SQL
-- Sankofa HW Infra initial schema
|
|
CREATE TABLE IF NOT EXISTS "org_units" (
|
|
"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
|
|
"name" text NOT NULL,
|
|
"parent_id" uuid,
|
|
"org_id" text NOT NULL,
|
|
"created_at" timestamp DEFAULT now() NOT NULL,
|
|
"updated_at" timestamp DEFAULT now() NOT NULL
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS "users" (
|
|
"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
|
|
"email" text NOT NULL UNIQUE,
|
|
"name" text,
|
|
"org_unit_id" uuid,
|
|
"org_id" text NOT NULL,
|
|
"external_id" text,
|
|
"created_at" timestamp DEFAULT now() NOT NULL,
|
|
"updated_at" timestamp DEFAULT now() NOT NULL
|
|
);
|
|
|
|
ALTER TABLE "users" ADD CONSTRAINT "users_org_unit_id_org_units_id_fk" FOREIGN KEY ("org_unit_id") REFERENCES "public"."org_units"("id");
|
|
ALTER TABLE "org_units" ADD CONSTRAINT "org_units_parent_id_org_units_id_fk" FOREIGN KEY ("parent_id") REFERENCES "public"."org_units"("id");
|
|
|
|
CREATE TABLE IF NOT EXISTS "vendors" (
|
|
"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
|
|
"org_id" text NOT NULL,
|
|
"legal_name" text NOT NULL,
|
|
"contacts" jsonb,
|
|
"trust_tier" text DEFAULT 'unknown' NOT NULL,
|
|
"created_at" timestamp DEFAULT now() NOT NULL,
|
|
"updated_at" timestamp DEFAULT now() NOT NULL
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS "vendor_bank_details" (
|
|
"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
|
|
"vendor_id" uuid NOT NULL,
|
|
"version" integer DEFAULT 1 NOT NULL,
|
|
"instructions" jsonb,
|
|
"approved_by_1" uuid,
|
|
"approved_by_2" uuid,
|
|
"approved_at" timestamp,
|
|
"created_at" timestamp DEFAULT now() NOT NULL
|
|
);
|
|
|
|
ALTER TABLE "vendor_bank_details" ADD CONSTRAINT "vendor_bank_details_vendor_id_vendors_id_fk" FOREIGN KEY ("vendor_id") REFERENCES "public"."vendors"("id");
|
|
|
|
CREATE TABLE IF NOT EXISTS "offers" (
|
|
"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
|
|
"org_id" text NOT NULL,
|
|
"vendor_id" uuid NOT NULL,
|
|
"sku" text,
|
|
"mpn" text,
|
|
"quantity" integer NOT NULL,
|
|
"unit_price" decimal(20, 4) NOT NULL,
|
|
"incoterms" text,
|
|
"lead_time_days" integer,
|
|
"country_of_origin" text,
|
|
"condition" text,
|
|
"warranty" text,
|
|
"evidence_refs" jsonb,
|
|
"risk_score" decimal(5, 2),
|
|
"risk_factors" jsonb,
|
|
"status" text DEFAULT 'draft' NOT NULL,
|
|
"created_at" timestamp DEFAULT now() NOT NULL,
|
|
"updated_at" timestamp DEFAULT now() NOT NULL
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS "offers_vendor_idx" ON "offers" ("vendor_id");
|
|
CREATE INDEX IF NOT EXISTS "offers_org_idx" ON "offers" ("org_id");
|
|
ALTER TABLE "offers" ADD CONSTRAINT "offers_vendor_id_vendors_id_fk" FOREIGN KEY ("vendor_id") REFERENCES "public"."vendors"("id");
|
|
|
|
CREATE TABLE IF NOT EXISTS "regions" (
|
|
"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
|
|
"org_id" text NOT NULL,
|
|
"name" text NOT NULL,
|
|
"created_at" timestamp DEFAULT now() NOT NULL,
|
|
"updated_at" timestamp DEFAULT now() NOT NULL
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS "sites" (
|
|
"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
|
|
"org_id" text NOT NULL,
|
|
"region_id" uuid,
|
|
"name" text NOT NULL,
|
|
"address" text,
|
|
"network_metadata" jsonb,
|
|
"created_at" timestamp DEFAULT now() NOT NULL,
|
|
"updated_at" timestamp DEFAULT now() NOT NULL
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS "sites_org_idx" ON "sites" ("org_id");
|
|
ALTER TABLE "sites" ADD CONSTRAINT "sites_region_id_regions_id_fk" FOREIGN KEY ("region_id") REFERENCES "public"."regions"("id");
|
|
|
|
CREATE TABLE IF NOT EXISTS "rooms" (
|
|
"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
|
|
"site_id" uuid NOT NULL,
|
|
"name" text NOT NULL,
|
|
"created_at" timestamp DEFAULT now() NOT NULL,
|
|
"updated_at" timestamp DEFAULT now() NOT NULL
|
|
);
|
|
|
|
ALTER TABLE "rooms" ADD CONSTRAINT "rooms_site_id_sites_id_fk" FOREIGN KEY ("site_id") REFERENCES "public"."sites"("id");
|
|
|
|
CREATE TABLE IF NOT EXISTS "rows" (
|
|
"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
|
|
"room_id" uuid NOT NULL,
|
|
"name" text NOT NULL,
|
|
"created_at" timestamp DEFAULT now() NOT NULL,
|
|
"updated_at" timestamp DEFAULT now() NOT NULL
|
|
);
|
|
|
|
ALTER TABLE "rows" ADD CONSTRAINT "rows_room_id_rooms_id_fk" FOREIGN KEY ("room_id") REFERENCES "public"."rooms"("id");
|
|
|
|
CREATE TABLE IF NOT EXISTS "racks" (
|
|
"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
|
|
"row_id" uuid NOT NULL,
|
|
"name" text NOT NULL,
|
|
"ru_total" integer NOT NULL,
|
|
"power_feeds" jsonb,
|
|
"created_at" timestamp DEFAULT now() NOT NULL,
|
|
"updated_at" timestamp DEFAULT now() NOT NULL
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS "racks_row_idx" ON "racks" ("row_id");
|
|
ALTER TABLE "racks" ADD CONSTRAINT "racks_row_id_rows_id_fk" FOREIGN KEY ("row_id") REFERENCES "public"."rows"("id");
|
|
|
|
CREATE TABLE IF NOT EXISTS "assets" (
|
|
"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
|
|
"org_id" text NOT NULL,
|
|
"asset_id" text NOT NULL UNIQUE,
|
|
"category" text NOT NULL,
|
|
"manufacturer_serial" text,
|
|
"service_tag" text,
|
|
"macs" jsonb,
|
|
"wwns" jsonb,
|
|
"part_number" text,
|
|
"condition" text,
|
|
"warranty" text,
|
|
"proof_artifact_refs" jsonb,
|
|
"site_id" uuid,
|
|
"position_id" uuid,
|
|
"owner_id" uuid,
|
|
"project_id" text,
|
|
"sensitivity_tier" text,
|
|
"status" text DEFAULT 'pending' NOT NULL,
|
|
"chain_of_custody" jsonb,
|
|
"created_at" timestamp DEFAULT now() NOT NULL,
|
|
"updated_at" timestamp DEFAULT now() NOT NULL
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS "assets_org_idx" ON "assets" ("org_id");
|
|
CREATE INDEX IF NOT EXISTS "assets_site_idx" ON "assets" ("site_id");
|
|
CREATE INDEX IF NOT EXISTS "assets_category_idx" ON "assets" ("category");
|
|
CREATE INDEX IF NOT EXISTS "assets_status_idx" ON "assets" ("status");
|
|
ALTER TABLE "assets" ADD CONSTRAINT "assets_site_id_sites_id_fk" FOREIGN KEY ("site_id") REFERENCES "public"."sites"("id");
|
|
ALTER TABLE "assets" ADD CONSTRAINT "assets_owner_id_users_id_fk" FOREIGN KEY ("owner_id") REFERENCES "public"."users"("id");
|
|
|
|
CREATE TABLE IF NOT EXISTS "positions" (
|
|
"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
|
|
"rack_id" uuid NOT NULL,
|
|
"ru_start" integer NOT NULL,
|
|
"ru_end" integer NOT NULL,
|
|
"asset_id" uuid,
|
|
"created_at" timestamp DEFAULT now() NOT NULL,
|
|
"updated_at" timestamp DEFAULT now() NOT NULL
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS "positions_rack_idx" ON "positions" ("rack_id");
|
|
ALTER TABLE "positions" ADD CONSTRAINT "positions_rack_id_racks_id_fk" FOREIGN KEY ("rack_id") REFERENCES "public"."racks"("id");
|
|
ALTER TABLE "positions" ADD CONSTRAINT "positions_asset_id_assets_id_fk" FOREIGN KEY ("asset_id") REFERENCES "public"."assets"("id");
|
|
ALTER TABLE "assets" ADD CONSTRAINT "assets_position_id_positions_id_fk" FOREIGN KEY ("position_id") REFERENCES "public"."positions"("id");
|
|
|
|
CREATE TABLE IF NOT EXISTS "purchase_orders" (
|
|
"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
|
|
"org_id" text NOT NULL,
|
|
"vendor_id" uuid NOT NULL,
|
|
"line_items" jsonb NOT NULL,
|
|
"status" text DEFAULT 'draft' NOT NULL,
|
|
"approval_stage" text,
|
|
"escrow_terms" text,
|
|
"inspection_site_id" uuid,
|
|
"delivery_site_id" uuid,
|
|
"created_at" timestamp DEFAULT now() NOT NULL,
|
|
"updated_at" timestamp DEFAULT now() NOT NULL
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS "po_vendor_idx" ON "purchase_orders" ("vendor_id");
|
|
CREATE INDEX IF NOT EXISTS "po_org_idx" ON "purchase_orders" ("org_id");
|
|
ALTER TABLE "purchase_orders" ADD CONSTRAINT "purchase_orders_vendor_id_vendors_id_fk" FOREIGN KEY ("vendor_id") REFERENCES "public"."vendors"("id");
|
|
ALTER TABLE "purchase_orders" ADD CONSTRAINT "purchase_orders_inspection_site_id_sites_id_fk" FOREIGN KEY ("inspection_site_id") REFERENCES "public"."sites"("id");
|
|
ALTER TABLE "purchase_orders" ADD CONSTRAINT "purchase_orders_delivery_site_id_sites_id_fk" FOREIGN KEY ("delivery_site_id") REFERENCES "public"."sites"("id");
|
|
|
|
CREATE TABLE IF NOT EXISTS "shipments" (
|
|
"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
|
|
"org_id" text NOT NULL,
|
|
"purchase_order_id" uuid NOT NULL,
|
|
"tracking" text,
|
|
"cartons_pallets" jsonb,
|
|
"customs_docs_refs" jsonb,
|
|
"status" text DEFAULT 'pending' NOT NULL,
|
|
"created_at" timestamp DEFAULT now() NOT NULL,
|
|
"updated_at" timestamp DEFAULT now() NOT NULL
|
|
);
|
|
|
|
ALTER TABLE "shipments" ADD CONSTRAINT "shipments_purchase_order_id_purchase_orders_id_fk" FOREIGN KEY ("purchase_order_id") REFERENCES "public"."purchase_orders"("id");
|
|
|
|
CREATE TABLE IF NOT EXISTS "asset_components" (
|
|
"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
|
|
"parent_asset_id" uuid NOT NULL,
|
|
"child_asset_id" uuid NOT NULL,
|
|
"role" text NOT NULL,
|
|
"slot_index" integer,
|
|
"created_at" timestamp DEFAULT now() NOT NULL
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS "asset_components_parent_idx" ON "asset_components" ("parent_asset_id");
|
|
CREATE INDEX IF NOT EXISTS "asset_components_child_idx" ON "asset_components" ("child_asset_id");
|
|
ALTER TABLE "asset_components" ADD CONSTRAINT "asset_components_parent_asset_id_assets_id_fk" FOREIGN KEY ("parent_asset_id") REFERENCES "public"."assets"("id");
|
|
ALTER TABLE "asset_components" ADD CONSTRAINT "asset_components_child_asset_id_assets_id_fk" FOREIGN KEY ("child_asset_id") REFERENCES "public"."assets"("id");
|
|
|
|
CREATE TABLE IF NOT EXISTS "provisioning_records" (
|
|
"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
|
|
"asset_id" uuid NOT NULL,
|
|
"os_image" text,
|
|
"hypervisor_node" text,
|
|
"cluster_id" text,
|
|
"metadata" jsonb,
|
|
"created_at" timestamp DEFAULT now() NOT NULL,
|
|
"updated_at" timestamp DEFAULT now() NOT NULL
|
|
);
|
|
|
|
ALTER TABLE "provisioning_records" ADD CONSTRAINT "provisioning_records_asset_id_assets_id_fk" FOREIGN KEY ("asset_id") REFERENCES "public"."assets"("id");
|
|
|
|
CREATE TABLE IF NOT EXISTS "maintenances" (
|
|
"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
|
|
"org_id" text NOT NULL,
|
|
"asset_id" uuid NOT NULL,
|
|
"type" text NOT NULL,
|
|
"vendor_ticket_ref" text,
|
|
"description" text,
|
|
"status" text DEFAULT 'open' NOT NULL,
|
|
"created_at" timestamp DEFAULT now() NOT NULL,
|
|
"updated_at" timestamp DEFAULT now() NOT NULL
|
|
);
|
|
|
|
ALTER TABLE "maintenances" ADD CONSTRAINT "maintenances_asset_id_assets_id_fk" FOREIGN KEY ("asset_id") REFERENCES "public"."assets"("id");
|
|
|
|
CREATE TABLE IF NOT EXISTS "audit_events" (
|
|
"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
|
|
"org_id" text NOT NULL,
|
|
"actor_id" uuid,
|
|
"actor_email" text,
|
|
"action" text NOT NULL,
|
|
"resource_type" text NOT NULL,
|
|
"resource_id" text NOT NULL,
|
|
"before_state" jsonb,
|
|
"after_state" jsonb,
|
|
"occurred_at" timestamp DEFAULT now() NOT NULL
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS "audit_events_org_idx" ON "audit_events" ("org_id");
|
|
CREATE INDEX IF NOT EXISTS "audit_events_resource_idx" ON "audit_events" ("resource_type", "resource_id");
|
|
CREATE INDEX IF NOT EXISTS "audit_events_occurred_idx" ON "audit_events" ("occurred_at");
|
|
ALTER TABLE "audit_events" ADD CONSTRAINT "audit_events_actor_id_users_id_fk" FOREIGN KEY ("actor_id") REFERENCES "public"."users"("id");
|
|
|
|
CREATE TABLE IF NOT EXISTS "roles" (
|
|
"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
|
|
"name" text NOT NULL UNIQUE,
|
|
"description" text,
|
|
"permissions" jsonb DEFAULT '[]' NOT NULL,
|
|
"created_at" timestamp DEFAULT now() NOT NULL,
|
|
"updated_at" timestamp DEFAULT now() NOT NULL
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS "user_roles" (
|
|
"user_id" uuid NOT NULL,
|
|
"role_id" uuid NOT NULL,
|
|
"scope_site_id" uuid,
|
|
"scope_project_id" text,
|
|
"assigned_at" timestamp DEFAULT now() NOT NULL
|
|
);
|
|
|
|
ALTER TABLE "user_roles" ADD CONSTRAINT "user_roles_user_id_users_id_fk" FOREIGN KEY ("user_id") REFERENCES "public"."users"("id");
|
|
ALTER TABLE "user_roles" ADD CONSTRAINT "user_roles_role_id_roles_id_fk" FOREIGN KEY ("role_id") REFERENCES "public"."roles"("id");
|
|
ALTER TABLE "user_roles" ADD CONSTRAINT "user_roles_scope_site_id_sites_id_fk" FOREIGN KEY ("scope_site_id") REFERENCES "public"."sites"("id");
|
|
ALTER TABLE "user_roles" ADD PRIMARY KEY ("user_id", "role_id");
|