database

db blueprint schema

Database (PostgreSQL)

• Table: persons

  id SERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  email TEXT,
  company TEXT,
  mobile TEXT,
  details TEXT,
  company_size INT,
  url TEXT,
  location TEXT,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
  

cmds

CREATE DATABASE daybuddy;

CREATE ROLE daybuddy_user WITH LOGIN PASSWORD 'Dayb@4321#8767!';

GRANT CONNECT ON DATABASE daybuddy TO daybuddy_user;

GRANT TEMP ON DATABASE daybuddy TO daybuddy_user;


CREATE TABLE IF NOT EXISTS public.persons (
  id SERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  email TEXT,
  company TEXT,
  mobile TEXT,
  details TEXT,
  company_size INTEGER,
  url TEXT,
  location TEXT,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

GRANT USAGE ON SCHEMA public TO daybuddy_user;

GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE public.persons TO daybuddy_user;

GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO daybuddy_user;

CREATE INDEX IF NOT EXISTS idx_persons_email ON public.persons (email);
CREATE INDEX IF NOT EXISTS idx_persons_company_size ON public.persons (company_size);
CREATE INDEX IF NOT EXISTS idx_persons_created_at ON public.persons (created_at);

INSERT INTO public.persons (name) VALUES ('Alice Example');

INSERT INTO public.persons (name, email, company, mobile, details, company_size, url, location)
VALUES (
  'John Doe',
  '[email protected]',
  'Acme Corp',
  '[phone]',
  'Lead contact for Acme vendor integration',
  120,
  'https://acme.example.com',
  'Mumbai, India'
);

extra

-- 1) Create database and a dedicated DB user (run as postgres superuser)
CREATE DATABASE daybuddy;

-- create a login user (change the password)
CREATE ROLE daybuddy_user WITH LOGIN PASSWORD 'change_me_strong_password';

-- allow the user to connect to the database
GRANT CONNECT ON DATABASE daybuddy TO daybuddy_user;

-- (optional) if you want the user to create temporary tables:
GRANT TEMP ON DATABASE daybuddy TO daybuddy_user;


-- 2) Connect to the new DB (in psql you can use: \c daybuddy)
-- From CLI you can also run: psql -d daybuddy -U postgres -f create_table.sql


-- 3) Create the persons table (public schema)
CREATE TABLE IF NOT EXISTS public.persons (
  id SERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  email TEXT,
  company TEXT,
  mobile TEXT,
  details TEXT,
  company_size INTEGER,
  url TEXT,
  location TEXT,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- 4) Grant privileges on the table (so the daybuddy_user can read/write)
GRANT USAGE ON SCHEMA public TO daybuddy_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE public.persons TO daybuddy_user;

-- Ensure the user can use the sequence backing the SERIAL column
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO daybuddy_user;


-- 5) Useful indexes (optional but recommended for reads/filters)
CREATE INDEX IF NOT EXISTS idx_persons_email ON public.persons (email);
CREATE INDEX IF NOT EXISTS idx_persons_company_size ON public.persons (company_size);
CREATE INDEX IF NOT EXISTS idx_persons_created_at ON public.persons (created_at);

-- If you expect lots of searches by name prefix, consider:
-- CREATE INDEX IF NOT EXISTS idx_persons_name_trgm ON public.persons USING gin (name gin_trgm_ops);
-- (requires extension: CREATE EXTENSION IF NOT EXISTS pg_trgm;)


-- 6) Optional helpful constraints (commented — enable if you want more validation)
-- Example email format check (simple, not RFC-perfect):
-- ALTER TABLE public.persons
--   ADD CONSTRAINT chk_persons_email_format CHECK (email IS NULL OR email ~* '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$');

-- Example mobile basic validation (allow international + digits, 7-15 chars):
-- ALTER TABLE public.persons
--   ADD CONSTRAINT chk_persons_mobile_format CHECK (mobile IS NULL OR mobile ~* '^\+?[0-9]{7,15}$');

-- Example company_size must be non-negative if provided:
-- ALTER TABLE public.persons
--   ADD CONSTRAINT chk_company_size_non_negative CHECK (company_size IS NULL OR company_size >= 0);


-- 7) Sample DML you will use while developing/testing

-- Insert a row (all optional fields omitted except name)
INSERT INTO public.persons (name) VALUES ('Alice Example');

-- Insert a full row
INSERT INTO public.persons (name, email, company, mobile, details, company_size, url, location)
VALUES (
  'John Doe',
  '[email protected]',
  'Acme Corp',
  '[phone]',
  'Lead contact for Acme vendor integration',
  120,
  'https://acme.example.com',
  'Mumbai, India'
);

-- Read all persons (latest first)
SELECT * FROM public.persons
ORDER BY created_at DESC;

-- Read a single person by id
SELECT * FROM public.persons WHERE id = 1;

-- Update a person
UPDATE public.persons
SET email = '[email protected]', company = 'Acme Co'
WHERE id = 1;

-- Delete a person
DELETE FROM public.persons WHERE id = 1;


-- 8) Helpful admin queries

-- Show table structure
\d+ public.persons

-- Show sequences (to check SERIAL)
SELECT sequence_name FROM information_schema.sequences WHERE sequence_schema = 'public';

-- Revoke all privileges (if needed)
-- REVOKE ALL PRIVILEGES ON TABLE public.persons FROM PUBLIC;