zhougf fa1b286463 Add WmsLite monorepo with JWT auth, tenant context, and project READMEs.
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>
2026-05-24 14:17:47 +08:00

159 lines
6.0 KiB
SQL
Raw Permalink Blame History

This file contains ambiguous Unicode characters

This file contains Unicode characters that might be confused with other characters. If you think that this is intentional, you can safely ignore this warning. Use the Escape button to reveal them.

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