1f4ccfb54e
- JWT claims extended with tenantId; login enforces strict tenant verification - AuthorityMiddleware: tenant scope check + Casbin path permission + anti-spoofing - CRM relation API (upstream/downstream one-hop, create/update/history, full graph) - CrmRepo backed by PostgreSQL with $N placeholders - gRPC tenant propagation via UnaryClientInterceptor (x-tenant-id metadata) - All legacy tables (12) gain tenant_id column with indexes - All model queries inject WHERE tenant_id filter - Casbin gorm-adapter downgraded to v3.28.0 for v2 compatibility - GraphSyncWorker (Kafka -> Neo4j) with idempotent MERGE - Full graph API restricted to admin role only - Database migrations for MySQL (CRM tables + tenant columns) and PostgreSQL (CRM init) - Docker Compose: added postgres service to main stack, graph stack with Kafka/Debezium/Neo4j Made-with: Cursor
106 lines
3.8 KiB
SQL
106 lines
3.8 KiB
SQL
-- Muyu CRM Graph Schema (PostgreSQL)
|
|
-- Long-term architecture baseline:
|
|
-- PostgreSQL (source of truth) -> CDC/Kafka -> Neo4j (graph analytics)
|
|
|
|
CREATE SCHEMA IF NOT EXISTS crm;
|
|
SET search_path TO crm, public;
|
|
|
|
-- ==================== Tenant and Identity ====================
|
|
|
|
CREATE TABLE IF NOT EXISTS tenant (
|
|
id BIGSERIAL PRIMARY KEY,
|
|
tenant_code VARCHAR(64) NOT NULL UNIQUE,
|
|
tenant_name VARCHAR(128) NOT NULL,
|
|
status SMALLINT NOT NULL DEFAULT 1,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS tenant_user (
|
|
id BIGSERIAL PRIMARY KEY,
|
|
tenant_id BIGINT NOT NULL REFERENCES tenant(id),
|
|
user_id VARCHAR(64) NOT NULL,
|
|
role_key VARCHAR(64) NOT NULL DEFAULT 'user',
|
|
is_primary BOOLEAN NOT NULL DEFAULT TRUE,
|
|
status SMALLINT NOT NULL DEFAULT 1,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
UNIQUE (tenant_id, user_id)
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_tenant_user_user_id ON tenant_user(user_id);
|
|
|
|
-- ==================== Graph Nodes and Edges ====================
|
|
|
|
CREATE TABLE IF NOT EXISTS relationship_type (
|
|
id BIGSERIAL PRIMARY KEY,
|
|
type_code VARCHAR(32) NOT NULL UNIQUE,
|
|
type_name VARCHAR(64) NOT NULL,
|
|
description TEXT NOT NULL DEFAULT '',
|
|
status SMALLINT NOT NULL DEFAULT 1,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS tenant_relationship (
|
|
id BIGSERIAL PRIMARY KEY,
|
|
owner_tenant_id BIGINT NOT NULL REFERENCES tenant(id),
|
|
from_tenant_id BIGINT NOT NULL REFERENCES tenant(id),
|
|
to_tenant_id BIGINT NOT NULL REFERENCES tenant(id),
|
|
relationship_type_id BIGINT NOT NULL REFERENCES relationship_type(id),
|
|
status SMALLINT NOT NULL DEFAULT 1,
|
|
valid_from TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
valid_to TIMESTAMPTZ NULL,
|
|
source VARCHAR(32) NOT NULL DEFAULT 'manual',
|
|
created_by VARCHAR(64) NOT NULL DEFAULT 'system',
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
CHECK (from_tenant_id <> to_tenant_id),
|
|
CHECK (valid_to IS NULL OR valid_to > valid_from)
|
|
);
|
|
|
|
CREATE UNIQUE INDEX IF NOT EXISTS uk_tenant_relationship_version
|
|
ON tenant_relationship(owner_tenant_id, from_tenant_id, to_tenant_id, relationship_type_id, valid_from);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_tenant_relationship_from
|
|
ON tenant_relationship(owner_tenant_id, from_tenant_id, status);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_tenant_relationship_to
|
|
ON tenant_relationship(owner_tenant_id, to_tenant_id, status);
|
|
|
|
CREATE TABLE IF NOT EXISTS tenant_relationship_history (
|
|
id BIGSERIAL PRIMARY KEY,
|
|
relationship_id BIGINT NOT NULL REFERENCES tenant_relationship(id),
|
|
change_type VARCHAR(32) NOT NULL,
|
|
before_json JSONB,
|
|
after_json JSONB,
|
|
changed_by VARCHAR(64) NOT NULL DEFAULT 'system',
|
|
changed_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_tenant_relationship_history_rel
|
|
ON tenant_relationship_history(relationship_id, changed_at DESC);
|
|
|
|
-- ==================== Seed Data ====================
|
|
|
|
INSERT INTO relationship_type(type_code, type_name, description)
|
|
VALUES
|
|
('SUPPLY', '供货关系', 'from_tenant 作为 to_tenant 的上游'),
|
|
('PURCHASE', '采购关系', 'from_tenant 向 to_tenant 发起采购'),
|
|
('OEM', '代工关系', '委托代工上下游关系')
|
|
ON CONFLICT (type_code) DO NOTHING;
|
|
|
|
-- ==================== One-hop Query Examples ====================
|
|
-- Upstream (who supplies current tenant):
|
|
-- SELECT r.from_tenant_id
|
|
-- FROM crm.tenant_relationship r
|
|
-- WHERE r.owner_tenant_id = $1
|
|
-- AND r.to_tenant_id = $2
|
|
-- AND r.status = 1;
|
|
--
|
|
-- Downstream (who current tenant supplies):
|
|
-- SELECT r.to_tenant_id
|
|
-- FROM crm.tenant_relationship r
|
|
-- WHERE r.owner_tenant_id = $1
|
|
-- AND r.from_tenant_id = $2
|
|
-- AND r.status = 1;
|