Explore

CoGov Schema Tables (Supabase SQL)

Supabase SQL schema tables

Run in Supabase SQL Editor. (Assumes Postgres.)

Design goals

Immutable version history (no destructive edits to ratified configs)

Schema-first storage (JSONB + validator + normalized mirrors)

Instance-scoped querying (roles, decision types, treasury accounts per instance/version)

Separation between Instance identity and Versioned configuration

-- Enable UUID extension

create extension if not exists "uuid-ossp";

-- -----------------------------

-- CORE: Instances + Versioning

-- -----------------------------

create table governance_instances (

id uuid primary key default uuid_generate_v4(),

slug text unique not null, -- stable identifier e.g. "holodao-gaianheart"

name text not null,

instance_profile text not null check (instance_profile in ('organizational','commons','subdao','custom')),

mandate_type text not null default 'general' check (mandate_type in ('general','project','initiative','program','council','regional','custom')),

parent_instance_id uuid references governance_instances(id) on delete set null,

status text not null default 'active' check (status in ('draft','active','paused','archived')),

created_at timestamptz not null default now(),

updated_at timestamptz not null default now()

);

create table governance_versions (

id uuid primary key default uuid_generate_v4(),

instance_id uuid not null references governance_instances(id) on delete cascade,

version_number int not null,

created_by uuid, -- map to auth.users if you want FK

change_summary text not null,

change_diff jsonb, -- optional: structured diff

config_snapshot jsonb, -- optional: JSON export of config at this version

created_at timestamptz not null default now(),

unique(instance_id, version_number)

);

-- Current version pointer (fast reads)

create table governance_instance_state (

instance_id uuid primary key references governance_instances(id) on delete cascade,

current_version_id uuid not null references governance_versions(id) on delete restrict,

activated_at timestamptz,

updated_at timestamptz not null default now()

);

-- -----------------------------------

-- MODULES: Definitions + Config per Version

-- -----------------------------------

create table governance_modules (

id uuid primary key default uuid_generate_v4(),

module_key text unique not null, -- e.g. "foundational_pattern", "constitutional_core"

module_name text not null,

description text

);

-- each version can have module-specific config blocks

create table governance_module_configs (

id uuid primary key default uuid_generate_v4(),

version_id uuid not null references governance_versions(id) on delete cascade,

module_id uuid not null references governance_modules(id) on delete restrict,

config jsonb not null default '{}'::jsonb,

unique(version_id, module_id)

);

-- -----------------------------

-- GOVERNANCE: Roles + Councils

-- -----------------------------

create table role_definitions (

id uuid primary key default uuid_generate_v4(),

version_id uuid not null references governance_versions(id) on delete cascade,

role_key text not null, -- stable within instance (e.g. "treasury_steward")

role_name text not null,

mandate text,

authority_scope jsonb not null default '{}'::jsonb, -- boundaries, domains, limits

permissions jsonb not null default '{}'::jsonb, -- RBAC permissions for TAO actions

term_rules jsonb not null default '{}'::jsonb, -- duration, renewal, removal rules

reporting_rules jsonb not null default '{}'::jsonb, -- cadence, required reports

created_at timestamptz not null default now(),

unique(version_id, role_key)

);

create table council_definitions (

id uuid primary key default uuid_generate_v4(),

version_id uuid not null references governance_versions(id) on delete cascade,

council_key text not null, -- "treasury_council"

council_name text not null,

mandate text,

composition_rules jsonb not null default '{}'::jsonb, -- seats, eligibility, selection method

decision अधिकार jsonb not null default '{}'::jsonb, -- decision scopes + thresholds if needed

created_at timestamptz not null default now(),

unique(version_id, council_key)

);

-- Members: keep generic. Later you can map to auth.users, wallets, DIDs, etc.

create table participants (

id uuid primary key default uuid_generate_v4(),

instance_id uuid not null references governance_instances(id) on delete cascade,

display_name text not null,

identity_type text not null default 'user' check (identity_type in ('user','wallet','did','org','service')),

identity_ref text, -- e.g. auth uid, wallet address, did string

status text not null default 'active' check (status in ('invited','active','suspended','removed')),

metadata jsonb not null default '{}'::jsonb,

created_at timestamptz not null default now(),

unique(instance_id, identity_type, identity_ref)

);

create table role_assignments (

id uuid primary key default uuid_generate_v4(),

instance_id uuid not null references governance_instances(id) on delete cascade,

role_definition_id uuid not null references role_definitions(id) on delete restrict,

participant_id uuid not null references participants(id) on delete cascade,

assigned_by uuid references participants(id) on delete set null,

status text not null default 'active' check (status in ('pending','active','revoked','expired')),

start_at timestamptz not null default now(),

end_at timestamptz,

assignment_notes text,

created_at timestamptz not null default now()

);

create table council_memberships (

id uuid primary key default uuid_generate_v4(),

instance_id uuid not null references governance_instances(id) on delete cascade,

council_definition_id uuid not null references council_definitions(id) on delete restrict,

participant_id uuid not null references participants(id) on delete cascade,

role_in_council text not null default 'member' check (role_in_council in ('member','chair','secretary','observer')),

status text not null default 'active' check (status in ('active','revoked','expired')),

start_at timestamptz not null default now(),

end_at timestamptz,

created_at timestamptz not null default now()

);

-- -----------------------------

-- DECISIONS: Types + Proposal State Machine

-- -----------------------------

create table decision_types (

id uuid primary key default uuid_generate_v4(),

version_id uuid not null references governance_versions(id) on delete cascade,

decision_key text not null, -- "operational", "strategic", etc.

name text not null,

resolution_method text not null check (resolution_method in ('vote','consent','delegated','hybrid')),

quorum_rules jsonb not null default '{}'::jsonb, -- min participation, who counts, etc.

approval_rules jsonb not null default '{}'::jsonb, -- majority/supermajority/consent rules

timing_rules jsonb not null default '{}'::jsonb, -- deliberation/voting windows

created_at timestamptz not null default now(),

unique(version_id, decision_key)

);

create table proposal_state_machine (

id uuid primary key default uuid_generate_v4(),

version_id uuid not null references governance_versions(id) on delete cascade,

states jsonb not null, -- e.g. ["draft","submitted","deliberation","voting","closed","executed"]

transitions jsonb not null, -- rules for state changes + who can do them

created_at timestamptz not null default now(),

unique(version_id)

);

-- -----------------------------

-- EXECUTION: Proposals + Votes + Actions

-- -----------------------------

create table proposals (

id uuid primary key default uuid_generate_v4(),

instance_id uuid not null references governance_instances(id) on delete cascade,

version_id uuid not null references governance_versions(id) on delete restrict, -- rules in effect

decision_type_id uuid not null references decision_types(id) on delete restrict,

title text not null,

description text,

state text not null default 'draft',

created_by uuid references participants(id) on delete set null,

metadata jsonb not null default '{}'::jsonb, -- tags, links, affected modules, etc.

submitted_at timestamptz,

voting_start_at timestamptz,

voting_end_at timestamptz,

closed_at timestamptz,

created_at timestamptz not null default now()

);

create table votes (

id uuid primary key default uuid_generate_v4(),

proposal_id uuid not null references proposals(id) on delete cascade,

voter_id uuid not null references participants(id) on delete cascade,

choice text not null check (choice in ('yes','no','abstain')),

weight numeric not null default 1, -- supports weighted models if enabled

rationale text,

created_at timestamptz not null default now(),

unique(proposal_id, voter_id)

);

-- "Execution actions" are the authoritative log of what governance triggered (treasury, role change, policy update)

create table execution_actions (

id uuid primary key default uuid_generate_v4(),

instance_id uuid not null references governance_instances(id) on delete cascade,

proposal_id uuid references proposals(id) on delete set null,

Want to print your doc?
This is not the way.

Try clicking the ··· in the right corner or using a keyboard shortcut (

CtrlP

) instead.