sensgw/schema2.sql

begin;

-- roles
alter schema public owner to sensor_admin;
grant usage on schema public to sensor_writer;

grant all on all tables in schema public to sensor_admin;
grant all on all sequences in schema public to sensor_admin;

grant insert, update, select on all tables in schema public to sensor_writer;
grant usage, select on all sequences in schema public to sensor_writer;

alter default privileges for role sensor_admin in schema public
    grant insert, update, select on tables to sensor_writer;

alter default privileges for role sensor_admin in schema public
    grant usage, select on sequences to sensor_writer;

-- pg_partman
create schema if not exists partman;
create extension if not exists pg_partman schema partman;

do $$
begin
  if not exists (select 1 from pg_roles where rolname = 'partman_user') then
    create role partman_user with login;
  end if;
end$$;

grant all on schema partman to partman_user;
grant all on all tables in schema partman to partman_user;
grant execute on all functions in schema partman to partman_user;
grant execute on all procedures in schema partman to partman_user;

grant all on schema public to partman_user;
grant all on all tables in schema public to partman_user;
grant temporary on database sensor_db to partman_user;
grant create on database sensor_db to partman_user;

grant usage, create on schema partman to sensor_admin;
grant execute on all functions in schema partman to sensor_admin;
grant all on all tables in schema partman to sensor_admin;
grant all on all sequences in schema partman to sensor_admin;

set role sensor_admin;

-- physical/transport endpoints
create table if not exists endpoints (
    endpoint_id int primary key generated by default as identity,
    endpoint_key text not null unique,

    -- 'snmp' | 'mqtt' | 'visa'
    protocol text not null,
    conn jsonb not null default '{}'::jsonb,

    is_enabled boolean not null default true,

    created_at timestamptz not null default now(),
    updated_at timestamptz not null default now()
);

create index if not exists idx_endpoints_enabled_protocol
on endpoints (is_enabled, protocol);

create table if not exists locations (
    location_id int primary key generated by default as identity,
    name text not null,
    is_active bool not null default true,
    constraint locations_name_unique unique (name)
);

create table if not exists devices (
    device_id int primary key generated by default as identity,
    -- "snmp:10.0.0.12:probe1"
    -- "mqtt:esp32-1"
    -- "visa:prologix:gpib5"
    device_key text not null unique,

    endpoint_id int references endpoints(endpoint_id),
    location_id int references locations(location_id),
    is_enabled boolean not null default true,

    metadata jsonb not null default '{}'::jsonb,

    created_at timestamptz not null default now(),
    updated_at timestamptz not null default now()
);

create index if not exists idx_devices_endpoint on devices (endpoint_id);
create index if not exists idx_devices_location on devices (location_id);
create index if not exists idx_devices_enabled on devices (is_enabled);

-- per-device measurement defs
create table if not exists device_channels (
    channel_id int primary key generated by default as identity,
    device_id int not null references devices(device_id) on delete cascade,

    -- corresponds to a column name in sensor_data (wide mapping)
    metric text not null,

    -- SNMP: {"type":"snmp_oid","oid":".1.3.6...","datatype":"float"}
    -- MQTT: {"type":"mqtt_topic","topic":"sensors/x/telemetry","payload":"json","field":"temperature_c"}
    -- VISA: {"type":"scpi","query":"MEAS:VOLT:DC?","read_termination":"\n"}
    source jsonb not null,

    scale_value double precision not null default 1.0,
    offset_value double precision not null default 0.0,

    poll_interval_s int,
    is_enabled boolean not null default true,

    created_at timestamptz not null default now(),

    constraint device_channels_unique_metric unique (device_id, metric)
);

create index if not exists idx_device_channels_device on device_channels (device_id);

-- device health/status
create table if not exists device_status (
    device_id int primary key references devices(device_id) on delete cascade,
    last_seen timestamptz,
    last_ok timestamptz,
    last_error_at timestamptz,
    last_error text,
    updated_at timestamptz not null default now()
);

create table if not exists sensor_data (
    ts          timestamptz not null,
    device_id   int not null references devices(device_id),
    location_id int references locations(location_id),

    temp_c      real,
    humidity_rh real,
    pressure_pa real,
    light_lux   real,
    soil_moist  real,
    co2_ppm     real,

    voltage_v   real,
    current_a   real,
    resistance_ohm real,
    freq_hz     real,
    power_w     real,

    constraint sensor_data_pk primary key (device_id, ts)
) partition by range (ts);

create index if not exists idx_sensor_data_device_ts on sensor_data (device_id, ts desc);
create index if not exists idx_sensor_data_location_ts on sensor_data (location_id, ts desc);
create index if not exists idx_sensor_data_ts_brin on sensor_data using brin(ts);

-- partition
select partman.create_parent(
    p_parent_table  => 'public.sensor_data',
    p_control       => 'ts',
    p_interval      => '1 year',
    p_premake       => 2
);

commit;