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
71 lines
2.7 KiB
SQL
71 lines
2.7 KiB
SQL
-- CRM tables in PostgreSQL (mirrors MySQL crm_* schema for CrmRepo compatibility)
|
|
|
|
CREATE TABLE IF NOT EXISTS crm_tenant (
|
|
id BIGSERIAL PRIMARY KEY,
|
|
tenant_id VARCHAR(32) NOT NULL UNIQUE,
|
|
tenant_name VARCHAR(100) NOT NULL,
|
|
tenant_code VARCHAR(64) NOT NULL DEFAULT '' UNIQUE,
|
|
status SMALLINT NOT NULL DEFAULT 1,
|
|
created_at TIMESTAMP NOT NULL DEFAULT NOW(),
|
|
updated_at TIMESTAMP NOT NULL DEFAULT NOW()
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS crm_tenant_user (
|
|
id BIGSERIAL PRIMARY KEY,
|
|
tenant_user_id VARCHAR(32) NOT NULL UNIQUE,
|
|
tenant_id VARCHAR(32) NOT NULL,
|
|
user_id VARCHAR(32) NOT NULL,
|
|
is_primary SMALLINT NOT NULL DEFAULT 1,
|
|
status SMALLINT NOT NULL DEFAULT 1,
|
|
created_at TIMESTAMP NOT NULL DEFAULT NOW(),
|
|
updated_at TIMESTAMP NOT NULL DEFAULT NOW(),
|
|
UNIQUE (tenant_id, user_id)
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_crm_tenant_user_uid ON crm_tenant_user(user_id);
|
|
|
|
CREATE TABLE IF NOT EXISTS crm_tenant_relationship (
|
|
id BIGSERIAL PRIMARY KEY,
|
|
relationship_id VARCHAR(40) NOT NULL UNIQUE,
|
|
owner_tenant_id VARCHAR(32) NOT NULL,
|
|
from_tenant_id VARCHAR(32) NOT NULL,
|
|
to_tenant_id VARCHAR(32) NOT NULL,
|
|
relation_type VARCHAR(32) NOT NULL,
|
|
status SMALLINT NOT NULL DEFAULT 1,
|
|
valid_from TIMESTAMP NOT NULL DEFAULT NOW(),
|
|
valid_to TIMESTAMP NULL,
|
|
source VARCHAR(32) NOT NULL DEFAULT 'manual',
|
|
created_by VARCHAR(32) NOT NULL DEFAULT '',
|
|
created_at TIMESTAMP NOT NULL DEFAULT NOW(),
|
|
updated_at TIMESTAMP NOT NULL DEFAULT NOW(),
|
|
CHECK (from_tenant_id <> to_tenant_id)
|
|
);
|
|
|
|
CREATE UNIQUE INDEX IF NOT EXISTS uk_crm_rel_unique
|
|
ON crm_tenant_relationship(owner_tenant_id, from_tenant_id, to_tenant_id, relation_type, valid_from);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_crm_rel_from ON crm_tenant_relationship(owner_tenant_id, from_tenant_id, status);
|
|
CREATE INDEX IF NOT EXISTS idx_crm_rel_to ON crm_tenant_relationship(owner_tenant_id, to_tenant_id, status);
|
|
|
|
CREATE TABLE IF NOT EXISTS crm_tenant_relationship_history (
|
|
id BIGSERIAL PRIMARY KEY,
|
|
history_id VARCHAR(40) NOT NULL UNIQUE,
|
|
relationship_id VARCHAR(40) NOT NULL,
|
|
change_type VARCHAR(32) NOT NULL,
|
|
before_json JSONB NULL,
|
|
after_json JSONB NULL,
|
|
changed_by VARCHAR(32) NOT NULL DEFAULT '',
|
|
changed_at TIMESTAMP NOT NULL DEFAULT NOW()
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_crm_rel_hist ON crm_tenant_relationship_history(relationship_id, changed_at DESC);
|
|
|
|
-- Seed
|
|
INSERT INTO crm_tenant (tenant_id, tenant_name, tenant_code, status)
|
|
VALUES ('t_default_001', '默认租户', 'DEFAULT', 1)
|
|
ON CONFLICT (tenant_id) DO NOTHING;
|
|
|
|
INSERT INTO crm_tenant_user (tenant_user_id, tenant_id, user_id, is_primary, status)
|
|
VALUES ('tu_admin_001', 't_default_001', 'u_admin_001', 1, 1)
|
|
ON CONFLICT (tenant_id, user_id) DO NOTHING;
|