Design & Implement Project Membership Using Existing DB Architecture Docs

May 23, 2025 by ADMIN 78 views

Summary

Leverage the current database architecture documentation to understand the existing schema. Based on that context, propose and implement the minimal set of changes required to support multi-user project membership (roles: owner / admin / member). Update the docs once the migration is merged.

Introduction

In today's collaborative work environment, project membership is a crucial aspect of managing access and permissions. To support this feature, we need to design and implement a project membership system using the existing database architecture documentation. This article will guide you through the process of proposing and implementing the necessary changes to support multi-user project membership.

Tasks

1. Context Gathering

To begin, we need to gather context about the existing database architecture. This involves reading the /docs/architecture/database.md document and any ER diagrams to understand table names, conventions, and Row Level Security (RLS) patterns already in place.

2. Proposal

Once we have a good understanding of the existing architecture, we need to propose a concise schema that addresses the following:

Membership table(s) and primary keys
Role enumeration and ownership constraint
RLS policy outline (reuse existing patterns)

We should post this proposal in the issue thread and @mention reviewers (@backend-team) for approval.

3. Migration & Policies

After sign-off, we need to generate SQL migrations in /supabase/migrations/* and implement RLS policies that:

Permit project members CRUD (Create, Read, Update, Delete) as per their role
Deny non-members

4. Type & Code Updates

We need to regenerate TS types (npm run gen:types) and adjust any Zod schemas to reflect the changes.

5. Documentation

We should append a “Project Membership” section to docs/architecture/database.md, including:

New table diagrams
Example policy snippet
Ownership transfer rules

6. Validation

We need to seed sample data for local dev (scripts/seed_dev.sql) and add unit tests for membership look-ups (/tests/db-membership.test.ts). We should also confirm that non-members receive a 403 error.

Acceptance Checklist

To ensure that the project membership feature is implemented correctly, we need to check the following items:

Item	Pass When
Proposal comment approved by reviewers	✅
Migration applies with `supabase db reset`	✅
RLS prevents non-members from reading tasks	✅
Docs updated with new tables & policies	✅

Labels

This task is labeled with the following keywords:

database
design
RLS
docs

Estimate

The estimated time required to complete this task is 3 points.

Benefits

Implementing project membership using the existing database architecture documentation provides several benefits, including:

Improved collaboration and access control
Enhanced security and data protection
Simplified management of user roles and permissions

Conclusion

In conclusion, designing and implementing project membership using the existing database architecture documentation is a crucial step in supporting multi-user project collaboration. By following the tasks outlined in this article, we can ensure that the project membership feature is correctly and meets the requirements of our users.

Future Work

Future work may include:

Implementing additional features, such as user authentication and authorization
Enhancing the project membership system to support more complex access control scenarios
Integrating the project membership system with other tools and services

References

For further information on database architecture and RLS, please refer to the following resources:

Appendices

Appendix A: Example SQL Migration

-- Create membership table
CREATE TABLE membership (
  id SERIAL PRIMARY KEY,
  user_id INTEGER NOT NULL,
  project_id INTEGER NOT NULL,
  role VARCHAR(255) NOT NULL,
  CONSTRAINT fk_user_id FOREIGN KEY (user_id) REFERENCES users (id),
  CONSTRAINT fk_project_id FOREIGN KEY (project_id) REFERENCES projects (id)
);

-- Create RLS policy
CREATE POLICY membership_policy ON membership FOR SELECT, INSERT, UPDATE, DELETE
TO authenticated
USING (membership.role = 'owner' OR membership.role = 'admin');

Appendix B: Example TS Type Update

// Update membership type
export type Membership = {
  id: number;
  userId: number;
  projectId: number;
  role: string;
};

Appendix C: Example Zod Schema Update

// Update membership schema
import { z } from 'zod';

const membershipSchema = z.object({
  id: z.number(),
  userId: z.number(),
  projectId: z.number(),
  role: z.string(),
});
```<br/>
**Design & Implement Project Membership Using Existing DB Architecture Docs: Q&A**
================================================================================

### Introduction
In our previous article, we discussed the process of designing and implementing project membership using the existing database architecture documentation. In this article, we will address some frequently asked questions (FAQs) related to this topic.

### Q&A

#### Q: What is the purpose of project membership?
A: Project membership is a crucial aspect of managing access and permissions in a collaborative work environment. It allows users to join or leave projects, and assigns roles to users based on their permissions.

#### Q: What are the different roles in project membership?
A: The different roles in project membership are:

* Owner: Has full control over the project and its members.
* Admin: Has administrative privileges, but not full control over the project.
* Member: Has read-only access to the project and its members.

#### Q: How do I implement RLS policies for project membership?
A: To implement RLS policies for project membership, you need to create a policy that grants access to users based on their role. For example:
```sql
CREATE POLICY membership_policy ON membership FOR SELECT, INSERT, UPDATE, DELETE
TO authenticated
USING (membership.role = 'owner' OR membership.role = 'admin');

Q: How do I regenerate TS types and Zod schemas for project membership?

A: To regenerate TS types and Zod schemas for project membership, you need to update the membership type and schema to reflect the changes. For example:

// Update membership type
export type Membership = {
  id: number;
  userId: number;
  projectId: number;
  role: string;
};

// Update membership schema
import { z } from 'zod';

const membershipSchema = z.object({
  id: z.number(),
  userId: z.number(),
  projectId: z.number(),
  role: z.string(),
});

Q: How do I seed sample data for local dev and add unit tests for membership look-ups?

A: To seed sample data for local dev and add unit tests for membership look-ups, you need to create a script that inserts sample data into the database and writes unit tests for membership look-ups. For example:

-- Seed sample data for local dev
INSERT INTO membership (id, user_id, project_id, role)
VALUES (1, 1, 1, 'owner'),
       (2, 2, 1, 'admin'),
       (3, 3, 1, 'member');

-- Add unit tests for membership look-ups
import { Membership } from './membership';

describe('Membership', () => {
  it('should return membership data for owner', async () => {
    const membership = await Membership.findOne({ where: { role: 'owner' } });
    expect(membership).not.toBeNull();
  });

  it('should return membership data for admin', async () => {
    const membership = await Membership.findOne({ where: { role: 'admin' } });
    expect(membership).not.toBeNull();
  });

  it('should return membership data for member', async () => {
    const membership = await Membership.findOne({ where: { role: 'member' } });
    expect(membership).not.toBeNull();
  });
});

Q: How do I confirm that non receive a 403 error?

A: To confirm that non-members receive a 403 error, you need to test the membership look-ups for a non-member user. For example:

// Test membership look-ups for non-member user
import { Membership } from './membership';

describe('Membership', () => {
  it('should return 403 error for non-member user', async () => {
    const membership = await Membership.findOne({ where: { role: 'non-member' } });
    expect(membership).toBeNull();
  });
});