6-level hierarchical groups: the most complex feature

Key points: Building a hierarchical group system with 6 levels of depth, 4 roles, recursive PostgreSQL queries (CTEs), and 31 RLS policies is TAMSIV's most complex feature. This article covers the data model, permission inheritance, the HierarchicalGroupPicker frontend component, and lessons learned.

Some features seem simple on paper. "Add groups" — sounds innocent. Like adding a shopping cart to an e-commerce app. Then you start digging and realize you've just opened Pandora's Box.

TAMSIV supports hierarchical groups up to 6 levels deep. My diving club was the perfect use case: Club → Technical Committee → Level 1 → Tuesday Group. A family uses: Family → Home → Kitchen / Garden / Garage. An SME: Company → Department → Team → Project.

This is the feature that took me the longest. It's also what makes TAMSIV usable for real organizations, not just for solo use.

How to model hierarchical groups in PostgreSQL?

The data model is based on a simple concept: each group has a parent_id that points to its parent group. A root group has parent_id = NULL.

-- Simplified table
CREATE TABLE collaborative.groups (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name TEXT NOT NULL,
  parent_id UUID REFERENCES collaborative.groups(id),
  created_by UUID REFERENCES auth.users(id),
  depth INTEGER DEFAULT 0,
  created_at TIMESTAMPTZ DEFAULT now()
);

To display the complete tree of a group, I use recursive queries (Common Table Expressions, or CTEs) in PostgreSQL:

WITH RECURSIVE group_tree AS (
  -- Base: the root group
  SELECT id, name, parent_id, 0 AS depth
  FROM collaborative.groups
  WHERE id = p_group_id

  UNION ALL

  -- Recursion: subgroups
  SELECT g.id, g.name, g.parent_id, gt.depth + 1
  FROM collaborative.groups g
  JOIN group_tree gt ON g.parent_id = gt.id
  WHERE gt.depth < 6  -- Safeguard
)
SELECT * FROM group_tree;

The WHERE depth < 6 clause is a critical safeguard. Without it, a data error (a reference cycle, for example) would cause the query to loop indefinitely. Six levels cover all real-world use cases I've encountered.

This data model lives in the collaborative schema, separate from the rest — I explain why in the article on database restructuring.

What are the four roles and how do they work?

Each group member has a role that determines their permissions:

• Admin — Do everything: create, modify, delete, manage members, invite, modify group settings. This is the group creator by default.
• Manager — Manage content: create/modify/delete tasks and memos, assign members, validate checklists. But not manage members or settings.
• Member — Contribute: create content, view all group content, validate their own checklist items.
• Viewer — Read-only: view content, nothing else. Useful for observers or stakeholders who want to follow progress without intervening.

The trap of permission inheritance

If you are an Admin of the "Diving Club" group, are you automatically an Admin of "Technical Committee" (a subgroup)? In TAMSIV, the answer is yes — but with nuances.

Inheritance works downwards: an Admin of a parent group has the same rights in all subgroups. But an Admin of a subgroup has no rights in the parent group. This asymmetry is natural (the director sees everything, the team leader sees their team), but it adds complexity to SQL queries.

To check permissions, each query must traverse the hierarchical tree until it finds a role or reaches the root:

-- Does the user have a role in this group or a parent?
WITH RECURSIVE parent_chain AS (
  SELECT id, parent_id FROM collaborative.groups WHERE id = p_group_id
  UNION ALL
  SELECT g.id, g.parent_id
  FROM collaborative.groups g
  JOIN parent_chain pc ON g.id = pc.parent_id
)
SELECT role FROM collaborative.group_members
WHERE group_id IN (SELECT id FROM parent_chain)
AND user_id = auth.uid()
ORDER BY role ASC  -- Admin > Manager > Member > Viewer
LIMIT 1;

How to write and test 31 RLS policies?

This is the painful number: 31 Row Level Security policies for the collaborative schema. Supabase uses RLS to secure data access — each table has rules that determine who can read, write, modify, delete.

Here are concrete examples:

Simple read policy

-- A member can view tasks of their groups
CREATE POLICY "members_read_tasks" ON collaborative.group_tasks
  FOR SELECT USING (
    EXISTS (
      SELECT 1 FROM collaborative.group_members gm
      WHERE gm.group_id = group_tasks.group_id
      AND gm.user_id = auth.uid()
    )
  );

Policy with hierarchical inheritance

-- An admin can delete tasks from their groups AND subgroups
CREATE POLICY "admin_delete_tasks" ON collaborative.group_tasks
  FOR DELETE USING (
    EXISTS (
      WITH RECURSIVE parent_chain AS (
        SELECT id, parent_id FROM collaborative.groups
        WHERE id = group_tasks.group_id
        UNION ALL
        SELECT g.id, g.parent_id
        FROM collaborative.groups g
        JOIN parent_chain pc ON g.id = pc.parent_id
      )
      SELECT 1 FROM collaborative.group_members gm
      WHERE gm.group_id IN (SELECT id FROM parent_chain)
      AND gm.user_id = auth.uid()
      AND gm.role = 'admin'
    )
  );

Testing 31 policies means at least 31 test scenarios. In reality, it's much more, because each policy must be tested positively (access is granted) AND negatively (access is denied). Methodical, tedious, indispensable. Details on the testing approach are in the article on security audit.

How to build the HierarchicalGroupPicker component?

On the frontend, displaying a group tree with indentation, role icons, and interactions is a UI challenge in itself.

The HierarchicalGroupPicker is a React Native component that:

• Displays the tree with indentation proportional to depth (using spacing() from the scaling system).
• Indicates the user's role in each group via a colored icon.
• Allows selection with an "include subgroups" toggle — when you select a parent group, subgroups can be automatically included.
• Supports fold/unfold: subgroups are collapsible to avoid cluttering the screen on deep hierarchies.

This component is reused everywhere: in the agenda filter, in task creation, in the group management screen. It's a huge time investment initially, but a massive time saver afterward.

The FilterBar pattern

The HierarchicalGroupPicker works with the FilterBar, a filtering component with 3 contexts (Private / Shared / All) and modes (all tasks / created by me / assigned to me). The combination of the two allows for very precise queries: "show me tasks assigned to me in the Family group and its subgroups".

What are the real-world use cases for hierarchical groups?

Here are the use cases my testers actively use:

• Family: Family → Home (household chores) / Vacations (planning) / School (children's homework). I talk about this in the article on family organization.
• Sports club: Club → Committees → Levels → Groups by slot.
• Small team: Company → Department → Project → Sprint.
• Association: Association → Hub → Event.

The 6-level limit is rarely reached. In practice, 3-4 levels cover 95% of needs. But this maximum depth is a safety net — better to have it and not need it.

What lessons can be learned from this implementation?

1. Recursive PostgreSQL queries are powerful but costly: Each query that traverses the hierarchy performs N joins (where N = depth). An index on parent_id is essential.
2. RLS policies accumulate quickly: Each table × each operation (SELECT, INSERT, UPDATE, DELETE) × each role = many policies. Document them in a dedicated file.
3. Permission inheritance is the trickiest part: Decide early whether permissions propagate downwards, upwards, or both. Changing this decision later is extremely costly.
4. The frontend component is an investment: A good HierarchicalGroupPicker takes a week to build correctly, but is reusable everywhere.
5. Test with real data: A tree of 3 groups in dev doesn't reveal the same bugs as a tree of 15 groups across 4 levels in production.

If you're building a collaborative system, hierarchy is what transforms a simple "list of groups" into a tool that organizations truly adopt. It's hard, it's long, but it's what makes the difference from competitors who limit themselves to flat groups.

FAQ

Why limit to 6 levels of depth?

Six levels cover all real-world use cases I've encountered. Beyond that, the tree becomes unmanageable for the user. The limit also protects against overly deep recursive queries that could impact performance. In practice, 3-4 levels are sufficient for 95% of organizations.

Do recursive queries pose performance issues?

With an index on parent_id and a depth limit, performance is excellent even with hundreds of groups. A 6-level recursive query takes less than 10ms on Supabase PostgreSQL. The real risk is a reference cycle (group A parent of B, B parent of A) — the WHERE depth < 6 clause protects against this.

How to manage inviting new members?

The admin of a group can invite by email or by sharing link. The invitee joins the group with the role chosen by the admin. If the group has subgroups, the invitee only has access to the group they were invited to — unless a parent admin explicitly grants them access.

Do group checklists work differently from personal checklists?

Yes. Group checklists have two validation modes: "single" (one person validates for everyone) and "everyone" (each member must validate individually). The "everyone" mode is perfect for family grocery lists where no one buys the same thing.

Can a group be moved to another parent group?

Yes, an admin can reorganize the hierarchy by changing a group's parent_id. But this is a sensitive operation: inherited permissions change immediately, and members of the new parent see the content of the moved group. An explicit confirmation message warns the admin of the consequences.