Implement API security baseline (sys_user, JWT, audit fields, transactions, unique constraints) and Web real login with 401 handling. Document setup and development workflows in root, WmsLite.Api, and WmsLite.Web README files. Co-authored-by: Cursor <cursoragent@cursor.com>
159 lines
6.0 KiB
SQL
159 lines
6.0 KiB
SQL
-- PostgreSQL 主数据与库存结构(与 WmsLite.Api 实体、BaseEntity 审计字段一致)
|
||
-- 列顺序:主键 id → 业务字段 → 审计字段(create/update/delete)置于表末
|
||
-- 执行前请按需调整库名/Schema。
|
||
|
||
-- 仓库
|
||
CREATE TABLE IF NOT EXISTS warehouse (
|
||
id BIGINT NOT NULL PRIMARY KEY,
|
||
code VARCHAR(64) NOT NULL,
|
||
name VARCHAR(256) NOT NULL,
|
||
attrs JSONB NULL,
|
||
create_time TIMESTAMP NULL,
|
||
create_by VARCHAR(128) NULL,
|
||
update_by VARCHAR(128) NULL,
|
||
update_time TIMESTAMP NULL,
|
||
is_deleted BOOLEAN NOT NULL DEFAULT FALSE,
|
||
delete_time TIMESTAMP NULL,
|
||
delete_by VARCHAR(128) NULL
|
||
);
|
||
CREATE UNIQUE INDEX IF NOT EXISTS ux_warehouse_code ON warehouse (code) WHERE is_deleted = FALSE;
|
||
|
||
-- 库区(通过 warehouse_code 关联仓库)
|
||
CREATE TABLE IF NOT EXISTS zone (
|
||
id BIGINT NOT NULL PRIMARY KEY,
|
||
warehouse_code VARCHAR(64) NOT NULL,
|
||
code VARCHAR(64) NOT NULL,
|
||
name VARCHAR(256) NOT NULL,
|
||
attrs JSONB NULL,
|
||
create_time TIMESTAMP NULL,
|
||
create_by VARCHAR(128) NULL,
|
||
update_by VARCHAR(128) NULL,
|
||
update_time TIMESTAMP NULL,
|
||
is_deleted BOOLEAN NOT NULL DEFAULT FALSE,
|
||
delete_time TIMESTAMP NULL,
|
||
delete_by VARCHAR(128) NULL
|
||
);
|
||
CREATE UNIQUE INDEX IF NOT EXISTS ux_zone_wh_zone ON zone (warehouse_code, code) WHERE is_deleted = FALSE;
|
||
CREATE INDEX IF NOT EXISTS ix_zone_warehouse_code ON zone (warehouse_code) WHERE is_deleted = FALSE;
|
||
|
||
-- 储位(通过 warehouse_code + zone_code 关联层级;行列层/高度/状态/双伸位外侧/容器编码)
|
||
CREATE TABLE IF NOT EXISTS location (
|
||
id BIGINT NOT NULL PRIMARY KEY,
|
||
warehouse_code VARCHAR(64) NOT NULL,
|
||
zone_code VARCHAR(64) NOT NULL,
|
||
code VARCHAR(64) NOT NULL,
|
||
name VARCHAR(256) NULL,
|
||
row_no INTEGER NULL,
|
||
column_no INTEGER NULL,
|
||
layer_no INTEGER NULL,
|
||
height NUMERIC(18, 6) NULL,
|
||
location_status SMALLINT NOT NULL DEFAULT 1,
|
||
is_outer BOOLEAN NOT NULL DEFAULT FALSE,
|
||
container_code VARCHAR(64) NULL,
|
||
attrs JSONB NULL,
|
||
CONSTRAINT ck_location_status CHECK (location_status IN (1, 2, 3, 4, 5)),
|
||
create_time TIMESTAMP NULL,
|
||
create_by VARCHAR(128) NULL,
|
||
update_by VARCHAR(128) NULL,
|
||
update_time TIMESTAMP NULL,
|
||
is_deleted BOOLEAN NOT NULL DEFAULT FALSE,
|
||
delete_time TIMESTAMP NULL,
|
||
delete_by VARCHAR(128) NULL
|
||
);
|
||
CREATE UNIQUE INDEX IF NOT EXISTS ux_location_wh_zone_loc
|
||
ON location (warehouse_code, zone_code, code) WHERE is_deleted = FALSE;
|
||
CREATE INDEX IF NOT EXISTS ix_location_container_code ON location (container_code) WHERE is_deleted = FALSE AND container_code IS NOT NULL;
|
||
|
||
-- 物料主数据
|
||
CREATE TABLE IF NOT EXISTS material (
|
||
id BIGINT NOT NULL PRIMARY KEY,
|
||
code VARCHAR(64) NOT NULL,
|
||
name VARCHAR(256) NOT NULL,
|
||
spec VARCHAR(512) NULL,
|
||
unit VARCHAR(32) NOT NULL,
|
||
attrs JSONB NULL,
|
||
create_time TIMESTAMP NULL,
|
||
create_by VARCHAR(128) NULL,
|
||
update_by VARCHAR(128) NULL,
|
||
update_time TIMESTAMP NULL,
|
||
is_deleted BOOLEAN NOT NULL DEFAULT FALSE,
|
||
delete_time TIMESTAMP NULL,
|
||
delete_by VARCHAR(128) NULL
|
||
);
|
||
CREATE UNIQUE INDEX IF NOT EXISTS ux_material_code ON material (code) WHERE is_deleted = FALSE;
|
||
|
||
-- 容器主数据
|
||
CREATE TABLE IF NOT EXISTS container (
|
||
id BIGINT NOT NULL PRIMARY KEY,
|
||
code VARCHAR(64) NOT NULL,
|
||
name VARCHAR(256) NULL,
|
||
container_type VARCHAR(64) NULL,
|
||
attrs JSONB NULL,
|
||
create_time TIMESTAMP NULL,
|
||
create_by VARCHAR(128) NULL,
|
||
update_by VARCHAR(128) NULL,
|
||
update_time TIMESTAMP NULL,
|
||
is_deleted BOOLEAN NOT NULL DEFAULT FALSE,
|
||
delete_time TIMESTAMP NULL,
|
||
delete_by VARCHAR(128) NULL
|
||
);
|
||
CREATE UNIQUE INDEX IF NOT EXISTS ux_container_code ON container (code) WHERE is_deleted = FALSE;
|
||
|
||
-- 库存(含物料行与空容器行;通过 warehouse/zone/location code 与储位对齐)
|
||
CREATE TABLE IF NOT EXISTS inventory (
|
||
id BIGINT NOT NULL PRIMARY KEY,
|
||
inventory_kind SMALLINT NOT NULL DEFAULT 1,
|
||
material_code VARCHAR(64) NULL,
|
||
container_code VARCHAR(64) NULL,
|
||
warehouse_code VARCHAR(64) NOT NULL,
|
||
zone_code VARCHAR(64) NOT NULL,
|
||
location_code VARCHAR(64) NOT NULL,
|
||
qty NUMERIC(18, 6) NOT NULL,
|
||
unit VARCHAR(32) NOT NULL,
|
||
attrs JSONB NULL,
|
||
CONSTRAINT ck_inventory_kind CHECK (inventory_kind IN (1, 2)),
|
||
create_time TIMESTAMP NULL,
|
||
create_by VARCHAR(128) NULL,
|
||
update_by VARCHAR(128) NULL,
|
||
update_time TIMESTAMP NULL,
|
||
is_deleted BOOLEAN NOT NULL DEFAULT FALSE,
|
||
delete_time TIMESTAMP NULL,
|
||
delete_by VARCHAR(128) NULL
|
||
);
|
||
CREATE INDEX IF NOT EXISTS ix_inventory_wh_zone_loc ON inventory (warehouse_code, zone_code, location_code) WHERE is_deleted = FALSE;
|
||
CREATE INDEX IF NOT EXISTS ix_inventory_material ON inventory (material_code) WHERE is_deleted = FALSE AND material_code IS NOT NULL;
|
||
CREATE INDEX IF NOT EXISTS ix_inventory_container ON inventory (container_code) WHERE is_deleted = FALSE AND container_code IS NOT NULL;
|
||
|
||
-- HTTP 访问日志(TEXT 存请求/响应正文,不落库 SQL)
|
||
CREATE TABLE IF NOT EXISTS http_access_log (
|
||
id BIGINT NOT NULL PRIMARY KEY,
|
||
trace_id VARCHAR(64) NOT NULL,
|
||
http_method VARCHAR(16) NOT NULL,
|
||
path VARCHAR(512) NOT NULL,
|
||
query_string TEXT NULL,
|
||
status_code INTEGER NOT NULL,
|
||
request_body TEXT NULL,
|
||
response_body TEXT NULL,
|
||
duration_ms INTEGER NULL,
|
||
client_ip VARCHAR(64) NULL,
|
||
user_id VARCHAR(128) NULL,
|
||
create_time TIMESTAMP NULL
|
||
);
|
||
CREATE INDEX IF NOT EXISTS ix_http_access_log_create_time ON http_access_log (create_time DESC);
|
||
CREATE INDEX IF NOT EXISTS ix_http_access_log_trace_id ON http_access_log (trace_id);
|
||
|
||
-- 业务模板
|
||
CREATE TABLE IF NOT EXISTS sys_template (
|
||
id BIGINT NOT NULL PRIMARY KEY,
|
||
biz_type VARCHAR(64) NOT NULL,
|
||
biz_name VARCHAR(128) NOT NULL,
|
||
config_json JSONB NULL,
|
||
create_time TIMESTAMP NULL,
|
||
create_by VARCHAR(128) NULL,
|
||
update_by VARCHAR(128) NULL,
|
||
update_time TIMESTAMP NULL,
|
||
is_deleted BOOLEAN NOT NULL DEFAULT FALSE,
|
||
delete_time TIMESTAMP NULL,
|
||
delete_by VARCHAR(128) NULL
|
||
);
|