🗄️ Database Schema

This schema powers the RedeemBooks Accounting App, designed for multi-company accounting operations.
It includes full support for Accounts Payable, vendor management, invoice lifecycle, partial payments, audit logging, and more.

---

🔗 Live Diagram

RedeemBooks Database Schema – dbdiagram.io

---

📋 Tables

companies

column name	data type	details
id	integer	primary key
name	varchar	not null
tax_id	varchar	optional
street	varchar	optional
city	varchar	optional
county	varchar	optional
state	varchar	optional
zipcode	varchar	optional
phone	varchar	optional
email	varchar	optional
created_at	timestamp	defaults to now
updated_at	timestamp	auto-updated

---

users

column name	data type	details
id	integer	primary key
username	varchar	not null
email	varchar	not null, unique
hashed_password	varchar	not null
role	varchar	Enum: ('owner', 'manager', 'staff')
created_at	timestamp	defaults to now
updated_at	timestamp	auto-updated

---

user_companies

column name	data type	details
id	integer	primary key
user_id	integer	foreign key → users
company_id	integer	foreign key → companies
created_at	timestamp	defaults to now
updated_at	timestamp	auto-updated

---

approvers

column name	data type	details
id	integer	primary key
user_id	integer	foreign key → users
company_id	integer	foreign key → companies
created_at	timestamp	defaults to now
updated_at	timestamp	auto-updated

---

vendors

column name	data type	details
id	integer	primary key
company_id	integer	foreign key → companies
name	varchar	not null
contact_name	varchar	optional
email	varchar	optional
phone	varchar	optional
tax_id	varchar	optional
street	varchar	optional
city	varchar	optional
county	varchar	optional
state	varchar	optional
zipcode	varchar	optional
preferred_payment_method	varchar	Enum: ('ACH', 'Check', 'Wire')
payment_terms	varchar	e.g., Net 30
w9_document_url	varchar	optional
created_at	timestamp	defaults to now
updated_at	timestamp	auto-updated

---

categories

column name	data type	details
id	integer	primary key
company_id	integer	foreign key → companies
name	varchar	not null
description	text	optional
created_at	timestamp	defaults to now
updated_at	timestamp	auto-updated

---

invoices

column name	data type	details
id	integer	primary key
company_id	integer	foreign key → companies
vendor_id	integer	foreign key → vendors
user_id	integer	foreign key → users
category_id	integer	foreign key → categories
invoice_number	varchar	unique per company
invoice_date	date	not null
voucher_date	date	optional
due_date	date	not null
amount	numeric(10,2)	not null
status	varchar	Enum: ('pending', 'approved', 'paid'), default: pending
approved_by	integer	foreign key → users
approval_date	date	optional
description	text	optional
attachment_url	varchar	optional
created_at	timestamp	defaults to now
updated_at	timestamp	auto-updated

---

payments

column name	data type	details
id	integer	primary key
company_id	integer	foreign key → companies
invoice_id	integer	foreign key → invoices
vendor_id	integer	foreign key → vendors
user_id	integer	foreign key → users
payment_date	date	not null
amount	numeric(10,2)	not null
method	varchar	Enum: ('ACH', 'Check', 'Wire')
reference_number	varchar	optional
notes	text	optional
batch_id	integer	foreign key → payment_batches
created_at	timestamp	defaults to now
updated_at	timestamp	auto-updated

---

payment_batches

column name	data type	details
id	integer	primary key
company_id	integer	foreign key → companies
user_id	integer	foreign key → users
batch_date	date	not null
total_amount	numeric(12,2)	calculated
scheduled_for	date	optional
status	varchar	Enum: ('scheduled', 'processed'), default: scheduled
created_at	timestamp	defaults to now
updated_at	timestamp	auto-updated

---

bank_balances

column name	data type	details
id	integer	primary key
company_id	integer	foreign key → companies
balance	numeric(12,2)	not null
effective_date	date	not null
created_at	timestamp	defaults to now
updated_at	timestamp	auto-updated

---

audit_logs

column name	data type	details
id	integer	primary key
user_id	integer	foreign key → users
company_id	integer	foreign key → companies
action	varchar	Enum: ('create', 'update', 'delete')
table_name	varchar	name of affected table
record_id	integer	affected record
old_data	text	JSON of previous values
new_data	text	JSON of new values
created_at	timestamp	defaults to now