marco.chan
d3b6c5032f
- Implemented camera slice in Redux store for managing camera state. - Added new tables in PostgreSQL for camera devices, lending, and audit logs. - Created camera-related schemas in contracts for validation. - Developed camera data flow documentation outlining ingest and read processes. - Introduced async thunks for fetching, creating, lending, returning, and updating cameras.
45 lines
1.2 KiB
SQL
45 lines
1.2 KiB
SQL
CREATE TABLE IF NOT EXISTS device_current_state (
|
|
device_id TEXT PRIMARY KEY,
|
|
payload JSONB NOT NULL DEFAULT '{}'::jsonb,
|
|
last_seen TIMESTAMPTZ NOT NULL,
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS devices (
|
|
camera_id TEXT PRIMARY KEY,
|
|
name TEXT NOT NULL,
|
|
location TEXT NOT NULL,
|
|
model TEXT,
|
|
notes TEXT,
|
|
is_active BOOLEAN NOT NULL DEFAULT TRUE,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS device_status_snapshots (
|
|
id BIGSERIAL PRIMARY KEY,
|
|
device_id TEXT NOT NULL,
|
|
payload JSONB NOT NULL DEFAULT '{}'::jsonb,
|
|
last_seen TIMESTAMPTZ NOT NULL,
|
|
changed_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS camera_lending (
|
|
camera_id TEXT PRIMARY KEY,
|
|
borrower TEXT NOT NULL,
|
|
due_date TIMESTAMPTZ NOT NULL,
|
|
purpose TEXT,
|
|
lent_at TIMESTAMPTZ NOT NULL,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS camera_audit_logs (
|
|
id BIGSERIAL PRIMARY KEY,
|
|
camera_id TEXT NOT NULL,
|
|
action TEXT NOT NULL,
|
|
actor TEXT NOT NULL,
|
|
metadata JSONB NOT NULL DEFAULT '{}'::jsonb,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
|
);
|