Skip to content

Geosyntec/Dave-s-Awesome-Cost-Proposal-Database

BranchesTags

Repository files navigation

Cost Proposal Database Tool

A database-driven web application that replaces Excel-based cost proposal workflows for Geosyntec Consultants. Project managers build, price, and export cost proposals through a guided five-step wizard. Admins manage rate schedules, markup percentages, expense catalogs, and WBS templates through a dedicated panel.

Built with Python 3.11, Streamlit, SQLAlchemy, and openpyxl. Runs locally against a SQLite file for development; connects to Microsoft SQL Server with Entra ID authentication in production.

Table of Contents

  1. Quick Start — Local Development
  2. Project Structure
  3. Configuration Reference
  4. Database Backends
  5. Authentication Modes
  6. Data Model
  7. Calculation Engine
  8. Schema Migrations
  9. Pages and Navigation
  10. Excel Export
  11. Development Conventions
  12. Deploying to Production
  13. What Is and Is Not Committed to Git
  14. Known Gaps and Planned Work

1. Quick Start — Local Development

Prerequisites

  • Python 3.11+ (Conda environment recommended)
  • The streamlit Conda environment at C:\Users\<you>\AppData\Local\miniforge3\envs\streamlit
  • No SQL Server, no Azure credentials required for local dev

Steps

# 1. Clone the repository
git clone <repo-url>
cd "Dave's Awesome Cost Proposal Database"

# 2. Activate the Conda environment
conda activate streamlit

# 3. Install dependencies
pip install -r requirements.txt

# 4. Copy and configure the environment file
copy .env.example .env
# The defaults in .env.example work as-is for local SQLite dev —
# no edits required unless you want a custom DB path.

# 5. Launch the app
streamlit run app.py

Open http://localhost:8501 in your browser. On first run, the app automatically:

  • Creates cost_proposal.db (SQLite) in the project root.
  • Applies all schema migrations.
  • Seeds reference data: 11 labor categories, sub/direct expense catalogs, a demo client, contract, markup schedule, and rate schedule.

Log in is bypassed in dev mode — you land directly as an admin user.

2. Project Structure

.
├── app.py                        # Entry point: page config, global CSS, sidebar
├── config.py                     # DB_TYPE, AUTH_MODE, connection URL builder
├── auth.py                       # get_current_user(), require_admin()
├── utils.py                      # Shared UI helpers: styles, sidebar, top bar, status badges
│
├── pages/
│   ├── 01_dashboard.py           # Proposal list, KPI cards, create/clone dialogs
│   ├── 02_proposal_editor.py     # Five-step proposal wizard
│   └── 03_admin.py               # Rate schedules, markups, catalogs, WBS templates
│
├── database/
│   ├── models.py                 # All 18 SQLAlchemy ORM models; get_engine(), get_session()
│   ├── seed_data.py              # Reference data seeding (safe to re-run; upserts by sort_order)
│   └── migrations.py             # Idempotent ALTER TABLE runner; called on every startup
│
├── services/
│   ├── proposal_service.py       # Proposal, phase, task, and labor entry CRUD
│   ├── rate_service.py           # Client, contract, rate schedule, and markup lookups
│   ├── expense_service.py        # Sub/direct expense catalog and proposal line CRUD
│   ├── calc_engine.py            # Cost rollup: TaskCosts → PhaseCosts → ProposalCosts
│   └── export_service.py         # Excel workbook builder (live formulas, multi-sheet)
│
├── docs/
│   └── UI_SPEC.md                # Screen-by-screen build spec (authoritative UI reference)
│
├── templates/                    # Placeholder for future Excel template files
├── requirements.txt
├── .env.example                  # Copy to .env and fill in secrets
└── .gitignore

3. Configuration Reference

Copy .env.example to .env before running. The file is gitignored and never committed.

Variable Default Description
DB_TYPE sqlite sqlite for local dev; mssql for production SQL Server
SQLITE_PATH ./cost_proposal.db Path to the local SQLite file
DB_SERVER — SQL Server hostname or FQDN (MSSQL only)
DB_NAME CostProposalDB Database name (MSSQL only)
DB_DRIVER ODBC Driver 17 for SQL Server pyodbc driver string
DB_USER (blank) Leave blank to use Windows/Entra integrated auth
DB_PASSWORD (blank) Leave blank to use Windows/Entra integrated auth
AUTH_MODE dev dev to bypass login; entra for Entra ID SSO
AZURE_TENANT_ID — Entra ID tenant GUID (production only)
AZURE_CLIENT_ID — App registration client ID (production only)
AZURE_CLIENT_SECRET — App registration client secret (production only)
AZURE_REDIRECT_URI http://localhost:8501/ OAuth redirect URI
AD_GROUP_PM — Object ID of the PM Azure AD security group
AD_GROUP_ADMIN — Object ID of the Admin Azure AD security group

4. Database Backends

SQLite (local development)

Set DB_TYPE=sqlite. No installation or credentials needed. The database file (cost_proposal.db) is created automatically on first run and is gitignored.

SQLite mode always uses AUTH_MODE=dev — Entra authentication is not available without a SQL Server backend.

Microsoft SQL Server (production)

Set DB_TYPE=mssql and provide DB_SERVER and DB_NAME. Two auth options:

  • Integrated / Entra auth (recommended): leave DB_USER and DB_PASSWORD blank. Requires the app service principal to have SQL Server permissions.
  • SQL auth fallback: set DB_USER and DB_PASSWORD.

pyodbc and an ODBC driver must be installed on the host. The default driver string is ODBC Driver 17 for SQL Server; update DB_DRIVER if a different version is installed.

Schema creation and migrations run automatically on startup via Base.metadata.create_all() followed by database/migrations.py. No manual CREATE TABLE scripts are required.

5. Authentication Modes

Dev mode (AUTH_MODE=dev)

Injects a hardcoded admin user:

{"name": "Dev User", "email": "dev@local", "role": "admin"}

No login prompt. Intended only for local SQLite development.

Entra mode (AUTH_MODE=entra)

Uses MSAL (Microsoft Authentication Library) to run an OAuth 2.0 authorization code flow. After login, auth.py calls the Microsoft Graph API to retrieve the user's group memberships and resolves the role as follows:

AD group match Role assigned
AD_GROUP_ADMIN admin
AD_GROUP_PM pm
Neither Access denied

require_admin(user) in auth.py calls st.stop() for non-admin users on protected pages.

6. Data Model

All 18 entities live in database/models.py. Key relationships:

Client
  └─ Contract (1:many)
       ├─ ContractMarkup          ← comm_fee_pct, sub_markup_pct, direct_expense_markup_pct
       └─ RateSchedule (1:many)
            └─ RateScheduleRate   ← hourly_rate per LaborCategory

LaborCategory                    ← 11 standard roles (SRP, P, SPF, PP, PRO, SSF, SPR, SDR, DFT, SET, AA)

Proposal
  ├─ contract_id → Contract
  ├─ schedule_id → RateSchedule
  ├─ proposal_number              ← CP-YYYY-NNN (auto-generated)
  ├─ status                       ← draft | pending_review | final | archived
  └─ ProposalPhase (1:many)
       └─ ProposalTask (1:many)
            ├─ LaborEntry (1:many)         ← hours per LaborCategory
            ├─ ProposalSubExpense (1:many) ← catalog or custom sub expense lines
            └─ ProposalDirectExpense (1:many)

SubExpenseItem   ← master catalog (admin-managed)
DirectExpenseItem

WBSTemplate
  └─ WBSTemplatePhase
       └─ WBSTemplateTask

Markup values are stored as decimals (0.10 = 10%) in ContractMarkup. They are never hardcoded anywhere in the application — always read from the database.

Custom expense items flagged during proposal entry (is_custom=True, flagged_for_catalog=True) appear in the Admin → Flagged Items queue, where admins can promote them to the master catalog.

7. Calculation Engine

services/calc_engine.py implements the full cost rollup. The formula chain per task:

Labor Cost      = SUM(hours × hourly_rate)  for each LaborEntry
Comm Fee        = Labor Cost × comm_fee_pct
Sub Expenses    = SUM(quantity × unit_cost) for each ProposalSubExpense line
Sub Markup      = Sub Expenses × sub_markup_pct
Direct Expenses = SUM(quantity × unit_cost) for each ProposalDirectExpense line
Direct Markup   = Direct Expenses × direct_expense_markup_pct
─────────────────────────────────────────────────────────────────────────────
Task Total      = Labor + Comm Fee + Sub Exp + Sub Markup + Direct Exp + Direct Markup
Phase Total     = SUM(Task Totals)
Proposal Total  = SUM(Phase Totals)

calculate_proposal(proposal, session) returns a ProposalCosts dataclass tree:

costs = calculate_proposal(proposal, session)
costs.total                             # grand total
costs.phases[phase_id].total            # phase subtotal
costs.phases[phase_id].tasks[task_id]   # TaskCosts object

Unit cost on an expense line resolves as: unit_cost_override if set, else catalog_item.unit_cost.

8. Schema Migrations

database/migrations.py contains apply_migrations(engine), which runs automatically on every startup after Base.metadata.create_all().

create_all() only creates tables that do not yet exist — it never alters columns. Migrations handles all ALTER TABLE ADD COLUMN operations for columns added after the initial schema was deployed. Each migration:

  1. Checks whether the column already exists (via PRAGMA table_info on SQLite or INFORMATION_SCHEMA.COLUMNS on SQL Server).
  2. Runs ALTER TABLE … ADD COLUMN only if absent.
  3. Is safe to run repeatedly — a no-op on an up-to-date database.

Adding a new migration

Append a _add_column(engine, ...) call at the bottom of apply_migrations() in database/migrations.py. Prefix the comment with the DM ticket ID (e.g., # DM-07). No other steps are required — the runner fires on every startup.

Current migrations

ID Table Column Purpose
DM-03 proposals proposal_number Auto-generated CP-YYYY-NNN identifier
DM-05 proposal_sub_expenses reviewed, review_status Admin catalog review tracking
DM-05 proposal_direct_expenses reviewed, review_status Admin catalog review tracking

9. Pages and Navigation

Streamlit's built-in file-list sidebar is suppressed via CSS. Navigation is handled entirely by utils.render_sidebar(), which renders context-sensitive sub-navigation depending on which page is active.

Dashboard (pages/01_dashboard.py)

  • KPI cards: active proposals, pending review, created this month, total active value.
  • Action bar: + New Proposal and Clone Existing dialogs.
  • Proposal table: sortable columns, status badges, clickable proposal numbers, ⋮ per-row menu (Open, Export, Clone, Archive).

Proposal Editor (pages/02_proposal_editor.py)

Five steps, driven by st.session_state["editor_step"] and the sidebar sub-nav:

Step Key Content
1 contract_rates View contract, rate schedule, and markup details
2 wbs_builder Add, rename, reorder, and delete phases and tasks
3 labor_hours Enter hours per labor category per task in a data grid
4 expenses Pivot grid: items as rows, tasks as columns, quantities as cell values
5 review_export Cost summary, staff mix chart, Excel export

Admin Panel (pages/03_admin.py)

Six sections, driven by st.session_state["admin_section"] and the sidebar sub-nav:

Section Key Content
Clients & Contracts clients_contracts Add clients; view and add contracts per client
Rate Schedules rate_schedules Edit hourly rates per labor category per schedule
Markups markups Set comm fee, sub markup, and direct expense markup per contract
Expense Catalogs expense_catalogs Add/view sub and direct expense catalog items
WBS Templates wbs_templates Create and list reusable phase/task templates
Flagged Items flagged_items Approve custom expense items submitted by PMs

Admin access is enforced via require_admin(user) at the top of the page.

10. Excel Export

services/export_service.py generates a multi-sheet .xlsx workbook. All calculated cells in the Cost Table use live Excel formulas — the file is fully editable after download.

Workbook sheets

Sheet Content
Contract Proposal metadata, hourly rates (B2:B12), markup percentages (B15:B17)
Sub Expenses Line-level sub expense detail with WBS references in column A
Direct Expenses Line-level direct expense detail with WBS references in column A
Cost Table Full WBS with labor hours, costs, and expense rollups

Cost Table column layout (columns C–U)

Columns Content
C–M Labor hours per category (one column per labor category, 11 total)
N Total hours (=SUM(C:M))
O Labor cost (=SUMPRODUCT(hours, Contract!$B$2:$B$12))
P Comm fee (=O × Contract!$B$15)
Q Sub expenses (=SUMIF('Sub Expenses'!$A:$A, WBS, 'Sub Expenses'!$G:$G))
R Sub markup (=Q × Contract!$B$16)
S Direct expenses (=SUMIF('Direct Expenses'!$A:$A, WBS, ...))
T Direct markup (=S × Contract!$B$17)
U Task total (=SUM(O,P,Q,R,S,T))

Phase subtotals use SUMIF($A:$A, "N.*", $U:$U) — the N.* pattern matches all task rows belonging to phase N. The grand total row uses "*.*" to match all task rows (the dot distinguishes tasks from phase header rows).

Do not reorder columns C–U without updating all cross-sheet SUMPRODUCT and SUMIF formulas throughout export_service.py.

11. Development Conventions

Python style

  • PEP 8; 4-space indentation; snake_case throughout.
  • Google-style docstrings on all public functions.
  • All database access goes through services/ — page files never import ORM models directly.
  • Use with get_session(engine) as s: for all DB operations.
  • Never use st.cache_data for anything that writes to the database.

Markup percentages

Markups are stored as decimals (0.10 = 10%) in ContractMarkup. Always read from the database. Never hardcode a markup value anywhere in the codebase.

Status values

Valid proposal statuses: draft, pending_review, final, archived.

Proposal numbers

Format: CP-{year}-{seq:03d} (e.g., CP-2026-001). Auto-generated by _next_proposal_number() in proposal_service.py on create_proposal() and clone_proposal(). Stored in proposals.proposal_number.

Zero-hour display

Zero-value labor cells display as – (em dash), not 0. Consistent with the Excel template.

Color palette

Name Hex
Primary (forest green) #2C5F2D
Secondary #4A8C5C
Accent #97BC62
Dark #1A3A1C
Light background #F5F7F2
Text (charcoal) #2D3436
Text (muted) #636E72

Apply global styles once per page via utils.apply_global_styles(). Never use default Streamlit blue/red theme colors.

12. Deploying to Production

The following changes are required to move from local SQLite to production SQL Server with Entra authentication.

Environment file

DB_TYPE=mssql
DB_SERVER=your-server.database.windows.net
DB_NAME=CostProposalDB
AUTH_MODE=entra
AZURE_TENANT_ID=<tenant-guid>
AZURE_CLIENT_ID=<app-registration-client-id>
AZURE_CLIENT_SECRET=<app-registration-secret>
AZURE_REDIRECT_URI=https://your-app-url/
AD_GROUP_PM=<pm-group-object-id>
AD_GROUP_ADMIN=<admin-group-object-id>

SQL Server setup

  1. Create the database and grant the app service principal db_owner or at minimum db_ddladmin + db_datawriter + db_datareader.
  2. Install ODBC Driver 17 for SQL Server (or 18) on the host running the app.
  3. On first run, Base.metadata.create_all() creates all tables; apply_migrations() handles subsequent column additions.

App registration (Azure)

  1. Register an app in Entra ID.
  2. Add http://localhost:8501/ (dev) and the production URL as redirect URIs.
  3. Add Microsoft Graph API permission: GroupMember.Read.All (delegated).
  4. Create two security groups in Entra ID — one for PMs, one for admins — and copy their object IDs to AD_GROUP_PM and AD_GROUP_ADMIN.

Hosting options

The app runs as a standard Streamlit process. Suitable hosts include Azure App Service (Python runtime), a Docker container on Azure Container Apps, or an internal VM. Set the startup command to streamlit run app.py --server.port 8501 --server.headless true.

13. What Is and Is Not Committed to Git

The .gitignore explicitly excludes the following — do not force-add them:

Excluded Reason
.env Contains secrets (Azure credentials, DB passwords)
*.db, *.sqlite, *.sqlite3 Local development database files
.streamlit/secrets.toml Alternative secrets file
exports/, *.xlsx Generated output files (large, ephemeral)
__pycache__/, *.pyc Compiled bytecode
.claude/, CLAUDE.md AI assistant context files (internal; not for collaborators)
Idea List*.txt Personal notes

Everything else — source code, requirements.txt, .env.example, docs/UI_SPEC.md, database/, services/, pages/, utils.py — is committed and should be kept current.

Commit workflow

git add app.py config.py auth.py utils.py
git add pages/ database/ services/ docs/
git add requirements.txt .env.example .gitignore README.md
git commit -m "Brief description of what changed and why"
git push origin main

Write commit messages in the imperative mood, focused on intent rather than mechanics:

  • ✅ Add expense pivot grid with catalog dropdown
  • ✅ Fix sidebar nav not responding to session state on admin page
  • ❌ Updated files
  • ❌ Bug fix

14. Known Gaps and Planned Work

The following items are defined in docs/UI_SPEC.md but not yet implemented. Reference the DM and requirement IDs in that document for full specifications.

Item ID Notes
WBS phase/task reordering (drag or ▲▼ buttons) DM-06, E-13 sort_order column exists; UI arrows not yet wired
Cloning WBS from an existing proposal into the editor E-09 "Clone from Proposal" button in WBS Builder
Contract date display and expiry warnings in rate schedule dropdowns DM-04 start_date/end_date columns exist in Contract
Step-by-step wizard navigation buttons (Back / Next) at the bottom of each editor step E-03 Sidebar sub-nav is implemented; in-page buttons are not
Detailed cost breakdown expander on the Review step E-30 Summary table exists; per-component breakdown not shown
Bulk import (CSV/Excel) for expense catalog items A-08 Admin panel add-one-at-a-time only
WBS template editor (add phases/tasks to a template from the UI) A-09 Templates can be created; phase/task builder not wired
Entra display name resolution from id_token_claims DM-02 auth.py scaffolded; Graph API call needed

Built by Raphael Siebenmann, Geosyntec Consultants — Atlanta, GA. Initiated March 2026.

About

No description, website, or topics provided.

Resources

Readme
Activity
Custom properties

Stars

0 stars

Watchers

0 watching

Forks

0 forks
Report repository

Releases

No releases published

Packages

 
 
 

Contributors

Languages