第三章：表、行、列 —— 一份档案到底长什么样

CREATE TABLE districts (
  district_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name text NOT NULL UNIQUE
);

CREATE TABLE citizens (
  citizen_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  district_id bigint NOT NULL REFERENCES districts(district_id),
  national_id text NOT NULL UNIQUE,
  full_name text NOT NULL,
  birth_date date NOT NULL,
  status text NOT NULL CHECK (status IN ('active', 'suspended', 'archived')),
  profile jsonb NOT NULL DEFAULT '{}'::jsonb,
  created_at timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE permits (
  permit_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  citizen_id bigint NOT NULL REFERENCES citizens(citizen_id),
  permit_type text NOT NULL,
  valid_from date NOT NULL,
  valid_to date NOT NULL,
  status text NOT NULL CHECK (status IN ('draft', 'active', 'expired', 'revoked')),
  meta jsonb NOT NULL DEFAULT '{}'::jsonb,
  created_at timestamptz NOT NULL DEFAULT now(),
  CHECK (valid_to >= valid_from)
);

CREATE TABLE permit_events (
  event_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  permit_id bigint NOT NULL REFERENCES permits(permit_id) ON DELETE CASCADE,
  event_type text NOT NULL,
  payload jsonb NOT NULL DEFAULT '{}'::jsonb,
  created_at timestamptz NOT NULL DEFAULT now()
);

districts      -> 行政区档案柜
citizens       -> 市民基础档案柜
permits        -> 通行证档案柜
permit_events  -> 通行证操作日志柜

INSERT INTO districts (name) VALUES ('东城');

INSERT INTO citizens (
  district_id, national_id, full_name, birth_date, status
) VALUES (
  1, 'CN-310100-0001', '林远', DATE '1998-06-12', 'active'
);

SELECT citizen_id, full_name, status
FROM citizens
WHERE national_id = 'CN-310100-0001';