Inventory database proposal

chaos.pgsql

-- modified from SQLite to PostgreSQL, --Andi 2024-07-01

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

-- Units

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

-- IDs

CREATE TABLE id_schemas (
  id INTEGER PRIMARY KEY NOT NULL,
  -- 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 INTEGER PRIMARY KEY NOT NULL,
  --
  name VARCHAR(255) NOT NULL,
  note TEXT NOT NULL DEFAULT '',
  slug VARCHAR(255) NOT NULL UNIQUE
);

-- Data (user-defined)

-- TODO in postgres this should be an enum
CREATE TABLE data_types (
  name VARCHAR PRIMARY KEY NOT NULL
);

INSERT INTO data_types (name) VALUES
  ('text'),
  ('number'),
  ('date');

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

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

CREATE TABLE product_tags (
  id INTEGER PRIMARY KEY NOT NULL,
  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 INTEGER PRIMARY KEY NOT NULL,
  --
  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 INTEGER PRIMARY KEY NOT NULL,
  --
  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 INTEGER PRIMARY KEY NOT NULL,
  uuid uuid NOT NULL UNIQUE,
  -- Metadata
  name VARCHAR NOT NULL,
  note TEXT NOT NULL DEFAULT '',
  -- Product data
  product_id INTEGER REFERENCES products(id)
);

CREATE UNIQUE INDEX idx_items_uuid ON items (uuid);

CREATE TABLE item_tags (
  id INTEGER PRIMARY KEY NOT NULL,
  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 INTEGER PRIMARY KEY NOT NULL,
  --
  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 INTEGER PRIMARY KEY NOT NULL,
  --
  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 INTEGER PRIMARY KEY NOT NULL,
  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 INTEGER PRIMARY KEY NOT NULL,
  item_id INTEGER NOT NULL REFERENCES items(id),
  date TIMESTAMPTZ NOT NULL DEFAULT now(),
  quantity INTEGER NOT NULL DEFAULT 1,
  quantity_unit_id INTEGER REFERENCES units(id),
  --
  CHECK (quantity >= 0)
);

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



-- previous SQLite specific version:
--
--CREATE VIEW item_quantities_latest AS
--  SELECT *
--  FROM item_quantities
--  GROUP BY item_id
--  HAVING MAX(date);

CREATE VIEW item_quantities_latest AS
  SELECT iq.*
  FROM item_quantities iq
  INNER JOIN (
    SELECT item_id, MAX(date) AS latest_date
    FROM item_quantities
    GROUP BY item_id
  ) iq_latest
  ON iq.item_id = iq_latest.item_id AND iq.date = iq_latest.latest_date;


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

CREATE TABLE locations (
  id INTEGER PRIMARY KEY NOT NULL,
  -- 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 INTEGER PRIMARY KEY NOT NULL,
  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 INTEGER PRIMARY KEY NOT NULL,
  uuid uuid NOT NULL UNIQUE,
  --
  item_id INTEGER NOT NULL REFERENCES items(id),
  --
  name VARCHAR NOT NULL,
  note TEXT NOT NULL DEFAULT '',
  -- Dates
  due TIMESTAMP,
  start TIMESTAMP,
  -- 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 INTEGER PRIMARY KEY NOT NULL,
  task_id INTEGER NOT NULL REFERENCES tasks(id),
  --
  date TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
  done BOOLEAN NOT NULL DEFAULT FALSE
  --
);

-- previous SQLite specific version:
--
-- CREATE VIEW task_status_latest AS  SELECT *
--  FROM task_status
--  GROUP BY task_id
--  HAVING MAX(date);
  
CREATE VIEW task_status_latest AS
  SELECT DISTINCT ON (task_id) *
  FROM task_status
  ORDER BY task_id, date DESC;

-- previous SQLite version:
--CREATE VIEW _tasks_with_status AS
--  SELECT * FROM tasks
--  LEFT JOIN task_status_latest ON tasks.id = task_status_latest.task_id;
 
-- ERROR:  column "id" specified more than once

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,
    d.next_due,
    e.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;

-- ERROR:  column "d.next_due" must appear in the GROUP BY clause or be used in an aggregate function
-- LINE 23:     d.next_due,
--              ^


-- Lists -----------------------------------------------------------------------  o

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

CREATE TABLE list_entries (
  id INTEGER PRIMARY KEY NOT NULL,
  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_id INTEGER REFERENCES units(id),
  --
  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 INTEGER PRIMARY KEY NOT NULL,
  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, list_entry_id,
    -- 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),
      0
    )
    AS ok
  FROM (
    -- Looking for item
	  SELECT
      list_checks.list_id,
      list_checks.id AS list_check_id,
      list_entries.id AS list_entry_id,
      -- 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_id AS target_quantity_unit,
      -- Actual states
      COALESCE(item_quantities_latest.quantity, 1) AS latest_quantity,
      item_quantities_latest.quantity_unit_id 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,
      -- 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_id AS target_quantity_unit,
      -- Actual states
      COALESCE(product_quantities_latest.quantity, 1) AS latest_quantity,
      product_quantities_latest.quantity_unit_id 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_id
        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_id
      ) 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_id = list_entries.quantity_unit_id
          OR (
            product_quantities_latest.quantity_unit_id IS NULL
            AND list_entries.quantity_unit_id IS NULL
          )
        )
      WHERE list_entries.product_id IS NOT NULL
  ) ORDER BY list_id, check_id, rank;

-- ERROR:  subquery in FROM must have an alias
-- LINE 12:   FROM (
--                ^
-- HINT:  For example, FROM (SELECT ...) [AS] foo.



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

CREATE TABLE tree_log_types (
  type VARCHAR PRIMARY KEY NOT NULL
);

-- TODO enum
INSERT INTO tree_log_types (type) VALUES
  ('actual'),
  ('target'),
  ('incidental');

CREATE TABLE tree_adjacency_log (
  id INTEGER PRIMARY KEY NOT NULL,
  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 VARCHAR NOT NULL REFERENCES tree_log_types(type),
  --
  -- 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
  -- Note that this is unlikely to be optimal;
  -- it will need to be revised at some later point.
  SELECT *
  FROM tree_adjacency_log
  WHERE id IN (
    SELECT max(id)
    FROM tree_adjacency_log
    GROUP BY item_id, location_id, log_type
  );

CREATE VIEW tree_adjacency_state AS
  SELECT *
  FROM tree_adjacency_current
  WHERE log_type = 'actual'; -- state TODO?

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

-- Loop detection

-- Postgres specific:

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);