migrations/001_create_categories.sql

-- up
CREATE TABLE categories (
  id          INTEGER PRIMARY KEY AUTOINCREMENT,
  parent_id   INTEGER REFERENCES categories(id),
  level       INTEGER NOT NULL CHECK (level IN (1,2,3)),
  code        TEXT NOT NULL,
  name        TEXT NOT NULL,
  noisy       INTEGER NOT NULL DEFAULT 0,
  metadata    TEXT NOT NULL DEFAULT '{}',
  created_at  TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%SZ','now')),
  UNIQUE (parent_id, code)
);

-- down
DROP TABLE categories;