| column | data type | nullable? | key type | notes |
|---|---|---|---|---|
| public_id | char(10) | N | primary | server-side generated, unique hash, alphanumeric |
The id table provides a way of generating unique ids for both landlords and tenants. Whenever a new landlord or tenant is created, the server generates a random ten-character string, then attempts to insert it into the db, repeating until it succeeds. This prevents leaking user numbers.
| column | data type | nullable? | key type | notes |
|---|---|---|---|---|
| landlord_id | char(10) | N | primary | N/A |
| contact_name | varchar | N | N/A | N/A |
| contact_number | varchar | N | N/A | phone number |
Likely need more info.
| column | data type | nullable? | key type | notes |
|---|---|---|---|---|
| tenant_id | char(10) | N | primary | N/A |
| contact_name | varchar | N | N/A | N/A |
| contact_number | varchar | N | N/A | phone number |
Likely need more info.
| column | data type | nullable? | key type | notes |
|---|---|---|---|---|
| tenant_notes_id | bigint | N | primary | db-provided, auto-inc |
| notes | varchar | N | N/A | An info dump on the tenant, provided by landlord |
| column | data type | nullable? | key type | notes |
|---|---|---|---|---|
| landlord_id | char(10) N | fk(landlord.landlord_id) | N/A | |
| tenant_id | char(10) | N | fk(tenant.tenant_id) | N/A |
| tenant_notes_id | N | fk(tenant_notes.tenant_notes_id) | N/A |
PrimaryKey(landlord_id, tenant_id, tenant_notes_id)
These allows landlords to create custom notes for each tenant, potentially multiple
| column | data type | nullable? | key type | notes |
|---|---|---|---|---|
| payment_due_id | bigint | N | primary | db-provided, auto-inc |
| lease_id | bigint | N | fk(lease.lease_id) | N/A |
| amount | varchar | N | N/A | big decimal represented as text, USD |
| type | varchar | N | N/A | [Rent,LateFee,Manual] |
| due_date | datetime | Y | N/A | UTC |
| description | varchar | N | N/A | human-friendly description of what these charges are for (September rent, September late fee) |
| status | varchar | N | N/A | [Active,Cancelled,Fulfilled,Overdue] |
| landlord_notes | varchar | Y | N/A | Landlord-provided details, if any |
payment_due is the "bill" that the tenant's see. Server validates that any payments made don't overpay the amount due. Underpayment is perfectly fine. Whenever a payment is created or updated, or whenever a payment_due is updated, the status is re-calculated based on existing payments, in combination with current status (cancelled or fulfilled payment_dues cannot be changed)
| column | data type | nullable? | key type | notes |
|---|---|---|---|---|
| payment_method_id | bigint | N | primary | db-provided, auto-inc |
| name | varchar | N | unique | friendly name of the payment method (venmo, paypal, ad-hoc) |
This table is primarily for data cleanliness - we want every payment to have a method attached to it. Hardcoded strings can change in server code pretty easily. Making a table for payment methods and having payment.method point into it means that any changes to payment metohds will be a very conscious thing, and won't be done by accident.
| column | data type | nullable? | key type | notes |
|---|---|---|---|---|
| payment_id | bigint | N | primary | db-provided,auto-inc |
| tenant_id | char(10) | N | fk(tenant.tenant_id) | who made the payment |
| payment_due_id | bigint | N | fk(payment_due.payment_due_id) | what the payment was for |
| payment_date | datetime | N | N/A | Time payment submitted |
| amount | varchar | N | N/A | bigdecimal represented as text, USD |
| method | varchar | N | fk(payment_method.name) | how the payment was made |
| status | varchar | N | N/A | [Pending,Processing,Declined,Approved,Canceled] |
| tenant_notes | varchar | Y | N/A | Whatever kind of cute messages the tenant wants to attach, visible to anyone with access to the payment_due |
| column | data type | nullable? | key type | notes |
|---|---|---|---|---|
| lease_id | bigint | N | primary | db-provided, auto-inc |
| landlord_id | char(10) | N | fk(landlord.landlord_id) | N/A |
| start_date | datetime | N | N/A | Start of the lease, should be a localdate |
| end_date | datetime | Y | N/A | End of the lease, should be a localdate |
| valid | bool | N | N/A | If the lease is currently valid (not canceled) |
| late_fee_policy | varchar | Y | N/A | Big jumble of text, provided by the landlord. |
| day_of_month_due | byte | Y | N/A | What day of the month payment is expected |
| column | data type | nullable? | key type | notes |
|---|---|---|---|---|
| lease_breakdown_id | bigint | N | primary | db-provided, auto-inc |
| lease_id | bigint | N | fk(lease.lease_id) | what lease this is for |
| amount | varchar | N | N/A | bigdecimal represented as text, USD |
| description | varchar | Y | N/A | what this charge is for |
The lease breakdown table explains how the total amount the tenant owes every month is due. For one-off payments, like application fee, payment_dues can be manually created. This is primarily for rolling up a monthly bill for the tenants.
| column | data type | nullable? | key type | notes |
|---|---|---|---|---|
| tenant_id | char(10) | N | fk(tenant.tenant_id) | the tenant... |
| lease_id | bigint | N | fk(lease.lease_id) | and the lease |
primary key(tenant_id, lease_id)
Maps tenants to their leases. This allows for roomates, or tenants that have multiple leases, for whatever reason.
TODO: Credit, overpayment TODO: History tables. We want to almost every table above to have an audit trail of changes
- payment_history_id (db-provided,bigint,auto-inc)
- payment_id (non-null)
- change_date (datetime) (non-null)
- change_author (varchar) (non-null)
- previous_status (varchar) (non-null)
- new_status (varchar) (non-null)
- previuos_amount (big decimal?) (non-null)
- current_amount (big decimal?) (non-null)
- previous_lease_id (FK) (non-null)
- current_lease_id (FK) (non-null)
- previous_notes_