Rental Property Management

[{"_1":2,"_3":-5,"_4":-5},"loaderData",{"_5":6,"_7":8,"_9":10},"actionData","errors","root",{"_279":280,"_281":282},"routes/_app.x.$handle.$name",{"_100":99,"_32":49,"_108":90,"_27":109,"_13":14,"_110":-5,"_11":111,"_112":113,"_114":115,"_116":117,"_118":119,"_21":-5,"_120":-5,"_121":-5,"_122":25,"_123":124,"_125":25,"_126":-5,"_127":-5,"_128":129},"routes/_app.x.$handle.$name.branch.$branch.version.$version.diff",{"_11":12,"_13":14,"_15":16,"_17":-5,"_18":-5},"project",{"_47":48,"_32":49,"_36":50,"_51":-5,"_52":25,"_53":54,"_55":54,"_56":-5,"_57":-5,"_18":58,"_59":60,"_61":62,"_63":62,"_64":-5,"_65":66,"_67":68,"_69":-5,"_70":-5,"_71":-5,"_72":-5,"_73":74,"_75":74,"_76":77,"_78":77,"_79":77,"_80":74,"_81":74,"_82":83,"_84":85,"_86":87,"_88":25},"version",10,"changes",[19,20],"aiChatInfo","userId",{"_21":41,"_23":-7,"_24":25,"_26":25,"_27":42,"_29":42},{"_21":22,"_23":-7,"_24":25,"_26":25,"_27":28,"_29":28},"file",{"_30":31,"_32":28,"_33":34,"_35":-5,"_36":37,"_38":14,"_39":40,"_24":25},"previous","deleted",false,"deletedHere","path","frontend","latestPath","fileId","a1f66990-27b5-4e54-8d75-a14791247db7","name","fileType","directory","content","createdAt",["D",1748867091716],"branchVersion","branchId","f9077912-3fab-11f0-8ba7-76b3cceeab13",{"_30":43,"_32":44,"_33":21,"_35":45,"_36":46,"_38":14,"_39":40,"_24":25},"frontend/index.html","951f324a-3fac-11f0-87cf-76b3cceeab13","index.html","\n\n\n \n \n Rental Property Management\n \n \n \n\n\n

\n \n\n",["D",1748867091716],"id","f9072a0c-3fab-11f0-8ba7-76b3cceeab13","NdaulaPM",["D",1748866829842],"deletedAt","legacyEnvVars","privacy","public","httpPrivacy","description","imageUrl","1214b5b0-3fab-11f0-b6e9-ab0b2f88b858","pinnedFileId","8de99538-3fac-11f0-b7af-76b3cceeab13","pinPreview",true,"pinMigrated","screenshotVersion","likeCount",0,"orgId","c81ae74e-0bae-4445-97ed-1d826ddd8812","tursoName","tursoHostname","tursoMeasuredAt","pinned","userPerm","r","valPerm","blobPerm","rw","emailPerm","sqlitePerm","projectPerm","telemetryPerm","user",{"_100":99,"_107":103},"org",{"_100":99,"_101":-5,"_102":103,"_18":58,"_104":105,"_106":25},"branch",{"_13":89,"_32":90,"_47":40,"_91":48,"_92":-5,"_93":-5,"_94":-5,"_95":-5,"_96":-5,"_97":49,"_98":99},"isOwned",19,"main","projectId","forkedBranchId","forkedBranchVersion","forkedProjectPrivacy","forkedProjectId","forkedProjectOrgId","projectName","userHandle","sakwah","handle","displayName","avatar","https://img.clerk.com/eyJ0eXBlIjoicHJveHkiLCJzcmMiOiJodHRwczovL2ltYWdlcy5jbGVyay5kZXYvb2F1dGhfZ2l0aHViL2ltZ18yeHg0VnNVTHM1VEhha21ldTNTQk5VMnVEekgifQ","tier","free","canRestrictVals","profileImageUrl","branchName","","adoptOrgId",{"_47":48,"_32":49,"_36":275,"_51":-5,"_52":25,"_53":54,"_55":54,"_56":-5,"_57":-5,"_18":58,"_59":60,"_61":62,"_63":62,"_64":-5,"_65":66,"_67":68,"_69":-5,"_70":-5,"_71":-5,"_72":-5,"_73":74,"_75":74,"_76":77,"_78":77,"_79":77,"_80":74,"_81":74,"_82":276,"_84":277,"_86":278,"_88":25},"tree",[169,179,181,183],"files",[169,170,171,172,173,174,175,176,177,178,179,180,181,182,183],"counts",{"_159":66,"_160":138,"_161":66,"_162":163,"_164":165},"contents",[130,131,132,133],"upstreamProject","upstreamBranch","emptyEnvVars","flags",[],"hasHttpFiles","grants","orgs","customDomains",{},{"_47":153,"_32":154,"_30":155,"_137":-5,"_39":40,"_38":156,"_139":62,"_36":157,"_13":66,"_33":34,"_35":-5,"_24":25,"_142":-5,"_27":158},{"_47":150,"_32":28,"_30":31,"_137":-5,"_39":40,"_38":14,"_139":62,"_36":151,"_13":66,"_33":34,"_35":-5,"_24":25,"_142":-5,"_27":152},{"_47":144,"_32":145,"_30":146,"_137":-5,"_39":40,"_38":147,"_139":62,"_36":148,"_13":66,"_33":34,"_35":-5,"_24":25,"_142":-5,"_27":149},{"_47":134,"_32":135,"_30":136,"_137":-5,"_39":40,"_38":138,"_139":62,"_36":140,"_13":66,"_33":21,"_35":141,"_24":25,"_142":-5,"_27":143},"5412ef26-3fac-11f0-bb1f-76b3cceeab13","README.md","5412eff8-3fac-11f0-bb1f-76b3cceeab13","parentId",1,"latest",["D",1748866982585],"# Rental Property Management App\n\nA comprehensive web application for managing rental properties, tracking rent payments, maintenance costs, staff payments, and taxes.\n\n## Features\n\n- **Rent Payments**: Track tenant payments, due dates, and payment history\n- **Maintenance Costs**: Log and categorize property maintenance expenses\n- **Staff Payments**: Manage payments to contractors, cleaners, and other staff\n- **Tax Management**: Track tax-deductible expenses and generate reports\n- **Financial Dashboard**: Overview of income, expenses, and profitability\n\n## Project Structure\n\n```\n├── backend/\n│ ├── database/\n│ │ ├── migrations.ts # Database schema setup\n│ │ └── queries.ts # Database query functions\n│ ├── routes/\n│ │ ├── payments.ts # Rent payment endpoints\n│ │ ├── maintenance.ts # Maintenance cost endpoints\n│ │ ├── staff.ts # Staff payment endpoints\n│ │ └── taxes.ts # Tax-related endpoints\n│ └── index.ts # Main API server\n├── frontend/\n│ ├── components/\n│ │ ├── App.tsx # Main application component\n│ │ ├── Dashboard.tsx # Financial overview dashboard\n│ │ ├── RentPayments.tsx # Rent payment management\n│ │ ├── Maintenance.tsx # Maintenance cost tracking\n│ │ ├── StaffPayments.tsx# Staff payment management\n│ │ └── TaxReports.tsx # Tax reporting and analytics\n│ ├── index.html # Main HTML template\n│ └── index.tsx # Frontend entry point\n└── shared/\n └── types.ts # Shared TypeScript interfaces\n```\n\n## Getting Started\n\n1. The app will automatically set up the database on first run\n2. Navigate to the main page to access the dashboard\n3. Use the navigation to switch between different management sections\n\n## Database Schema\n\n- **tenants**: Tenant information and lease details\n- **rent_payments**: Rent payment records\n- **maintenance_costs**: Property maintenance expenses\n- **staff_payments**: Payments to staff and contractors\n- **tax_categories**: Tax-deductible expense categories","label",[135],"5a36c314-3fac-11f0-8185-76b3cceeab13","shared","7a0d0a80-27d3-4b8e-b4f0-a0f9b6579a12",2,["D",1748866992886],[145],"951f3bbe-3fac-11f0-87cf-76b3cceeab13",["D",1748867091716],[28],"63b0d5b0-3fac-11f0-be38-76b3cceeab13","backend","1b730b5d-cfd5-4777-ad8d-086d3833bce8",3,["D",1748867008786],[154],"pulls","branches","forks","envVars",{"_166":66,"_168":66},"connections",{"_166":66,"_167":66},"count","unfilledCount","emptyCount",{"_47":153,"_32":154,"_30":155,"_137":-5,"_39":40,"_38":156,"_139":62,"_36":272,"_13":66,"_33":34,"_35":-5,"_24":25,"_142":-5,"_185":273,"_27":274},{"_47":268,"_32":260,"_30":254,"_137":155,"_39":40,"_38":156,"_139":62,"_36":269,"_13":66,"_33":34,"_35":-5,"_24":25,"_142":-5,"_185":270,"_27":271},{"_47":261,"_32":262,"_30":263,"_137":254,"_39":40,"_38":156,"_139":62,"_36":264,"_13":66,"_33":192,"_35":265,"_24":25,"_142":-5,"_185":266,"_27":267},{"_47":251,"_32":252,"_30":253,"_137":254,"_39":40,"_38":255,"_139":62,"_36":256,"_13":66,"_33":192,"_35":257,"_24":25,"_142":-5,"_185":258,"_27":259},{"_47":247,"_32":222,"_30":216,"_137":155,"_39":40,"_38":234,"_139":62,"_36":248,"_13":66,"_33":34,"_35":-5,"_24":25,"_142":-5,"_185":249,"_27":250},{"_47":239,"_32":240,"_30":241,"_137":216,"_39":40,"_38":242,"_139":62,"_36":243,"_13":66,"_33":192,"_35":244,"_24":25,"_142":-5,"_185":245,"_27":246},{"_47":231,"_32":232,"_30":233,"_137":216,"_39":40,"_38":234,"_139":62,"_36":235,"_13":66,"_33":192,"_35":236,"_24":25,"_142":-5,"_185":237,"_27":238},{"_47":223,"_32":224,"_30":225,"_137":216,"_39":40,"_38":226,"_139":62,"_36":227,"_13":66,"_33":192,"_35":228,"_24":25,"_142":-5,"_185":229,"_27":230},{"_47":213,"_32":214,"_30":215,"_137":216,"_39":40,"_38":217,"_139":62,"_36":218,"_13":66,"_33":192,"_35":219,"_24":25,"_142":-5,"_185":220,"_27":221},{"_47":206,"_32":207,"_30":60,"_137":155,"_39":40,"_38":208,"_139":25,"_36":209,"_13":66,"_33":192,"_35":210,"_24":25,"_142":-5,"_185":211,"_27":212},{"_47":150,"_32":28,"_30":31,"_137":-5,"_39":40,"_38":14,"_139":62,"_36":203,"_13":66,"_33":34,"_35":-5,"_24":25,"_142":-5,"_185":204,"_27":205},{"_47":199,"_32":44,"_30":43,"_137":31,"_39":40,"_38":14,"_139":62,"_36":200,"_13":66,"_33":21,"_35":45,"_24":25,"_142":-5,"_185":201,"_27":202},{"_47":144,"_32":145,"_30":146,"_137":-5,"_39":40,"_38":147,"_139":62,"_36":196,"_13":66,"_33":34,"_35":-5,"_24":25,"_142":-5,"_185":197,"_27":198},{"_47":188,"_32":189,"_30":190,"_137":146,"_39":40,"_38":147,"_139":62,"_36":191,"_13":66,"_33":192,"_35":193,"_24":25,"_142":-5,"_185":194,"_27":195},{"_47":134,"_32":135,"_30":136,"_137":-5,"_39":40,"_38":138,"_139":62,"_36":184,"_13":66,"_33":21,"_35":141,"_24":25,"_142":-5,"_185":186,"_27":187},["D",1748866982585],"children",[],[135],"5a36bd56-3fac-11f0-8185-76b3cceeab13","types.ts","5a36be00-3fac-11f0-8185-76b3cceeab13",["D",1748866992886],"script","// Shared TypeScript interfaces for the rental property management app\n\nexport interface Tenant {\n id: number;\n name: string;\n email: string;\n phone: string;\n unit: string;\n lease_start: string;\n lease_end: string;\n monthly_rent: number;\n security_deposit: number;\n created_at: string;\n}\n\nexport interface RentPayment {\n id: number;\n tenant_id: number;\n tenant_name?: string;\n amount: number;\n due_date: string;\n paid_date: string | null;\n payment_method: string;\n notes: string;\n status: 'pending' | 'paid' | 'late' | 'partial';\n created_at: string;\n}\n\nexport interface MaintenanceCost {\n id: number;\n description: string;\n category: string;\n amount: number;\n date: string;\n vendor: string;\n unit: string;\n receipt_url: string;\n tax_deductible: boolean;\n created_at: string;\n}\n\nexport interface StaffPayment {\n id: number;\n staff_name: string;\n role: string;\n amount: number;\n payment_date: string;\n payment_type: 'hourly' | 'fixed' | 'commission';\n hours_worked: number | null;\n hourly_rate: number | null;\n description: string;\n tax_form_type: string; // 1099, W2, etc.\n created_at: string;\n}\n\nexport interface TaxCategory {\n id: number;\n name: string;\n description: string;\n deductible_percentage: number;\n}\n\nexport interface FinancialSummary {\n total_rent_collected: number;\n total_maintenance_costs: number;\n total_staff_payments: number;\n net_income: number;\n tax_deductible_expenses: number;\n pending_rent: number;\n}\n\nexport interface MonthlyReport {\n month: string;\n year: number;\n rent_income: number;\n maintenance_expenses: number;\n staff_expenses: number;\n net_profit: number;\n tax_deductions: number;\n}",[],[145,189],["D",1748866992886],[182],[145],"951f3196-3fac-11f0-87cf-76b3cceeab13",["D",1748867091716],[],[28,44],["D",1748867091716],[180],[28],"8de9942a-3fac-11f0-b7af-76b3cceeab13","index.ts",9,["D",1748867079623],"import { Hono } from \"https://esm.sh/hono@3.11.7\";\nimport { readFile, serveFile } from \"https://esm.town/v/std/utils@85-main/index.ts\";\nimport { runMigrations } from \"./database/migrations.ts\";\nimport { getFinancialSummary } from \"./database/queries.ts\";\n\nimport payments from \"./routes/payments.ts\";\nimport maintenance from \"./routes/maintenance.ts\";\nimport staff from \"./routes/staff.ts\";\nimport taxes from \"./routes/taxes.ts\";\n\nconst app = new Hono();\n\n// Unwrap Hono errors to see original error details\napp.onError((err, c) => {\n throw err;\n});\n\n// Initialize database on startup\nawait runMigrations();\n\n// API routes\napp.route(\"/api/payments\", payments);\napp.route(\"/api/maintenance\", maintenance);\napp.route(\"/api/staff\", staff);\napp.route(\"/api/taxes\", taxes);\n\n// Serve static files\napp.get(\"/frontend/*\", c => serveFile(c.req.path, import.meta.url));\napp.get(\"/shared/*\", c => serveFile(c.req.path, import.meta.url));\n\n// Serve main page with initial data\napp.get(\"/\", async (c) => {\n try {\n let html = await readFile(\"/frontend/index.html\", import.meta.url);\n \n // Inject initial financial summary to avoid extra round-trip\n const initialData = await getFinancialSummary();\n const dataScript = ``;\n \n html = html.replace(\"\", `${dataScript}`);\n return c.html(html);\n } catch (error) {\n console.error(\"Error serving main page:\", error);\n return c.text(\"Error loading application\", 500);\n }\n});\n\nexport default app;",[],[154,207],"877ddfec-3fac-11f0-ab96-76b3cceeab13","taxes.ts","877de0be-3fac-11f0-ab96-76b3cceeab13","13363509-fb03-4247-828f-eb97b9946cda",8,["D",1748867068852],"import { Hono } from \"https://esm.sh/hono@3.11.7\";\nimport { \n getAllTaxCategories, \n getFinancialSummary, \n getMonthlyReports \n} from \"../database/queries.ts\";\n\nconst taxes = new Hono();\n\n// Get all tax categories\ntaxes.get(\"/categories\", async (c) => {\n try {\n const categories = await getAllTaxCategories();\n return c.json(categories);\n } catch (error) {\n console.error(\"Error fetching tax categories:\", error);\n return c.json({ error: \"Failed to fetch tax categories\" }, 500);\n }\n});\n\n// Get financial summary\ntaxes.get(\"/summary\", async (c) => {\n try {\n const summary = await getFinancialSummary();\n return c.json(summary);\n } catch (error) {\n console.error(\"Error fetching financial summary:\", error);\n return c.json({ error: \"Failed to fetch financial summary\" }, 500);\n }\n});\n\n// Get monthly reports\ntaxes.get(\"/reports/:year\", async (c) => {\n try {\n const year = parseInt(c.req.param(\"year\"));\n const reports = await getMonthlyReports(year);\n return c.json(reports);\n } catch (error) {\n console.error(\"Error fetching monthly reports:\", error);\n return c.json({ error: \"Failed to fetch monthly reports\" }, 500);\n }\n});\n\nexport default taxes;",[],[154,222,214],"routes","8322b4ea-3fac-11f0-9459-76b3cceeab13","staff.ts","8322b594-3fac-11f0-9459-76b3cceeab13",7,["D",1748867061541],"import { Hono } from \"https://esm.sh/hono@3.11.7\";\nimport { getAllStaffPayments, createStaffPayment } from \"../database/queries.ts\";\n\nconst staff = new Hono();\n\n// Get all staff payments\nstaff.get(\"/\", async (c) => {\n try {\n const payments = await getAllStaffPayments();\n return c.json(payments);\n } catch (error) {\n console.error(\"Error fetching staff payments:\", error);\n return c.json({ error: \"Failed to fetch staff payments\" }, 500);\n }\n});\n\n// Create new staff payment\nstaff.post(\"/\", async (c) => {\n try {\n const payment = await c.req.json();\n const id = await createStaffPayment(payment);\n return c.json({ id, message: \"Staff payment created successfully\" });\n } catch (error) {\n console.error(\"Error creating staff payment:\", error);\n return c.json({ error: \"Failed to create staff payment\" }, 500);\n }\n});\n\nexport default staff;",[],[154,222,224],"7c1e81ec-3fac-11f0-bc9d-76b3cceeab13","payments.ts","7c1e82d2-3fac-11f0-bc9d-76b3cceeab13",5,["D",1748867049768],"import { Hono } from \"https://esm.sh/hono@3.11.7\";\nimport { \n getAllRentPayments, \n createRentPayment, \n updateRentPayment,\n getAllTenants,\n createTenant\n} from \"../database/queries.ts\";\n\nconst payments = new Hono();\n\n// Get all rent payments\npayments.get(\"/\", async (c) => {\n try {\n const payments = await getAllRentPayments();\n return c.json(payments);\n } catch (error) {\n console.error(\"Error fetching rent payments:\", error);\n return c.json({ error: \"Failed to fetch rent payments\" }, 500);\n }\n});\n\n// Create new rent payment\npayments.post(\"/\", async (c) => {\n try {\n const payment = await c.req.json();\n const id = await createRentPayment(payment);\n return c.json({ id, message: \"Rent payment created successfully\" });\n } catch (error) {\n console.error(\"Error creating rent payment:\", error);\n return c.json({ error: \"Failed to create rent payment\" }, 500);\n }\n});\n\n// Update rent payment\npayments.put(\"/:id\", async (c) => {\n try {\n const id = parseInt(c.req.param(\"id\"));\n const updates = await c.req.json();\n await updateRentPayment(id, updates);\n return c.json({ message: \"Rent payment updated successfully\" });\n } catch (error) {\n console.error(\"Error updating rent payment:\", error);\n return c.json({ error: \"Failed to update rent payment\" }, 500);\n }\n});\n\n// Get all tenants\npayments.get(\"/tenants\", async (c) => {\n try {\n const tenants = await getAllTenants();\n return c.json(tenants);\n } catch (error) {\n console.error(\"Error fetching tenants:\", error);\n return c.json({ error: \"Failed to fetch tenants\" }, 500);\n }\n});\n\n// Create new tenant\npayments.post(\"/tenants\", async (c) => {\n try {\n const tenant = await c.req.json();\n const id = await createTenant(tenant);\n return c.json({ id, message: \"Tenant created successfully\" });\n } catch (error) {\n console.error(\"Error creating tenant:\", error);\n return c.json({ error: \"Failed to create tenant\" }, 500);\n }\n});\n\nexport default payments;",[],[154,222,232],"7fb7faae-3fac-11f0-9141-76b3cceeab13","maintenance.ts","7fb7fc7a-3fac-11f0-9141-76b3cceeab13",6,["D",1748867055809],"import { Hono } from \"https://esm.sh/hono@3.11.7\";\nimport { getAllMaintenanceCosts, createMaintenanceCost } from \"../database/queries.ts\";\n\nconst maintenance = new Hono();\n\n// Get all maintenance costs\nmaintenance.get(\"/\", async (c) => {\n try {\n const costs = await getAllMaintenanceCosts();\n return c.json(costs);\n } catch (error) {\n console.error(\"Error fetching maintenance costs:\", error);\n return c.json({ error: \"Failed to fetch maintenance costs\" }, 500);\n }\n});\n\n// Create new maintenance cost\nmaintenance.post(\"/\", async (c) => {\n try {\n const cost = await c.req.json();\n const id = await createMaintenanceCost(cost);\n return c.json({ id, message: \"Maintenance cost created successfully\" });\n } catch (error) {\n console.error(\"Error creating maintenance cost:\", error);\n return c.json({ error: \"Failed to create maintenance cost\" }, 500);\n }\n});\n\nexport default maintenance;",[],[154,222,240],"7c1e9254-3fac-11f0-bc9d-76b3cceeab13",["D",1748867049768],[174,175,176,177],[154,222],"75e2edb8-3fac-11f0-8185-76b3cceeab13","queries.ts","75e2ee58-3fac-11f0-8185-76b3cceeab13","e1d1b0f1-3887-4e65-a63a-f5f85db4535b",4,["D",1748867039315],"import { sqlite } from \"https://esm.town/v/stevekrouse/sqlite\";\nimport type { \n Tenant, \n RentPayment, \n MaintenanceCost, \n StaffPayment, \n TaxCategory, \n FinancialSummary,\n MonthlyReport \n} from \"../../shared/types.ts\";\n\n// Tenant queries\nexport async function getAllTenants(): Promise {\n const result = await sqlite.execute(\"SELECT * FROM tenants ORDER BY unit\");\n return result.rows as Tenant[];\n}\n\nexport async function createTenant(tenant: Omit): Promise {\n const result = await sqlite.execute(`\n INSERT INTO tenants (name, email, phone, unit, lease_start, lease_end, monthly_rent, security_deposit)\n VALUES (?, ?, ?, ?, ?, ?, ?, ?)\n `, [tenant.name, tenant.email, tenant.phone, tenant.unit, tenant.lease_start, tenant.lease_end, tenant.monthly_rent, tenant.security_deposit]);\n return result.lastInsertRowId as number;\n}\n\n// Rent payment queries\nexport async function getAllRentPayments(): Promise {\n const result = await sqlite.execute(`\n SELECT rp.*, t.name as tenant_name \n FROM rent_payments rp \n JOIN tenants t ON rp.tenant_id = t.id \n ORDER BY rp.due_date DESC\n `);\n return result.rows as RentPayment[];\n}\n\nexport async function createRentPayment(payment: Omit): Promise {\n const result = await sqlite.execute(`\n INSERT INTO rent_payments (tenant_id, amount, due_date, paid_date, payment_method, notes, status)\n VALUES (?, ?, ?, ?, ?, ?, ?)\n `, [payment.tenant_id, payment.amount, payment.due_date, payment.paid_date, payment.payment_method, payment.notes, payment.status]);\n return result.lastInsertRowId as number;\n}\n\nexport async function updateRentPayment(id: number, updates: Partial): Promise {\n const fields = Object.keys(updates).filter(key => key !== 'id' && key !== 'created_at' && key !== 'tenant_name');\n const setClause = fields.map(field => `${field} = ?`).join(', ');\n const values = fields.map(field => updates[field as keyof RentPayment]);\n \n await sqlite.execute(`UPDATE rent_payments SET ${setClause} WHERE id = ?`, [...values, id]);\n}\n\n// Maintenance cost queries\nexport async function getAllMaintenanceCosts(): Promise {\n const result = await sqlite.execute(\"SELECT * FROM maintenance_costs ORDER BY date DESC\");\n return result.rows as MaintenanceCost[];\n}\n\nexport async function createMaintenanceCost(cost: Omit): Promise {\n const result = await sqlite.execute(`\n INSERT INTO maintenance_costs (description, category, amount, date, vendor, unit, receipt_url, tax_deductible)\n VALUES (?, ?, ?, ?, ?, ?, ?, ?)\n `, [cost.description, cost.category, cost.amount, cost.date, cost.vendor, cost.unit, cost.receipt_url, cost.tax_deductible]);\n return result.lastInsertRowId as number;\n}\n\n// Staff payment queries\nexport async function getAllStaffPayments(): Promise {\n const result = await sqlite.execute(\"SELECT * FROM staff_payments ORDER BY payment_date DESC\");\n return result.rows as StaffPayment[];\n}\n\nexport async function createStaffPayment(payment: Omit): Promise {\n const result = await sqlite.execute(`\n INSERT INTO staff_payments (staff_name, role, amount, payment_date, payment_type, hours_worked, hourly_rate, description, tax_form_type)\n VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)\n `, [payment.staff_name, payment.role, payment.amount, payment.payment_date, payment.payment_type, payment.hours_worked, payment.hourly_rate, payment.description, payment.tax_form_type]);\n return result.lastInsertRowId as number;\n}\n\n// Tax category queries\nexport async function getAllTaxCategories(): Promise {\n const result = await sqlite.execute(\"SELECT * FROM tax_categories ORDER BY name\");\n return result.rows as TaxCategory[];\n}\n\n// Financial summary queries\nexport async function getFinancialSummary(): Promise {\n const [rentResult, maintenanceResult, staffResult, pendingResult, taxResult] = await Promise.all([\n sqlite.execute(\"SELECT COALESCE(SUM(amount), 0) as total FROM rent_payments WHERE status = 'paid'\"),\n sqlite.execute(\"SELECT COALESCE(SUM(amount), 0) as total FROM maintenance_costs\"),\n sqlite.execute(\"SELECT COALESCE(SUM(amount), 0) as total FROM staff_payments\"),\n sqlite.execute(\"SELECT COALESCE(SUM(amount), 0) as total FROM rent_payments WHERE status IN ('pending', 'late')\"),\n sqlite.execute(\"SELECT COALESCE(SUM(amount), 0) as total FROM maintenance_costs WHERE tax_deductible = 1\")\n ]);\n\n const totalRentCollected = (rentResult.rows[0] as any).total;\n const totalMaintenanceCosts = (maintenanceResult.rows[0] as any).total;\n const totalStaffPayments = (staffResult.rows[0] as any).total;\n const pendingRent = (pendingResult.rows[0] as any).total;\n const taxDeductibleExpenses = (taxResult.rows[0] as any).total + totalStaffPayments;\n\n return {\n total_rent_collected: totalRentCollected,\n total_maintenance_costs: totalMaintenanceCosts,\n total_staff_payments: totalStaffPayments,\n net_income: totalRentCollected - totalMaintenanceCosts - totalStaffPayments,\n tax_deductible_expenses: taxDeductibleExpenses,\n pending_rent: pendingRent\n };\n}\n\n// Monthly report queries\nexport async function getMonthlyReports(year: number): Promise {\n const result = await sqlite.execute(`\n WITH months AS (\n SELECT 1 as month UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION \n SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION \n SELECT 9 UNION SELECT 10 UNION SELECT 11 UNION SELECT 12\n ),\n rent_income AS (\n SELECT \n strftime('%m', paid_date) as month,\n COALESCE(SUM(amount), 0) as income\n FROM rent_payments \n WHERE strftime('%Y', paid_date) = ? AND status = 'paid'\n GROUP BY strftime('%m', paid_date)\n ),\n maintenance_expenses AS (\n SELECT \n strftime('%m', date) as month,\n COALESCE(SUM(amount), 0) as expenses\n FROM maintenance_costs \n WHERE strftime('%Y', date) = ?\n GROUP BY strftime('%m', date)\n ),\n staff_expenses AS (\n SELECT \n strftime('%m', payment_date) as month,\n COALESCE(SUM(amount), 0) as expenses\n FROM staff_payments \n WHERE strftime('%Y', payment_date) = ?\n GROUP BY strftime('%m', payment_date)\n ),\n tax_deductions AS (\n SELECT \n strftime('%m', date) as month,\n COALESCE(SUM(amount), 0) as deductions\n FROM maintenance_costs \n WHERE strftime('%Y', date) = ? AND tax_deductible = 1\n GROUP BY strftime('%m', date)\n )\n SELECT \n printf('%02d', m.month) as month,\n ? as year,\n COALESCE(ri.income, 0) as rent_income,\n COALESCE(me.expenses, 0) as maintenance_expenses,\n COALESCE(se.expenses, 0) as staff_expenses,\n COALESCE(ri.income, 0) - COALESCE(me.expenses, 0) - COALESCE(se.expenses, 0) as net_profit,\n COALESCE(td.deductions, 0) + COALESCE(se.expenses, 0) as tax_deductions\n FROM months m\n LEFT JOIN rent_income ri ON printf('%02d', m.month) = ri.month\n LEFT JOIN maintenance_expenses me ON printf('%02d', m.month) = me.month\n LEFT JOIN staff_expenses se ON printf('%02d', m.month) = se.month\n LEFT JOIN tax_deductions td ON printf('%02d', m.month) = td.month\n ORDER BY m.month\n `, [year.toString(), year.toString(), year.toString(), year.toString(), year]);\n\n return result.rows as MonthlyReport[];\n}",[],[154,260,252],"database","63b0cab6-3fac-11f0-be38-76b3cceeab13","migrations.ts","63b0cc6e-3fac-11f0-be38-76b3cceeab13",["D",1748867008786],"import { sqlite } from \"https://esm.town/v/stevekrouse/sqlite\";\n\nexport async function runMigrations() {\n console.log(\"Running database migrations...\");\n\n // Tenants table\n await sqlite.execute(`\n CREATE TABLE IF NOT EXISTS tenants (\n id INTEGER PRIMARY KEY AUTOINCREMENT,\n name TEXT NOT NULL,\n email TEXT UNIQUE NOT NULL,\n phone TEXT,\n unit TEXT NOT NULL,\n lease_start DATE NOT NULL,\n lease_end DATE NOT NULL,\n monthly_rent DECIMAL(10,2) NOT NULL,\n security_deposit DECIMAL(10,2) DEFAULT 0,\n created_at DATETIME DEFAULT CURRENT_TIMESTAMP\n )\n `);\n\n // Rent payments table\n await sqlite.execute(`\n CREATE TABLE IF NOT EXISTS rent_payments (\n id INTEGER PRIMARY KEY AUTOINCREMENT,\n tenant_id INTEGER NOT NULL,\n amount DECIMAL(10,2) NOT NULL,\n due_date DATE NOT NULL,\n paid_date DATE,\n payment_method TEXT DEFAULT 'check',\n notes TEXT,\n status TEXT DEFAULT 'pending' CHECK (status IN ('pending', 'paid', 'late', 'partial')),\n created_at DATETIME DEFAULT CURRENT_TIMESTAMP,\n FOREIGN KEY (tenant_id) REFERENCES tenants(id)\n )\n `);\n\n // Maintenance costs table\n await sqlite.execute(`\n CREATE TABLE IF NOT EXISTS maintenance_costs (\n id INTEGER PRIMARY KEY AUTOINCREMENT,\n description TEXT NOT NULL,\n category TEXT NOT NULL,\n amount DECIMAL(10,2) NOT NULL,\n date DATE NOT NULL,\n vendor TEXT,\n unit TEXT,\n receipt_url TEXT,\n tax_deductible BOOLEAN DEFAULT 1,\n created_at DATETIME DEFAULT CURRENT_TIMESTAMP\n )\n `);\n\n // Staff payments table\n await sqlite.execute(`\n CREATE TABLE IF NOT EXISTS staff_payments (\n id INTEGER PRIMARY KEY AUTOINCREMENT,\n staff_name TEXT NOT NULL,\n role TEXT NOT NULL,\n amount DECIMAL(10,2) NOT NULL,\n payment_date DATE NOT NULL,\n payment_type TEXT DEFAULT 'fixed' CHECK (payment_type IN ('hourly', 'fixed', 'commission')),\n hours_worked DECIMAL(5,2),\n hourly_rate DECIMAL(10,2),\n description TEXT,\n tax_form_type TEXT DEFAULT '1099',\n created_at DATETIME DEFAULT CURRENT_TIMESTAMP\n )\n `);\n\n // Tax categories table\n await sqlite.execute(`\n CREATE TABLE IF NOT EXISTS tax_categories (\n id INTEGER PRIMARY KEY AUTOINCREMENT,\n name TEXT UNIQUE NOT NULL,\n description TEXT,\n deductible_percentage DECIMAL(5,2) DEFAULT 100.00\n )\n `);\n\n // Insert default tax categories\n await sqlite.execute(`\n INSERT OR IGNORE INTO tax_categories (name, description, deductible_percentage) VALUES\n ('Repairs & Maintenance', 'Property repairs and maintenance costs', 100.00),\n ('Property Management', 'Property management fees and services', 100.00),\n ('Insurance', 'Property insurance premiums', 100.00),\n ('Utilities', 'Utilities paid by landlord', 100.00),\n ('Advertising', 'Costs to advertise rental property', 100.00),\n ('Legal & Professional', 'Legal and professional service fees', 100.00),\n ('Depreciation', 'Property depreciation expenses', 100.00),\n ('Travel', 'Travel expenses for property management', 100.00)\n `);\n\n console.log(\"Database migrations completed successfully!\");\n}",[],[154,260,262],"63b0d740-3fac-11f0-be38-76b3cceeab13",["D",1748867008786],[171,172],[154,260],["D",1748867008786],[170,173,178],[154],["D",1748866829842],{"_100":99,"_107":103},{"_100":99,"_101":-5,"_102":103,"_18":58,"_104":105,"_106":25},{"_13":89,"_32":90,"_47":40,"_91":48,"_92":-5,"_93":-5,"_94":-5,"_95":-5,"_96":-5,"_97":49,"_98":99},"ctx",{"_82":-5,"_127":312,"_313":314,"_315":316,"_317":25,"_318":25,"_319":25,"_320":62,"_321":-5,"_322":-7,"_323":-7,"_324":66,"_325":25,"_326":25,"_327":-5,"_328":25,"_329":25,"_330":25,"_331":-5},"ENV",{"_283":284,"_285":286,"_287":90,"_288":289,"_290":291,"_292":293,"_294":295,"_296":297,"_298":299,"_300":301,"_302":303,"_304":305,"_306":307,"_308":309,"_310":311},"REACT_APP_SENTRY_DSN","https://586020d86f05489db63a554690acf1e9@o1398359.ingest.sentry.io/6724499","RENDER_GIT_COMMIT","71c1b20804b3232a7e513be260cfffc1932a9e97","RENDER_GIT_BRANCH","POSTHOG_API_TOKEN","phc_nmoyuAkStEZuUp69rYlg1DZgrIt19Gx0Y4Hwf8tGgJP","POSTHOG_API_HOST","https://wart.val.town","WEB_API_TEMPLATE","https://{val}.web.val.run","APP_URL","https://www.val.town","MODULE_URL","https://esm.town","VANITY_WEB_API_TEMPLATE","https://{label}.val.run/","DENOLS_URL","https://lsp.val.town","NODE_ENV","production","CLOUDFLARE_TURNSTILE_SITE_KEY","0x4AAAAAACtaIZJH7s9rBMZ6","GOOGLE_CLIENT_ID","875989696899-jtsqkhlbdv5jrlsk8631p7ipjg3c6o62.apps.googleusercontent.com","STRIPE_PUBLISHABLE_KEY","pk_live_51N2zvWEpZTTLipIkVEwXrUG9AT0yeOOEYxszarZT1jpsZV58Ly6wlg3SltZpHN26CUalOBmUSY7adQRQiSkCZz2500qSXNkf8Y","API_URL","https://api.val.town",[],"preferencesKeybindings","default","preferencesTheme","light","preferencesFixers","preferencesCopilot","preferencesSelectionAi","preferencesCloseBrackets","preferencesChatPanelState","preferencesDprint","preferencesDenofmt","notificationCount","flagDenols","isBusinessOrgMember","businessOrgHandle","isBusinessOrgAdmin","isHigherTownieTier","navCollapsed","currentOrgId"]

Diff · v10