APIs for GovAction in Dbsync

[mesudip]

/api/proposals?include-vote-count=false/true (default false)
/api/proposals/${id}?include-vote-count=false/true (default false)
/api/propopsals/${id}/vote-count
/api/proposals/${id}/votes

in response include a field

created_at:{
date:
block:
block_no:
epoch:
block_slot:
tx:
proposal_index:
}

Indexer Service:

Add a module govActionIndexer. It will listen to all the proposals.
Add a module govActionVotes. It will listen and index all govActionVotes.

Reference query

WITH LatestDRepDistr AS (
    SELECT
        *,
        ROW_NUMBER() OVER (PARTITION BY hash_id ORDER BY epoch_no DESC) AS rn
    FROM
        drep_distr
),
CurrentEpoch AS (
    SELECT MAX(no) AS epoch_no FROM epoch
),
DRepActivity AS (
    SELECT
        drep_activity AS activity_period,
        epoch_no AS latest_epoch_no
    FROM
        epoch_param
    WHERE
        epoch_no IS NOT NULL
    ORDER BY
        epoch_no DESC
    LIMIT 1
),
LatestVotingProcedure AS (
    SELECT
        vp.*,
        ROW_NUMBER() OVER (PARTITION BY drep_voter ORDER BY tx_id DESC) AS rn
    FROM
        voting_procedure vp
),
DRepVotingPower AS (
    SELECT
        SUM(CASE WHEN drep_hash.view = 'drep_always_no_confidence' THEN amount ELSE 0 END) AS auto_no_confidence_stake,
        SUM(CASE WHEN drep_hash.view = 'drep_always_abstain' THEN amount ELSE 0 END) AS auto_abstain_stake
    FROM
        drep_hash
    LEFT JOIN drep_distr ON drep_hash.id = drep_distr.hash_id 
        AND drep_distr.epoch_no = (SELECT epoch_no FROM CurrentEpoch)
    WHERE drep_hash.view IN ('drep_always_no_confidence', 'drep_always_abstain')
),
RankedDRepRegistration AS (
    SELECT
        dr.id,
        dr.drep_hash_id,
        dr.deposit,
        dr.voting_anchor_id,
        ROW_NUMBER() OVER (PARTITION BY dr.drep_hash_id ORDER BY dr.tx_id DESC) AS rn,
        encode(tx.hash, 'hex') AS tx_hash,
        block.epoch_no
    FROM
        drep_registration dr
    JOIN tx ON tx.id = dr.tx_id
    JOIN block ON block.id = tx.block_id
),
LatestVoteEpoch AS (
    SELECT
        block.epoch_no,
        lvp.drep_voter AS drep_id
    FROM
        LatestVotingProcedure lvp
        JOIN tx ON tx.id = lvp.tx_id
        JOIN block ON block.id = tx.block_id
    WHERE
        lvp.rn = 1
),
ActiveDRepStake AS (
    SELECT
        COALESCE(SUM(ldd.amount), 0) AS total_active_drep_stake
    FROM
        drep_hash dh
    LEFT JOIN LatestDRepDistr ldd ON ldd.hash_id = dh.id AND ldd.rn = 1
    LEFT JOIN RankedDRepRegistration rd ON ldd.hash_id = rd.drep_hash_id AND rd.rn = 1
    LEFT JOIN LatestVoteEpoch lve ON lve.drep_id = dh.id
    CROSS JOIN DRepActivity
    WHERE
        ldd.epoch_no = (SELECT epoch_no FROM CurrentEpoch)
        AND COALESCE(rd.deposit, 0) >= 0
        AND ((DRepActivity.latest_epoch_no - GREATEST(COALESCE(lve.epoch_no, 0), COALESCE(rd.epoch_no, 0))) <= DRepActivity.activity_period)
),
TotalActiveStake AS (
    SELECT ActiveDRepStake.total_active_drep_stake + DRepVotingPower.auto_no_confidence_stake AS total_stake 
    FROM ActiveDRepStake, DRepVotingPower
),
RankedDRepVotes AS (
    SELECT
        *,
        ROW_NUMBER() OVER (PARTITION BY vp.drep_voter ORDER BY vp.tx_id DESC) AS rn
    FROM
        voting_procedure vp
),
GovernanceActionVotes AS (
    SELECT
        gov_action_proposal.id,
        gov_action_proposal.type,
        COALESCE(SUM(ldd_drep.amount) FILTER (WHERE rdv.vote::text = 'Yes'), 0) AS yes_votes,
        COALESCE(SUM(ldd_drep.amount) FILTER (WHERE rdv.vote::text = 'No'), 0) AS no_votes,
        COALESCE(SUM(ldd_drep.amount) FILTER (WHERE rdv.vote::text = 'Abstain'), 0) AS abstain_votes
    FROM
        gov_action_proposal
    LEFT JOIN RankedDRepVotes rdv ON rdv.gov_action_proposal_id = gov_action_proposal.id AND rdv.rn = 1
    LEFT JOIN RankedDRepRegistration rdr ON rdr.drep_hash_id = rdv.drep_voter AND COALESCE(rdr.deposit, 0) >= 0 AND rdr.rn = 1
    LEFT JOIN LatestDRepDistr ldd_drep ON ldd_drep.hash_id = rdr.drep_hash_id
        AND ldd_drep.epoch_no = (SELECT epoch_no FROM CurrentEpoch)
    GROUP BY gov_action_proposal.id, gov_action_proposal.type
)
SELECT
    gov_action_proposal.id,
    encode(creator_tx.hash, 'hex') AS tx_hash,
    gov_action_proposal.index,
    gov_action_proposal.type::text,
    GovernanceActionVotes.abstain_votes + DRepVotingPower.auto_abstain_stake AS abstain_total,
    CASE
        WHEN gov_action_proposal.type != 'NoConfidence' THEN GovernanceActionVotes.yes_votes
        ELSE GovernanceActionVotes.yes_votes + DRepVotingPower.auto_no_confidence_stake
    END AS yes_total,
    ROUND(
        (CASE
            WHEN gov_action_proposal.type != 'NoConfidence' THEN GovernanceActionVotes.yes_votes
            ELSE GovernanceActionVotes.yes_votes + DRepVotingPower.auto_no_confidence_stake
        END) / NULLIF(TotalActiveStake.total_stake, 0) * 100, 2
    ) AS yes_percentage,
    CASE
        WHEN gov_action_proposal.type != 'NoConfidence' THEN GovernanceActionVotes.no_votes + DRepVotingPower.auto_no_confidence_stake
        ELSE GovernanceActionVotes.no_votes
    END AS no_total,
    ROUND(
        (CASE
            WHEN gov_action_proposal.type != 'NoConfidence' THEN GovernanceActionVotes.no_votes + DRepVotingPower.auto_no_confidence_stake
            ELSE GovernanceActionVotes.no_votes
        END) / NULLIF(TotalActiveStake.total_stake, 0) * 100, 2
    ) AS no_percentage,
    (TotalActiveStake.total_stake - (GovernanceActionVotes.yes_votes + GovernanceActionVotes.no_votes + DRepVotingPower.auto_no_confidence_stake)) AS not_voted_total,
    ROUND(
        (100 - ((GovernanceActionVotes.no_votes + GovernanceActionVotes.yes_votes + DRepVotingPower.auto_no_confidence_stake) / NULLIF(TotalActiveStake.total_stake, 0) * 100)), 2
    ) AS not_voted_percentage
FROM
    gov_action_proposal
    CROSS JOIN CurrentEpoch
    CROSS JOIN DRepVotingPower
    CROSS JOIN TotalActiveStake
    LEFT JOIN tx AS creator_tx ON creator_tx.id = gov_action_proposal.tx_id
    LEFT JOIN GovernanceActionVotes ON gov_action_proposal.id = GovernanceActionVotes.id
WHERE
    gov_action_proposal.expiration > (SELECT epoch_no FROM CurrentEpoch)
    AND gov_action_proposal.ratified_epoch IS NULL
    AND gov_action_proposal.enacted_epoch IS NULL
    AND gov_action_proposal.expired_epoch IS NULL
    AND gov_action_proposal.dropped_epoch IS NULL;

[reeshavacharya]

• /api/propopsals/{proposalIdHexOrBech32}/vote-count
• /api/proposals/${id}/votes

[reeshavacharya]

Return type for api/proposal/{:id}/votes?voting_power=boolean

type VoteRecord = {
        voter: {
            role: string
            credential: string
            hasScript: boolean
            votingPower?: string
        }
        vote: string
        createdAt: {
            time: string
            block: number
            blockHash: string
            epoch: number
            slot: number
            tx: string
            index: number
        }
    }

[reeshavacharya]

Sample response for /api/propopsals/${id}/vote-count

{
    "drep": {
        "yes": 6,
        "no": 2,
        "abstain": 2
    },
    "pool": {
        "yes": 0,
        "no": 1,
        "abstain": 0
    },
    "cc": {
        "yes": 0,
        "no": 0,
        "abstain": 0
    }
}

[reeshavacharya]

TODO:

• Add not voted count on APIs
• Add collective voting power of the votes on APIs

[reeshavacharya]

WITH govAction AS (
   SELECT g.id, b.epoch_no AS submittedEpoch, g.expiration AS expirationEpoch
   FROM gov_action_proposal g
   JOIN tx ON tx.id = g.tx_id
   JOIN block b ON b.id = tx.block_id
   WHERE tx.hash = DECODE('f72fb9e4438d7075d3ccd15d6c2ef743016125fef25a46ce63e48ac8bc29172c', 'hex')
   AND g.index = 0
),
rankedVotes AS (
    SELECT DISTINCT ON (COALESCE(vp.drep_voter, vp.pool_voter, vp.committee_voter)) 
        vp.id, vp.vote, vp.voter_role, vp.drep_voter, vp.pool_voter, vp.committee_voter, vp.gov_action_proposal_id
    FROM voting_procedure vp 
    JOIN govAction ON vp.gov_action_proposal_id = govAction.id
    ORDER BY COALESCE(vp.drep_voter, vp.pool_voter, vp.committee_voter), vp.id DESC
),
latestOrGovActionExpiration AS (
    SELECT LEAST (
        (SELECT e.no FROM epoch e ORDER BY e.id DESC LIMIT 1), 
        (SELECT g.expirationEpoch FROM govAction g)
    ) AS epoch
),
possibleDrepVoters AS (
    WITH drepIds AS (
        SELECT DISTINCT dr.hash_id AS drepHash
        FROM drep_distr dr 
        WHERE dr.epoch_no BETWEEN (SELECT submittedEpoch FROM govAction) 
                              AND (SELECT expirationEpoch FROM govAction)
    )
    SELECT dr.hash_id AS drepHash, dr.amount AS votingPower
    FROM drep_distr dr
    JOIN drepIds dids ON dr.hash_id = dids.drepHash
    WHERE dr.epoch_no = (select epoch from latestOrGovActionExpiration)
),
abstainAndNoConfidenceDreps AS (
    SELECT DISTINCT dr.hash_id,
           dh.view AS drepType,
           COALESCE(
               (SELECT dr_inner.amount 
                FROM drep_distr dr_inner 
                WHERE dr_inner.hash_id = dr.hash_id 
                AND dr_inner.epoch_no = (SELECT epoch FROM latestOrGovActionExpiration)
               ), 0) AS latestVotingPower
    FROM drep_distr dr 
    JOIN drep_hash dh ON dh.id = dr.hash_id
    WHERE dr.epoch_no BETWEEN (SELECT submittedEpoch FROM govAction) 
                              AND (SELECT expirationEpoch FROM govAction)
    AND dh.view IN ('drep_always_abstain', 'drep_always_no_confidence')
),
drepVoteRecord AS (
    SELECT 
        'Yes' AS vote_type,
        COALESCE(SUM(pdv.votingPower) FILTER (WHERE rv.vote::TEXT = 'Yes'), 0) AS totalVotingPower,
        COUNT(rv.vote) FILTER (WHERE rv.vote::TEXT = 'Yes') AS voteCount
    FROM rankedVotes rv
    LEFT JOIN possibleDrepVoters pdv ON rv.drep_voter = pdv.drepHash
    WHERE rv.voter_role = 'DRep'
    
    UNION ALL

    SELECT 
        'No' AS vote_type,
        COALESCE(SUM(pdv.votingPower) FILTER (WHERE rv.vote::TEXT = 'No'), 0) AS totalVotingPower,
        COUNT(rv.vote) FILTER (WHERE rv.vote::TEXT = 'No') AS voteCount
    FROM rankedVotes rv
    LEFT JOIN possibleDrepVoters pdv ON rv.drep_voter = pdv.drepHash
    WHERE rv.voter_role = 'DRep'
    
    UNION ALL

    SELECT 
        'Abstain' AS vote_type,
        COALESCE(SUM(pdv.votingPower) FILTER (WHERE rv.vote::TEXT = 'Abstain'), 0) + 
        COALESCE((SELECT SUM(latestVotingPower) FROM abstainAndNoConfidenceDreps WHERE drepType = 'drep_always_abstain'), 0) AS totalVotingPower,
        COUNT(rv.vote) FILTER (WHERE rv.vote::TEXT = 'Abstain') + 
        COUNT(DISTINCT (SELECT hash_id from abstainAndNoConfidenceDreps WHERE drepType='drep_always_abstain')) AS voteCount
    FROM rankedVotes rv
    LEFT JOIN possibleDrepVoters pdv ON rv.drep_voter = pdv.drepHash
    WHERE rv.voter_role = 'DRep'

    UNION ALL

    SELECT 
        'NoConfidence' AS vote_type,
        COALESCE((SELECT SUM(latestVotingPower) FROM abstainAndNoConfidenceDreps WHERE drepType = 'drep_always_no_confidence'), 0) AS totalVotingPower, 
        COUNT(DISTINCT (SELECT hash_id from abstainAndNoConfidenceDreps WHERE drepType='drep_always_no_confidence')) AS voteCount
    FROM rankedVotes rv
    LEFT JOIN possibleDrepVoters pdv ON rv.drep_voter = pdv.drepHash
    WHERE rv.voter_role = 'DRep'
)
SELECT * FROM drepVoteRecord;

[mesudip]

Current progress is this

WITH ga AS (
    SELECT g.id, b.epoch_no AS submittedEpoch, g.expiration AS expirationEpoch
    FROM gov_action_proposal g
             JOIN tx ON tx.id = g.tx_id
             JOIN block b ON b.id = tx.block_id
    WHERE tx.hash = DECODE('8c653ee5c9800e6d31e79b5a7f7d4400c81d44717ad4db633dc18d4c07e4a4fd', 'hex')
      AND g.index = 0
),
    govActionVotes AS (select  rv.voter_role,rv.vote,rv.drep_voter,rv.committee_voter,rv.pool_voter from (
    SELECT
        vp.*,
        t.hash as tx_hash,
        b.block_no as block_no,
        ROW_NUMBER() OVER (PARTITION BY vp.voter_role, COALESCE(vp.drep_voter, vp.pool_voter, vp.committee_voter) ORDER BY t.block_id DESC) AS rn
    FROM voting_procedure vp
             JOIN ga on vp.gov_action_proposal_id = ga.id
             JOIN public.tx t ON t.id = vp.tx_id
             JOIN public.block b ON b.id = t.block_id
                    and vp.invalid is null
    ) rv where  rn=1),
     latestOrGovActionExpiration AS (
         SELECT LEAST (
                        (SELECT e.no FROM epoch e ORDER BY e.id DESC LIMIT 1),
                        (SELECT g.expirationEpoch FROM ga g)
                ) AS epoch
     ),

     epochDreps AS (
         select * from  drep_distr ddr
         WHERE ddr.epoch_no = (select epoch from latestOrGovActionExpiration)
     ),

     abstainAndNoConfidenceDreps AS (
         SELECT DISTINCT dr.hash_id,
                         dh.view AS drepType,
                         COALESCE(
                                 (SELECT dr_inner.amount
                                  FROM drep_distr dr_inner
                                  WHERE dr_inner.hash_id = dr.hash_id
                                    AND dr_inner.epoch_no = (SELECT epoch FROM latestOrGovActionExpiration)
                                 ), 0) AS latestVotingPower
         FROM drep_distr dr
                  JOIN drep_hash dh ON dh.id = dr.hash_id
         WHERE dr.epoch_no BETWEEN (SELECT submittedEpoch FROM ga)
             AND (SELECT expirationEpoch FROM ga)
           AND dh.view IN ('drep_always_abstain', 'drep_always_no_confidence')
     ),
     inactiveDreps as  (select sum(amount) amnt from epochDreps
                        left join govActionVotes on govActionVotes.drep_voter = epochDreps.hash_id

                        where active_until > (select expirationEpoch from ga)
                                 and govActionVotes.voter_role is  null
     ),
     votePowers as (select  govActionVotes.vote,sum(epochDreps.amount) power
                    from  govActionVotes
                        join epochDreps  on epochDreps.hash_id = govActionVotes.drep_voter
                     group by govActionVotes.vote)
    (select text(votePowers.vote),votePowers.power from votePowers
        union
    select drep_hash.view,epochDreps.amount
      from drep_hash
               join epochDreps on drep_hash.id = epochDreps.hash_id
      where drep_hash.view in ('drep_always_abstain', 'drep_always_no_confidence')
    union  (select 'total_distribution' ,(select sum(amount) from epochDreps))
    union (select 'inactive_votes', (select amnt from inactiveDreps))

      );

[reeshavacharya]

WITH ga AS (
        SELECT 
            g.id, 
            b.epoch_no AS submittedEpoch, 
            g.expiration AS expirationEpoch
        FROM gov_action_proposal g
        JOIN tx ON tx.id = g.tx_id
        JOIN block b ON b.id = tx.block_id
        WHERE tx.hash = DECODE(
            'f72fb9e4438d7075d3ccd15d6c2ef743016125fef25a46ce63e48ac8bc29172c', 'hex'
        ) 
        AND g.index = 0
    ),
    
    govActionVotes AS (
        SELECT 
            rv.voter_role, 
            rv.vote, 
            rv.drep_voter, 
            rv.committee_voter, 
            rv.pool_voter 
        FROM (
            SELECT 
                vp.*, 
                t.hash AS tx_hash, 
                b.block_no AS block_no,
                ROW_NUMBER() OVER (
                    PARTITION BY vp.voter_role, 
                    COALESCE(vp.drep_voter, vp.pool_voter, vp.committee_voter) 
                    ORDER BY t.block_id DESC
                ) AS rn
            FROM voting_procedure vp
            JOIN ga ON vp.gov_action_proposal_id = ga.id
            JOIN public.tx t ON t.id = vp.tx_id
            JOIN public.block b ON b.id = t.block_id
            WHERE vp.invalid IS NULL
        ) rv 
        WHERE rn = 1
    ),
    
    latestOrGovActionExpiration AS (
        SELECT LEAST(
            (SELECT e.no FROM epoch e ORDER BY e.id DESC LIMIT 1),
            (SELECT g.expirationEpoch FROM ga g)
        ) AS epoch
    ),
    
    poolVoteRecord AS (
    WITH RankedPoolVotes AS (
        SELECT
            *,
            ROW_NUMBER() OVER (PARTITION BY vp.pool_voter ORDER BY vp.tx_id DESC) AS rn
        FROM
            voting_procedure vp
    ),
    PoolVotes AS (
        SELECT
            rpv.gov_action_proposal_id,
            ps.epoch_no,
            COUNT(DISTINCT CASE WHEN vote = 'Yes' THEN rpv.pool_voter ELSE 0 END) AS total_unique_yes_votes,
            COUNT(DISTINCT CASE WHEN vote = 'No' THEN rpv.pool_voter ELSE 0 END) AS total_unique_no_votes,
            COUNT(DISTINCT CASE WHEN vote = 'Abstain' THEN rpv.pool_voter ELSE 0 END) AS total_unique_abstain_votes,
            SUM(CASE WHEN rpv.vote = 'Yes' THEN ps.voting_power ELSE 0 END) AS poolYesVotingPower,
            SUM(CASE WHEN rpv.vote = 'No' THEN ps.voting_power ELSE 0 END) AS poolNoVotingPower,
            SUM(CASE WHEN rpv.vote = 'Abstain' THEN ps.voting_power ELSE 0 END) AS poolAbstainVotingPower
        FROM 
            RankedPoolVotes rpv
        JOIN 
            pool_stat ps
            ON rpv.pool_voter = ps.pool_hash_id
        JOIN ga 
            ON ga.id =  rpv.gov_action_proposal_id
        WHERE
            rpv.rn = 1 AND ps.epoch_no = (SELECT MAX(no) FROM epoch)
        GROUP BY
            rpv.gov_action_proposal_id, ps.epoch_no
    ) select * from poolVotes
    ),
    
    committeeVoteRecord AS (
    
    WITH CommitteeVotes AS (
        SELECT 
            gov_action_proposal_id,
    
            SUM(CASE WHEN vote = 'Yes' THEN 1 ELSE 0 END) AS ccYesVotes,
            SUM(CASE WHEN vote = 'No' THEN 1 ELSE 0 END) AS ccNoVotes,
            SUM(CASE WHEN vote = 'Abstain' THEN 1 ELSE 0 END) AS ccAbstainVotes
        FROM 
            voting_procedure AS vp
            JOIN ga on ga.id = vp.gov_action_proposal_id
        WHERE 
            vp.committee_voter IS NOT NULL
            AND (vp.tx_id, vp.committee_voter, vp.gov_action_proposal_id) IN (
                SELECT MAX(tx_id), committee_voter, gov_action_proposal_id
                FROM voting_procedure
                WHERE committee_voter IS NOT NULL
                GROUP BY committee_voter, gov_action_proposal_id
            )
        GROUP BY 
            gov_action_proposal_id
    ) select * from CommitteeVotes
    ),
    
    dRepVoteRecord AS(
    WITH epochDreps AS (
        SELECT * 
        FROM drep_distr ddr
        WHERE ddr.epoch_no = (SELECT epoch FROM latestOrGovActionExpiration)
    ),
    abstainAndNoConfidenceDreps AS (
        SELECT DISTINCT 
            dr.hash_id,
            dh.view AS drepType,
            COALESCE(
                (SELECT dr_inner.amount
                 FROM drep_distr dr_inner
                 WHERE dr_inner.hash_id = dr.hash_id
                 AND dr_inner.epoch_no = (SELECT epoch FROM latestOrGovActionExpiration)
                ), 0
            ) AS latestVotingPower
        FROM drep_distr dr
        JOIN drep_hash dh ON dh.id = dr.hash_id
        WHERE dr.epoch_no BETWEEN (SELECT submittedEpoch FROM ga)
        AND (SELECT expirationEpoch FROM ga)
        AND dh.view IN ('drep_always_abstain', 'drep_always_no_confidence')
    ),
    
    inactiveDreps AS (
        SELECT SUM(amount) AS amnt,
        epochDreps.hash_id as dreps 
        FROM epochDreps
        LEFT JOIN govActionVotes ON govActionVotes.drep_voter = epochDreps.hash_id
        WHERE active_until > (SELECT expirationEpoch FROM ga)
        AND govActionVotes.voter_role IS NULL
        GROUP BY epochDreps.hash_id
    ),
    votePowers AS (
        SELECT 
            govActionVotes.vote, 
            SUM(epochDreps.amount) AS power,
            COUNT(DISTINCT epochDreps.hash_id) AS count
        FROM govActionVotes
        JOIN epochDreps ON epochDreps.hash_id = govActionVotes.drep_voter
        GROUP BY govActionVotes.vote
    )
    
    SELECT 
        text(votePowers.vote), 
        votePowers.power,
        votePowers.count 
    FROM votePowers
    
    UNION
    
    SELECT 
        drep_hash.view, 
        SUM(epochDreps.amount),
        COUNT (DISTINCT drep_hash.id)
    FROM drep_hash
    JOIN epochDreps ON drep_hash.id = epochDreps.hash_id
    WHERE drep_hash.view IN ('drep_always_abstain')
    GROUP BY drep_hash.view
    
    
    UNION
    
    SELECT 
        drep_hash.view, 
        SUM(epochDreps.amount),
        COUNT (DISTINCT drep_hash.id)
    FROM drep_hash
    JOIN epochDreps ON drep_hash.id = epochDreps.hash_id
    WHERE drep_hash.view IN ('drep_always_no_confidence')
    GROUP BY drep_hash.view
    
    UNION  
    
    SELECT 
        'total_distribution', 
        (SELECT SUM(amount) FROM epochDreps), 
        (SELECT COUNT(DISTINCT hash_id) from epochDreps)
    UNION 
    
    SELECT 
        'inactive_votes', 
        (SELECT SUM(amnt) FROM inactiveDreps),
        (SELECT COUNT(DISTINCT dreps) from inactiveDreps)
    ) 
    
    SELECT jsonb_build_object(
        'drepVoteRecord', (SELECT jsonb_agg(dRepVoteRecord) FROM dRepVoteRecord),
        'spoVoteRecord', (SELECT jsonb_agg(poolVoteRecord) FROM poolVoteRecord),
        'ccVoteRecord', (SELECT jsonb_agg(committeeVoteRecord) FROM committeeVoteRecord)
    ) AS result;

[reeshavacharya]

cardanoapi/autonomous-agents#337