Objective and Proposal Tables

These tables define the data known about Challenges and Proposals.

Objective and Proposal Table Diagram

Objective Schema

-- Catalyst Event Database

-- objective types table - Defines all currently known objectives types.
CREATE TABLE objective_category
(
    name TEXT PRIMARY KEY,
    description TEXT
);

COMMENT ON TABLE objective_category IS 'Defines all known and valid objective categories.';
COMMENT ON COLUMN objective_category.name IS 'The name of this objective category.';
COMMENT ON COLUMN objective_category.description IS 'A Description of this kind of objective category.';

-- Define known objective categories
INSERT INTO objective_category (name,  description)
VALUES
    ('catalyst-simple','A Simple choice'),
    ('catalyst-native','??'),
    ('catalyst-community-choice','Community collective decision'),
    ('sve-decision','Special voting event decision');

-- known currencies - Defines all currently known currencies.
CREATE TABLE currency
(
    name TEXT PRIMARY KEY,
    description TEXT
);

COMMENT ON TABLE currency IS 'Defines all known and valid currencies.';
COMMENT ON COLUMN currency.name IS 'The name of this currency type.';
COMMENT ON COLUMN currency.description IS 'A Description of this kind of currency type.';


-- Define known currencies
INSERT INTO currency (name,  description)
VALUES
    ('USD_ADA','US Dollars, converted to Cardano ADA at time of reward calculation.'),
    ('ADA','Cardano ADA.'),
    ('CLAP', 'CLAP tokens.'),
    ('COTI', 'COTI tokens.');

-- known vote options - Defines all currently known vote options.
CREATE TABLE vote_options
(
    id SERIAL PRIMARY KEY,

    idea_scale TEXT ARRAY UNIQUE,
    objective TEXT ARRAY UNIQUE
);

COMMENT ON TABLE vote_options IS 'Defines all known vote plan option types.';
COMMENT ON COLUMN vote_options.id IS 'Unique ID for each possible option set.';
COMMENT ON COLUMN vote_options.idea_scale IS 'How this vote option is represented in idea scale.';
COMMENT ON COLUMN vote_options.objective IS 'How the vote options is represented in the objective.';

-- Define known vote_options
INSERT INTO vote_options (idea_scale,  objective)
VALUES
    ('{"blank", "yes", "no"}','{"yes", "no"}');



-- goals

CREATE TABLE goal
(
    id SERIAL PRIMARY KEY,
    event_id INTEGER NOT NULL,

    idx INTEGER NOT NULL,
    name VARCHAR NOT NULL,

    FOREIGN KEY(event_id) REFERENCES event(row_id) ON DELETE CASCADE
);

CREATE UNIQUE INDEX goal_index ON goal(event_id, idx);

COMMENT ON TABLE goal IS 'The list of campaign goals for this event.';
COMMENT ON COLUMN goal.id IS 'Synthetic Unique Key.';
COMMENT ON COLUMN goal.idx IS 'The index specifying the order/priority of the goals.';
COMMENT ON COLUMN goal.name IS 'The description of this event goal.';
COMMENT ON COLUMN goal.event_id IS 'The ID of the event this goal belongs to.';
COMMENT ON INDEX goal_index IS 'An index to enforce uniqueness of the relative `idx` field per event.';


-- objective table - Defines all objectives for all known funds.


CREATE TABLE objective
(
    row_id SERIAL PRIMARY KEY,

    id INTEGER NOT NULL,
    event INTEGER NOT NULL,

    category TEXT NOT NULL,
    title TEXT NOT NULL,
    description TEXT NOT NULL,

    deleted BOOLEAN NOT NULL DEFAULT FALSE,

    rewards_currency TEXT,
    rewards_total BIGINT,
    rewards_total_lovelace BIGINT,
    proposers_rewards BIGINT,
    vote_options INTEGER,

    extra JSONB,

    FOREIGN KEY(event) REFERENCES event(row_id) ON DELETE CASCADE,
    FOREIGN KEY(category) REFERENCES objective_category(name) ON DELETE CASCADE,
    FOREIGN KEY(rewards_currency) REFERENCES currency(name) ON DELETE CASCADE,
    FOREIGN KEY(vote_options) REFERENCES vote_options(id) ON DELETE CASCADE
);

CREATE UNIQUE INDEX objective_idx ON objective (id, event);

COMMENT ON TABLE objective IS
'All objectives for all events.
A objective is a group category for selection in an event.';
COMMENT ON COLUMN objective.row_id IS 'Synthetic Unique Key';
COMMENT ON COLUMN objective.id IS
'Event specific objective ID.
Can be non-unique between events (Eg, Ideascale ID for objective).';
COMMENT ON COLUMN objective.event IS 'The specific Event ID this objective is part of.';
COMMENT ON COLUMN objective.category IS
'What category of objective is this.
See the objective_category table for allowed values.';
COMMENT ON COLUMN objective.title IS 'The  title of the objective.';
COMMENT ON COLUMN objective.description IS 'Long form description of the objective.';
COMMENT ON COLUMN objective.deleted IS 'Flag which defines was this objective (challenge) deleted from ideascale or not. DEPRECATED: only used for ideascale compatibility.';
COMMENT ON COLUMN objective.rewards_currency IS 'The currency rewards values are represented as.';
COMMENT ON COLUMN objective.rewards_total IS 'The total reward pool to pay on this objective to winning proposals. In the Objective Currency.';
COMMENT ON COLUMN objective.rewards_total_lovelace IS 'The total reward pool to pay on this objective to winning proposals. In Lovelace.';
COMMENT ON COLUMN objective.proposers_rewards IS 'Not sure how this is different from rewards_total???';
COMMENT ON COLUMN objective.vote_options IS 'The Vote Options applicable to all proposals in this objective.';
COMMENT ON COLUMN objective.extra IS
'Extra Data  for this objective represented as JSON.
"url"."objective" is a URL for more info about the objective.
"highlights" is ???
';

CREATE TABLE review_metric ( row_id SERIAL PRIMARY KEY, name VARCHAR NOT NULL, description VARCHAR NULL, min INTEGER NOT NULL, max INTEGER NOT NULL, map JSONB ARRAY NULL ); COMMENT ON TABLE review_metric IS ‘Definition of all possible review metrics.’; COMMENT ON COLUMN review_metric.row_id IS ‘The synthetic ID of this metric.’; COMMENT ON COLUMN review_metric.name IS ‘The short name for this review metric.’; COMMENT ON COLUMN review_metric.description IS ‘Long form description of what the review metric means.’; COMMENT ON COLUMN review_metric.min IS ‘The minimum value of the metric (inclusive).’; COMMENT ON COLUMN review_metric.max IS ‘The maximum value of the metric (inclusive).’; COMMENT ON COLUMN review_metric.map IS ’OPTIONAL: JSON Array which defines extra details for each metric score. There MUST be one entry per possible score in the range. Entries are IN ORDER, from the lowest numeric score to the highest. Each entry =

{
   "name" : "<name>", // Symbolic Name for the metric score.
   "description" : "<desc>", // Description of what the named metric score means.
}

’;

– Define known review metrics INSERT INTO review_metric (name, description, min, max, map) VALUES (‘impact’, ‘Impact Rating’, 0, 5, NULL), (‘feasibility’,‘Feasibility Rating’, 0, 5, NULL), (‘auditability’,‘Auditability Rating’, 0, 5, NULL), (‘value’,‘Value Proposition Rating’, 0, 5, NULL), (‘vpa_ranking’,‘VPA Ranking of the review’,0,3, ARRAY [ ‘{“name”:“Excellent”,“desc”:“Excellent Review”}’, ‘{“name”:“Good”,“desc”:“Could be improved.”}’, ‘{“name”:“FilteredOut”,“desc”:“Exclude this review”}’, ‘{“name”:“NA”, “desc”:“Not Applicable”}’]::JSON[]);

CREATE TABLE objective_review_metric ( row_id SERIAL PRIMARY KEY, objective INTEGER NOT NULL, metric INTEGER NOT NULL, note BOOLEAN, review_group VARCHAR,

UNIQUE(objective, metric, review_group),

FOREIGN KEY (objective) REFERENCES objective(row_id) ON DELETE CASCADE, FOREIGN KEY (metric) REFERENCES review_metric(row_id) ON DELETE CASCADE );

COMMENT ON TABLE objective_review_metric IS ‘All valid metrics for reviews on an objective.’; COMMENT ON COLUMN objective_review_metric.objective IS ‘The objective that can use this review metric.’; COMMENT ON COLUMN objective_review_metric.metric IS ‘The review metric that the objective can use.’; COMMENT ON COLUMN objective_review_metric.note IS ‘Does the metric require a Note? NULL = Optional. True = MUST include Note. False = MUST NOT include Note.’; COMMENT ON COLUMN objective_review_metric.review_group IS ‘The review group that can give this metric. Details TBD.’;

CREATE TABLE review_rating ( row_id SERIAL PRIMARY KEY, review_id INTEGER NOT NULL, metric INTEGER NOT NULL, rating INTEGER NOT NULL, note VARCHAR,

UNIQUE ( review_id, metric ),

FOREIGN KEY (review_id) REFERENCES proposal_review(row_id) ON DELETE CASCADE, FOREIGN KEY (metric) REFERENCES review_metric(row_id) ON DELETE CASCADE );

COMMENT ON TABLE review_rating IS ‘An Individual rating for a metric given on a review.’; COMMENT ON COLUMN review_rating.row_id IS ‘Synthetic ID of this individual rating.’; COMMENT ON COLUMN review_rating.review_id IS ‘The review the metric is being given for.’; COMMENT ON COLUMN review_rating.metric IS ‘Metric the rating is being given for.’; COMMENT ON COLUMN review_rating.rating IS ‘The rating being given to the metric.’; COMMENT ON COLUMN review_rating.note IS ‘OPTIONAL: Note about the rating given.’;


<footer id="open-on-gh">Found a bug? <a href="https://github.com/input-output-hk/catalyst-core/edit/main/book/src/08_event-db/04_objective_tables.md">Edit this page on GitHub.</a></footer>

Catalyst Voting System - Core Technology

Objective and Proposal Tables

Objective and Proposal Table Diagram

Objective Schema

Proposal Schema