Skip to main content
The schema is a map of the information Prism can query. It lists each type of Yorlet record and the details available on it. Open Prism and choose Schema. Search by table or column name, then hover a column for a short description. You do not need to memorise it — the SQL assistant can choose the right tables and columns for you.

​
Understand the schema

  • A table is a type of record, such as Customers, Invoices, Units, or Tenancies.
  • A column is a detail on that record, such as status, amount, or created_at.
  • id is the unique identifier for one record.
  • A column ending in _id usually connects to another table. For example, building_id connects a Unit to its Building.
  • created_at and updated_at record when the record was created or last updated.
  • null in a result means the detail is empty or does not apply to that record.
If you are not sure where to start, enter this in Ask assistant to edit this query: Which tables do I need to show overdue Invoices with the Customer name? Explain the joins. See SQL and AI for beginners for a plain-language introduction to tables, rows, columns, and queries.

​
Choose the main table

Start with the record you want one row to represent: You can then join another table when you need related information. For example, start with units for one row per Unit, then join buildings to show the Building name.

​
Amounts and times

Money columns are integers in the smallest currency unit (pence for GBP). Divide by 100 when you want pounds. For example, ask the SQL assistant: Show amount_remaining in pounds with two decimal places. Timestamps are UTC. Scheduled queries also run in UTC. If a report is grouped by day, tell the assistant which time zone you want to use.

​
Tenancies and applications

An Application is the leasing pipeline. A Tenancy is the agreement created from that pipeline. Join them with tenancies.application_id = applications.id.
  • Use tenancies for rent roll, active agreements, start and end dates, occupancy, and when the next rent increase is due.
  • Use applications for conversion funnels, open pipeline steps, and Applications that have not yet produced a Tenancy.
  • Use renewal_intents for Renewals and rent reviews that already exist. Join with renewal_intents.source_application_id = tenancies.id.
Do not count Applications and Tenancies as though they are the same thing. An open Application may not have created a Tenancy yet.
Connecting tables is called a join. Common connections include:
  • Units to Buildings: units.building_id = buildings.id
  • Invoices to Customers: invoices.customer_id = customers.id
  • Invoice Items to Invoices: invoice_items.invoice_id = invoices.id
  • Payments to Invoices: payments.invoice_id = invoices.id
  • Subscription Items to Subscriptions: subscription_items.subscription_id = subscriptions.id
  • Balance Transactions to Payouts: balance_transactions.payout_id = payouts.id
  • Owner Payouts to Owners: owner_payouts.owner_id = owners.id
  • Maintenance issues to Units: maintenance_issues.unit_id = units.id
  • Renewal and rent-review records to Tenancies: renewal_intents.source_application_id = tenancies.id
A record may not have every related record. The Templates use joins that keep the main record even when the related detail is missing. Ask the SQL assistant to explain a join if you are unsure why rows disappeared or appeared more than once.

​
Every table includes account

Every table includes account_id. In organisation view, use it to separate records after setting Query scope. Ask the SQL assistant to include account_id, group by account, or filter to a particular account when needed.

​
Tables

​
Portfolio

  • buildings — Properties in the portfolio.
  • units — Lettable units within buildings. Status is occupied, available, or maintenance.
  • tenancies — Agreements with a start date, optional end date, and normalised monthly rent. Status includes pending, active, complete, and canceled. last_rent_increase_effective_date is the last completed rent increase; if it is empty, use date_start. The next notice is due 10 months after that date, and the increase can take effect 12 months after it.

​
Leasing

  • applications — Pipeline records. Use open and current_step_type for funnels. type includes standard and renewal.
  • renewal_intents — Renewals, no-renewals, and rent reviews. For type rent_increase, due_at is the suggested notice date, not the date the new rent takes effect. This table only includes reviews that already exist; use last_rent_increase_effective_date on tenancies to find every Tenancy due a review.

​
Billing

  • customers — People and companies you bill, let to, or otherwise hold on file.
  • invoices — Invoices issued to customers, including rent, charges, and one-off bills. Status includes draft, open, paid, uncollectible, and void.
  • invoice_items — Line items on invoices, including transfer behaviour.
  • subscriptions — Recurring billing agreements, typically rent.
  • subscription_items — Line items on subscriptions.
  • payments — Collected payments. Amounts are in pence.
  • refunds — Refunds against payments.
  • credit_notes — Credit notes against invoices.

​
Finance

  • balance_transactions — Movements on the Yorlet balance, including fees and payouts.
  • payouts — Payouts from the Yorlet balance to your bank account.

​
Owners

  • owners — Landlord, leaseholder, and supplier records.
  • owner_payouts — Payouts from owner balances to owner bank accounts.

​
Maintenance

  • maintenance_issues — Repair and maintenance issues against Units, including status, category, priority, Building, Unit, reporting Customer, and assignee.
Search the Schema tab for a word such as “due”, “rent”, “owner”, or “status”. It searches both table and column names.