sql
CREATE TABLE orgs(
    id serial PRIMARY KEY,
    owner_id INTEGER NOT NULL REFERENCES auth.users(id) ON DELETE RESTRICT,
    name VARCHAR(256) NOT NULL
);

CREATE TABLE org_members(
    id serial PRIMARY KEY,
    user_id INTEGER NOT NULL REFERENCES auth.users(id) ON DELETE RESTRICT,
    name VARCHAR(256) NOT NULL
);

sql
CREATE TABLE accounts(
    id serial PRIMARY KEY,
    org_id INTEGER NOT NULL REFERENCES orgs(id) ON DELETE RESTRICT,
    name VARCHAR(256) NOT NULL
);

CREATE TABLE entries(
    id serial PRIMARY KEY,
    org_id INTEGER NOT NULL REFERENCES orgs(id) ON DELETE RESTRICT,
    description VARCHAR(1024) NOT NULL,
    amount NUMERIC(20, 2) NOT NULL CHECK (amount > 0.0),
    credit INTEGER NOT NULL REFERENCES accounts(id) ON DELETE RESTRICT,
    debit INTEGER NOT NULL REFERENCES accounts(id) ON DELETE RESTRICT
);

CREATE INDEX ON entries(credit);
CREATE INDEX ON entries(debit);
CREATE INDEX ON entries(org_id);

CREATE VIEW account_ledgers(
    account_id,
    entry_id,
    amount
) AS
    SELECT
        entries.credit,
        entries.id,
        entries.amount
    FROM
        entries
    UNION ALL
    SELECT
        entries.debit,
        entries.id,
        (0.0 - entries.amount)
    FROM
        entries;


CREATE MATERIALIZED VIEW account_balances(
    id, -- INTEGER REFERENCES accounts(id) NOT NULL UNIQUE
    balance -- NUMERIC NOT NULL
) AS
    SELECT
        accounts.id,
        COALESCE(sum(account_ledgers.amount), 0.0)
    FROM
        accounts
        LEFT OUTER JOIN account_ledgers
        ON accounts.id = account_ledgers.account_id
    GROUP BY accounts.id;

CREATE UNIQUE INDEX ON account_balances(id);

CREATE FUNCTION update_balances() RETURNS TRIGGER AS $$
BEGIN
    REFRESH MATERIALIZED VIEW account_balances;
    RETURN NULL;
END
$$ LANGUAGE plpgsql;

CREATE TRIGGER trigger_fix_balance_entries
AFTER INSERT 
OR UPDATE OF amount, credit, debit 
OR DELETE OR TRUNCATE
ON entries
FOR EACH STATEMENT
EXECUTE PROCEDURE update_balances();

CREATE TRIGGER trigger_fix_balance_accounts
AFTER INSERT 
OR UPDATE OF id 
OR DELETE OR TRUNCATE
ON accounts
FOR EACH STATEMENT
EXECUTE PROCEDURE update_balances();

js
let { data, error } = await supabase
  .from("items")
  .select(
    `id, title, content, 
     tags!inner(id, name)`
  )

create table cards (
  id uuid default uuid_generate_v4(),
  deck_id uuid references public.decks.id on delete cascade,
  created_at timestamp with time zone default CURRENT_DATE,
  front text not null,
  back text not null,
  primary key (id)
);

cross-database references are not implemented: "public.decks.id"

permissiiontomaketeam