Homegraph concept database schema permissions

Question

I'm trying to make schema for my PostgreSQL database basing on the Homegraph concept, however I'm not really sure about correctness of current schema and whether user should have per-structure access or per-device. I can't find any real-world example of Homegraph concept implementation, and Google seem to not provide such information.

CREATE EXTENSION hstore;

CREATE TABLE users (
  id            CHAR(32)        NOT NULL,
  username      TEXT            NOT NULL,
  email         TEXT     UNIQUE NOT NULL,
  password_hash TEXT            NOT NULL,

  PRIMARY KEY (
    id
  )
);

CREATE TABLE user_structures (
  structure_id  CHAR(32) REFERENCES structures (id) ON DELETE CASCADE,
  user_id       CHAR(32) REFERENCES users      (id) ON DELETE CASCADE,
  manager       BOOL     NOT NULL,

  PRIMARY KEY (
    structure_id,
    user_id,
  )
);


CREATE TABLE structures (
  id          CHAR(32)  UNIQUE NOT NULL,
  label       TEXT      NOT NULL,

  PRIMARY KEY (
    id
  )
);


CREATE TABLE rooms (
  id           CHAR(32) UNIQUE NOT NULL,
  structure_id CHAR(32) REFERENCES structures (id) ON DELETE CASCADE,
  label        TEXT     NOT NULL,

  PRIMARY KEY (
    room_id
  )
);

CREATE TABLE devices (
  id              CHAR(32)    UNIQUE NOT NULL,
  room_id         CHAR(32)    REFERENCES room (id) ON DELETE CASCADE,
  password_hash   TEXT        NOT NULL,
  type            TEXT        NOT NULL,
  traits          TEXT[]      NOT NULL,
  name            TEXT        NOT NULL,
  will_push_state BOOL        NOT NULL,
  model           TEXT        NOT NULL,
  hw_version      TEXT        NOT NULL,
  sw_version      TEXT        NOT NULL,
  attributes      hstore      NOT NULL,

  PRIMARY KEY (
    id,
    structure_id
  )
);

score 0 · Answer 1 · answered Jul 01 '21 at 16:01

With regard to the devices table, there is a canonical protobuf that defines each of the fields and their types.

Beyond that, the implementation of the HomeGraph schema is an internal definition within Google's smart home platform and its structure shouldn't matter from a developer perspective.

Homegraph concept database schema permissions

1 Answers1