69 lines
1.7 KiB
PL/PgSQL
69 lines
1.7 KiB
PL/PgSQL
BEGIN;
|
|
|
|
CREATE TABLE organizations (
|
|
id UUID PRIMARY KEY,
|
|
name TEXT NOT NULL,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
CREATE TABLE devices (
|
|
id UUID PRIMARY KEY,
|
|
organization_id UUID NOT NULL
|
|
REFERENCES organizations(id)
|
|
ON DELETE RESTRICT,
|
|
device_fingerprint TEXT NOT NULL UNIQUE,
|
|
hostname TEXT,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
last_seen TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
CREATE TABLE hardware_snapshots (
|
|
id UUID PRIMARY KEY,
|
|
device_id UUID NOT NULL
|
|
REFERENCES devices(id)
|
|
ON DELETE CASCADE,
|
|
collected_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
architecture TEXT NOT NULL,
|
|
manufacturer TEXT,
|
|
product_name TEXT,
|
|
product_version TEXT,
|
|
system_uuid TEXT,
|
|
system_serial TEXT,
|
|
board_vendor TEXT,
|
|
board_name TEXT,
|
|
board_serial TEXT,
|
|
bios_vendor TEXT,
|
|
bios_version TEXT,
|
|
boot_mode TEXT,
|
|
secure_boot TEXT,
|
|
tpm_version TEXT,
|
|
cpu_model TEXT,
|
|
cpu_logical_count INTEGER,
|
|
memory_bytes BIGINT
|
|
);
|
|
|
|
CREATE TABLE network_interfaces (
|
|
id UUID PRIMARY KEY,
|
|
hardware_snapshot_id UUID NOT NULL
|
|
REFERENCES hardware_snapshots(id)
|
|
ON DELETE CASCADE,
|
|
name TEXT NOT NULL,
|
|
type TEXT NOT NULL,
|
|
mac_address TEXT NOT NULL
|
|
);
|
|
|
|
CREATE TABLE storage_devices (
|
|
id UUID PRIMARY KEY,
|
|
hardware_snapshot_id UUID NOT NULL
|
|
REFERENCES hardware_snapshots(id)
|
|
ON DELETE CASCADE,
|
|
name TEXT NOT NULL,
|
|
model TEXT,
|
|
serial TEXT,
|
|
size_bytes BIGINT NOT NULL,
|
|
transport TEXT
|
|
);
|
|
|
|
COMMIT;
|