Inventory database proposal

chaos.sql

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

-- Units

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

-- 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,
  -- Visibility
  public BOOLEAN NOT NULL DEFAULT FALSE,
  -- Constraints
  CONSTRAINT unq UNIQUE (slug)
);

-- 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,
  -- Constraints
  CONSTRAINT unq UNIQUE (slug)
);

-- Data (user-defined)

CREATE TABLE data_types (
  id INTEGER PRIMARY KEY NOT NULL,
  -- Metadata
  name VARCHAR NOT NULL
);

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

CREATE TABLE data_schemas (
  id INTEGER PRIMARY KEY NOT NULL,
  -- Content
  type_id INTEGER NOT NULL,
  -- Metadata
  name VARCHAR NOT NULL,
  note TEXT NOT NULL DEFAULT "",
  slug VARCHAR(128) NOT NULL,
  -- Permissions
  read_only BOOLEAN NOT NULL DEFAULT FALSE,
  -- Constraints
  FOREIGN KEY(type_id) REFERENCES data_types(id),
  CONSTRAINT unq UNIQUE (slug)
);

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

CREATE TABLE items (
  id INTEGER PRIMARY KEY NOT NULL,
  uuid VARCHAR(36) NOT NULL,
  -- Metadata
  name VARCHAR NOT NULL,
  note TEXT NOT NULL DEFAULT "",
  -- Product data
  product_id INTEGER,
  -- Constraints
  CONSTRAINT unq UNIQUE (uuid),
  FOREIGN KEY(product_id) 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,
  tag_id INTEGER NOT NULL,
  -- Constraints
  FOREIGN KEY(item_id) REFERENCES items(id),
  FOREIGN KEY(tag_id) REFERENCES tags(id),
  CONSTRAINT unq UNIQUE (item_id, tag_id)
);

CREATE TABLE item_ids (
  id INTEGER PRIMARY KEY NOT NULL,
  --
  item_id INTEGER NOT NULL,
  schema_id INTEGER NOT NULL,
  value VARCHAR NOT NULL,
  -- Constraints
  FOREIGN KEY(item_id) REFERENCES items(id),
  FOREIGN KEY(schema_id) REFERENCES id_schemas(id),
  CONSTRAINT unq UNIQUE (schema_id, value),
  CONSTRAINT unq 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,
  schema_id INTEGER NOT NULL,
  value VARCHAR NOT NULL,
  -- Constraints
  FOREIGN KEY(item_id) REFERENCES items(id),
  FOREIGN KEY(schema_id) REFERENCES data_schemas(id),
  CONSTRAINT unq UNIQUE (item_id, schema_id)
);

CREATE TABLE item_notes (
  id INTEGER PRIMARY KEY NOT NULL,
  uuid VARCHAR(36) NOT NULL,
  --
  item_id INTEGER NOT NULL,
  timestamp TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  content TEXT NOT NULL DEFAULT "",
  -- Constraints
  FOREIGN KEY(item_id) REFERENCES items(id),
  CONSTRAINT unq UNIQUE (uuid)
);

-- Time series for quantity

CREATE TABLE item_quantities (
  id INTEGER PRIMARY KEY NOT NULL,
  item_id INTEGER NOT NULL,
  timestamp TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  quantity INTEGER NOT NULL DEFAULT 1,
  quantity_unit_id INTEGER,
  --
  FOREIGN KEY(item_id) REFERENCES items(id),
  FOREIGN KEY(quantity_unit_id) 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)

CREATE VIEW item_quantities_latest AS
  SELECT *
  FROM item_quantities
  GROUP BY item_id
  HAVING MAX(timestamp);

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

CREATE TABLE products (
  id INTEGER PRIMARY KEY NOT NULL,
  uuid VARCHAR(36) NOT NULL,
  -- Metadata
  name VARCHAR NOT NULL,
  note TEXT NOT NULL DEFAULT "",
  -- Constraints
  CONSTRAINT unq UNIQUE (uuid)
);

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

CREATE TABLE product_ids (
  id INTEGER PRIMARY KEY NOT NULL,
  --
  product_id INTEGER NOT NULL,
  schema_id INTEGER NOT NULL,
  value VARCHAR NOT NULL,
  -- Constraints
  FOREIGN KEY(product_id) REFERENCES products(id),
  FOREIGN KEY(schema_id) REFERENCES id_schemas(id),
  CONSTRAINT unq UNIQUE (schema_id, value)
  CONSTRAINT unq 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,
  schema_id INTEGER NOT NULL,
  value VARCHAR NOT NULL,
  -- Constraints
  FOREIGN KEY(product_id) REFERENCES products(id),
  FOREIGN KEY(schema_id) REFERENCES data_schemas(id),
  CONSTRAINT unq UNIQUE (product_id, schema_id)
);

-- 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,
  -- Hierarchy
  parent_id INTEGER,
  -- Constraints
  FOREIGN KEY(parent_id) REFERENCES locations(id),
  CONSTRAINT unq UNIQUE (slug)
);

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

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

CREATE TABLE tasks (
  id INTEGER PRIMARY KEY NOT NULL,
  uuid VARCHAR(36) NOT NULL,
  --
  item_id INTEGER NOT NULL,
  --
  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
  CONSTRAINT unq UNIQUE (uuid),
  FOREIGN KEY(item_id) REFERENCES items(id),
  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,
  --
  timestamp TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  done BOOLEAN NOT NULL DEFAULT FALSE,
  --
  FOREIGN KEY(task_id) REFERENCES tasks(id)
);

CREATE VIEW task_status_latest AS
  SELECT *
  FROM task_status
  GROUP BY task_id
  HAVING MAX(timestamp);

CREATE VIEW _tasks_with_status AS
  SELECT * 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 == FALSE AND
        (start IS NULL OR start <= datetime('now'))
      ) THEN 1 ELSE NULL END
    ) AS pending,
    COUNT(
      CASE WHEN (
        done == FALSE AND
        due <= datetime('now')
      ) THEN 1 ELSE NULL END
    ) AS overdue,
    COUNT(
      CASE WHEN (
        expires_item == TRUE AND
        done == FALSE AND
        due <= datetime('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 <= datetime('now'))
    GROUP BY item_id
  ) d USING (item_id)
  LEFT JOIN (
    SELECT
      item_id,
      MIN(due) AS expiry
    FROM _tasks_with_status
    WHERE
      done == FALSE AND
      (start IS NULL OR start <= datetime('now')) AND
      expires_item == TRUE
    GROUP BY item_id
  ) e USING (item_id)
  GROUP BY item_id;

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

CREATE TABLE lists (
  id INTEGER PRIMARY KEY NOT NULL,
  uuid VARCHAR(36) NOT NULL,
  -- Metadata
  name VARCHAR NOT NULL,
  note TEXT NOT NULL DEFAULT "",
  -- Constraints
  CONSTRAINT unq UNIQUE (uuid)
);

CREATE TABLE list_entries (
  id INTEGER PRIMARY KEY NOT NULL,
  uuid VARCHAR(36) NOT NULL,
  --
  list_id INTEGER NOT NULL,
  rank VARCHAR,
  --
  item_id INTEGER,
  product_id INTEGER,
  quantity INTEGER,
  quantity_unit_id INTEGER,
  --
  CONSTRAINT unq UNIQUE (uuid),
  FOREIGN KEY(list_id) REFERENCES lists(id),
  CONSTRAINT unq UNIQUE (list_id, rank),
  FOREIGN KEY(item_id) REFERENCES items(id),
  FOREIGN KEY(product_id) REFERENCES products(id),
  FOREIGN KEY(quantity_unit_id) REFERENCES units(id),
  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 VARCHAR(36) NOT NULL,
  --
  list_id INTEGER NOT NULL,
  parent_item_id INTEGER,
  parent_location_id INTEGER,
  --
  CONSTRAINT unq UNIQUE (uuid),
  FOREIGN KEY(list_id) REFERENCES lists(id),
  FOREIGN KEY(parent_item_id) REFERENCES items(id),
  FOREIGN KEY(parent_location_id) 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;


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

CREATE TABLE tree_log_types (
  id INTEGER PRIMARY KEY NOT NULL,
  -- Metadata
  type VARCHAR NOT NULL
);

INSERT INTO tree_log_types (id, type) VALUES
  (1, 'actual'),
  (2, 'target'),
  (3, 'incidental');

CREATE TABLE tree_adjacency_log (
  id INTEGER PRIMARY KEY NOT NULL,
  timestamp TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  -- Child
  item_id INTEGER,
  location_id INTEGER,
  -- Parent
  parent_item_id INTEGER,
  parent_location_id INTEGER,
  --
  log_type_id INTEGER NOT NULL,
  --
  FOREIGN KEY(item_id) REFERENCES items(id),
  FOREIGN KEY(location_id) REFERENCES locations(id),
  FOREIGN KEY(parent_item_id) REFERENCES items(id),
  FOREIGN KEY(parent_location_id) REFERENCES locations(id),
  FOREIGN KEY(log_type_id) REFERENCES tree_log_types(id),
  -- 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_id
  );

CREATE VIEW tree_adjacency_state AS
  SELECT *
  FROM tree_adjacency_current
  WHERE log_type_id = 1; -- state

CREATE VIEW tree_adjacency_target AS
  SELECT *
  FROM tree_adjacency_current
  WHERE log_type_id = 2; -- target

-- Loop detection

CREATE TRIGGER tree_validate_insert BEFORE INSERT ON tree_adjacency_log
  WHEN EXISTS (
    SELECT * 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)
  )
  BEGIN
    SELECT RAISE(ABORT, 'Insert would create a loop');
  END;

CREATE TRIGGER tree_validate_update BEFORE UPDATE ON tree_adjacency_log
  WHEN EXISTS (
    SELECT * 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)
  )
  BEGIN
    SELECT RAISE(ABORT, 'Update would create a loop');
  END;

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