vanilla-rails-data-modeling

Vanilla Rails Data Modeling

Database schema conventions from production 37signals patterns.

For state-as-records pattern details, see vanilla-rails-models.

UUID Primary Keys

All tables use UUIDs. No auto-incrementing integers.

create_table :cards, id: :uuid do |t|
  t.uuid :account_id, null: false
  t.string :title
  t.timestamps
end

UUIDv7 (timestamp-ordered), base36 encoded as 25-character strings. No ID enumeration, merge-safe, no sequence contention.

Multi-Tenancy via account_id

Every tenant-scoped table has account_id. No exceptions for user data.

Tables WITHOUT account_id (global/cross-tenant): identities, sessions, magic_links

Scope queries via Current.account:

class ApplicationRecord < ActiveRecord::Base
  def self.default_scope
    where(account_id: Current.account.id) if Current.account
  end
end

Don't forget account_id on join tables.

No Foreign Key Constraints

Use application-level integrity, not database constraints.

# Bad
t.references :card, foreign_key: true

# Good
t.uuid :card_id, null: false
add_index :table, :card_id

Prevents deadlocks during bulk operations. Maintain integrity via dependent: :destroy.

Index Strategy

Pattern	Rule
Composite indexes	Lead with account_id
Polymorphic	Always [type, id]
Binary state	unique: true on parent_id
Per-user state	unique: [parent_id, user_id]
Tenant uniqueness	[:account_id, :field]

add_index :cards, [:account_id, :status]
add_index :events, [:eventable_type, :eventable_id]
add_index :closures, :card_id, unique: true
add_index :pins, [:card_id, :user_id], unique: true

Join Table Patterns

Need	Pattern	Has ID?	Has account_id?
Just link two things	HABTM (id: false)	No	No
Track when/who linked	has_many :through	Yes (id: :uuid)	Yes

HABTM naming: plural_plural alphabetically (boards_filters)

Through naming: Singular noun (taggings, assignments)

# HABTM - no metadata needed
create_table :boards_filters, id: false do |t|
  t.uuid :board_id, null: false
  t.uuid :filter_id, null: false
end

# Through - timestamps, account scoping
create_table :taggings, id: :uuid do |t|
  t.uuid :account_id, null: false
  t.uuid :card_id, null: false
  t.uuid :tag_id, null: false
  t.timestamps
end

Polymorphic Associations

Use semantic names describing the relationship:

Name	Meaning
eventable	thing the event is about
source	where it came from
container	what holds it
searchable	what is searchable
recordable	what it's attached to

Counter Caches

Manual increment!/decrement!, not Rails counter_cache: option:

after_create :increment_account_counter

private
  def increment_account_counter
    account.increment!(:cards_count)
  end

Settings Tables

Polymorphic config with inheritance fallback:

class Board < ApplicationRecord
  def auto_postpone_period
    entropy&.auto_postpone_period || account.auto_postpone_period
  end
end

Migration Conventions

Rule	Example
Prefer change	def change; add_column ...; end
Explicit UUID refs	t.uuid :card_id not t.references :card
Large table indexes	add_index :table, :col, algorithm: :concurrently
up/down only when irreversible	remove_column in up

Quick Reference

Decision	Pattern
Primary key	id: :uuid always
Tenant column	account_id on all tenant tables
Foreign keys	None — app-level integrity
Simple join	id: false, no account_id
Rich join	id: :uuid, with account_id
Polymorphic index	[type, id] compound
Query index	Lead with account_id
Counter cache	Manual increment!

Sharding (Advanced)

For large tables, shard by account using CRC32:

def shard_for(account_id)
  Zlib.crc32(account_id.to_s) % 16
end

16 identical tables, MySQL native fulltext across shards.