.Pirátská strana » Technický odbor

Doplnění.

Samozřejmostí je propojení s identitou a zadávat pouze s přihlášením přes identitu
Vyvíjet systém s vědomím, že se bude výrazně rozšiřovat v budoucnu čekáme, že odmeny týmu (třeba poslance) moci zadávat asistent, pak by to vedoucí týmu (poslanec) schválil, ale až doděláme správu týmů v chobotnici. 1. Fáze má pouze ruční zadávání sám za sebe.

Analyzovat stávající DB strukturu, pokud se nevyplatí ji využít navrhneme novou a zajistíme migraci dat.

Aktualni stav

                                     Table "public.nalodeni_income"
  Column  |          Type          | Collation | Nullable |                   Default                   
----------+------------------------+-----------+----------+---------------------------------------------
 id       | integer                |           | not null | nextval('nalodeni_income_id_seq'::regclass)
 reason   | character varying(100) |           | not null | 
 month    | integer                |           | not null | 
 year     | integer                |           | not null | 
 user_id  | integer                |           | not null | 
 amount   | integer                |           | not null | 
 currency | character varying(10)  |           | not null | 
 brutto   | boolean                |           | not null | 
Indexes:
    "nalodeni_income_pkey" PRIMARY KEY, btree (id)
    "nalodeni_income_month_year_user_id_reason_49dd7030_uniq" UNIQUE CONSTRAINT, btree (month, year, user_id, reason)
    "nalodeni_income_user_id_df342e08" btree (user_id)
Check constraints:
    "nalodeni_income_amount_check" CHECK (amount >= 0)
Foreign-key constraints:
    "nalodeni_income_user_id_df342e08_fk_nalodeni_appuser_id" FOREIGN KEY (user_id) REFERENCES nalodeni_appuser(id) DEFERRABLE INITIALLY DEFERRED

                                    Table "public.nalodeni_incomelink"
 Column  |          Type          | Collation | Nullable |                     Default                     
---------+------------------------+-----------+----------+-------------------------------------------------
 id      | integer                |           | not null | nextval('nalodeni_incomelink_id_seq'::regclass)
 name    | character varying(100) |           | not null | 
 url     | character varying(200) |           | not null | 
 user_id | integer                |           | not null | 
Indexes:
    "nalodeni_incomelink_pkey" PRIMARY KEY, btree (id)
    "nalodeni_incomelink_url_user_id_f1215f4a_uniq" UNIQUE CONSTRAINT, btree (url, user_id)
    "nalodeni_incomelink_user_id_816e9327" btree (user_id)
Foreign-key constraints:
    "nalodeni_incomelink_user_id_816e9327_fk_nalodeni_appuser_id" FOREIGN KEY (user_id) REFERENCES nalodeni_appuser(id) DEFERRABLE INITIALLY DEFERRED

Navrh struktury z draftu (jo, pripravoval jsem to trochu)

create sequence "uid_seq" start 100000;

-- uzivatele
create table "users" (
    "id" integer not null default nextval('uid_seq'),
    "uuid" uuid not null,
    "octid" integer not null,
    "username" text,
    "is_active" bool not null default true,
    "displayname" text,
    "notes" text,
    "properties" text,
    primary key("id"),
    unique("uuid"),
    unique("username")
);

-- asistenti, zastupci, boti
create table "agents" (
    "id" integer not null default nextval('uid_seq'),
    "principal_id" integer not null,
    "agent_id" integer not null,
    "is_active" bool not null default true,
    "function" text,
    primary key("id"),
    foreign key("principal_id") references "users" ("id") on update cascade on delete restrict,
    foreign key("agent_id") references "users" ("id") on update cascade on delete restrict
);

-- verejne funkce
create table "functions" (
    "id" integer not null default nextval('uid_seq'),
    "user_id" integer not null,
    "kind_id" integer not null default 0,
    "is_active" bool not null default true,
    "begin" date,
    "end" date,
    "name" text not null,
    "description" text,
    "url" text,

    "income_reason" text not null,
    "income_amount" numeric not null,
    "income_currency" char(3) not null default 'CZK',
    "income_is_brutto" boolean not null default false,

    "income_autofill" boolean not null default false,
    "income_autoapprove" boolean not null default false,

    "properties" text,
    primary key("id"),
    foreign key("user_id") references "users" ("id") on update cascade on delete restrict
);

-- povereni (zadavat, schvalovat a t.d.)
create table "grants" (
    "id" integer not null default nextval('uid_seq'),
    "agent_id" integer not null,
    "function_id" integer not null,
    "is_active" bool not null default true,
    "grants" text,
    primary key("id"),
    foreign key("agent_id") references "agents" ("id") on update cascade on delete restrict,
    foreign key("function_id") references "functions" ("id") on update cascade on delete cascade
);

-- penizky
create table "profits" (
    "id" integer not null default nextval('uid_seq'),
    "user_id" integer not null,
    "agent_id" integer,
    "function_id" integer,
    "created" timestamp(0) not null default now(),
    "deleted" timestamp(0),

    "year" integer not null,
    "month" integer not null,

    "reason" text not null,
    "amount" numeric not null,
    "currency" char(3) not null default 'CZK',
    "is_brutto" boolean not null default false,
    "is_approved" boolean not null default false,

    "notes" text,
    primary key("id"),
    foreign key("user_id") references "users" ("id") on update cascade on delete restrict,
    foreign key("agent_id") references "agents" ("id") on update cascade on delete restrict,
    foreign key("function_id") references "functions" ("id") on update cascade on delete set null
);

Nový návrh DB struktury zde: https://dbdiagram.io/d/ODMENY-DB-660c39b703593b6b61fd45e0