Tables, Types, and TypeScript

The Data Architect Role

Before a single line of code is written, someone has to decide:

• What tables exist?
• What columns does each table have?
• What is the data type of each column?
• How do the tables relate to each other?

This is the Data Architect role. In a large company, this is a dedicated job. As a solo developer, you wear this hat before you wear any other hat.

The most expensive mistake in software development is designing your database wrong and discovering it after you have thousands of rows of live data. Changing a column type or restructuring a relationship on a populated production database requires careful migration planning — sometimes hours of work to avoid data loss.

Design first. Code second. Always.

The Four Standard Columns — Every Table Has These

These four columns appear in every table in Udyogaseva. They should appear in every table you ever create.

id — The primary key. A UUID (universally unique identifier) is generated automatically. Never use sequential integers as IDs in a multi-user system — they are predictable, can be guessed, and cause conflicts when merging data.

created_at — Immutable. Set once when the row is created. Never updated. Useful for "show newest first" sorting, for analytics, and for audit trails.

updated_at — Updated every time any column in the row changes. Tells you when something last changed without needing an audit log.

deleted_at — The soft delete column. This is critical. See below.

Soft Delete — Why You Never Hard-Delete

The scenario: A recruiter accidentally deletes a job posting that has 40 applications. The applications reference that job. They call you in a panic. You check the database. The job row is gone — and so are all 40 application records because of the CASCADE delete.

"I deleted a record by mistake, can you recover it?" You can't. It's gone.

Soft delete solves this. Instead of permanently deleting the row, you set a deleted_at timestamp. The row still exists. The data is intact. You can restore it in seconds. Your application simply filters out soft-deleted rows in every query.

Standard soft-delete columns: deleted_at (timestamptz, null when active) and deleted_by (UUID referencing the user who deleted it).

Queries always filter .is("deleted_at", null). An admin panel shows a Recycle Bin tab where items can be restored. Permanent deletion requires a super_admin role and is a deliberate, irreversible action.

Designing Real Tables — The Udyogaseva Schema

Here is a simplified view of the core Udyogaseva tables and why each design decision was made:

The profiles table — Every user account

Notice:

• id references auth.users(id) — Supabase's authentication system creates the auth record, and profiles extends it with app-specific data. One source of truth.
• role has a CHECK constraint — the database physically prevents invalid role values. You can't accidentally insert role = 'superuser' — the database rejects it.
• UNIQUE on email — one account per email address, enforced at the database level.

The jobs table — Job postings

Notice:

• recruiter_id and company_id are foreign keys — a job must belong to a recruiter and a company.
• CHECK constraints on role_type and status — invalid values are rejected by the database, not just by your form validation.
• NUMERIC(12, 2) for salary — stores up to 12 digits with 2 decimal places. Appropriate for currency. Never use floating-point (float, real) for money.

The job_skills table — Many-to-many junction

ON DELETE CASCADE means: if the job is deleted, all its skill requirements are automatically deleted too. This is appropriate for child records that have no meaning without their parent.

UNIQUE(job_id, skill_id) is a composite unique constraint — the combination of both columns must be unique. You can have the same skill in many jobs, and a job can have many skills — but you can't add the same skill to the same job twice.

TypeScript Types — Making Queries Safe

Without TypeScript types, your queries return any. You don't know what columns the response has. You can write data.tiitle (with a typo) and TypeScript won't complain. The bug only surfaces at runtime.

With generated TypeScript types, the editor catches these mistakes before you run the code.

Generating types from your schema

Run npx supabase gen types typescript --linked every time your schema changes.

This generates a database.types.ts file that describes every table, every column, and every type. Three shapes are generated for every table:

• Row — what SELECT returns
• Insert — what INSERT accepts
• Update — what UPDATE accepts (all fields optional, since you might only update some columns)

Using types in queries

Pass your generated types as generics to Supabase queries. TypeScript will then autocomplete column names and flag typos immediately.

Defining your own types for complex queries

When you select specific columns or join related tables, define custom types for exactly what you're fetching.

Data Types Reference

A complete reference for the data types you will use:

Exercise: Design a CA Client Portal

Before moving to the next section, design the database tables for a simple CA client portal:

Requirements:

• CA firm needs to track clients
• Each client has multiple engagements (GST filing, audit, IT return, etc.)
• Each engagement has documents attached
• Engagements have deadlines and statuses

Your task:

1. List the tables (entities)
2. List the columns for each table with data types
3. Identify the foreign keys (relationships)
4. Identify which columns need indexes

Draft your schema before reading further. There is no single correct answer — but there are clearly wrong answers (duplicate data, missing foreign keys, using text where a CHECK constraint would be better).

A reference design is discussed in the Data Architecture section.