Maintenance Billing and Amenity Booking System for a 120-Flat Apartment Society (Flask + MySQL)

SocietyDesk is a web application that digitises the daily administration of a mid-size apartment complex — the maintenance dues, receipts and amenity bookings that a residents' welfare association (RWA) otherwise tracks in paper ledgers, Excel sheets and WhatsApp groups. It is modelled on Greenfield Residency, a fictional 120-flat, three-block society in south Bengaluru, and can be adapted to any similar community.

Three roles use the system. The secretary (admin) maintains blocks, flats and member records, posts notices and runs the monthly billing. The accountant records payments, issues receipts and watches the dues-ageing report. Owners and residents log in to see their own bills and payment history, book the clubhouse or badminton court, and read notices.

Every month the billing engine generates one invoice per flat: carpet area × per-sq-ft rate + fixed charges + sinking-fund contribution. Payments are recorded against the invoice with mode and UPI reference; a bill left unpaid past its due date is flagged Overdue, attracts a penalty, and appears in the treasurer's ageing report split into 0–30, 31–60, 61–90 and 90+ day buckets. Amenity slots are booked inside a database transaction, so the clubhouse can never be double-booked.

The project is built with Python 3.12, Flask 3 and Jinja2 on MySQL 8, styled with Bootstrap 5 and tested with pytest. It demonstrates normalised relational design, role-based access control, server-side validation and report generation — a solid, viva-defensible B.Sc IT project for the Sem-6 slot.

A 120-flat residents' welfare association collects maintenance, records payments and manages shared amenities using paper ledgers, Excel sheets and WhatsApp. Because there is no central record, receipts get lost, part-payments are mis-posted, and old arrears stay invisible because nobody computes an ageing report. Penalty calculation by hand is inconsistent, which turns follow-up calls into arguments. Amenity bookings taken over the phone collide — two families arrive for the same clubhouse slot — and refundable deposits are tracked nowhere. Residents have no way to check their own balance without visiting the office, and the treasurer has no consolidated view for the annual general meeting.

There is a need for a secure, role-based web application that keeps one authoritative ledger of flats, members, invoices and payments, automates bill and penalty calculation, prevents double booking of amenities through database-level checks, and gives the secretary, the accountant and each resident exactly the views and actions their role allows.

Abstract

Apartment societies in India collect maintenance, pay common-area bills and manage shared amenities, but most associations still keep this work in paper ledgers and spreadsheets. Receipts are handwritten, dues follow-up happens through WhatsApp reminders, and the treasurer cannot easily say how much each flat owes or how old the arrears are. SocietyDesk is a role-based web application that centralises flat and member records, monthly maintenance billing, payment tracking, amenity booking and notices for a 120-flat society. It is implemented with Flask and Jinja2 on MySQL and follows a three-tier architecture.

Introduction

A residents' welfare association of 120 flats handles roughly ₹4–6 lakh of monthly collections. The treasurer must know which flats have paid, which are partial and which are in arrears; the secretary must publish notices and maintain records of owners and tenants; residents want a fair, first-come-first-served way to book the clubhouse or the badminton court. When this information lives in registers and Excel files, every answer needs a phone call, and every billing month needs a weekend of manual work.

Existing System

• Maintenance dues are written in a ledger; receipts are handwritten counterfoils.
• Payments arrive by cash, bank transfer or UPI and are reconciled against bank statements by hand.
• Arrears are tracked by memory; there is no ageing analysis, so old dues go unnoticed.
• Amenity bookings are taken on phone or in a WhatsApp poll, which leads to double bookings and disputes about deposits.
• Limitations: no single source of truth, no audit trail, arithmetic errors in penalties, and no reports the association can act on.

Proposed System

• A central MySQL database of blocks, flats, members, invoices, payments, amenities and bookings.
• Automatic monthly billing: carpet area × per-sq-ft rate + fixed charges + sinking-fund contribution, with a configurable penalty for late payment.
• Payment entry with mode (cash/NEFT/UPI) and reference number; statuses Paid, Partial, Due and Overdue computed from the ledger.
• A dues-ageing report (0–30, 31–60, 61–90, 90+ days) that ranks defaulters for follow-up.
• Transaction-safe amenity booking: overlapping slots for the same amenity are rejected inside the database transaction; refundable deposits are tracked per booking.
• Notices posted by the secretary, visible to every resident; collection and utilisation reports exportable as CSV.
• Role-based dashboards so each user sees only what they are entitled to see.

Feasibility Study

• Technical: Flask and MySQL are open-source, run on any laptop with Python 3.12, and are covered by mature documentation. Nothing in the design needs cloud services, so the demo works offline during the viva.
• Economic: Licence cost is zero; the association can host it on an existing office computer or a ₹200–500/month VPS — far below the cost of the staff hours it saves.
• Operational: The interface is ordinary web forms on Bootstrap, readable on a phone. The secretary and accountant need one short training session; residents need none.

In scope

• One society with multiple blocks and 100–200 flats; owners and tenants as members.
• Three roles: secretary (admin), accountant, owner/resident.
• Monthly maintenance billing with per-sq-ft rates, fixed charges, sinking fund and penalty rules configurable by the secretary.
• Payment recording (cash / NEFT / UPI reference), receipts, and dues-ageing reports with CSV export.
• Amenity master, slot booking with overlap prevention, deposits and cancellation.
• Notices and a monthly collection summary dashboard.

Out of scope (v1)

• Online payment gateway integration — payments are recorded manually with a UPI/bank reference.
• Email/SMS/WhatsApp notification delivery; notices are shown inside the app.
• Vendor, staff and asset (pumps, lifts, security) management modules.
• Income-tax and audit-report generation for the association's chartered accountant.
• Native mobile apps; the web UI is responsive instead.

The project follows an Incremental SDLC model. A working core (auth, flats, members) exists by week 7, and each later increment adds a module that is designed, coded and tested as a unit — a good fit for the 14-week BCU Sem-6 window of 8 hours per week, because guide feedback can be absorbed between increments.

Testing approach: unit tests for money rules (bill amount, penalty after due date, ageing bucket placement) using pytest; integration tests that call Flask routes with the test client against a throwaway MySQL database; and black-box test cases per screen recorded as ID / input / expected / actual / status. Booking tests explicitly race two overlapping reservations to prove only one survives.

SocietyDesk follows a three-tier architecture.

1. Presentation tier — Jinja2 templates styled with Bootstrap 5 render every page server-side; the layout is responsive for phone use by residents.
2. Application tier — Flask blueprints (auth, billing, payments, amenities, notices, reports) hold the business rules: bill computation, penalty application, ageing buckets and the role check that runs before every protected route. Server-side validation on every form; nothing is trusted from the browser.
3. Data tier — MySQL 8 accessed through PyMySQL with parameterised queries only; the schema is in 3NF and InnoDB transactions protect the booking and payment paths.

flowchart LR
  B["Browser: Secretary / Accountant / Owner"] -->|"HTTPS session cookie"| F["Flask app: blueprints + Jinja2"]
  F --> G["login_required + role check"]
  G --> V["Server-side form validation"]
  V --> Q["PyMySQL parameterised queries"]
  Q --> DB[("MySQL 8, InnoDB")]
  G --> E["Billing engine / booking transaction"]
  E --> Q

ER diagram

erDiagram
  FLAT ||--o{ MEMBER : "houses"
  FLAT ||--o{ BILL : "is invoiced"
  BILL ||--o{ PAYMENT : "is settled by"
  MEMBER ||--o{ PAYMENT : makes
  AMENITY ||--o{ BOOKING : "is reserved as"
  MEMBER ||--o{ BOOKING : reserves
  MEMBER ||--o{ NOTICE : posts

  FLAT {
    int id PK
    string block
    string flat_no UK
    decimal area_sqft
    string occupancy
  }
  MEMBER {
    int id PK
    int flat_id FK
    string full_name
    string email UK
    string phone
    string password_hash
    string role
  }
  BILL {
    int id PK
    int flat_id FK
    string month
    decimal per_sqft_rate
    decimal fixed_charges
    decimal sinking_fund
    decimal amount
    date due_date
    decimal penalty
    string status
  }
  PAYMENT {
    int id PK
    int bill_id FK
    int member_id FK
    decimal amount
    string mode
    string reference
    datetime paid_on
  }
  AMENITY {
    int id PK
    string name
    int slot_minutes
    decimal deposit
  }
  BOOKING {
    int id PK
    int amenity_id FK
    int member_id FK
    datetime slot_start
    datetime slot_end
    string status
  }
  NOTICE {
    int id PK
    int member_id FK
    string title
    text body
    datetime posted_on
  }

DFD — Level 0 (context diagram)

flowchart LR
  SEC([Secretary]) -- "flats, rates, notices" --> SYS(("SocietyDesk"))
  SYS -- "collection and defaulter reports" --> SEC
  ACC([Accountant]) -- "payment entries" --> SYS
  SYS -- "receipts, ageing report" --> ACC
  OWN([Owner / Resident]) -- "login, booking request" --> SYS
  SYS -- "bills, notices, booking status" --> OWN

• UPI payment gateway (e.g. a PSP's payment links) so residents pay from the app and the ledger reconciles automatically against the reference.
• WhatsApp/email notification service for bill generation, due-date reminders and booking confirmations.
• Gate-pass and visitor logging with daily vehicle entries for security staff.
• Solar and common-area meter reading capture so electricity charges can join the monthly invoice.
• Multi-society SaaS version with per-association branding for facility-management companies.
• Analytics dashboard — collection trends, amenity-utilisation heat-map and predicted arrears from payment history.