Re: content management in emacs

[Jean Louis]

* Ian Garmaise <ian.g@phorixsol.com> [2021-02-27 21:59]:
> Hi Bob,
> 
> How do you send mail to mailing lists with BBDB?

I have complete mailing list system in Emacs that uses the
database. Sending to a list without recording who received which email
is leading sooner or later to nonsensical situations that ruin
relations with people. When somebody wish to setup email, and has
patience, I will help with installation until it starts working and
until the user becomes able to create and send first email series.

One has to track which person received which email at what time.

Imagine the timeline of people being entered into any kind of a
database, in January there are 30 people, they need to receive maybe
2-3 emails named A, B, and C each week, but in February there are
other 22 people who need to receive 2-3 emails A, B, C and the first
30 people from January have to receieve emails D, E, F, G in
February.

I am including here the dump of the `emails` table in the database and
if other tables are required, I will send it.

The `mailings` table below is tracking which person received which
email at what time. Only by defining the periods between emails and
tracking emails in the database one can make a series of follow-up
emails that are logical and useful in sales or relation building.

We sell for hundreds of thousands of dollars by using this
system. There is no need for remote databases or keeping contact lists
on remote servers.

Emails are dispatched to the local computer's queue, and then
forwarded to remote SMTP server. Currently I am using `msmtp` to
forward bulk emails.

Jean

--
-- PostgreSQL database dump
--

-- Dumped from database version 13.0
-- Dumped by pg_dump version 13.0

SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SELECT pg_catalog.set_config('search_path', '', false);
SET check_function_bodies = false;
SET xmloption = content;
SET client_min_messages = warning;
SET row_security = off;

SET default_tablespace = '';

SET default_table_access_method = heap;

--
-- Name: emails; Type: TABLE; Schema: public; Owner: maddox
--

CREATE TABLE public.emails (
    emails_id integer NOT NULL,
    emails_datecreated timestamp without time zone DEFAULT now(),
    emails_datemodified timestamp without time zone,
    emails_date timestamp with time zone,
    emails_time time without time zone,
    emails_account integer,
    emails_contact integer,
    emails_opportunity integer,
    emails_assignedto integer,
    emails_subject text NOT NULL,
    emails_body text,
    emails_sent boolean DEFAULT true,
    emails_templates integer NOT NULL,
    emails_mailinglist integer,
    emails_priority integer DEFAULT 1000 NOT NULL,
    emails_emailtypes integer DEFAULT 1 NOT NULL,
    emails_delay text DEFAULT 7 NOT NULL,
    emails_intervals integer DEFAULT 2 NOT NULL,
    emails_languages integer DEFAULT 1 NOT NULL,
    emails_periodic boolean DEFAULT false,
    emails_blastnow boolean DEFAULT false NOT NULL,
    emails_blastonce boolean DEFAULT false NOT NULL,
    emails_maxdate timestamp with time zone,
    emails_maxtime text,
    emails_timezones integer,
    emails_weekdays integer,
    emails_includecountries integer,
    emails_excludecountries integer,
    emails_pages integer,
    emails_emailformats integer DEFAULT 1 NOT NULL,
    emails_marks integer,
    emails_city text,
    emails_countries integer,
    emails_excludemarks integer
);


ALTER TABLE public.emails OWNER TO maddox;

--
-- Name: TABLE emails; Type: COMMENT; Schema: public; Owner: maddox
--

COMMENT ON TABLE public.emails IS 'E-mails';


--
-- Name: COLUMN emails.emails_id; Type: COMMENT; Schema: public; Owner: maddox
--

COMMENT ON COLUMN public.emails.emails_id IS 'ID';


--
-- Name: COLUMN emails.emails_datecreated; Type: COMMENT; Schema: public; 
Owner: maddox
--

COMMENT ON COLUMN public.emails.emails_datecreated IS 'Date created';


--
-- Name: COLUMN emails.emails_datemodified; Type: COMMENT; Schema: public; 
Owner: maddox
--

COMMENT ON COLUMN public.emails.emails_datemodified IS 'Date modified';


--
-- Name: COLUMN emails.emails_date; Type: COMMENT; Schema: public; Owner: maddox
--

COMMENT ON COLUMN public.emails.emails_date IS 'Date';


--
-- Name: COLUMN emails.emails_time; Type: COMMENT; Schema: public; Owner: maddox
--

COMMENT ON COLUMN public.emails.emails_time IS 'Time';


--
-- Name: COLUMN emails.emails_account; Type: COMMENT; Schema: public; Owner: 
maddox
--

COMMENT ON COLUMN public.emails.emails_account IS 'Account';


--
-- Name: COLUMN emails.emails_contact; Type: COMMENT; Schema: public; Owner: 
maddox
--

COMMENT ON COLUMN public.emails.emails_contact IS 'Contact';


--
-- Name: COLUMN emails.emails_opportunity; Type: COMMENT; Schema: public; 
Owner: maddox
--

COMMENT ON COLUMN public.emails.emails_opportunity IS 'Opportunity';


--
-- Name: COLUMN emails.emails_assignedto; Type: COMMENT; Schema: public; Owner: 
maddox
--

COMMENT ON COLUMN public.emails.emails_assignedto IS 'Assigned to';


--
-- Name: COLUMN emails.emails_subject; Type: COMMENT; Schema: public; Owner: 
maddox
--

COMMENT ON COLUMN public.emails.emails_subject IS 'Subject';


--
-- Name: COLUMN emails.emails_body; Type: COMMENT; Schema: public; Owner: maddox
--

COMMENT ON COLUMN public.emails.emails_body IS 'Body';


--
-- Name: COLUMN emails.emails_sent; Type: COMMENT; Schema: public; Owner: maddox
--

COMMENT ON COLUMN public.emails.emails_sent IS 'Sent';


--
-- Name: COLUMN emails.emails_templates; Type: COMMENT; Schema: public; Owner: 
maddox
--

COMMENT ON COLUMN public.emails.emails_templates IS 'Template';


--
-- Name: COLUMN emails.emails_mailinglist; Type: COMMENT; Schema: public; 
Owner: maddox
--

COMMENT ON COLUMN public.emails.emails_mailinglist IS 'Mailing List';


--
-- Name: COLUMN emails.emails_priority; Type: COMMENT; Schema: public; Owner: 
maddox
--

COMMENT ON COLUMN public.emails.emails_priority IS 'Priority';


--
-- Name: COLUMN emails.emails_emailtypes; Type: COMMENT; Schema: public; Owner: 
maddox
--

COMMENT ON COLUMN public.emails.emails_emailtypes IS 'Type';


--
-- Name: COLUMN emails.emails_delay; Type: COMMENT; Schema: public; Owner: 
maddox
--

COMMENT ON COLUMN public.emails.emails_delay IS 'Delay';


--
-- Name: COLUMN emails.emails_intervals; Type: COMMENT; Schema: public; Owner: 
maddox
--

COMMENT ON COLUMN public.emails.emails_intervals IS 'Interval';


--
-- Name: COLUMN emails.emails_languages; Type: COMMENT; Schema: public; Owner: 
maddox
--

COMMENT ON COLUMN public.emails.emails_languages IS 'Language';


--
-- Name: COLUMN emails.emails_periodic; Type: COMMENT; Schema: public; Owner: 
maddox
--

COMMENT ON COLUMN public.emails.emails_periodic IS 'Periodic';


--
-- Name: COLUMN emails.emails_blastnow; Type: COMMENT; Schema: public; Owner: 
maddox
--

COMMENT ON COLUMN public.emails.emails_blastnow IS 'Blast now';


--
-- Name: COLUMN emails.emails_blastonce; Type: COMMENT; Schema: public; Owner: 
maddox
--

COMMENT ON COLUMN public.emails.emails_blastonce IS 'Blast once';


--
-- Name: COLUMN emails.emails_maxdate; Type: COMMENT; Schema: public; Owner: 
maddox
--

COMMENT ON COLUMN public.emails.emails_maxdate IS 'Max date';


--
-- Name: COLUMN emails.emails_maxtime; Type: COMMENT; Schema: public; Owner: 
maddox
--

COMMENT ON COLUMN public.emails.emails_maxtime IS 'Max time';


--
-- Name: COLUMN emails.emails_timezones; Type: COMMENT; Schema: public; Owner: 
maddox
--

COMMENT ON COLUMN public.emails.emails_timezones IS 'Time zone';


--
-- Name: COLUMN emails.emails_weekdays; Type: COMMENT; Schema: public; Owner: 
maddox
--

COMMENT ON COLUMN public.emails.emails_weekdays IS 'Weekday';


--
-- Name: COLUMN emails.emails_includecountries; Type: COMMENT; Schema: public; 
Owner: maddox
--

COMMENT ON COLUMN public.emails.emails_includecountries IS 'Include countries';


--
-- Name: COLUMN emails.emails_excludecountries; Type: COMMENT; Schema: public; 
Owner: maddox
--

COMMENT ON COLUMN public.emails.emails_excludecountries IS 'Exclude countries';


--
-- Name: COLUMN emails.emails_pages; Type: COMMENT; Schema: public; Owner: 
maddox
--

COMMENT ON COLUMN public.emails.emails_pages IS 'Page to send';


--
-- Name: COLUMN emails.emails_emailformats; Type: COMMENT; Schema: public; 
Owner: maddox
--

COMMENT ON COLUMN public.emails.emails_emailformats IS 'Format';


--
-- Name: COLUMN emails.emails_marks; Type: COMMENT; Schema: public; Owner: 
maddox
--

COMMENT ON COLUMN public.emails.emails_marks IS 'Marked by';


--
-- Name: COLUMN emails.emails_city; Type: COMMENT; Schema: public; Owner: maddox
--

COMMENT ON COLUMN public.emails.emails_city IS 'City';


--
-- Name: COLUMN emails.emails_countries; Type: COMMENT; Schema: public; Owner: 
maddox
--

COMMENT ON COLUMN public.emails.emails_countries IS 'Country';


--
-- Name: COLUMN emails.emails_excludemarks; Type: COMMENT; Schema: public; 
Owner: maddox
--

COMMENT ON COLUMN public.emails.emails_excludemarks IS 'Exclude marked as';


--
-- Name: emails_emails_id_seq; Type: SEQUENCE; Schema: public; Owner: maddox
--

CREATE SEQUENCE public.emails_emails_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE public.emails_emails_id_seq OWNER TO maddox;

--
-- Name: emails_emails_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: 
maddox
--

ALTER SEQUENCE public.emails_emails_id_seq OWNED BY public.emails.emails_id;


--
-- Name: emails emails_id; Type: DEFAULT; Schema: public; Owner: maddox
--

ALTER TABLE ONLY public.emails ALTER COLUMN emails_id SET DEFAULT 
nextval('public.emails_emails_id_seq'::regclass);


--
-- Name: emails emails_pkey; Type: CONSTRAINT; Schema: public; Owner: maddox
--

ALTER TABLE ONLY public.emails
    ADD CONSTRAINT emails_pkey PRIMARY KEY (emails_id);


--
-- Name: emails emails_emails_account_fkey; Type: FK CONSTRAINT; Schema: 
public; Owner: maddox
--

ALTER TABLE ONLY public.emails
    ADD CONSTRAINT emails_emails_account_fkey FOREIGN KEY (emails_account) 
REFERENCES public.accounts(accounts_id);


--
-- Name: emails emails_emails_assignedto_fkey; Type: FK CONSTRAINT; Schema: 
public; Owner: maddox
--

ALTER TABLE ONLY public.emails
    ADD CONSTRAINT emails_emails_assignedto_fkey FOREIGN KEY 
(emails_assignedto) REFERENCES public.people(people_id);


--
-- Name: emails emails_emails_contact_fkey; Type: FK CONSTRAINT; Schema: 
public; Owner: maddox
--

ALTER TABLE ONLY public.emails
    ADD CONSTRAINT emails_emails_contact_fkey FOREIGN KEY (emails_contact) 
REFERENCES public.people(people_id);


--
-- Name: emails emails_emails_countries_fkey; Type: FK CONSTRAINT; Schema: 
public; Owner: maddox
--

ALTER TABLE ONLY public.emails
    ADD CONSTRAINT emails_emails_countries_fkey FOREIGN KEY (emails_countries) 
REFERENCES public.countries(countries_id);


--
-- Name: emails emails_emails_emailformats_fkey; Type: FK CONSTRAINT; Schema: 
public; Owner: maddox
--

ALTER TABLE ONLY public.emails
    ADD CONSTRAINT emails_emails_emailformats_fkey FOREIGN KEY 
(emails_emailformats) REFERENCES public.emailformats(emailformats_id);


--
-- Name: emails emails_emails_emailtypes_fkey; Type: FK CONSTRAINT; Schema: 
public; Owner: maddox
--

ALTER TABLE ONLY public.emails
    ADD CONSTRAINT emails_emails_emailtypes_fkey FOREIGN KEY 
(emails_emailtypes) REFERENCES public.emailtypes(emailtypes_id);


--
-- Name: emails emails_emails_excludecountries_fkey; Type: FK CONSTRAINT; 
Schema: public; Owner: maddox
--

ALTER TABLE ONLY public.emails
    ADD CONSTRAINT emails_emails_excludecountries_fkey FOREIGN KEY 
(emails_excludecountries) REFERENCES public.countrylists(countrylists_id);


--
-- Name: emails emails_emails_excludemarks_fkey; Type: FK CONSTRAINT; Schema: 
public; Owner: maddox
--

ALTER TABLE ONLY public.emails
    ADD CONSTRAINT emails_emails_excludemarks_fkey FOREIGN KEY 
(emails_excludemarks) REFERENCES public.marks(marks_id);


--
-- Name: emails emails_emails_includecountries_fkey; Type: FK CONSTRAINT; 
Schema: public; Owner: maddox
--

ALTER TABLE ONLY public.emails
    ADD CONSTRAINT emails_emails_includecountries_fkey FOREIGN KEY 
(emails_includecountries) REFERENCES public.countrylists(countrylists_id);


--
-- Name: emails emails_emails_intervals_fkey; Type: FK CONSTRAINT; Schema: 
public; Owner: maddox
--

ALTER TABLE ONLY public.emails
    ADD CONSTRAINT emails_emails_intervals_fkey FOREIGN KEY (emails_intervals) 
REFERENCES public.intervals(intervals_id);


--
-- Name: emails emails_emails_languages_fkey; Type: FK CONSTRAINT; Schema: 
public; Owner: maddox
--

ALTER TABLE ONLY public.emails
    ADD CONSTRAINT emails_emails_languages_fkey FOREIGN KEY (emails_languages) 
REFERENCES public.languages(languages_id);


--
-- Name: emails emails_emails_mailinglist_fkey; Type: FK CONSTRAINT; Schema: 
public; Owner: maddox
--

ALTER TABLE ONLY public.emails
    ADD CONSTRAINT emails_emails_mailinglist_fkey FOREIGN KEY 
(emails_mailinglist) REFERENCES public.accounts(accounts_id);


--
-- Name: emails emails_emails_marks_fkey; Type: FK CONSTRAINT; Schema: public; 
Owner: maddox
--

ALTER TABLE ONLY public.emails
    ADD CONSTRAINT emails_emails_marks_fkey FOREIGN KEY (emails_marks) 
REFERENCES public.marks(marks_id);


--
-- Name: emails emails_emails_opportunity_fkey; Type: FK CONSTRAINT; Schema: 
public; Owner: maddox
--

ALTER TABLE ONLY public.emails
    ADD CONSTRAINT emails_emails_opportunity_fkey FOREIGN KEY 
(emails_opportunity) REFERENCES public.opportunities(opportunities_id);


--
-- Name: emails emails_emails_pages_fkey; Type: FK CONSTRAINT; Schema: public; 
Owner: maddox
--

ALTER TABLE ONLY public.emails
    ADD CONSTRAINT emails_emails_pages_fkey FOREIGN KEY (emails_pages) 
REFERENCES public.pages(pages_id);


--
-- Name: emails emails_emails_templates_fkey; Type: FK CONSTRAINT; Schema: 
public; Owner: maddox
--

ALTER TABLE ONLY public.emails
    ADD CONSTRAINT emails_emails_templates_fkey FOREIGN KEY (emails_templates) 
REFERENCES public.templates(templates_id);


--
-- Name: emails emails_emails_timezones_fkey; Type: FK CONSTRAINT; Schema: 
public; Owner: maddox
--

ALTER TABLE ONLY public.emails
    ADD CONSTRAINT emails_emails_timezones_fkey FOREIGN KEY (emails_timezones) 
REFERENCES public.timezones(timezones_id);


--
-- Name: emails emails_emails_weekdays_fkey; Type: FK CONSTRAINT; Schema: 
public; Owner: maddox
--

ALTER TABLE ONLY public.emails
    ADD CONSTRAINT emails_emails_weekdays_fkey FOREIGN KEY (emails_weekdays) 
REFERENCES public.weekdays(weekdays_id);


--
-- PostgreSQL database dump complete
--

--
-- PostgreSQL database dump
--

-- Dumped from database version 13.0
-- Dumped by pg_dump version 13.0

SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SELECT pg_catalog.set_config('search_path', '', false);
SET check_function_bodies = false;
SET xmloption = content;
SET client_min_messages = warning;
SET row_security = off;

SET default_tablespace = '';

SET default_table_access_method = heap;

--
-- Name: mailings; Type: TABLE; Schema: public; Owner: maddox
--

CREATE TABLE public.mailings (
    mailings_id integer NOT NULL,
    mailings_datecreated timestamp without time zone DEFAULT now() NOT NULL,
    mailings_usercreated text DEFAULT "current_user"() NOT NULL,
    mailings_contacts integer NOT NULL,
    mailings_mailingtypes integer DEFAULT 1 NOT NULL,
    mailings_fromcompany integer NOT NULL,
    mailings_subject text NOT NULL,
    mailings_description text,
    mailings_emails integer,
    mailings_mailingstatuses integer
);


ALTER TABLE public.mailings OWNER TO maddox;

--
-- Name: TABLE mailings; Type: COMMENT; Schema: public; Owner: maddox
--

COMMENT ON TABLE public.mailings IS 'Mailings';


--
-- Name: COLUMN mailings.mailings_id; Type: COMMENT; Schema: public; Owner: 
maddox
--

COMMENT ON COLUMN public.mailings.mailings_id IS 'ID';


--
-- Name: COLUMN mailings.mailings_datecreated; Type: COMMENT; Schema: public; 
Owner: maddox
--

COMMENT ON COLUMN public.mailings.mailings_datecreated IS 'Date created';


--
-- Name: COLUMN mailings.mailings_usercreated; Type: COMMENT; Schema: public; 
Owner: maddox
--

COMMENT ON COLUMN public.mailings.mailings_usercreated IS 'User created';


--
-- Name: COLUMN mailings.mailings_contacts; Type: COMMENT; Schema: public; 
Owner: maddox
--

COMMENT ON COLUMN public.mailings.mailings_contacts IS 'Contact';


--
-- Name: COLUMN mailings.mailings_mailingtypes; Type: COMMENT; Schema: public; 
Owner: maddox
--

COMMENT ON COLUMN public.mailings.mailings_mailingtypes IS 'Mailing Type';


--
-- Name: COLUMN mailings.mailings_fromcompany; Type: COMMENT; Schema: public; 
Owner: maddox
--

COMMENT ON COLUMN public.mailings.mailings_fromcompany IS 'Sender';


--
-- Name: COLUMN mailings.mailings_subject; Type: COMMENT; Schema: public; 
Owner: maddox
--

COMMENT ON COLUMN public.mailings.mailings_subject IS 'Subject';


--
-- Name: COLUMN mailings.mailings_description; Type: COMMENT; Schema: public; 
Owner: maddox
--

COMMENT ON COLUMN public.mailings.mailings_description IS 'Description';


--
-- Name: COLUMN mailings.mailings_emails; Type: COMMENT; Schema: public; Owner: 
maddox
--

COMMENT ON COLUMN public.mailings.mailings_emails IS 'E-mail ID';


--
-- Name: COLUMN mailings.mailings_mailingstatuses; Type: COMMENT; Schema: 
public; Owner: maddox
--

COMMENT ON COLUMN public.mailings.mailings_mailingstatuses IS 'Status';


--
-- Name: mailings_mailings_id_seq; Type: SEQUENCE; Schema: public; Owner: maddox
--

CREATE SEQUENCE public.mailings_mailings_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE public.mailings_mailings_id_seq OWNER TO maddox;

--
-- Name: mailings_mailings_id_seq; Type: SEQUENCE OWNED BY; Schema: public; 
Owner: maddox
--

ALTER SEQUENCE public.mailings_mailings_id_seq OWNED BY 
public.mailings.mailings_id;


--
-- Name: mailings mailings_id; Type: DEFAULT; Schema: public; Owner: maddox
--

ALTER TABLE ONLY public.mailings ALTER COLUMN mailings_id SET DEFAULT 
nextval('public.mailings_mailings_id_seq'::regclass);


--
-- Name: mailings mailings_pkey; Type: CONSTRAINT; Schema: public; Owner: maddox
--

ALTER TABLE ONLY public.mailings
    ADD CONSTRAINT mailings_pkey PRIMARY KEY (mailings_id);


--
-- Name: mailings mailings_mailings_contacts_fkey; Type: FK CONSTRAINT; Schema: 
public; Owner: maddox
--

ALTER TABLE ONLY public.mailings
    ADD CONSTRAINT mailings_mailings_contacts_fkey FOREIGN KEY 
(mailings_contacts) REFERENCES public.people(people_id);


--
-- Name: mailings mailings_mailings_emails_fkey; Type: FK CONSTRAINT; Schema: 
public; Owner: maddox
--

ALTER TABLE ONLY public.mailings
    ADD CONSTRAINT mailings_mailings_emails_fkey FOREIGN KEY (mailings_emails) 
REFERENCES public.emails(emails_id);


--
-- Name: mailings mailings_mailings_fromcompany_fkey; Type: FK CONSTRAINT; 
Schema: public; Owner: maddox
--

ALTER TABLE ONLY public.mailings
    ADD CONSTRAINT mailings_mailings_fromcompany_fkey FOREIGN KEY 
(mailings_fromcompany) REFERENCES public.accounts(accounts_id);


--
-- Name: mailings mailings_mailings_mailingstatuses_fkey; Type: FK CONSTRAINT; 
Schema: public; Owner: maddox
--

ALTER TABLE ONLY public.mailings
    ADD CONSTRAINT mailings_mailings_mailingstatuses_fkey FOREIGN KEY 
(mailings_mailingstatuses) REFERENCES 
public.mailingstatuses(mailingstatuses_id);


--
-- Name: mailings mailings_mailings_mailingtypes_fkey; Type: FK CONSTRAINT; 
Schema: public; Owner: maddox
--

ALTER TABLE ONLY public.mailings
    ADD CONSTRAINT mailings_mailings_mailingtypes_fkey FOREIGN KEY 
(mailings_mailingtypes) REFERENCES public.mailingtypes(mailingtypes_id);


--
-- Name: TABLE mailings; Type: ACL; Schema: public; Owner: maddox
--

GRANT ALL ON TABLE public.mailings TO PUBLIC;


--
-- PostgreSQL database dump complete
--