School Fee Instalment Plans with Paystack

School fee billing breaks every assumption of the Subscriptions API:

• Amounts vary. Term 1 might be 150,000 NGN, Term 2 might be 120,000 NGN (no exam fees). Instalments within a term may not be equal.
• Schedules follow the school calendar. Due dates are January 15, May 1, September 1, not "monthly." They shift each year.
• Billing is per student, not per account. A parent might have three children in different classes, each with different fee structures.
• The billing ends. When the school year ends or the student graduates, billing stops. There is no indefinite subscription.

Authorization codes with charge_authorization give you the flexibility to handle all of this.

This article is part of the subscriptions and recurring billing guide.

CREATE TABLE students (
  id SERIAL PRIMARY KEY,
  parent_user_id INTEGER REFERENCES users(id),
  full_name VARCHAR(200) NOT NULL,
  class VARCHAR(50),
  admission_number VARCHAR(50) UNIQUE
);

CREATE TABLE fee_schedules (
  id SERIAL PRIMARY KEY,
  student_id INTEGER REFERENCES students(id),
  academic_year VARCHAR(10) NOT NULL,
  term VARCHAR(20) NOT NULL,
  total_fee INTEGER NOT NULL,
  currency VARCHAR(3) DEFAULT 'NGN',
  created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE instalments (
  id SERIAL PRIMARY KEY,
  fee_schedule_id INTEGER REFERENCES fee_schedules(id),
  instalment_number INTEGER NOT NULL,
  amount INTEGER NOT NULL,
  due_date DATE NOT NULL,
  status VARCHAR(20) DEFAULT 'pending',
  paid_at TIMESTAMPTZ,
  paystack_reference VARCHAR(100),
  reminder_sent BOOLEAN DEFAULT false,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_instalments_due ON instalments(due_date, status);

async function createInstalmentPlan(studentId, academicYear, term, totalFee, instalmentDates) {
  // Create fee schedule
  var schedule = await db.query(
    'INSERT INTO fee_schedules (student_id, academic_year, term, total_fee) VALUES ($1, $2, $3, $4) RETURNING id',
    [studentId, academicYear, term, totalFee]
  );

  var scheduleId = schedule.rows[0].id;

  // Calculate instalment amounts
  var numInstalments = instalmentDates.length;
  var baseAmount = Math.floor(totalFee / numInstalments);
  var remainder = totalFee - (baseAmount * numInstalments);

  for (var i = 0; i < instalmentDates.length; i++) {
    // Add remainder to the first instalment
    var amount = i === 0 ? baseAmount + remainder : baseAmount;

    await db.query(
      'INSERT INTO instalments (fee_schedule_id, instalment_number, amount, due_date) VALUES ($1, $2, $3, $4)',
      [scheduleId, i + 1, amount, instalmentDates[i]]
    );
  }

  return scheduleId;
}

// Example: Term 1 fees of 150,000 NGN in 3 instalments
createInstalmentPlan(
  studentId,
  '2026/2027',
  'Term 1',
  15000000, // 150,000 NGN in kobo
  ['2026-09-01', '2026-10-01', '2026-11-01']
);

async function collectFirstInstalment(parentEmail, studentId, scheduleId) {
  var firstInstalment = await db.query(
    'SELECT id, amount FROM instalments WHERE fee_schedule_id = $1 AND instalment_number = 1',
    [scheduleId]
  );

  var instalment = firstInstalment.rows[0];
  var student = await db.query('SELECT full_name, class FROM students WHERE id = $1', [studentId]);

  var response = await axios.post(
    'https://api.paystack.co/transaction/initialize',
    {
      email: parentEmail,
      amount: instalment.amount,
      callback_url: 'https://yourschool.com/fees/callback',
      metadata: {
        custom_fields: [
          { display_name: 'Student', variable_name: 'student', value: student.rows[0].full_name },
          { display_name: 'Class', variable_name: 'class', value: student.rows[0].class },
          { display_name: 'Instalment', variable_name: 'instalment', value: '1 of 3' },
        ],
        instalment_id: instalment.id,
        schedule_id: scheduleId,
      },
    },
    {
      headers: {
        Authorization: 'Bearer ' + process.env.PAYSTACK_SECRET_KEY,
        'Content-Type': 'application/json',
      },
    }
  );

  return response.data.data.authorization_url;
}

// Webhook handler: save authorization and mark first instalment paid
function handleFirstFeePayment(eventData) {
  var auth = eventData.authorization;
  var metadata = eventData.metadata;

  if (auth.reusable) {
    saveParentAuthorization(eventData.customer.email, auth);
  }

  db.query(
    'UPDATE instalments SET status = $1, paid_at = NOW(), paystack_reference = $2 WHERE id = $3',
    ['paid', eventData.reference, metadata.instalment_id]
  );
}

// Run daily via cron
async function processSchoolFeeInstalments() {
  var dueToday = await db.query(
    'SELECT i.id, i.amount, i.instalment_number, fs.student_id, s.parent_user_id, u.email FROM instalments i JOIN fee_schedules fs ON i.fee_schedule_id = fs.id JOIN students s ON fs.student_id = s.id JOIN users u ON s.parent_user_id = u.id WHERE i.due_date = CURRENT_DATE AND i.status = $1',
    ['pending']
  );

  for (var j = 0; j < dueToday.rows.length; j++) {
    var instalment = dueToday.rows[j];
    var paymentMethod = await getDefaultPaymentMethod(instalment.parent_user_id);

    if (!paymentMethod) {
      console.log('No payment method for parent ' + instalment.parent_user_id);
      await sendManualPaymentReminder(instalment.email, instalment);
      continue;
    }

    var authCode = await getAuthorizationCode(paymentMethod.id);
    var reference = 'SCHOOLFEE_' + instalment.id + '_' + Date.now();

    var result = await chargeAuthorization(
      instalment.email,
      instalment.amount,
      authCode,
      reference
    );

    if (result.status === 'success') {
      await db.query(
        'UPDATE instalments SET status = $1, paid_at = NOW(), paystack_reference = $2 WHERE id = $3',
        ['paid', reference, instalment.id]
      );
      await sendInstalmentReceiptEmail(instalment.email, instalment);
    } else {
      await db.query(
        'UPDATE instalments SET status = $1 WHERE id = $2',
        ['failed', instalment.id]
      );
      await sendInstalmentFailedEmail(instalment.email, instalment, result.gateway_response);
    }
  }
}

// Run daily: send reminders for upcoming instalments
async function sendUpcomingInstalmentReminders() {
  // 7-day reminder
  var sevenDays = await db.query(
    'SELECT i.id, i.amount, i.due_date, i.instalment_number, s.full_name as student_name, u.email FROM instalments i JOIN fee_schedules fs ON i.fee_schedule_id = fs.id JOIN students s ON fs.student_id = s.id JOIN users u ON s.parent_user_id = u.id WHERE i.due_date = CURRENT_DATE + INTERVAL '7 days' AND i.status = $1 AND i.reminder_sent = false',
    ['pending']
  );

  for (var i = 0; i < sevenDays.rows.length; i++) {
    var row = sevenDays.rows[i];
    await sendEmail(row.email, 'instalment_reminder_7days', {
      studentName: row.student_name,
      amount: (row.amount / 100).toLocaleString(),
      dueDate: row.due_date,
      instalmentNumber: row.instalment_number,
    });

    await db.query('UPDATE instalments SET reminder_sent = true WHERE id = $1', [row.id]);
  }
}

School fee communication needs to be clear and respectful. Parents are often managing tight budgets. State the student name, the amount, and the due date. Include a link to pay early if they want to.

For failed payments, avoid aggressive language. "We were unable to process the payment for [Student]'s school fees. Please ensure funds are available and we will try again in 3 days, or click here to pay with a different card." This is a community, not a collections agency.

// Get all instalments for a parent across all children
async function getParentFeeOverview(parentUserId) {
  var results = await db.query(
    'SELECT s.full_name as student, s.class, fs.term, fs.academic_year, i.instalment_number, i.amount, i.due_date, i.status FROM instalments i JOIN fee_schedules fs ON i.fee_schedule_id = fs.id JOIN students s ON fs.student_id = s.id WHERE s.parent_user_id = $1 ORDER BY s.full_name, i.due_date',
    [parentUserId]
  );

  // Group by student
  var byStudent = {};
  for (var i = 0; i < results.rows.length; i++) {
    var row = results.rows[i];
    if (!byStudent[row.student]) {
      byStudent[row.student] = {
        class: row.class,
        instalments: [],
        totalDue: 0,
        totalPaid: 0,
      };
    }
    byStudent[row.student].instalments.push(row);
    if (row.status === 'pending' || row.status === 'failed') {
      byStudent[row.student].totalDue = byStudent[row.student].totalDue + row.amount;
    } else {
      byStudent[row.student].totalPaid = byStudent[row.student].totalPaid + row.amount;
    }
  }

  return byStudent;
}

One parent can have one authorization code used for all their children's fees. The same card pays for multiple students. Build a parent dashboard that shows all children, their fee schedules, and payment history in one view.