Inventory database proposal

chaos.sql

-- Common metadata -------------------------------------------------------------

-- Units

CREATE TABLE units (
  id SERIAL PRIMARY KEY,
  -- Metadata
  name VARCHAR NOT NULL UNIQUE,
  note TEXT NOT NULL DEFAULT '',
  --
  abbr VARCHAR(128) NOT NULL UNIQUE
);

-- IDs

CREATE TABLE id_schemas (
  id SERIAL PRIMARY KEY,
  -- Metadata
  name VARCHAR NOT NULL,
  note TEXT NOT NULL DEFAULT '',
  slug VARCHAR(128) NOT NULL UNIQUE,
  -- Visibility
  public BOOLEAN NOT NULL DEFAULT FALSE
);

-- Tags

CREATE TABLE tags (
  id SERIAL PRIMARY KEY,
  --
  name VARCHAR(255) NOT NULL,
  note TEXT NOT NULL DEFAULT '',
  slug VARCHAR(128) NOT NULL UNIQUE
);

-- Data (user-defined)

CREATE TYPE data_type AS ENUM ('text', 'number', 'date');

CREATE TABLE data_schemas (
  id SERIAL PRIMARY KEY,
  -- Content
  type data_type NOT NULL,
  -- Metadata
  name VARCHAR NOT NULL,
  note TEXT NOT NULL DEFAULT '',
  slug VARCHAR(128) NOT NULL UNIQUE,
  -- Permissions
  read_only BOOLEAN NOT NULL DEFAULT FALSE
);

-- Entities --------------------------------------------------------------------

CREATE TABLE entities (
  id SERIAL PRIMARY KEY,
  handle VARCHAR NOT NULL
);

-- Products --------------------------------------------------------------------

CREATE TABLE products (
  id BIGSERIAL PRIMARY KEY,
  uuid UUID NOT NULL UNIQUE,
  -- Metadata
  name VARCHAR NOT NULL,
  note TEXT NOT NULL DEFAULT ''
);

CREATE TABLE product_tags (
  id BIGSERIAL PRIMARY KEY,
  product_id INTEGER NOT NULL REFERENCES products(id),
  tag_id INTEGER NOT NULL REFERENCES tags(id),
  -- Constraints
  UNIQUE (product_id, tag_id)
);

CREATE TABLE product_ids (
  id BIGSERIAL PRIMARY KEY,
  --
  product_id INTEGER NOT NULL REFERENCES products(id),
  schema_id INTEGER NOT NULL REFERENCES id_schemas(id),
  value VARCHAR NOT NULL,
  -- Constraints
  UNIQUE (schema_id, value),
  UNIQUE (product_id, schema_id)
  -- (see above for uniqueness considerations)
);

CREATE TABLE product_data (
  id BIGSERIAL PRIMARY KEY,
  --
  product_id INTEGER NOT NULL REFERENCES products(id),
  schema_id INTEGER NOT NULL REFERENCES data_schemas(id),
  value VARCHAR NOT NULL,
  -- Constraints
  UNIQUE (product_id, schema_id)
);

-- Items -----------------------------------------------------------------------

CREATE TABLE items (
  id BIGSERIAL PRIMARY KEY,
  uuid UUID NOT NULL UNIQUE,
  -- Metadata
  name VARCHAR NOT NULL,
  note TEXT NOT NULL DEFAULT '',
  owner_id INTEGER REFERENCES entities(id),
  -- Product data
  product_id INTEGER REFERENCES products(id)
);

CREATE UNIQUE INDEX idx_items_uuid ON items (uuid);

CREATE TABLE item_tags (
  id BIGSERIAL PRIMARY KEY,
  item_id INTEGER NOT NULL REFERENCES items(id),
  tag_id INTEGER NOT NULL REFERENCES tags(id),
  UNIQUE (item_id, tag_id)
);

CREATE TABLE item_ids (
  id BIGSERIAL PRIMARY KEY,
  --
  item_id INTEGER NOT NULL REFERENCES items(id),
  schema_id INTEGER NOT NULL REFERENCES id_schemas(id),
  value VARCHAR NOT NULL,
  -- Constraints
  UNIQUE (schema_id, value),
  UNIQUE (item_id, schema_id)
  -- We currently enforce uniqueness on the product_id / schema_id
  -- combination but that may change in the future -- it makes some
  -- sense for items to have multiple ids in the same schema,
  -- but it complicates the API quite a bit.
);

CREATE TABLE item_data (
  id BIGSERIAL PRIMARY KEY,
  --
  item_id INTEGER NOT NULL REFERENCES items(id),
  schema_id INTEGER NOT NULL REFERENCES data_schemas(id),
  value VARCHAR NOT NULL,
  -- Constraints
  UNIQUE (item_id, schema_id)
);

CREATE TABLE item_notes (
  id BIGSERIAL PRIMARY KEY,
  uuid UUID NOT NULL UNIQUE,
  --
  item_id INTEGER NOT NULL REFERENCES items(id),
  date TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
  content TEXT NOT NULL DEFAULT ''
);

-- Time series for quantity

CREATE TABLE item_quantities (
  id BIGSERIAL PRIMARY KEY,
  item_id INTEGER NOT NULL REFERENCES items(id),
  date TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
  quantity INTEGER NOT NULL DEFAULT 1,
  quantity_unit VARCHAR(128) REFERENCES units(abbr),
  --
  CHECK (quantity >= 0)
);

-- (none of these tables have uniqueness constraints applied to them,
-- as only the latest entry per item determines its current status)

CREATE VIEW item_quantities_latest AS
  SELECT
    DISTINCT ON (item_id)
    *
  FROM item_quantities
  ORDER BY item_id, date DESC;

-- Locations -------------------------------------------------------------------

CREATE TABLE locations (
  id SERIAL PRIMARY KEY,
  -- Metadata
  name VARCHAR NOT NULL,
  note TEXT NOT NULL DEFAULT '',
  slug VARCHAR(128) NOT NULL UNIQUE,
  -- Hierarchy
  parent_id INTEGER REFERENCES locations(id)
);

CREATE TABLE location_tags (
  id SERIAL PRIMARY KEY,
  location_id INTEGER NOT NULL REFERENCES locations(id),
  tag_id INTEGER NOT NULL REFERENCES tags(id),
  -- Constraints
  UNIQUE (location_id, tag_id)
);

-- Tasks -----------------------------------------------------------------------

CREATE TABLE tasks (
  id BIGSERIAL PRIMARY KEY,
  uuid UUID NOT NULL UNIQUE,
  --
  item_id INTEGER NOT NULL REFERENCES items(id),
  --
  name VARCHAR NOT NULL,
  note TEXT NOT NULL DEFAULT '',
  -- Dates
  due TIMESTAMPTZ,
  start TIMESTAMPTZ,
  -- Whether a task can be completed
  -- (e.g. an expiry date cannot be invalidated through user action)
  locked BOOLEAN NOT NULL DEFAULT FALSE,
  -- Effects on associated item
  expires_item BOOLEAN NOT NULL DEFAULT FALSE,
  -- Constraints
  CHECK (NOT (due < start)),
  CHECK (NOT (expires_item IS TRUE AND due IS NULL))
);

CREATE TABLE task_status (
  id BIGSERIAL PRIMARY KEY,
  task_id INTEGER NOT NULL REFERENCES tasks(id),
  --
  date TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
  done BOOLEAN NOT NULL DEFAULT FALSE
);

CREATE VIEW task_status_latest AS
  SELECT
    DISTINCT ON (task_id)
    *
  FROM task_status
  ORDER BY task_id, date DESC;

CREATE VIEW _tasks_with_status AS
  SELECT
    tasks.*,
    task_status_latest.id AS task_status_id,
    task_status_latest.date AS date,
    task_status_latest.done AS done
  FROM tasks
  	LEFT JOIN task_status_latest ON tasks.id = task_status_latest.task_id;

CREATE VIEW item_task_status AS
  SELECT
    item_id,
    COUNT(
      CASE WHEN (
        done IS FALSE AND
        (start IS NULL OR start <= now())
      ) THEN 1 ELSE NULL END
    ) AS pending,
    COUNT(
      CASE WHEN (
        done IS FALSE AND
        due <= now()
      ) THEN 1 ELSE NULL END
    ) AS overdue,
    COUNT(
      CASE WHEN (
        expires_item = TRUE AND
        done = FALSE AND
        due <= now()
      ) THEN 1 ELSE NULL END
    ) > 0 AS expired,
    MIN(d.next_due) AS next_due,
    MIN(e.expiry) AS expiry
  FROM _tasks_with_status
  LEFT JOIN (
    SELECT
      item_id,
      MIN(due) AS next_due
    FROM _tasks_with_status
    WHERE
      done = FALSE AND
      (start IS NULL OR start <= now())
    GROUP BY item_id
  ) d USING (item_id)
  LEFT JOIN (
    SELECT
      item_id,
      MIN(due) AS expiry
    FROM _tasks_with_status
    WHERE
      done IS FALSE AND
      (start IS NULL OR start <= now()) AND
      expires_item = TRUE
    GROUP BY item_id
  ) e USING (item_id)
  GROUP BY item_id;

-- Tree structures -------------------------------------------------------------

CREATE TYPE log_type AS ENUM ('actual', 'target', 'incidental');

CREATE TABLE tree_adjacency_log (
  id BIGSERIAL PRIMARY KEY,
  date TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
  -- Child
  item_id INTEGER REFERENCES items(id),
  location_id INTEGER REFERENCES locations(id),
  -- Parent
  parent_item_id INTEGER REFERENCES items(id),
  parent_location_id INTEGER REFERENCES locations(id),
  --
  log_type log_type NOT NULL,
  --
  -- Node is either item or location
  CHECK (item_id IS NULL OR location_id IS NULL),
  CHECK (parent_item_id IS NULL OR parent_location_id IS NULL),
  CHECK (NOT (item_id IS NULL AND location_id IS NULL)),
  CHECK (NOT (parent_item_id IS NULL AND parent_location_id IS NULL)),
  -- Item and parent must be distinct
  CHECK (NOT (item_id = parent_item_id)),
  CHECK (NOT (location_id = parent_location_id)),
  -- Locations can only be nested in locations
  CHECK (NOT (location_id IS NOT NULL AND parent_item_id IS NOT NULL))
);

CREATE VIEW tree_adjacency_current AS
  SELECT
    DISTINCT ON (item_id, location_id, log_type)
    *
  FROM tree_adjacency_log
  ORDER BY item_id, location_id, log_type, date DESC;

CREATE VIEW tree_adjacency_state AS
  SELECT *
  FROM tree_adjacency_current
  WHERE log_type = 'actual';

CREATE VIEW tree_adjacency_target AS
  SELECT *
  FROM tree_adjacency_current
  WHERE log_type = 'target';

-- Loop detection

CREATE OR REPLACE FUNCTION validate_tree()
  RETURNS TRIGGER AS $$
  BEGIN
    IF EXISTS (
      SELECT 1 FROM tree_closure
      WHERE (
        descendant_item = NEW.parent_item_id
        OR descendant_location = NEW.parent_location_id
      ) AND (
        ancestor_item = NEW.item_id
        OR ancestor_location = NEW.location_id
      )
    ) THEN
      RAISE EXCEPTION 'Insert/Update would create a loop';
    END IF;
    RETURN NEW;
  END;
  $$ LANGUAGE plpgsql;

CREATE TRIGGER tree_validate_insert
  BEFORE INSERT ON tree_adjacency_log
  FOR EACH ROW
  EXECUTE FUNCTION validate_tree();

CREATE TRIGGER tree_validate_update
  BEFORE UPDATE ON tree_adjacency_log
  FOR EACH ROW
  EXECUTE FUNCTION validate_tree();

-- Views into tree -------------------------------------------------------------

-- A closure table is a far better starting point for queries into a tree
-- than an adjacency list. At some point we may want to manage the table
-- ourselves, but for now we generate it on demand.
CREATE VIEW tree_closure AS
  WITH RECURSIVE closure_cte AS (
    -- Anchor query
    SELECT
      parent_item_id AS ancestor_item,
      parent_location_id AS ancestor_location,
      parent_item_id AS descendant_item,
      parent_location_id AS descendant_location,
      0 AS depth
    FROM tree_adjacency_state
    --
    UNION
    -- Lookup nested
    SELECT
      cte.ancestor_item AS ancestor_item,
      cte.ancestor_location AS ancestor_location,
      cur.item_id AS descendant_item,
      cur.location_id AS descendant_location,
      cte.depth + 1 AS depth
    FROM tree_adjacency_state AS cur
    JOIN closure_cte AS cte
      ON cur.parent_item_id = cte.descendant_item OR
         cur.parent_location_id = cte.descendant_location
  )
  SELECT * from closure_cte
  WHERE NOT (ancestor_item IS NULL AND ancestor_location IS NULL);

-- Lists -----------------------------------------------------------------------

CREATE TABLE lists (
  id SERIAL PRIMARY KEY,
  uuid UUID NOT NULL UNIQUE,
  -- Metadata
  name VARCHAR NOT NULL,
  note TEXT NOT NULL DEFAULT ''
);

CREATE TABLE list_entries (
  id BIGSERIAL PRIMARY KEY,
  uuid UUID NOT NULL UNIQUE,
  --
  list_id INTEGER NOT NULL REFERENCES lists(id),
  rank VARCHAR,
  --
  item_id INTEGER REFERENCES items(id),
  product_id INTEGER REFERENCES products(id),
  quantity INTEGER,
  quantity_unit VARCHAR(128) REFERENCES units(abbr),
  --
  UNIQUE (list_id, rank),
  CHECK (
    (item_id IS NULL OR product_id IS NULL) AND NOT
    (item_id IS NULL AND product_id IS NULL)
  )
);

CREATE TABLE list_checks (
  id SERIAL PRIMARY KEY,
  uuid UUID NOT NULL UNIQUE,
  --
  list_id INTEGER NOT NULL REFERENCES lists(id),
  parent_item_id INTEGER REFERENCES items(id),
  parent_location_id INTEGER REFERENCES locations(id),
  --
  CHECK (
    (parent_item_id IS NULL OR parent_location_id IS NULL) AND NOT
    (parent_item_id IS NULL AND parent_location_id IS NULL)
  )
);

CREATE VIEW list_checks_status AS
  SELECT
    list_id, list_check_id, rank,
    -- Evaluate check
    (latest_quantity >= target_quantity)
    AND COALESCE(
      (latest_quantity_unit = target_quantity_unit)
      OR (latest_quantity_unit IS NULL AND target_quantity_unit IS NULL),
      false
    )
    AS ok
  FROM (
    -- Looking for item
	  SELECT
      list_checks.list_id,
      list_checks.id AS list_check_id,
      list_entries.rank AS rank,
      -- Target states for items in list
      list_entries.item_id,
      list_entries.product_id,
      COALESCE(list_entries.quantity, 1) AS target_quantity,
      list_entries.quantity_unit AS target_quantity_unit,
      -- Actual states
      COALESCE(item_quantities_latest.quantity, 1) AS latest_quantity,
      item_quantities_latest.quantity_unit AS latest_quantity_unit
    FROM
      list_checks
      LEFT JOIN list_entries USING (list_id)
      LEFT JOIN tree_closure
        ON (
          tree_closure.ancestor_item = list_checks.parent_item_id
          OR tree_closure.ancestor_location = list_checks.parent_location_id
        )
        AND tree_closure.descendant_item = list_entries.item_id
      LEFT JOIN item_quantities_latest
        ON item_quantities_latest.item_id = list_entries.item_id
      WHERE list_entries.item_id IS NOT NULL
    --
    UNION
    -- Looking for product
	  SELECT
      list_checks.list_id,
      list_checks.id AS check_id,
      list_entries.rank AS rank,
      -- Target states for items in list
      list_entries.item_id,
      list_entries.product_id,
      COALESCE(list_entries.quantity, 1) AS target_quantity,
      list_entries.quantity_unit AS target_quantity_unit,
      -- Actual states
      COALESCE(product_quantities_latest.quantity, 1) AS latest_quantity,
      product_quantities_latest.quantity_unit AS latest_quantity_unit
    FROM
      list_checks
      LEFT JOIN list_entries USING (list_id)
      LEFT JOIN (
        SELECT
          tree_closure.ancestor_item as ancestor_item,
          tree_closure.ancestor_location as ancestor_location,
          items.product_id as product_id,
          SUM(coalesce(item_quantities_latest.quantity, 1)) AS quantity,
          item_quantities_latest.quantity_unit
        FROM tree_closure
          LEFT JOIN items
            ON items.id = tree_closure.descendant_item
          LEFT JOIN item_quantities_latest
            ON item_quantities_latest.item_id = tree_closure.descendant_item
        GROUP BY
          tree_closure.ancestor_item, tree_closure.ancestor_location,
          items.product_id, item_quantities_latest.quantity_unit
      ) product_quantities_latest
        ON (
          product_quantities_latest.ancestor_item = list_checks.parent_item_id
          OR product_quantities_latest.ancestor_location = list_checks.parent_location_id
        )
        AND product_quantities_latest.product_id = list_entries.product_id
        AND (
          product_quantities_latest.quantity_unit = list_entries.quantity_unit
          OR (
            product_quantities_latest.quantity_unit IS NULL
            AND list_entries.quantity_unit IS NULL
          )
        )
      WHERE list_entries.product_id IS NOT NULL
  ) AS subquery ORDER BY list_id, list_check_id, rank;