Guide: Getting your database ready for NEST

Modern NGOs need more than spreadsheets and scattered tools. They need structure, clarity, and systems that scale without becoming fragile. NEST was designed to solve exactly this problem.

NEST is a multi-tenant admin platform where each organization connects its own database. Instead of forcing data into a shared schema, NEST respects data ownership while enforcing best practices for structure, security, and extensibility.

This article explains the expected database setup for organizations onboarding onto NEST, and why these design choices matter.

Bring your own Database (BYOD)

NEST does not store organizational data in a single shared database. Each organization connects its own Supabase instance. This approach provides:

• Full data ownership for the organization

• Clear separation between tenants

• Easier compliance with privacy and regulatory requirements

• Freedom to extend or audit data independently

To make this work reliably, NEST expects a well-defined schema and a small set of shared assumptions.

Core Public Tables

Articles

Used for blogs and content publishing.

Key features:

• Slug-based routing

• Metadata support via JSON

• Draft and published states

• Optional banner images

This structure keeps content flexible while remaining SEO friendly.

create table public.articles (
  id uuid not null default gen_random_uuid (),
  created_at timestamp with time zone not null default now(),
  updated_at timestamp with time zone not null default now(),
  title text not null,
  description text not null,
  slug text not null,
  metadata jsonb null,
  status text not null,
  banner_image text null,
  constraint articles_pkey primary key (id),
  constraint articles_slug_key unique (slug)
) TABLESPACE pg_default;

ALTER TABLE public.articles ENABLE ROW LEVEL SECURITY;

Campaigns

Campaigns represent fundraising initiatives, including a required default campaign for general donations.

Key features:

• Target amount and collection tracking

• Backer count and unallocated funds

• Status control such as Draft and Published

• Optional beneficiary metadata

A default campaign ensures that donations can always be accepted, even when no specific campaign is selected.

The uuid 935203bf-247a-4939-b652-c0a95277fa41 holds a special place in NEST - which always refers to the general (also known as fallback) donation campaign

create table public.campaigns (
  id uuid not null default gen_random_uuid (),
  title text not null,
  description text not null,
  slug text not null,
  amount integer not null,
  created_at timestamp with time zone not null default now(),
  ended_at timestamp with time zone null,
  collection integer not null default 0,
  backers smallint not null default '0'::smallint,
  unallocated_amount smallint not null default '0'::smallint,
  banner_image text null,
  status text not null default 'Draft'::text,
  beneficiary jsonb null,
  constraint campaigns_pkey primary key (id),
  constraint campaigns_slug_key unique (slug)
) TABLESPACE pg_default;

ALTER TABLE public.campaigns ENABLE ROW LEVEL SECURITY;

INSERT INTO public.campaigns (
  id,
  title,
  description,
  slug,
  amount,
  created_at,
  ended_at,
  collection,
  backers,
  unallocated_amount,
  banner_image,
  status
) VALUES (
  '935203bf-247a-4939-b652-c0a95277fa41',
  'Default campaign',
  'General donations',
  'default-campaign',
  0,
  NOW(),
  NULL,
  0,
  0,
  0,
  '',
  'Published'
);

Campaign Products

Campaigns can be broken down into tangible needs like food kits or supplies.

Key features:

• Unit-based tracking

• Required versus collected quantities

• Automatic updates during donation completion

This enables transparent impact reporting instead of abstract donation totals.

create table public.campaign_products (
  id uuid not null default gen_random_uuid (),
  campaign_id uuid not null default gen_random_uuid (),
  title text not null,
  description text null,
  image text null,
  price_per_unit integer not null default 0,
  units_required smallint not null default '0'::smallint,
  units_collected smallint not null default '0'::smallint,
  status text null,
  constraint campaign_products_pkey primary key (id),
  constraint campaign_products_campaign_id_fkey foreign KEY (campaign_id) references campaigns (id) on update CASCADE on delete CASCADE
) TABLESPACE pg_default;

ALTER TABLE public.campaign_products ENABLE ROW LEVEL SECURITY;

Backers

Backers represent one-time donors.

Key features:

• Donation intent and payment lifecycle

• Anonymous donation support

• Auto-allocation logic

• PAN and address fields for compliance

Backers are created before payment and finalized after payment confirmation.

create table public.backers (
  id uuid not null default gen_random_uuid (),
  campaign_id uuid not null default gen_random_uuid (),
  amount integer not null,
  email text not null,
  name text not null,
  is_anon boolean not null default false,
  order_id text null,
  payment_id text null,
  created_at timestamp with time zone not null default now(),
  contact_number character varying not null,
  status text not null default 'Pending'::text,
  auto_allocate boolean not null default true,
  notes text null,
  unallocated_amount integer not null default 0,
  pan_number text null,
  address text null,
  constraint backers_pkey primary key (id),
  constraint backers_order_id_key unique (order_id),
  constraint backers_payment_id_key unique (payment_id),
  constraint backers_campaign_id_fkey foreign KEY (campaign_id) references campaigns (id) on update CASCADE on delete CASCADE
) TABLESPACE pg_default;

ALTER TABLE public.backers ENABLE ROW LEVEL SECURITY;

Donated Products

This table links donors to specific products.

Why this matters:

• Enables itemized receipts

• Allows accurate inventory and fulfillment tracking

• Keeps financial and impact data aligned

create table public.donated_products (
  product_id uuid not null,
  backer_id uuid not null,
  quantity smallint not null,
  constraint donated_products_backer_id_fkey foreign KEY (backer_id) references backers (id) on update CASCADE on delete CASCADE,
  constraint donated_products_product_id_fkey foreign KEY (product_id) references campaign_products (id) on update CASCADE on delete CASCADE
) TABLESPACE pg_default;

ALTER TABLE public.donated_products ENABLE ROW LEVEL SECURITY;

Content Organization with Tags

NEST uses tags instead of rigid categories.

This allows flexible content discovery without restructuring data.

create table public.tags (
  id uuid not null default gen_random_uuid (),
  created_at timestamp with time zone not null default now(),
  name text not null,
  constraint tags_pkey primary key (id),
  constraint tags_name_key unique (name)
) TABLESPACE pg_default;

ALTER TABLE public.tags ENABLE ROW LEVEL SECURITY;

These tags are used to categorize fundraising campaigns.

create table public.tag_campaigns (
  campaign_id uuid null,
  tag_id uuid null,
  constraint tag_campaigns_campaign_id_fkey foreign KEY (campaign_id) references campaigns (id) on update CASCADE on delete CASCADE,
  constraint tag_campaigns_tag_id_fkey foreign KEY (tag_id) references tags (id) on update CASCADE on delete CASCADE
) TABLESPACE pg_default;
 
ALTER TABLE public.tag_campaigns ENABLE ROW LEVEL SECURITY;

These tags are also used to classify blog articles into buckets.

create table public.tag_articles (
  article_id uuid null,
  tag_id uuid null,
  constraint tag_articles_article_id_fkey foreign KEY (article_id) references articles (id) on update CASCADE on delete CASCADE,
  constraint tag_articles_tag_id_fkey foreign KEY (tag_id) references tags (id) on update CASCADE on delete CASCADE
) TABLESPACE pg_default;
 
ALTER TABLE public.tag_articles ENABLE ROW LEVEL SECURITY;

Newsletter and Forms

Newsletter Subscribers

A simple opt-in table with uniqueness enforced at the database level.

create table public.newsletter_subscribers (
  id uuid not null default gen_random_uuid (),
  created_at timestamp with time zone not null default now(),
  email text not null,
  constraint newsletter_subscribers_pkey primary key (id),
  constraint newsletter_subscribers_email_key unique (email)
) TABLESPACE pg_default;

ALTER TABLE public.newsletter_subscribers ENABLE ROW LEVEL SECURITY;

Dynamic Forms

Admins can create forms without redeploying code.

Structure:

• forms define schemas

• form_submissions store user responses as JSON, including their IP addresses to identify spam, and browser details to analyse donor usage.

This allows rapid experimentation and custom data collection.

create table public.forms (
  id uuid not null default gen_random_uuid (),
  created_at timestamp with time zone not null default now(),
  form_type text not null,
  schema jsonb not null,
  constraint forms_pkey primary key (id),
  constraint forms_form_type_key unique (form_type)
) TABLESPACE pg_default;

ALTER TABLE public.forms ENABLE ROW LEVEL SECURITY;

create table public.form_submissions (
  id uuid not null default gen_random_uuid (),
  form_type text not null,
  submitted_at timestamp with time zone not null default now(),
  form_data jsonb not null,
  source_ip inet null,
  user_agent text null,
  constraint form_submissions_pkey primary key (id)
) TABLESPACE pg_default;

ALTER TABLE public.form_submissions ENABLE ROW LEVEL SECURITY;

Subscriptions and Recurring Donations

NEST supports recurring donations through Razorpay.

Key tables:

• subscription_plans

• subscriptions

• subscription_charges

This separation allows:

• Plan reuse

• Accurate charge history

• Clean reconciliation with payment providers

create table public.subscription_plans (
  id uuid not null default gen_random_uuid (),
  name text not null,
  created_at timestamp without time zone not null default now(),
  razorpay_plan_id text not null,
  total_count numeric not null,
  constraint subscription_plans_pkey primary key (id)
) TABLESPACE pg_default;

ALTER TABLE public.subscription_plans ENABLE ROW LEVEL SECURITY;

create table public.subscriptions (
  id uuid not null default gen_random_uuid (),
  name text not null,
  email text not null,
  phone text not null,
  pan_number text null,
  address text null,
  razorpay_subscription_id text null,
  status text not null,
  start_date timestamp with time zone not null default now(),
  end_date timestamp without time zone null,
  plan_id uuid not null,
  constraint subscriptions_pkey primary key (id),
  constraint subscriptions_plan_id_fkey foreign KEY (plan_id) references subscription_plans (id) on update CASCADE on delete CASCADE
) TABLESPACE pg_default;

ALTER TABLE public.subscriptions ENABLE ROW LEVEL SECURITY;

create table public.subscription_charges (
  id uuid not null default gen_random_uuid (),
  created_at timestamp with time zone not null default now(),
  subscription_id uuid not null,
  amount numeric not null,
  razorpay_payment_id text not null,
  constraint subscription_charges_pkey primary key (id),
  constraint subscription_charges_subscription_id_fkey foreign KEY (subscription_id) references subscriptions (id) on update CASCADE on delete CASCADE
) TABLESPACE pg_default;

ALTER TABLE public.subscription_charges ENABLE ROW LEVEL SECURITY;

Database Functions for Consistency

record_donation_intent

Creates a donor record and links selected products before payment.

Why it exists:

• Ensures atomic creation of donation data

• Prevents partial or inconsistent records

create or replace function record_donation_intent(
  donation_data json,
  products json
)
returns uuid
language plpgsql
as $$
declare
  new_backer_id uuid;
  prod_key text;
  prod_qty integer;
begin
  insert into backers (
    campaign_id,
    amount,
    email,
    contact_number,
    name,
    is_anon,
    auto_allocate,
    notes,
    unallocated_amount,
    status,
    pan_number,
    address
  )
  values (
    (donation_data->>'campaign_id')::uuid,
    (donation_data->>'amount')::numeric,
    donation_data->>'email',
    donation_data->>'contact_number',
    donation_data->>'name',
    (donation_data->>'is_anon')::boolean,
    (donation_data->>'auto_allocate')::boolean,
    donation_data->>'notes',
    (donation_data->>'unallocated_amount')::numeric,
    donation_data->>'status',
    nullif(donation_data->>'pan_number', ''),
    nullif(donation_data->>'address', '')
  ) returning id into new_backer_id;

  if json_typeof(products) = 'object' then
    for prod_key in select * from json_object_keys(products)
    loop
      prod_qty := (products->>prod_key)::integer;

      insert into donated_products (
        backer_id,
        product_id,
        quantity
      ) values (
        new_backer_id,
        prod_key::uuid,
        prod_qty
      );
    end loop;
  end if;

  return new_backer_id;
end;
$$;

collect_donation_payment

Finalizes a donation after payment confirmation.

What it updates:

• Backer status

• Campaign totals

• Product units collected

Encapsulating this logic in the database prevents application-level bugs.

create or replace function collect_donation_payment(
  p_backer_id uuid,
  p_order_id text,
  p_payment_id text
)
returns boolean
language plpgsql
as $$
declare
  r_backer record;
  r_product record;
begin
  -- Update backer with order and payment info
  update backers
  set
    order_id = p_order_id,
    payment_id = p_payment_id,
    status = 'Completed'
  where id = p_backer_id;

  -- Ensure backer exists
  select * into r_backer from backers where id = p_backer_id;
  if not found then
    return false;
  end if;

  -- Update campaign stats
  update campaigns
  set
    collection = collection + r_backer.amount,
    backers = backers + 1,
    unallocated_amount = unallocated_amount + r_backer.unallocated_amount
  where id = r_backer.campaign_id;

  -- Update units_collected for each product
  for r_product in
    select product_id, quantity from donated_products where backer_id = p_backer_id
  loop
    update campaign_products
    set units_collected = units_collected + r_product.quantity
    where product_id = r_product.product_id
      and campaign_id = r_backer.campaign_id;
  end loop;

  return true;
exception
  when others then
    return false;
end;
$$;

get_table_row_count

A safe utility function used by the NEST dashboard, as well as your website for metrics.

CREATE OR REPLACE FUNCTION get_table_row_count(
    arg_schema_name TEXT,
    arg_table_name TEXT
)
RETURNS BIGINT
LANGUAGE plpgsql
SECURITY DEFINER
AS $$
DECLARE
    row_count BIGINT;
    query_text TEXT;
BEGIN
    -- Build the dynamic query with proper quoting
    query_text := format('SELECT COUNT(*) FROM %I.%I', arg_schema_name, arg_table_name);

    -- Execute the query and get the result
    EXECUTE query_text INTO row_count;

    RETURN row_count;
END;
$$;

Internal Schema for Operational Data

NEST separates operational data into an internal schema.

To allow NEST to connect to the internal schema we need to explicitly grant access to this internal schema.

-- Grant usage on the internal schema
GRANT USAGE ON SCHEMA internal TO service_role;

-- Grant select, insert, update, delete on all existing tables
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA internal TO service_role;

-- Grant select, usage on all sequences (for auto-increment IDs)
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA internal TO service_role;

-- Automatically grant permissions on future tables/sequences
ALTER DEFAULT PRIVILEGES IN SCHEMA internal 
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO service_role;

ALTER DEFAULT PRIVILEGES IN SCHEMA internal 
GRANT USAGE, SELECT ON SEQUENCES TO service_role;

This schema includes:

volunteers

This table contains the data for the volunteers who have worked with the NGO in the past, and who are currently working.

create table internal.volunteers (
  id uuid not null default gen_random_uuid (),
  first_name text not null,
  middle_name text null,
  last_name text null,
  email text not null,
  phone text not null,
  start_date timestamp with time zone not null default now(),
  last_working_date timestamp with time zone null,
  last_certificate_generated_at timestamp with time zone null,
  metadata jsonb null,
  created_at timestamp with time zone not null default now(),
  address text not null,
  city text not null,
  state text not null,
  zipcode text not null,

  constraint volunteers_pkey primary key (id),
  constraint volunteers_email_key unique (email),
  constraint volunteers_phone_key unique (phone)
) TABLESPACE pg_default;

ALTER TABLE internal.volunteers ENABLE ROW LEVEL SECURITY;

tasks

This table contains all the tasks created by organization members.

create table internal.tasks (
  id uuid not null default gen_random_uuid (),
  title text not null,
  description text not null,
  status text not null default 'To-do'::text,
  assigned_to_email text null,
  created_at timestamp with time zone not null default now(),
  updated_at timestamp with time zone not null default now(),
  created_by_email text not null,
  updated_by_email text not null,
  constraint tasks_pkey primary key (id)
) TABLESPACE pg_default;

ALTER TABLE internal.tasks ENABLE ROW LEVEL SECURITY;

-- Create index for faster queries
CREATE INDEX idx_tasks_status ON internal.tasks(status);
CREATE INDEX idx_tasks_assigned_to_email ON internal.tasks(assigned_to_email);
CREATE INDEX idx_tasks_created_by_email ON internal.tasks(created_by_email);

task_comments

This table contains comments on tasks.

create table internal.task_comments (
  id uuid not null default gen_random_uuid (),
  task_id uuid not null,
  comment text not null,
  commentor_email text not null,
  created_at timestamp with time zone not null default now(),
  constraint task_comments_pkey primary key (id),
  constraint task_comments_task_id_fkey foreign key (task_id) references internal.tasks (id) on update CASCADE on delete CASCADE
) TABLESPACE pg_default;

ALTER TABLE internal.task_comments ENABLE ROW LEVEL SECURITY;

-- Create index for faster queries
CREATE INDEX idx_task_comments_task_id ON internal.task_comments(task_id);
CREATE INDEX idx_task_comments_created_at ON internal.task_comments(created_at);

Benefits:

• Clear boundary between public and internal data

• Easier permission management

• Reduced risk of accidental exposure

The internal schema must be explicitly exposed in Supabase API Settings.

Storage Buckets

NEST expects three buckets:

content

This bucket will store all the public content for your organization's website. Anything that is stored here will be publically accessible - assets such as banners and images. So please don't store any sensitive, non-public or PII information in here.
Create a bucket with name - content (all lowercase)
Visibility - Public

user-uploads

This bucket will store all the user-generated content for your organization's website. For example the files uploaded by users in your forms, resumes of candidates, etc. This is a private bucket, because it stores PII.
Create a bucket with name - user-uploads (all lowercase)
Optionally, you can also restrict the file upload size to 5 MB.

internal

This bucket will store all internal data for your organization, including tasks, internal documents, and other non-public information essential for the functioning of the organization. This is a private bucket and must not be public.
Create a bucket with name - internal (all lowercase)
Visibility - Private

This is kept separate from user-uploads bucket to prevent accidental exposure of information through separation of concerns.

Why This Matters

NEST is not just an admin panel. It is a data discipline system.

By standardizing schemas, functions, and storage expectations, NEST helps organizations:

• Avoid data chaos

• Maintain long-term consistency

• Scale operations without rewrites

• Retain full control over their data

When organizations bring their own database and follow a clear contract, tools like NEST can focus on what they do best: helping teams work efficiently and responsibly.