## Project Overview
```bash
docker build --no-cache -t zjxteusa/collection-app:v1.1.22 .
docker push zjxteusa/collection-app:v1.1.22
docker compose down
docker compose pull
docker compose up -d
```
This is a **Municipal Collections Management System** - a Flask-based debt collection and accounts receivable management platform for municipal government. The system automates the collections process for overdue bills from multiple sources (MUNIS ERP and CIS), providing collection queues, activity tracking, metrics, reporting, and risk analysis for finance and collections teams.
**Technology Stack**: Flask (Python), Bootstrap CSS/JavaScript, MS-SQL Server (MUNIS data), MS-SQL/MS-SQL (collections activity database)
## Development Commands
### Environment Setup
```bash
# Install dependencies
pip install -r requirements.txt
# Initialize collections database
python init_db.py
# Run development server
python app.py
```
### Database Setup
```bash
# MUNIS MS-SQL connection uses Windows Authentication
# Collections database uses MS-SQL for development, MS-SQL for production
# Configuration in .env file
```
## Architecture Overview
### Project Structure
```
/
├── app.py # Main Flask application
├── config.py # Configuration management
├── requirements.txt # Python dependencies
├── .env # Environment variables (not committed)
├── models.py # SQLAlchemy models
├── queries/
│ ├───aging.sql
│ ├───bill_by_arcode.sql
│ ├───bill_by_county.sql
│ ├───bill_by_year.sql
│ ├───bills.sql
│ ├───customer_address.sql
│ ├───customer_contact.sql
│ └───munis_sp_conditions.sql
└── static/
└── js/
└── index.js # Main JavaScript for the index page
└── dashboard.js # Main JavaScript for the dashboard page
└── templates/
├── index.html # Main bill listing and filtering page
├── dashboard.html # Analytics and reporting dashboard
├── login.html # User login page
├── add_user.html # Admin page to add new users
├── nav.html # Shared navigation bar component
├── customer_contact.html # Displays customer contact information
└── special_conditions.html # Displays special conditions
```
### Key Components
1. **Dual Database Architecture**:
- **MUNIS Database (MS-SQL)**: Read-only connection to existing ERP system for bill/customer data
- **Collections Database (MS-SQL/MS-SQL)**: Separate database for collection activities, notes, tasks, and user actions
2. **Collection Queue System**: Role-based queue views showing customers by aging status with actionable workflows
3. **Activity Tracking Engine**: Complete audit trail of all collection activities with who/when/how/what-next logging
4. **Multi-System Integration**: Interfaces with MUNIS ERP and CIS systems for comprehensive receivables data
5. **Reporting & Analytics**: Real-time metrics for productivity, cash collected, aging analysis, and forecasting
## Database Architecture
### MUNIS Database (MS-SQL - Read Only)
**Connection**: MS SQL Authentication to existing MUNIS system for **MUNIS_PROD_HOST**
**Primary View**: `dbo.unpaidbills` (from bills.sql)
**Key Fields from MUNIS**:
- `ItemIdentifier` - Unique bill ID (also unique key)
- `BillId` - Bill id (Unique key, **use this key for foreigh key if join with other table**)
- `Number` - bill number
- `Category` (ARCode) - Account category
- `CustomerName` - Customer name
- `BilledAmount` - Original billed amount
- `UnpaidBalance` - Current outstanding balance
- `Year` - Bill year
- `DueDate` - bill due date
- Additional customer fields (phone, address, etc.)
### Collections Database (MS-SQL/MS-SQL - Read/Write)
**Schema Design**:
```sql
-- Bill Assignment
CREATE TABLE bill_assignments (
id INT IDENTITY(1,1) PRIMARY KEY,
bill_id NVARCHAR(50) UNIQUE NOT NULL, -- From MUNIS (BillID)
user_id INT NOT NULL, -- Assigned collector
assigned_at DATETIME DEFAULT GETDATE(), -- When it was assigned
assigned_by INT NOT NULL, -- Who assigned it
FOREIGN KEY (user_id) REFERENCES users(id),
FOREIGN KEY (assigned_by) REFERENCES users(id)
);
-- Customer Queue Assignment
CREATE TABLE queue_assignments (
id INTEGER PRIMARY KEY AUTOINCREMENT,
customer_id VARCHAR(50) NOT NULL, -- From MUNIS/CIS
queue_name VARCHAR(50) NOT NULL, -- Queue bucket
collector_id INTEGER, -- Assigned collector
status VARCHAR(50) DEFAULT 'Active', -- Collection status
follow_up_date DATE, -- Next action date
amount_due DECIMAL(15,2), -- Cached total
credits_available DECIMAL(15,2), -- Cached credits
last_activity_date DATETIME, -- Last contact
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (collector_id) REFERENCES users(id)
);
-- Collection Activity Log
CREATE TABLE collection_activities (
id INTEGER PRIMARY KEY AUTOINCREMENT,
customer_id VARCHAR(50) NOT NULL,
customer_source VARCHAR(10) NOT NULL,
activity_type VARCHAR(50) NOT NULL, -- 'Call', 'Email', 'Letter', 'Payment', etc.
activity_date DATETIME NOT NULL,
performed_by INTEGER NOT NULL, -- User ID
contact_method VARCHAR(50), -- 'Phone', 'Email', 'In-Person', 'Mail'
contact_outcome VARCHAR(50), -- 'Promised Payment', 'No Answer', 'Dispute', etc.
notes TEXT, -- Detailed notes
amount_discussed DECIMAL(15,2), -- Amount discussed
promise_amount DECIMAL(15,2), -- Promised payment amount
promise_date DATE, -- Promised payment date
next_action VARCHAR(50), -- 'Follow-up', 'Send Letter', 'Legal', etc.
next_action_date DATE, -- When to take next action
status_change VARCHAR(50), -- New status if changed
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (performed_by) REFERENCES users(id)
);
-- Task Management
CREATE TABLE collection_tasks (
id INTEGER PRIMARY KEY AUTOINCREMENT,
customer_id VARCHAR(50) NOT NULL,
customer_source VARCHAR(10) NOT NULL,
assigned_to INTEGER NOT NULL,
task_type VARCHAR(50) NOT NULL, -- 'Follow-up', 'Reminder', 'Legal Review', etc.
task_description TEXT,
due_date DATE NOT NULL,
priority VARCHAR(20) DEFAULT 'Normal', -- 'High', 'Normal', 'Low'
status VARCHAR(20) DEFAULT 'Pending', -- 'Pending', 'In Progress', 'Completed'
completed_date DATETIME,
completed_by INTEGER,
notes TEXT,
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (assigned_to) REFERENCES users(id),
FOREIGN KEY (completed_by) REFERENCES users(id)
);
-- Users and Roles
CREATE TABLE users (
id INTEGER PRIMARY KEY AUTOINCREMENT,
username VARCHAR(50) UNIQUE NOT NULL,
email VARCHAR(100) UNIQUE NOT NULL,
password_hash VARCHAR(255) NOT NULL,
full_name VARCHAR(100),
role VARCHAR(20) NOT NULL, -- 'Manager', 'Collector', 'Admin'
is_active BOOLEAN DEFAULT 1,
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
last_login DATETIME
);
-- Queue Configuration
CREATE TABLE queue_definitions (
id INTEGER PRIMARY KEY AUTOINCREMENT,
queue_name VARCHAR(50) UNIQUE NOT NULL,
aging_min INTEGER, -- Minimum days past due
aging_max INTEGER, -- Maximum days past due
description TEXT,
sort_order INTEGER,
is_active BOOLEAN DEFAULT 1
);
-- System Configuration
CREATE TABLE system_settings (
id INTEGER PRIMARY KEY AUTOINCREMENT,
setting_key VARCHAR(100) UNIQUE NOT NULL,
setting_value TEXT,
description TEXT,
updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
);
```
## Business Logic
### Collection Queue Management
**Aging Buckets**:
- Current (0-30 days)
- 31-60 days
- 61-90 days
- 91-120 days
- 121+ days
- 'Future Due' (not due yet)
**Bill by county**:
- Get county info from Munis by joining `dbo.unpaidbills` with `dbo.v_nash_edge_parcels` on the parcel ID.
- The dashboard provides a `By County` view that aggregates the total unpaid balance for each county.
**Queue Assignment Logic**:
1. Calculate days past due from MUNIS `DueDate`
2. Assign to appropriate aging bucket queue
3. Apply business rules (minimum balance thresholds, customer type filters)
4. Assign to collector based on workload balancing or territory
**Customer Queue Display**:
- Customer # (MUNIS/CIS)
- Name
- Phone
- Last Activity (auto-updated from collection_activities)
- Follow Up Date (from queue_assignments)
- Status (from queue_assignments)
- Amount Due (total unpaid balance)
- Credits (cached from MUNIS)
- Aging columns (0-30, 31-60, 61-90, 91-120, 121+)
- Total Unpaid Balance (matching filters)
### Collection Activity Tracking
**Required Information for Every Contact**:
- **Who**: User ID (performed_by)
- **When**: activity_date (timestamp)
- **How**: contact_method (Phone, Email, Letter, In-Person, Mail)
- **What Happened**: contact_outcome, notes
- **What's Next**: next_action, next_action_date
**Activity Types**:
- Outbound Call
- Inbound Call
- Email Sent
- Email Received
- Letter Mailed
- Payment Received
- Payment Plan Setup
- Dispute Filed
- Legal Action Initiated
- Account Review
- Customer Meeting
### Role-Based Access Control
**Roles**:
1. **Collector**:
- View assigned queue only
- Add activities and notes
- Create tasks
- Update customer status
- Cannot reassign customers
2. **Manager**:
- View all queues
- Reassign customers between queues/collectors
- View all reports
- Override collection status
- Manage team workload
3. **Admin**:
- All manager permissions
- User management
- System configuration
- Queue definitions
- Reporting configuration
- Cache management
### Reporting & Analytics
**Key Metrics**:
- Total Inventory (count and balance)
- Aging Distribution (by bucket)
- Collection Effectiveness (cash collected, success rate)
- Collector Productivity (contacts per day, promises obtained)
- DSO (Days Sales Outstanding)
- Right Party Contact Rate
- Promise-to-Pay Conversion Rate
- Payment Plan Adherence Rate
**Reports**:
1. Daily Activity Summary (by collector)
2. Aging Analysis (current vs prior period)
3. Cash Collected Report (by collector, period)
4. Customer Status Report
5. Forecast & Risk Analysis
6. Compliance Report (activity documentation)
## UI Requirements
### Framework & Styling
- **Backend**: Flask with minimal external libraries
- **Frontend**: Bootstrap 5 CSS/JS for all styling and components
- **JavaScript**: `static/js/index.js` for main page interactivity; `static/js/dashboard.js` for dashboard interactivity; vanilla JS or minimal jQuery for other interactivity
- **Charts**: Chart.js for analytics visualizations
- **Icons**: Bootstrap Icons
### Key Pages
1. **Login Page** (`/login`)
- Simple, secure authentication
- Role-based redirect after login
2. **Collection Queue** (`/` or `/?<filters>`)
- Filterable table of bills
- Sortable columns
- Quick action buttons (View/Add Notes)
- Bulk actions (Assign)
- Export to CSV
- Display total unpaid balance and record count on a single line after filters
3. **Notes Modal** (on Collection Queue page)
- Activity timeline (all contact history for a bill)
- Customer contact information
- Add new activity form
- Displays special conditions from the MUNIS database.
- Shows Bill ID and Customer ID in the header.
4. **Dashboard** (`/dashboard`)
- KPI cards (Total Inventory, Cash Collected, etc.)
- Aging chart
- Productivity metrics
- Recent activities feed
5. **Reports** (`/reports`)
- Pre-defined report templates
- Date range selection
- Export options (PDF, Excel, CSV)
6. **Admin Cache Management** (`/admin/clear-cache`)
- Allows Admin users to clear the application's cache via a GET or POST request.
- Redirects to the dashboard upon completion.
7. **Special Conditions Endpoint** (`/special_conditions_html/<bill_id>`)
- Fetches special conditions for the customer associated with the given `bill_id`.
- Returns an HTML fragment.
8. **Customer Contact Endpoint** (`/customer_contact_html/<customer_id>`)
- Fetches customer contact information for the given `customer_id`.
- Returns an HTML fragment.
### Shared Components
- **Navigation Bar (`nav.html`)**: A shared navigation component included in all major pages, providing consistent navigation links.
### Design Principles
- **Government-Appropriate**: Professional, clean, accessible
- **Data-Dense**: Maximize information density without clutter
- **Action-Oriented**: Quick access to common workflows
- **Mobile-Responsive**: Works on tablets for field collectors
- **WCAG 2.1 AA Compliant**: Accessible to all users
## Important Implementation Notes
### Data Integration
1. **MUNIS Interface**: Read-only SQL queries to `dbo.unpaidbills` and other views - NEVER write to MUNIS database
2. **CIS Interface**: Separate connection for utility billing data (to be configured based on CIS system type)
3. **Data Synchronization**: Nightly batch job to refresh customer balances and create/update queue assignments
4. **Real-Time Updates**: Activity tracking writes immediately to collections database
### Security Requirements
1. **Encrypted Connections**: All database connections use TLS/SSL
2. **Windows Authentication**: For MUNIS MS-SQL access (no stored passwords)
3. **Password Hashing**: Bcrypt for collections database user passwords
4. **Audit Logging**: All user actions logged with timestamps
5. **Role-Based Access**: Enforce at route level with decorators
6. **Session Management**: Secure session cookies with timeout
### Performance Requirements
1. **Queue Load Time**: < 2 seconds for up to 1,000 customers
2. **Customer Detail**: < 1 second load time
3. **Activity Save**: < 500ms response time
4. **Report Generation**: < 5 seconds for standard reports
5. **Database Indexing**: Proper indexes on customer_id, dates, status fields
6. **Consistent Function Returns**: Ensure all code paths in functions that return multiple values provide a consistent number of elements to prevent unpacking errors.
### Compliance & Best Practices
1. **FDCPA Compliance**: Collection activity tracking supports compliance documentation
2. **Data Retention**: Configurable retention policies for activity logs
3. **Backup Strategy**: Daily automated backups of collections database
4. **Error Handling**: Graceful degradation, user-friendly error messages
5. **Testing**: Unit tests for business logic, integration tests for database operations
### Feature Priorities (Phase 1 MVP)
1. ✅ User authentication and role-based access
2. ✅ Collection queue display with aging buckets
3. ✅ Customer detail view with bill list
4. ✅ Activity logging (who, when, how, what's next)
5. ✅ Task management (follow-ups, reminders)
6. ✅ Basic reporting (aging, activity summary)
7. ✅ Move customer between queues
8. ✅ CSV export
9. ✅ Exact matching for text filters (Category, Bill Number, Customer ID, City, State)
10. ✅ Bill assignment to users (Admin/Manager assign, Collector view only assigned, Admin/Manager filter by assigned user)
11. ✅ Display total unpaid balance for filtered bills
12. ✅ Clear Cache link in navigation for Admins
13. ✅ Display special conditions in the notes view.
14. ✅ Display customer contact information in the notes view.
### Future Enhancements (Phase 2+)
- Mail merge templates and automation
- Payment plan setup and tracking
- Automated email/SMS notifications
- Advanced forecasting and risk scoring
- Integration with payment processing
- Mobile app for field collectors
- AI-assisted contact recommendations
- Additional dashboard charts and visualizations
- Improved display of special conditions (e.g., highlighting key conditions).
## Development Guidelines
### Code Style
- Follow PEP 8 for Python code
- Use type hints where appropriate
- Docstrings for all functions and classes
- Keep functions small and focused (< 50 lines)
### Database Access Patterns
```python
# MUNIS queries (read-only)
with munis_connection() as conn:
cursor = conn.cursor()
cursor.execute("SELECT * FROM dbo.unpaidbills WHERE ...")
results = cursor.fetchall()
# Collections database (read/write)
from models.collections_models import CollectionActivity
activity = CollectionActivity(
customer_id=customer_id,
activity_type='Call',
performed_by=current_user.id,
notes='Customer promised payment by Friday'
)
db.session.add(activity)
db.session.commit()
```
### Error Handling
```python
try:
# Database operation
result = perform_query()
except DatabaseError as e:
logger.error(f"Database error: {e}")
flash("An error occurred. Please try again.", "error")
return redirect(url_for('queue.index'))
```
### Route Protection
```python
# Example for a route accessible by all logged-in users
@app.route('/notes/<bill_id>')
@login_required
def get_notes(bill_id):
# Route logic
# Example for an admin-only route
@app.route('/admin/clear-cache', methods=['GET', 'POST'])
@login_required
@admin_required
def clear_cache():
# Route logic
# Example for special_conditions_html route
@app.route('/special_conditions_html/<bill_id>')
@login_required
def get_special_conditions_html(bill_id):
# Route logic
```
## Testing Strategy
1. **Unit Tests**: Business logic, calculations, data transformations
2. **Integration Tests**: Database operations, API endpoints
3. **E2E Tests**: Critical user workflows (login, add activity, generate report)
4. **Performance Tests**: Load testing for queue queries
5. **Security Tests**: Authentication, authorization, SQL injection prevention
## Deployment Considerations
- **Cloud-Based**: Deploy to Azure, AWS, or GCP
- **Environment Variables**: Use .env for secrets, never commit
- **Database Migrations**: Use Alembic for schema versioning
- **Monitoring**: Application performance monitoring, error tracking
- **Backup & Recovery**: Automated backups, documented recovery procedures
---
**Note**: This is a financial collections system handling sensitive government data. All development must prioritize security, accuracy, compliance, and auditability.
SQL Stored precedure
Create table for DailyUnpaidSummary
```sql
CREATE TABLE [dbo].[DailyUnpaidSummary](
[UpdatedAt] [date] NOT NULL,
[Balance] [decimal](18, 2) NOT NULL
) ON [PRIMARY]
```
then create stored procedures
```sql
CREATE PROCEDURE [dbo].[SaveDailyUnpaidSummary]
AS
BEGIN
INSERT INTO dbo.DailyUnpaidSummary (UpdatedAt, Balance)
SELECT
CAST(UpdatedAt AS DATE) AS UpdatedAt,
SUM(UnpaidBalance) AS Balance
FROM dbo.unpaidbills
GROUP BY CAST(UpdatedAt AS DATE);
END;
```
create indexes in sql
```sql
-- Index on BillLines for the join
CREATE INDEX IX_BillLines_BillId_ChargeId
ON dbo.BillLines(BillId, ChargeId);
-- Index on archarge for the join
CREATE INDEX IX_archarge_id_code_desc
ON dbo.archarge(archg_id, archg_code, archg_desc1);
-- Most important: Index on BillLines
CREATE NONCLUSTERED INDEX IX_BillLines_BillId_ChargeId
ON dbo.BillLines(BillId)
INCLUDE (ChargeId);
-- Index on archarge
CREATE NONCLUSTERED INDEX IX_archarge_id_code
ON dbo.archarge(archg_id)
INCLUDE (archg_code);
-- Index on bill_assignments
CREATE NONCLUSTERED INDEX IX_BillAssignments_BillId_UserId
ON dbo.bill_assignments(bill_id)
INCLUDE (user_id);
-- Index on users
CREATE NONCLUSTERED INDEX IX_Users_Id_FullName
ON users(id)
INCLUDE (full_name);
-- If Bills table doesn't have one already
CREATE NONCLUSTERED INDEX IX_Bills_Id_HasBalanceDue
ON dbo.Bills(Id, HasBalanceDue, VoidedVersion)
WHERE HasBalanceDue = 1 AND VoidedVersion = 0;
-- Index for bill_assignments lookups
CREATE NONCLUSTERED INDEX IX_bill_assignments_bill_id_user_id
ON bill_assignments (bill_id, user_id)
INCLUDE (assigned_at, assigned_by);
-- Index for collection_notes existence check
CREATE NONCLUSTERED INDEX IX_collection_notes_bill_id
ON collection_notes (bill_id);
-- Index for users lookup
CREATE NONCLUSTERED INDEX IX_users_id_role
ON users (id, role)
INCLUDE (full_name);
CREATE NONCLUSTERED INDEX IX_archarge_archg_code_id
ON dbo.archarge (archg_code, archg_id);
CREATE NONCLUSTERED INDEX IX_BillLines_ChargeId_BillId
ON dbo.BillLines (ChargeId, BillId);
CREATE NONCLUSTERED INDEX IX_BillLines_BillId
ON dbo.BillLines (BillId);
```
special condition for SSIS
```sql
SET NOCOUNT ON;
SELECT
scn.SpecialConditionId,
scb.BillId,
scc.CustomerId,
LTRIM(RTRIM(scp.Parcel)) AS Parcel,
scp.Category,
scds.StartDate,
scds.EndDate,
scds.CreatedDate,
UPPER(scds.CreatedByUsername) AS ByClerk,
UPPER(REPLACE(
RTRIM(LTRIM(STUFF((
SELECT
REPLACE(REPLACE(
LTRIM(RTRIM(Note)),
CHAR(8), ' '), CHAR(10), ' ')
FROM AccountsReceivable.SpecialConditionNotes scn2
WHERE scn2.SpecialConditionId = scn.SpecialConditionId
ORDER BY scn2.Sequence
FOR XML PATH(''), TYPE
).value('.', 'nvarchar(max)'), 1, 0, ''))), -- ← Changed 1, 1 to 1, 0
' ', ' ') -- Replace double spaces
) AS Sp_Conditions
FROM AccountsReceivable.SpecialConditionNotes AS scn
LEFT JOIN AccountsReceivable.SpecialConditionBills AS scb
ON scn.SpecialConditionId = scb.SpecialConditionId
LEFT JOIN AccountsReceivable.SpecialConditionCustomers AS scc
ON scn.SpecialConditionId = scc.SpecialConditionId
LEFT JOIN AccountsReceivable.SpecialConditionParcels AS scp
ON scn.SpecialConditionId = scp.SpecialConditionId
LEFT JOIN AccountsReceivable.SpecialConditions AS scds
ON scn.SpecialConditionId = scds.Id
--WHERE --scc.CustomerId = 25521
--scp.Parcel = '375906386111'
GROUP BY
scn.SpecialConditionId,
scb.BillId,
scc.CustomerId,
scp.Parcel,
scp.Category,
scds.StartDate,
scds.EndDate,
scds.CreatedDate,
scds.CreatedByUsername
--ORDER BY scds.CreatedDate DESC
```
