针对多组织层级内容权限场景的合适数据架构方案咨询

Hey there, let's walk through a robust data architecture tailored for your multi-organization content scenario. I’ve built similar enterprise SaaS systems before, so this approach balances security, scalability, and maintainability.

1. Core Data Models #

Start with these foundational tables to model content and organizational hierarchy:

Content Table #

Stores metadata for all public and private content:

CREATE TABLE content (
  content_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  content_type VARCHAR(20) NOT NULL CHECK (content_type IN ('public', 'private')),
  owner_org_id UUID REFERENCES organization(org_id) NULL, -- NULL for public content
  content_data JSONB NOT NULL, -- Use JSONB for flexible content structures
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

Organization Hierarchy Table #

Manages parent-child relationships between organizations and sub-organizations:

CREATE TABLE organization (
  org_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  parent_org_id UUID REFERENCES organization(org_id) NULL, -- NULL for top-level orgs
  org_name VARCHAR(100) NOT NULL,
  org_code VARCHAR(50) NOT NULL UNIQUE,
  org_path VARCHAR(255) NOT NULL, -- Precomputed path like "/O1/SO1/SO1-1" for fast hierarchy checks
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

2. Access Control Logic #

Enforce access rules clearly to avoid unauthorized content exposure:

• Public Content: All organizations/sub-organizations can access this. Query filter: content_type = 'public'
• Private Content: Only the owning organization and its sub-organizations have access. Use one of these two efficient methods:
  1. Recursive Hierarchy Query (dynamic, great for frequent hierarchy changes):

     WITH RECURSIVE org_hierarchy AS (
       -- Start with the user's current organization
       SELECT org_id FROM organization WHERE org_id = :current_user_org_id
       UNION ALL
       -- Add all parent organizations (if you need to allow parents to access child content too)
       SELECT o.parent_org_id FROM organization o
       JOIN org_hierarchy oh ON o.org_id = oh.org_id
       WHERE o.parent_org_id IS NOT NULL
     )
     SELECT c.* FROM content c
     WHERE c.content_type = 'public'
        OR (c.content_type = 'private' AND c.owner_org_id IN (SELECT org_id FROM org_hierarchy));
     

  2. Precomputed Org Path (faster queries, ideal for stable hierarchies):
     Use the org_path field to check if the user's organization path starts with the owning organization's path. For example, if O1's path is /O1, any sub-org path like /O1/SO1 will match:

     SELECT c.* FROM content c
     JOIN organization o_owner ON c.owner_org_id = o_owner.org_id
     JOIN organization o_current ON o_current.org_id = :current_user_org_id
     WHERE c.content_type = 'public'
        OR (c.content_type = 'private' AND o_current.org_path LIKE CONCAT(o_owner.org_path, '%'));
     

3. Data Storage Strategy #

• Structured Metadata: Use a relational database like PostgreSQL (great for JSONB and recursive queries) or MySQL to store content metadata and organizational data.
• Unstructured Content: For files (docs, images, videos), store them in an object storage service (e.g., MinIO, AWS S3) and save the file path/URL in the content_data field. Add the owning organization ID to the file path (e.g., /O1/private_docs/report.pdf) to simplify access control at the storage layer.

4. Performance Optimization Tips #

• Indexing: Add indexes on content_type, owner_org_id, and org_path to speed up access control queries. For PostgreSQL, a GIN index on content_data can help if you need to query content attributes.
• Caching: Cache public content and frequently accessed private content in Redis to reduce database load. You can also cache the hierarchy of each organization to avoid repeated recursive queries.
• Row-Level Security (RLS): If using PostgreSQL, enable RLS on the content table to automatically enforce access rules at the database level, so your application doesn't have to handle filtering logic everywhere.

5. Future-Proof Extensions #

• Granular Permissions: If you need to restrict certain private content to specific sub-orgs, add a content_permissions table that maps content_id to allowed org_ids.
• Version Control: Add a content_version table to track changes to content, allowing rollbacks and audit trails.
• Multi-Tenant Isolation: For large-scale deployments, consider sharding databases by organization ID to isolate tenant data further.

内容的提问来源于stack exchange，提问作者andthereitgoes