A long-term rental operations database needs 22 tables in 7 layers, and its center of gravity is a pair: the move-in inspection and the move-out inspection, run as the same checklist twice, with every security deposit deduction required to point at the finding that documents it. Around that pair sit the lease and its append-only rent ledger, the seeded table of state deposit deadlines, the unit turn as a five-stage state machine, and maintenance. Deposit disputes are won on documentation, so this schema makes an undocumented deduction impossible to insert. This page publishes the full schema in three formats, executed against PostgreSQL before publishing, free to use and adapt.
This is the long-term rental companion to The Hotel Operations Data Model and The STR Operations Data Model, and it exists for the same reason those do: our audit of 1,125 GitHub repositories found open source has solved bookings and listings many times over while the operations layer, the part where condition gets documented and money changes hands over it, stays mostly unbuilt. Long-term rentals sharpen that gap. A hotel room turns every night with the same guest-shaped wear; a rental unit turns once a year or less, and when it does, a thousand dollars or more of deposit rides on the question "was that carpet burn there in January". The schema below is built so that question is a query.
The tenancy is bracketed by two inspections
Everything in a lease's life happens between two documented walkthroughs. The move-in inspection is the baseline; the move-out inspection is the comparison; the deposit disposition is arithmetic over the difference. That is not just good practice, it is increasingly the letter of the law. Massachusetts requires landlords who take a deposit to furnish a written statement of condition near move-in, sworn against later (M.G.L. c. 186, s. 15B, per the statute's text at malegislature.gov). California goes further: according to Civil Code 1950.5 (leginfo.legislature.ca.gov), tenancies beginning July 1, 2025 or later require photographs of the unit at the inception of the tenancy, again when possession is returned, and again after repairs, exactly the before-and-after pairing this model encodes.
rental_applicationleaseinspectionrent_ledger_entryunit_turninspection_itemsecurity_depositThe life of one lease. The two green stops are the same checklist run twice; the last stop is a legal deadline that starts the day the tenant hands back the keys.
The mechanism is deliberately boring. An inspection row is typed move_in, move_out, or routine, and a move-out points at the move-in it is judged against through baseline_inspection_id. Each inspection_item carries a room_label, an item, a condition_rating, notes, and a photo_count, and the pair (room_label, item) is unique within an inspection. That uniqueness is the entire pairing trick: the move-out row for bedroom_1 / carpet joins to the move-in row for bedroom_1 / carpet in one query, no fuzzy matching, no argument. Run the same checklist both times or the join has holes, which is itself a useful discipline.
What the pair query returns. The schema will not accept the first row without photos: an item rated damaged or missing with photo_count = 0 violates a CHECK constraint and is rejected.
The condition vocabulary matters less than the discipline, but it should still be a real vocabulary. The ratings here (new, good, fair, worn, damaged, missing) are CHECK-constrained, in the spirit of how HUD's national physical condition standards at 24 CFR 5.703 (ecfr.gov) frame the bar for its own inspections: housing must be "functionally adequate, operable, and free of health and safety hazards", assessed area by area rather than as one impression. A free-text condition column produces adjectives; adjectives do not survive disputes.
The deposit is a clock with documentation attached
Two facts shape the deposit tables. First, the return deadline is law and it varies wildly by state, so it belongs in a table, not in code. Second, every deduction must be provable, so the schema refuses deductions that are not.
The deposit clock in the eight states seeded into state_deposit_rule, in days after move-out. Every row was verified against the named statute before publishing; the table carries the citation so your application can show its work.
The spread is the argument. According to New York's General Obligations Law 7-108 (nysenate.gov), the itemized statement and refund are due within 14 days of vacancy and blowing the deadline forfeits the right to retain anything. Texas Property Code 92.103 (statutes.capitol.texas.gov) sets the 30th day after surrender. Florida Statute 83.49 (leg.state.fl.us) runs two clocks: 15 days to return when no claim is made, 30 days to give written notice of a claim, then a 15 day window for the tenant to object. Colorado's C.R.S. 38-12-103 (leg.colorado.gov) allows the lease itself to stretch one month to sixty days. Hardcode any one of these and your software is wrong in the other forty-nine states; the model ships them as seeded, statute-cited rows in state_deposit_rule and lets the application ask.
No finding, no deduction
The most opinionated line in the schema is a foreign key. Every deposit_deduction row must reference the inspection_item that documents the condition it charges for, and the column is NOT NULL. Try to insert a deduction without one and PostgreSQL refuses:
INSERT INTO deposit_deduction
(security_deposit_id, inspection_item_id, category, amount, description)
VALUES (:deposit_id, NULL, 'damage', 250, 'carpet burn');
ERROR: null value in column "inspection_item_id" of relation
"deposit_deduction" violates not-null constraintThat refusal is the design. A deduction that cannot point at a documented, photographed finding is exactly the deduction that loses in small claims court, and several statutes now demand the paper trail outright: Washington's RCW 59.18.280 (app.leg.wa.gov) requires the landlord's statement to include copies of estimates or invoices substantiating damage charges, with landlord-performed repairs documented down to time spent and hourly rate, and California requires receipts alongside the itemized statement whenever deductions for repairs and cleaning exceed $125. The schema simply makes the losing move unrepresentable. Two corollaries follow. Unpaid rent never appears as a deduction; it settles in the rent ledger as a deposit_applied entry, because rent owed is a ledger fact, not a condition finding. And wear and tear, which no state allows deducting, arrives naturally as worn ratings that make bad deduction targets and good turn scope.
The turn is a projection of dates
Between the old lease and the new one sits the unit turn, and it is a real workload: apartment turnover ran 46.9 percent even in the pandemic-depressed 2020 reporting year, the lowest in the survey's history, according to the National Apartment Association's survey of operating income and expenses (naahq.org). Roughly half your doors, every year, each one a mini-project with a deadline measured in lost rent days.
The model treats the turn the way the hotel model treats room status: the state is never typed by hand. A unit_turn row records milestone dates (moved_out_on, make_ready_started_on, market_ready_on, and the closing new_lease_id), and status is a stored generated column projected from which milestones exist. CHECK constraints force the milestones to arrive in order, and the legal edges live as seeded rows in unit_turn_transition, so validating a proposed move is one lookup.
The five states of unit_turn.status, generated from the milestone columns beneath them. Setting market_ready_on on a turn that never recorded a move-out violates a CHECK; the milestones must arrive in the order the physical work does.
Turn tasks carry their provenance. A unit_turn_task whose source is move_out_inspection links the inspection_item that raised it, the same item a deposit deduction may point at. That one link answers the question every owner asks about an expensive turn: how much of this was damage the tenant pays for, and how much was refresh the owner funds. The split is a GROUP BY, not a forensic reconstruction.
The model, layer by layer
Twenty-two tables, seven layers. Everything operational references unit; the highlighted tables are where rental tools live or die.
A few decisions worth defending. The rent ledger is append-only: corrections are new adjustment rows, a lease's balance is SUM(amount), and a CHECK constraint enforces the sign convention so a mis-signed payment cannot exist. Even late fees turn out to be jurisdiction-shaped data rather than free parameters: Colorado, for one, bars late fees until rent is at least seven days late and caps them at the greater of $50 or 5 percent of the past-due amount (C.R.S. 38-12-105). Renewals are new lease rows chained by previous_lease_id, never edits, so "what did rent do at this unit over five years" stays answerable. And the application is its own lifecycle with the decision and reason preserved, because "why was this applicant denied" is a question you must be able to answer long after the denial.
What the model deliberately leaves out
- Accounting beyond the lease ledger. Owner statements, trust accounting, and 1099 filing belong to the accounting stack. The ledger here is the operational truth a statement is built from.
- Listings, syndication, and pricing.
market_readyis the handoff to whatever advertises the unit. - Screening report contents. The application row records that screening happened and what was decided. The report itself stays in the screening vendor's system, where its compliance obligations live.
- Automated condition analysis. Whether the move-out photo of
bedroom_1 / carpetactually shows a burn is a computer-vision problem, not a schema problem. The schema's job is to guarantee the evidence exists, is paired against a baseline, and is attached to the deduction that charges for it. The analysis layer on top of exactly this kind of evidence trail is what RapidEye does commercially.
Get the schema
Three formats at stable URLs, free to use, adapt, and build on, with or without attribution. The SQL is not aspirational: it was executed top to bottom against PostgreSQL 14 before publishing, twice, because the file is written to be idempotent and re-running it is a no-op. The JSON and Markdown are derived renderings of the same 22 tables.
# fetch the schema in your format of choice
curl -O https://rapideyeinspections.com/blog/rental-property-data-model/schema.sql
curl -O https://rapideyeinspections.com/blog/rental-property-data-model/schema.md
curl -O https://rapideyeinspections.com/blog/rental-property-data-model/schema.json
# or run it straight into a scratch database
createdb rental_model && curl -s https://rapideyeinspections.com/blog/rental-property-data-model/schema.sql | psql rental_modelIf you are building with an AI agent
Paste this into your agent and let it start from a model that already encodes the domain rules:
Fetch https://rapideyeinspections.com/blog/rental-property-data-model/schema.sql
and use it as the data model for my rental property management tool. Keep the
table names, the generated status columns, and the constraint that every
deposit_deduction references an inspection_item; extend the schema rather than
renaming it. Seed state_deposit_rule with any additional states I operate in,
citing each statute the way the existing rows do.Three things in the schema were designed for agents specifically: unit_turn_transition means a proposed status change is validated with a single lookup instead of re-deriving rental conventions; state_deposit_rule means deadline logic is a JOIN, not a compliance research project; and every enum is a CHECK constraint, so the database itself rejects categories that do not exist in rental operations. If your tool needs live data from a property's existing systems, the RapidEye developer platform is the door to knock on.
Quick FAQ
What tables does a rental property management database need?
Twenty-two tables in seven layers cover the core: property owners, properties, units, and appliances (portfolio); staff, vendors, tenants, and rental applications (people); leases, lease tenants, the append-only rent ledger, and notices (leases and money); inspections and inspection items as paired move-in and move-out checklists; the state deposit-rule reference table, security deposits, and deposit deductions; the unit turn with its seeded transition table and turn tasks; plus maintenance requests and work orders. The full schema is downloadable free from this page.
How should move-in and move-out inspections be structured in a database?
As the same checklist run twice. Each inspection item carries a room label and an item name, unique within its inspection, so the move-out row for a given room and item joins to its move-in twin in one query. The move-out inspection points at the move-in it is compared against through a baseline_inspection_id column. Damage then becomes a delta between two documented states rather than an assertion about one, which is exactly how deposit disputes are decided.
How long does a landlord have to return a security deposit?
It depends entirely on the state, which is why the deadline belongs in a data table rather than in application code. New York gives 14 days (General Obligations Law 7-108), Arizona 14 days excluding weekends and holidays (A.R.S. 33-1321), Florida 15 days to return with no claim and 30 days to notice a claim (Fla. Stat. 83.49), California 21 days (Civil Code 1950.5), and Texas, Washington, Massachusetts, and Colorado 30 days under their own statutes, with Colorado allowing the lease to extend to 60. The schema ships these eight verified rows in a seeded state_deposit_rule table.
How should security deposit deductions be modeled?
Every deduction row should reference the inspection finding that documents the condition it charges for, and in this schema that foreign key is NOT NULL: an undocumented deduction cannot be inserted. Deposit disputes are won on documentation, and statutes like Washington's RCW 59.18.280 require substantiation for every charge. Unpaid rent is settled through the rent ledger as a deposit_applied entry, never as a condition deduction, and normal wear and tear is not deductible at all.
Sources
Sources are named at the publisher level with their root domain, rather than linked or titled; every figure is verifiable at the named source.
- Civil Code 1950.5, California Legislative Informationleginfo.legislature.ca.gov
- Property Code 92.103, Texas Legislature Onlinestatutes.capitol.texas.gov
- General Obligations Law 7-108, New York State Senatenysenate.gov
- Statute 83.49, Florida Legislature (Online Sunshine)leg.state.fl.us
- RCW 59.18.280, Washington State Legislatureapp.leg.wa.gov
- Revised Statutes 33-1321, Arizona State Legislatureazleg.gov
- General Laws c. 186, s. 15B, Massachusetts Legislaturemalegislature.gov
- Revised Statutes 38-12-103 and 38-12-105, Colorado General Assemblyleg.colorado.gov
- 24 CFR 5.703, national physical condition standards for HUD housing, Electronic Code of Federal Regulationsecfr.gov
- Survey of Operating Income and Expenses in Rental Apartment Communities, National Apartment Association, 2021naahq.org